Concept 4: One-to-Many Relationships Between tables
Concept 4: One-to-Many Relationships Between tables
As noted in Concept 2, primary keys are a common component in data tables. In order to form relationships between tables, dimension tables must have a primary key. In practice, this means that a primary key column is made up of unique values, with no repetition of any one value. In the screenshot below, the Geography dimension table is connected to the Sales table via the Country columns in both tables. On the geography side of the line is a numeral 1, which indicates that there are no repetitions of country name in the table. The Sales end of the line has an asterisk, which shows that country names appear multiple times in the country column
A one-to-many relationship as seen in Power BI Desktop
A screenshot of the Geography dimension table, showing that the country column contains only unique values
The Sales table, where each dimension column (Segment, Pricing Level, Product, Country and Period) contains repeated values, forming the Many side of the One-to-Many relationship.
Each one of the dimension columns in the Sales Table (Segment, Pricing Level, Product, Country, Period) will have a relationship with a corresponding dimension table, meaning there will be a total of 6 tables in the data model. In this example, each dimension column in the Sales Table is linked to a dimension table. A commonly asked question is whether it’s necessary to have a dimension table for each column in the fact table. The answer depends on what’s needed. If it’s necessary to connection different fact tables by the attribute in question, then a dimension table is necessary. Secondly, if more information about the attribute is useful, such as looking at quarters or years instead of just periods, then a dimension table should be used.
The next chapter in this series will introduce calculations and formula writing in Power BI.
Comments