2.2Star schema: facts & dimensions
The essentials in 30 seconds
- Facts = what you measure (sales, orders: many rows, amounts); dimensions = by what you analyse (products, customers, dates: labels).
- The star schema places the fact table at the centre and the dimensions around it, linked in 1:* — it is THE architecture rule of Power BI.
- It is not aesthetics: the star makes DAX simpler, performance better and reports more reliable; the single table is an anti-pattern.
Running mini-case — Zineb breaks her single table into a star
Zineb, a management controller in an agri-food SME in Algiers, has piled everything into one big "Sales" table: city, salesperson, product and amounts all mixed together. She applies the rule: she distinguishes the facts (amount, quantity) from the dimensions (city, salesperson, product), then builds the Salespeople dimension in Power Query (duplicate the query, keep the column, remove duplicates) and likewise Cities and Products. In the facts, she keeps only the keys and the measures, links each dimension in 1:* and lays out her star in the Model view. She notes the reflex: define and hold the granularity (one row = one sale, everywhere).
Standalone case for this section: it returns in the course, the diagram, the script and the 48h challenge of section 2.2.
1. Facts & dimensions
The star schema is THE architecture rule of Power BI, hammered home by Microsoft: at the centre, the fact table (the measurable events — sales, orders; many rows, amounts); around it, the dimensions (the analysis axes — products, customers, dates; few rows, labels). Each dimension points to the facts in a one-to-many relationship: the drawing forms a star. Zineb remembers the key sentence: facts = what you measure; dimensions = by what you analyse.
2. Why the star
It is not aesthetics: the star makes DAX simpler, performance better and reports more reliable. Conversely, the single table — everything in one big table — "works" at first, then DAX and performance degrade. Zineb invests an hour of modelling that will save her days.
3. Building the dimensions
A dimension has one row per element, without duplicates; Power Query builds them in three clicks (duplicate the query, keep the column, remove duplicates). We also define the granularity of the facts — one row = one sale, everywhere — and never mix it. Zineb keeps only the keys to the dimensions and the measures in the facts.
🔎 Concrete example — 🔎 Concrete example — Creating a dimension
Zineb extracts the Salespeople dimension in three clicks:
In Power Query, from Sales:
duplicate the query → keep Salesperson → Remove duplicates
→ Salespeople dimension (one row per salesperson)
Facts: keep only the keys + measures; link in 1:*.Reading: a dimension = one row per element (one salesperson once), without duplicates.
Lesson: we build dimensions in Power Query, we do not enter them by hand.
Section vocabulary
- Fact table
- The events to measure: one row = one transaction, with the keys to the dimensions and the numeric values.
Simple example: Zineb keeps the Sales table with the amounts and the keys.
What it is not: a reference table of labels without events.
Reflex: many rows + amounts → facts.
- Dimension table
- An analysis axis: one row = one unique element, with its descriptive attributes.
Simple example: Zineb builds the Products, Cities and Salespeople dimensions.
What it is not: repeated events with amounts.
Reflex: labels + one row per element → dimension.
- Star schema
- Facts at the centre, dimensions around, 1:* relationships: the reference architecture of Power BI models.
Simple example: Zineb places Sales at the centre and her dimensions around it.
What it is not: everything in a single big table.
Reflex: modelling → think star.
- Granularity
- The level of detail of a fact row (the sale? the order line? the day?).
Simple example: Zineb decides: one row = one sale, everywhere.
What it is not: rows at mixed levels of detail.
Reflex: define the granularity consciously and hold it.
- Single table (anti-pattern)
- Everything in a single big table: it "works" at first, then DAX and performance degrade.
Simple example: Zineb breaks her single table to turn it into a star.
What it is not: facts surrounded by linked dimensions.
Reflex: single table = debt to repay quickly.
In practice — In practice — Reshape your sales into a star
- Analyse your sales table: which columns are FACTS (amount, quantity) and which describe DIMENSIONS (city, salesperson, product)?
- In Power Query, create the Salespeople dimension: duplicate the Sales query → keep the Salesperson column → Remove duplicates. Do the same for Cities if relevant.
- In the fact table, keep only the keys and the measures; link each dimension in 1:*.
- Model view: lay out the facts at the centre, the dimensions around — your first star. Check that a visual Salesperson × Amount still works.
Step-by-step script — build a star like Zineb (10 min)
1) List your columns and classify them: amounts/quantities = facts, labels = dimensions. 2) Duplicate the Sales query in Power Query, keep the Salesperson column, remove duplicates: the Salespeople dimension is created. 3) Repeat for Cities and Products. 4) In Sales, keep only the keys and the measures. 5) Model view: facts at the centre, dimensions around, each link in 1:*, then check a visual Salesperson × Amount.
A dimension must have one row per element: if a duplicate remains, the 1:* will be refused.
Copy-paste template — my star plan
Facts (what you measure): ___ (e.g. Sales: amount, quantity — keys + measures)
Dimensions (by what you analyse): ___ (Products, Cities, Salespeople, Dates)
Fact granularity: one row = ___ (e.g. one sale), everywhere
Each dimension → facts in 1:*
Star: facts at the centre, dimensions around. Anti-pattern: the single table.Extract Salespeople
Zineb duplicates Sales, keeps Salesperson and removes duplicates: a clean dimension in three clicks.
Extract Cities
Zineb repeats the extraction for Cities: each analysis axis becomes a distinct dimension.
Break the single table
A single big table mixed everything up: Zineb separates it into facts + dimensions for simple DAX.
Hold the granularity
Zineb sets one row = one sale and sticks to it: running totals and ratios stay reliable.
✓ Best practices
- Think facts = what you measure, dimensions = by what you analyse.
- Build the dimensions in Power Query (duplicate → keep the column → remove duplicates).
- Define and hold the fact granularity (one row = one sale, everywhere).
✗ Common pitfalls
- Staying with a single table (DAX and performance degrade).
- Mixing granularities in the fact table.
- Leaving duplicates in a dimension.
Key takeaways
- Facts = what you measure; dimensions = by what you analyse.
- The star is not optional: simple DAX, performance and reliability depend on it.
- A dimension = one row per element, without duplicates — Power Query builds them in three clicks.
- Define the fact granularity and hold it: one row = one sale, everywhere.
48h challenge — apply it tomorrow
Reshape one of Zineb's tables into a star: classify its columns into facts and dimensions, create at least one dimension in Power Query (duplicate → keep the column → remove duplicates) and lay out facts at the centre and dimensions around in the Model view.
- I distinguish a fact table from a dimension table.
- I build a dimension in Power Query without duplicates.
- I lay out facts and dimensions as a star with 1:* relationships.
Frequently asked questions
My single table works very well: why change?
It works up to the first sophisticated calculations (time intelligence, ratios by dimension) and the first millions of rows. A single table complicates DAX, degrades performance and weakens reports (risk of double counting). The star costs an hour now and saves days later — the unanimous advice of practitioners and Microsoft.
Where does the date table go in this schema?
It is a dimension in its own right — so important that it has its own section (2.5): linked to the facts by the date (Dates[Date] → Sales[Date] in 1:*), it feeds all temporal analysis. It must be continuous (one row per day), officially marked and unique for the whole model.
What is the star schema?