Table De-Aggregation - Application

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]