Data Modeling and the Star Schema
A report built on one huge flat table works at first and then becomes slow and hard to maintain. Power BI is designed for a star schema.
Facts and dimensions
| Fact table | Dimension table | |
|---|---|---|
| Contains | Events or transactions | Descriptions of the things involved |
| Examples | Sales, orders, payments | Product, Customer, Date, Store |
| Shape | Many rows, mostly numbers and keys | Fewer rows, descriptive columns |
| Used for | Summarising | Filtering and grouping |
In a star schema, a fact table sits in the middle and each dimension table connects to it directly, which gives the shape its name.
A Sales fact table might hold OrderID, DateKey, ProductKey, CustomerKey, Quantity and Amount. The Product dimension holds ProductKey, Name, Category and Brand.
Relationships
A relationship connects a column in one table to a column in another. Open Model view to see and edit them. Drag a key column from one table onto the matching column in the other to create one.
Each relationship has two properties that matter.
Cardinality. The usual kind is one-to-many: one product appears in many sales rows. The “one” side is the dimension and must have unique key values. Many-to-many relationships are possible but are a sign that the model needs rethinking.
Cross-filter direction. With the default single direction, filters flow from the dimension to the fact table. Selecting a category filters the sales. This is what you want in almost every case. Setting both directions can produce ambiguous results and slower reports.
Why the star works
When a visual shows Amount by Category, Power BI filters Product to each category, follows the relationship to the matching Sales rows and adds them up. Every dimension can slice every measure, with no lookup formulas.
The date table
Time-based calculations need a dedicated date table with one row for every day and no gaps. Create one with DAX on the Modeling tab, using New table:
Date = CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2026, 12, 31 ) )Add columns for Year, Month and Quarter, then choose Table tools > Mark as date table and select the date column. Relate Date[Date] to the date column in the fact table.
To make month names sort in calendar order, select the month name column and use Sort by column with a month number column.
Good modelling habits
- Remove columns the report does not need.
- Hide key columns from Report view so readers only see useful fields.
- Give tables and columns business names.
- Keep text descriptions in dimensions, not in the fact table.
- Avoid relationships between two fact tables. Connect each to shared dimensions instead.
Practice
Sketch a star schema for a school: what is the fact table if the event is “a student receives a grade in a course”, and what are its dimensions?