Solving m2m Joins via De-Aggregation

Solving m2m Joins via 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

Instructions

Before you start:

• Ensure that you have separated your query setup to make use of staging queries

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.