Types of Relationships in Data Model

In comparison to other database management systems, Power BI relationships are associated with directionality. Directionality or cardinality plays a crucial role in filtering data between tables in the data model. While loading data, Power BI Desktop automatically looks for relationships in our data model (this function can, of course, be turned off, which is recommended when working with more complex data models).
There are three types of relationships:
  1. One to one (1:1).
  2. One to many (1:*).
  3. Many to many (*:*).

One-to-One Relationship (1:1)

This relationship describes a connection between two tables where there is a set of unique occurrences in both tables. Creating such a relationship is not recommended, as it indicates redundant information in tables, which may mean that the model has been improperly designed. In this case, it is worth considering merging the two tables.

One-to-Many Relationship (1:*)

The one-to-many relationship describes the dependency between two tables, where Table A contains a set of unique values for Table B. In the context discussed earlier, Table A serves as the dimension table for Table B, or the fact table. Alternatively, we can say that Table A is the dictionary for Table B. An example of such a relationship could be a calendar table, where the calendar contains a set of unique values, and the sales table containing the annual sales history.

Many-to-Many Relationship (*:*)

This relationship describes a case where many values (duplicates) are common between two tables. Ideally, such a relationship is not recommended because the lack of uniqueness introduces ambiguity and can mislead the user. This type of relationship should be used only in well-tested cases and should also be well explained in the technical documentation of the data model. It is crucial to carefully consider this relationship and correctly plan its direction, and consequently, plan filters and measures directly under this relationship.

Filter Directions

The filter direction is marked in the relationship with an arrow. Its tip indicates the dependency between tables, and usually, the arrow will be directed towards the fact table. Filter directions allow filtering data in two variants:
  1. One direction – In this case, only one table from the relationship can be used to filter data. An example would be filtering the sales table with the calendar table. This direction assumes that the sales table cannot filter the calendar table.
  2. Two directions – In this case, tables can be used to mutually filter each other. This way, dimension tables can be filtered using fact tables and vice versa. It is essential to note that two-way filtering significantly impacts performance.
Important! Two-way filtering should not be enabled if its consequences are not understood. Enabling two-way cross-filtering can lead to ambiguity, oversampling, reduced performance, and, most importantly, inconsistent results.

Interesting article? Share further!

Leave A Comment