1.7 — Fact and Dimension Tables, Keys, Reference and Duplicate
PL-300 Domain 1 · Skill 1.7
What You Need to Know
Power BI models perform best and are easiest to use as a star schema: fact tables in the middle, dimension tables around them, each linked by a one-to-many relationship.
| Fact table | Dimension table | |
|---|---|---|
| Holds | Events or measurements: sales lines, orders, readings | Descriptions: product, customer, date, store |
| Columns | Keys to dimensions plus numbers to aggregate | A unique key plus attributes to filter and group by |
| Rows | Many, and growing | Fewer, and fairly stable |
| In visuals | Summarized by measures | Used on axes, in slicers and as filters |
Building a star from a flat table
A single wide export (order lines with product name, category, customer name and city on every row) is split like this:
- Load the export once as a staging query.
- Reference it to create Product: keep only the product columns, then Remove Duplicates on the product key.
- Reference it again for Customer, and so on.
- Reference it once more for Sales: keep the keys and numeric columns, remove the descriptive ones.
- Turn off Enable load for the staging query (lesson 1.8).
Reference vs Duplicate
| Reference | Duplicate | |
|---|---|---|
| Creates | A new query whose source is the output of the original query (Source = Staging) |
A copy of every step of the original |
| When the original changes | Changes flow into the new query | The copy is independent |
| Use for | Several tables derived from one cleaned source, so cleanup is written once | Trying a different approach, or a variant that must not be affected by later edits |
- Deleting the original query breaks every query that references it. View > Query dependencies shows the chain.
- In Power BI Desktop, each referencing query generally evaluates the source again during refresh. Referencing doesn't make refresh read the source only once.
Keys for relationships
- A dimension key must be unique and not blank on the "one" side. Check it with column distribution (distinct = unique = row count).
- Power BI relationships use one column on each side. When a row is identified by several columns (Country + Store number), create a single key by merging the columns, or add an Index column as a surrogate key and merge it into the fact table.
- Prefer integer keys over long text: they compress better and relate faster.
- Make key formats match on both sides (same type, trimmed, same case) before relating.
Key Concept: Split flat data into facts and dimensions by referencing one cleaned staging query, removing duplicates in each dimension, and making sure every dimension has a single, unique key column.
Exam Traps
- Using Duplicate when later cleanup on the staging query must flow to every derived table (use Reference)
- Forgetting Remove Duplicates on a dimension, so the relationship becomes many-to-many
- Expecting a relationship to use two columns (create a combined or surrogate key instead)
- Keeping descriptive text columns in the fact table, which inflates the model
- Leaving the staging query loaded, so its data is stored twice
Check your understanding
Practice Scenario
A cleaned staging query holds a flat export with one row per order line, including ProductKey, ProductName and Category. You need a Product dimension, and later cleanup in the staging query must flow into it. What should you do?
Log in to save your progress.
Sources
- Understand star schema and the importance for Power BI, Microsoft Learn
- Referencing Power Query queries, Microsoft Learn
- Working with duplicate values, Microsoft Learn
- Create and manage relationships in Power BI Desktop, Microsoft Learn