Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
This module shows how to apply de-aggregation to our sample model. The end goal here is to avoid m:m (many to many) joins by creating new dimensional tables that can be linked into the model via 1:m (one to many) relationships.
Download this lesson’s related exercise files. You can download the source data files for the course from the resources section of your Lessons page.Table De-Aggregation - Application.pbix
369.6 KB Table De-Aggregation - Application - Completed.pbix
Table De-Aggregation - Application
Creating new dimensional tables for our sample model.
When to use
This module is a practical example of retro-fitting an existing model with staging queries, as well as creating new tables for one to many joins.
Assume we had a Sales and Budget table where each had a Category column with repeating values
Creating staging queries
- Select the Sales table
- Ensure the last step is “Changed Type” (and set the data types if not)
- Right click the “Changed Type” step -> Extract Previous
- Call the new query Staging-<table name>
- Right click the query in the queries pane (left) and uncheck “Enable Load”
- Repeat for the Budget table
- Move both queries to a new group called “Staging”
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 (Category)
- 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 (Categories)
- Load the new table to the model
Create the relationships
- Link the new (Categories) table to each of the original (not Staging) tables
- Sales[Category] to Categories[Category]
- Budget[Category] to Categories[Category]
- Hide the relationship field on the Many side (Sales and Budget tables)
- Update any visuals that used Sales[Category] or Budget[Category] to use Categories[Category]
Lesson notes are only available for subscribers.