Power BI Data Modeling Best Practices: A Star Schema Checklist
The modelling rules that prevent wrong totals, slow reports and unmaintainable measures — as a checklist you can run against any semantic model.
Core Engineering Takeaways
- •Model as a star schema: narrow fact tables of events and numbers, surrounded by dimension tables that describe them.
- •Use one-to-many relationships filtering from dimension to fact in a single direction; treat bidirectional filters and many-to-many as exceptions.
- •Mark a dedicated date table and use it for all time intelligence.
- •Prefer measures to calculated columns, remove columns nobody uses, and name everything for the people reading the report.
1. Why the star schema still wins
The Power BI engine (VertiPaq) is designed around the star schema, and so is DAX. A fact table records events — sales lines, transactions, readings — with keys and numbers. Dimension tables describe those events — customer, product, date, store — with the attributes people filter and group by.
Compared with one wide flat table or a copy of the source system's normalised tables, a star schema:
- compresses better, because repeated descriptions live once in a dimension;
- makes filters predictable, because they flow one way, from dimensions to facts;
- keeps DAX simple, because most measures are a sum or count over one fact table.
Microsoft's star schema guidance is the reference; the checklist below is how to apply it.
2. Facts and dimensions
- Keep fact tables narrow. Keys, dates and numeric columns. Move descriptive text into dimensions.
- One grain per fact table. Do not mix order headers and order lines, or daily and monthly budgets, in one table. Different grains get different fact tables that share dimensions.
- Use integer surrogate keys for relationships where you can; they compress and join better than long text keys.
- Flatten snowflakes. Product → Subcategory → Category is easier to use, and usually faster, as one Product dimension.
- Role-playing dimensions. When a fact has several dates (order, ship, due), keep one active relationship to the date table and use
USERELATIONSHIPin the measures that need the others.
3. Relationships
- One-to-many, single direction, from dimension to fact, is the default and should be the large majority.
- Bidirectional filters are an exception. They create ambiguous filter paths, slow queries and surprising totals. If one visual needs it, use
CROSSFILTERinside that measure instead of changing the relationship. - Many-to-many (for example customers and joint accounts) needs a bridge table and careful measures — totals can count a fact once per path.
- No relationships between fact tables. Facts relate through shared dimensions.
- Inactive relationships are fine when a measure activates them deliberately.
4. The date table
- Use one dedicated date table covering whole years, with no gaps, and mark it as a date table.
- Turn off Auto date/time in Power BI Desktop options. It creates a hidden date table for every date column and bloats the model.
- Add the columns people actually use — year, quarter, month name with a sort column, fiscal periods, working-day flags.
- Relate it to the date (not date/time) columns of each fact table. Split date/time columns into a date and, if needed, a time column.
5. Measures, columns and size
- Prefer measures to calculated columns. Measures are computed at query time and take no storage; calculated columns are stored for every row and are refreshed with the model.
- Push calculated columns upstream — to Power Query or the source — when you need them for slicing or relationships.
- Remove columns nobody uses, especially high-cardinality ones: GUIDs, transaction IDs, date/time stamps to the second. They often dominate model size.
- Use explicit measures for every number a report shows, and hide the raw numeric columns, so everyone uses the same definition.
- Organise measures in display folders, or a dedicated measures table, with consistent naming.
6. The review checklist
Run this against any model before it goes to production, and again whenever it changes significantly:
- Every table is clearly a fact or a dimension, and each fact has a single grain.
- Relationships are one-to-many and single-direction, with each exception justified.
- A marked date table exists and Auto date/time is off.
- No fact-to-fact relationships and no unnecessary snowflaking.
- Unused and high-cardinality columns have been removed.
- Every reported number is an explicit measure; raw numeric columns are hidden.
- Tables, columns and measures have business-friendly names and descriptions.
- Technical columns (keys, sort columns) are hidden.
- Security is defined with RLS or OLS, not by hiding pages — see the RLS guide.
- The model is in source control as a PBIP project, so changes are reviewed.
For the problems that come up most often, see 7 common semantic model problems.
How does your model score?
Run the checklist automatically in the browser with the Model Health Snapshot, or get my fixed-scope Health Check.
Get the next Engineering Note
Practical DAX, semantic model and Fabric notes. 1–2 a month, unsubscribe any time.