Module 3: Data modelling · Lesson 3.1 · about 15 min
Star schema: facts and dimensions
Why Power BI models are built as a star, with one fact table of events in the middle and dimension tables around it, and how to decide which columns go where.
Module 3 · about 15 minutes · read first: 1.2. You’ll be able to sort columns into facts and dimensions and explain why the star shape matters.
1. Why this matters
The model decides how easy every later step is. A good star makes DAX short and reports fast; a messy model makes even simple measures wrong. “What is a star schema?” is a standard BI interview question.
2. The idea in one picture
One flat table
| Date | Product | Category | Store | Region | Qty | Amount |
|---|---|---|---|---|---|---|
| 2025-03-01 | Desk lamp | Home | Rideau | East | 2 | 79.04 |
| 2025-03-01 | Desk lamp | Home | Queen Street | Central | 1 | 39.52 |
| 2025-03-02 | Desk lamp | Home | Rideau | East | 1 | 39.52 |
| 2025-03-02 | Smart plug | Electronics | Rideau | East | 3 | 93.60 |
Highlighted text repeats on every line: 7,000+ times "Desk lamp", "Home", "Rideau", "East"…
Star schema
| Date | ProductKey | StoreKey | Qty | Amount |
|---|---|---|---|---|
| 2025-03-01 | 5 | 4 | 2 | 79.04 |
| 2025-03-01 | 5 | 3 | 1 | 39.52 |
| 2025-03-02 | 5 | 4 | 1 | 39.52 |
| 2025-03-02 | 9 | 4 | 3 | 93.60 |
Sales keeps only keys and numbers. "Desk lamp, Home" lives once, in Product; "Rideau, East" once, in Store.
3. Facts and dimensions
Fact tables record events at a fixed grain: an order line, a stock count, a website visit, a monthly target. They are long (thousands to billions of rows) and thin: dates, keys and numbers.
Dimension tables describe the things in those events: product, store, customer, date. They are short and wide: one row per thing, with all its descriptive columns (name, category, region, opening date).
The fact table sits in the middle with a relationship to each dimension, which is why it looks like a star:
How to sort a column:
| Ask | If yes, it’s… |
|---|---|
| Is it a number you’d add up or average? (Quantity, Amount, Cost) | a fact column |
| Is it a word or category you’d filter or group by? (Region, Category) | a dimension column |
| Does it describe a thing that appears in many events? (product details) | a dimension column, in that thing’s table |
| Is it a key linking an event to a thing? (ProductKey in Sales) | a foreign key in the fact table |
4. Snowflakes and why to avoid them
A snowflake splits dimensions further: Product → Subcategory → Category tables. It saves a little space but adds relationships, slows filtering and confuses report users. In Power BI, flatten these back into one Product table with Power Query merges (Lesson 2.3). Keep the star.
Principle
Principle (Kimball): model around business processes. Each process (selling, planning, stocking) gets its own fact table at its own grain, and shared dimensions (Date, Store, Product) connect them, so you can compare sales with targets by store and month in one visual.
Try it: a new “Stock” table records units on hand per store per product per day. Which existing dimensions can it share? (Store, Product and Date.)
5. Worked example
A colleague hands you one flat export with 22 columns: Date, Store Name, City, Region, Store Manager, Product, Category, Supplier, Quantity, Price, Cost, … Split it: Date stays (to relate to the Date table); store columns go to a Store dimension with a StoreKey; product columns go to Product with a ProductKey; Sales keeps Date, StoreKey, ProductKey, Quantity, Price, Cost. In Power Query: reference the flat query three times, keep the relevant columns, remove duplicates in each dimension, add an index as the key, and merge the key back into the fact. The model gets smaller and every measure gets simpler.
6. Practice
- Fact or dimension column: Unit cost, Store opening date, Return reason, Units returned?
- Why is Date its own dimension instead of just a column in Sales?
- You need sales and targets on the same chart by region. Which shared dimension makes that work?
Answers
- Unit cost: fact. Store opening date: dimension (Store). Return reason: dimension (it describes the return). Units returned: fact.
- So every fact table can be filtered by the same Year, Month and Quarter columns, and so time intelligence functions work (Lesson 3.3).
- Store (Region lives in Store, and both Sales and Targets relate to it).
Check yourself
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Goods is a made-up shop and its numbers are generated. Power BI changes every month: menu names can move, so check Microsoft Learn if a button is not where the lesson says. The practice editor runs in your browser; nothing you type is sent anywhere.