A relationship represents the association between two entities. This article explains the common concepts, steps to add a relation, and its important properties. For more information, refer to the following articles.
This section describes the steps to add a relationship between the Orders and Customers entities such that many Orders records correspond to zero or one Customers record.
To add a new relationship in the model editor, select an entity for which you want to add a new attribute, and click the Add button in the Relations section.
In the Add Relation Properties, enter the name and description of the new relation.
In the Relations section, select the Condition option and set the target entity by selecting it from the drop-down list. Then add the source column and a target column that appear below.
Click the Add button. A newly added relationship will appear in the list of relations of the data model's entity.
The following section discusses the common concepts related to relations in data models.
Relationship cardinality dictates how relations are generated when you create a logical model, and how they are used to generate underlying SQL JOIN queries when you run reports.
Relation Types- When you add relations between entities, there are rules to determine cardinality. There are four ways that entities can relate:
Adding relation AB to entity A
Adding relation BA to entity B
We can add a relation between the Album and Artist tables using the ArtistID column. Assuming this relation does not exist in the database yet if we add the relation from the Album entity, we get an Album-to-Artist relation with OptionalOne cardinality and an Artist-to-Album relation with OptionalMany cardinality.
The role cardinality is not taken into account when determining what JOIN type to use. The relationship in the data source controls the behavior.
If it accepts null values, it uses outer joins, otherwise, it uses inner joins.
Suppose, the Album table has a foreign key constraint linked with the Artist table, there is a relationship created for it, and it belongs to the Album table. So long as Album.ArtistID does not accept null, then for queries joining the Album table to the Artist table, we use an INNER JOIN, but for queries joining the Artist table to the Album table, we use a LEFT JOIN. However, if Album.ArtistID is nullable, then even in the case of queries joining the Album table to the Artist table, we use a LEFT OUTER JOIN.
Consider relations based on foreign constraints between Playlist, Track and PlaylistTrack tables via PlaylistID and TrackID. The relations belong to the PlaylistTrack table as they refer to Playlist and Track. Assume for this purpose that CollapseInRelations is not set for PlaylistTrack. If you drop Playlist.Name and Track.Name to create a table, then the query tree looks like this: PlaylistTrack > Playlists > Tracks. In both cases, it uses an INNER JOIN, as both relations belong to the table (PlaylistTrack) from which the query pulls.
In the case of the Album and Artist tables, the relation connects an Album to an Artist by the ArtistID. So you can drop the Artist.Name attribute and a "Total Albums" aggregate to create a table. If you drop the Name attribute first, then the query looks like this: Artist > Album. In this case, it uses a LEFT OUTER JOIN because the relation belongs to the table (Album) from which the query pulls.
The following table describes the important properties of a relationship that you can set in the Data Model Designer.