Lesson 3 of 11 · 10 min · Modelling data
Data modelling: facts, dimensions, star schema and granularity
What you will learn
- What fact tables and dimension tables are
- Why a star schema is the best shape for Power BI models
- Choose the granularity (grain) of a fact table
- Build a date table
- Turn one wide, flat table into a star schema
In simple words
Picture a receipt book. Every line on a receipt is something that happened: a sale. Those lines go in one long table. Information about the things involved, like customer details, product details and calendar dates, sits in small separate lists. The long table of events is the fact table. The small lists are dimension tables. Arranged with the facts in the middle and the lists around them, it looks like a star, which is why this layout is called a star schema.
Words to know
- Fact table: a table of events or measurements, such as sales, with numbers you add up.
- Dimension table: a table that describes things, such as customers, products or dates.
- Star schema: one fact table in the centre joined to several dimension tables.
- Granularity (grain): what one row of a table represents.
Facts and dimensions
| Fact table | Dimension table | |
|---|---|---|
| Holds | events or measurements | descriptions of things |
| Examples | sales lines, web visits, support tickets, payments | customers, products, stores, dates, employees |
| Rows | many (millions) | relatively few |
| Columns | keys to dimensions, plus numbers (quantity, amount) | a unique key, plus descriptive text (name, city, category) |
| Used for | the numbers you add up in visuals | the groupings and filters in visuals (rows, slicers) |
A sales fact table might look like this (one row per order line):
Sales: OrderLineID | OrderDate | CustomerKey | ProductKey | Quantity | Amount
and the dimensions:
Customer: CustomerKey | Name | City | Segment
Product: ProductKey | Name | Category | Brand
Date: Date | Year | Quarter | Month | Month Number | Weekday
A rule of thumb: numbers you sum belong in facts; text you group or filter by belongs in dimensions.
The star schema
Connect each dimension to the fact table by its key, so the model looks like a star:
Customer
|
Product — Sales — Date
|
(Store)
Dimensions relate to the fact table with one-to-many relationships (one customer, many sales), and a filter on a dimension flows down to the fact. Microsoft's guidance is to build a star schema for Power BI because:
- Visuals are easy to build: slice any measure by any dimension.
- DAX is simpler and performance is better: the engine compresses and filters narrow dimension tables efficiently.
- Results are predictable: there are no ambiguous filter paths between tables.
- It is easy to extend: a new dimension is one more point on the star.
A snowflake schema normalises dimensions further (Product relates to Category, which relates to Department). It works but adds relationships and slows interaction; in Power BI it is usually better to flatten the snowflake back into one dimension table in Power Query.
Avoid one big flat table that mixes everything (customer name repeated on every sales row). It is fine for small, quick reports, but it duplicates data, makes it hard to add a second fact (budget, returns) and encourages measures that double-count.
Granularity (the grain)
The grain is what one row of a fact table represents. Decide it first and write it as a sentence:
- "One row per order line" (the most detail).
- "One row per order."
- "One row per product per store per day" (a snapshot).
- "One row per day per campaign."
Lower the grain only if you can never need the detail; you can always summarise a detailed table up, but you cannot split a summary down. Keep all rows in one fact table at the same grain. Mixing grains in one table (some rows are orders, some are order lines) produces wrong totals.
When two facts have different grains, such as Sales (daily, by product) and Budget (monthly, by category), keep them as separate fact tables and connect both through shared dimensions (a Date table and a Category dimension), at the level where they match. Be careful when you compare them: a budget at monthly grain cannot be filtered by day without special DAX.
The date table
Time intelligence (the DAX lessons) needs a proper Date table: one row for every day with no gaps, covering the full range of your data, plus columns for year, quarter, month, month name, month number, week and weekday. Build it in Power Query or DAX:
Date =
ADDCOLUMNS (
CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2026, 12, 31 ) ),
"Year", YEAR ( [Date] ),
"Month Number", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "MMM" ),
"Quarter", "Q" & QUARTER ( [Date] ),
"Year-Month", FORMAT ( [Date], "YYYY-MM" )
)
Then Mark as date table (Table tools) using the Date column, sort Month by Month Number (Column tools > Sort by column) so months appear in calendar order, and relate Date[Date] to every date column you use in facts. Do not use the auto-generated hidden date tables for serious work; turn off Auto date/time in Options for consistent behaviour and a smaller file. For a financial year starting in April, add Fiscal Year and Fiscal Quarter columns.
Reshaping a flat table into a star
Suppose you imported one wide table AllSales with order, customer, product and date details on every row. In Power Query:
- Reference the query (right-click > Reference) to make
Customer: keepCustomerID, Name, City, Segment; Remove Duplicates onCustomerID. - Reference again to make
Product: keep ProductID, Name, Category, Brand; remove duplicates on ProductID. - In the original (now
Sales), remove the descriptive columns, keeping the keys and the numeric measures. - Create the
Datetable as above. - In Model view, check relationships: Customer → Sales, Product → Sales, Date → Sales.
- Hide key columns in the fact table and the technical columns, so report authors see only useful fields.
If duplicates in a dimension key have different values (the same CustomerID with two cities), fix the data first; a dimension key must be unique.
Naming and housekeeping
- Name tables clearly (
Sales,Customer), notSheet1orQuery1. - Put measures in a dedicated measures table or in the fact table, with display folders.
- Hide columns that are only for relationships.
- Set each column's summarization to "Don't summarize" for keys and years (so Power BI does not sum years).
- Set data categories (City, Country) for map visuals.
Common mistakes
- Using one flat table for everything, then double-counting or being unable to add a second fact.
- Mixed grain in a fact table.
- No proper date table, so time intelligence fails.
- Duplicate values in a dimension key.
- Leaving snowflaked dimension chains instead of flattening.
- Summing a numeric dimension column, like a year or ID.
Try it yourself
- Sketch a star schema for an online course business: which table is the fact, which are the dimensions, and what is the grain?
- Take a flat sales table and split it into Sales, Customer and Product in Power Query.
- Create a Date table, mark it as the date table and sort Month by Month Number.
- Write the grain of your fact table in one sentence.
Check yourself
What is the difference between a fact table and a dimension table?
A fact table records events with measurable numbers (many rows); a dimension table describes the entities involved, such as customers or dates, and is used for grouping and filtering.
What is the grain of a fact table?
What a single row represents, such as one order line or one product per store per day.
Why is a star schema recommended for Power BI?
It gives simple, predictable relationships and filters, easier DAX, smaller models and faster visuals compared with flat or snowflaked tables.
Why do you need a marked Date table?
Time-intelligence functions rely on a continuous table of dates, and marking it tells Power BI which table and column to use.
Interview questions
Explain star schema versus snowflake schema.
A star schema has a central fact table joined directly to denormalised dimension tables. A snowflake normalises dimensions into multiple related tables. Star is preferred in Power BI for simplicity and performance.
How would you model sales and budget at different grains?
Keep them as two fact tables and relate both to shared dimensions (Date and Product category) at the level where they match, rather than mixing grains in one table.
What is a surrogate key and why use one?
An artificial, stable identifier for a dimension row, used instead of a business key that may change or repeat, making relationships reliable.
Summary
- Facts hold events and numbers; dimensions describe things and are used to group and filter.
- Build a star schema: one fact table in the centre, dimensions around it, one-to-many relationships.
- Decide and document the grain; keep one grain per fact table.
- Create a proper, marked Date table and tidy names, hidden keys and summarisation.
Going deeper
- Understand star schema and the importance for Power BI: Microsoft's modelling guidance.
- Set and use date tables in Power BI Desktop: marking and building date tables.
- Model relationships in Power BI Desktop: relationships overview.
See how much of this you can explain
A free mock interview asks you new questions on Power BI and dashboards for Data Analyst and scores every answer, with what to fix.