Locked lesson.
About this lesson
Creating new dimensional tables for our sample model
Exercise files
Download this lesson’s related exercise files.
3-06 - Begin.pbix386.2 KB 3-06 - Complete.pbix
400.9 KB
Quick reference
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
Instructions
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]
Sorry, we don’t have a transcript for this lesson yet.
Lesson notes are only available for subscribers.