Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
In this module we'll explore how to build dimensional tables that solve the m:m (many to many) linking problem, allowing you to create much more robust models.
There are no related exercise files for this lesson.
Solving m:m Joins via Table De-Aggregation
Creating new tables in order to link tables via one to many relationships.
When to use
Use when you are trying to create relationships, but both tables have many repeating values in the columns you wish to use for the relationship.
Before you start
- Creating the new dimension table
- Create a new query by referencing one of the Staging tables
- Select the column you wish to use for linking
- Right click the column --> Remove Other Columns
- Right click the column --> Remove Duplicates
- Set the data type(s)
- Rename the table with a descriptive name
- Load the new table to the model
Create the relationships
- Link the new table to each of the original (not Staging) tables
- Hide the relationship field on the Many side of the relationship
- Update any visuals that used fields from the column you used for creating the relationships
Hints & tips
- You don’t always need to remove all other columns when creating your new table. If it makes sense to take extra fields, then go back to the finalized table and remove the columns you pulled to the dimensional table
Lesson notes are only available for subscribers.