The star schema: why your model separates facts and dimensions
A data model only works when facts and dimensions are kept apart. What a star schema is, how relationships run, and the mistakes that quietly break a report.
Most reports start from one flat export. One row per sale, with the customer name, the product, the category and the branch all in the same table. That works, until the second question.
In the article on the data model I wrote that the model is the real work. This article is about the shape that model should have. For almost every Power BI model that shape is the same: a star schema.
Two kinds of tables
A star schema has two kinds of tables, and the whole idea is that you keep them apart.
Fact tables hold events: what happened. An invoice line, a payment, a stock count. Many rows, mostly numbers and keys.
Dimension tables hold the things you look through: date, product, customer, branch, channel. Few rows, mostly descriptions.
| Fact table | Dimension table | |
|---|---|---|
| Holds | Events | Descriptions |
| Number of rows | Many, grows every day | Few, grows slowly |
| Columns | Keys and amounts | Names, categories, attributes |
| Example | fct_factuurlijn | dim_product, dim_datum |
| What you do with it | Add up | Filter and group |
The star
In a model I built for a retailer with several channels, the invoice lines sit in the middle, in fct_factuurlijn. Around them sit the dimensions: date, product, customer, channel, branch, account and journal.
Every dimension touches the fact table exactly once. Draw it and you get a centre with points around it. Hence the name.
Why not one flat table
Repetition. In a flat table the product category sits on every line where that product was sold. Rename a category and thousands of rows change. In a dimension it is one row.
Several facts, the same dimensions. In that same model there are four fact tables: invoice lines, analytic lines, bookings with a cash date, and a budget. They share the same date and channel dimensions. One slicer on channel filters all four at once. That only works because the dimension is shared. The budget is a nice example: it lives per month and per channel, a completely different level of detail from an invoice line, and it still hangs off the same dimensions. Where that budget comes from is in the article on budgets.
DAX works the way it was designed. Filters flow from the dimension to the fact. Almost every DAX function assumes that shape. Anyone who calculates on a flat table fights the language. See the lesson on DAX.
Size and speed. Power BI compresses per column. A short key compresses well, a long text repeated a million times does not.
The relationships
A relationship in a star schema is almost always one-to-many: one row in the dimension, many in the fact. One product, many invoice lines.
Two rules make that work. The key in the dimension is unique. And the filter runs in one direction, from the dimension to the fact. A relationship in both directions is the exception, not the default.
The grain
Before you build a fact table, decide what one row is. One invoice line, not one invoice. That is called the grain.
Mixing grains is one of the most common mistakes. Put the invoice total on every invoice line, and anyone who adds up that column counts the total as many times as there are lines.
In that model, invoice lines and bookings are therefore two separate fact tables. The lines have one row per line, the bookings one row per document with the cash date. Two grains, two tables, the same dimensions.
The pitfalls
A key that is not unique. If a customer appears twice in your customer dimension, Power BI refuses the relationship or makes it many-to-many, and rows get counted twice. It is the same problem as the JOIN in the article on SQL.
Keys that are missing. A fact with a product number that does not exist in the product dimension ends up under an empty label in your report. Give every dimension an "Unknown" row, and add a control measure that counts how many facts land there. In that model exactly one invoice line had no branch. It turned out to be a test invoice, and the control measure is how we found it.
Linking facts to each other. A relationship between two fact tables almost always goes wrong. Link them through the dimensions they share.
A snowflake. Product, category and product group as three separate tables in a chain. It looks tidy, but it adds relationships and slows filters down. Fold them into one product dimension.
Several dates. A booking has an invoice date and a cash date. Only one relationship to the calendar can be active. You make the other one inactive and switch it on in DAX where you need it:
Received =
CALCULATE (
SUM ( fct_boeking[kasbedrag] ),
USERELATIONSHIP ( fct_boeking[kasdatum], dim_datum[datum] )
)No real date table. The calendar is a dimension of its own, with every day and no gaps. How that still goes wrong is in the article on empty measures.
How to start
Write down the questions you want to answer. Take "revenue per branch per month". The things you look through, branch and month, are dimensions. The number you add up, revenue, sits in a fact.
Start with one fact table and three dimensions. You add a new fact later, and it plugs straight into the dimensions that already exist.
The claim
A star schema is not a technique for large companies. It is the shape in which a report can answer questions you do not know yet today.