Launch Deal · 50% off 1st month ·

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:

  1. Load the export once as a staging query.
  2. Reference it to create Product: keep only the product columns, then Remove Duplicates on the product key.
  3. Reference it again for Customer, and so on.
  4. Reference it once more for Sales: keep the keys and numeric columns, remove the descriptive ones.
  5. 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?

Report issue

Log in to save your progress.