Star schema gets explained as a best practice, which makes it sound like a style preference. It is not. The Power BI engine is built for a specific data shape, and giving it something else has measurable costs in memory, speed and correctness.
Here is what the shape actually does, with the numbers, and how to move to it without rebuilding everything you have.
Why the Shape Matters to the Engine
Power BI stores data column by column rather than row by row, and compresses each column independently. Compression works by finding repetition: a column with forty distinct values across forty million rows compresses enormously; one with forty million distinct values barely compresses at all.
In a flat table, descriptive attributes repeat on every row. Customer name, region, product category, salesperson — each written out once per transaction. The engine compresses that repetition, but it still has to store and scan far more than it would if each value existed once in a dimension.
The relationship structure also carries meaning the engine uses to plan queries. A well-formed star gives it an unambiguous path from filter to fact. A flat table gives it nothing, so filter logic moves into your measures where it is harder to reason about and easier to get subtly wrong.
Facts and Dimensions, Concretely
A fact table holds events and measurements — things that happened, with numbers attached. A dimension holds descriptive attributes — things that exist, with names attached.
The shape, in practice
FACT: Sales 40,000,000 rows
DateKey int -> Date
CustomerKey int -> Customer
ProductKey int -> Product
StoreKey int -> Store
Quantity int
UnitPrice decimal
DiscountAmount decimal
-> keys and measures only. Nothing descriptive.
DIM: Customer 80,000 rows
CustomerKey int (unique)
CustomerName text
Segment text
Region text
AccountManager text
-> each value stored once, not 40M times.
DIM: Date 3,650 rows
DateKey, Date, Year, Quarter, Month,
MonthName, DayOfWeek, IsWorkingDay, FiscalYear
-> contiguous, no gaps, marked as a date table.The discipline is that nothing descriptive belongs on the fact table. If you find yourself adding a text column to a forty-million-row table, it belongs on a dimension instead.
What It Does to Model Size
The reduction is usually larger than people expect, and it compounds into query speed because less data means less to scan.
Same data, two shapes
FLAT
40,000,000 rows x 60 columns
Names, regions, categories repeated per row
Model size: 1.8 GB
Slowest page: 14.2 s
STAR
Fact 40,000,000 x 12 (keys + measures)
Dims: Customer 80k, Product 12k, Date 3.6k
Model size: 420 MB
Slowest page: 1.9 s
Nothing was removed. The same data, shaped for
the engine instead of for whoever built it first.Snowflake Schema: Usually Don't
A snowflake normalises dimensions further — Product links to Category, Category links to Department, rather than Product carrying both. It is tidier by database normalisation standards and generally worse here.
Every extra hop is another relationship the engine traverses on every query. Dimensions are small; the storage you save by normalising them is negligible, and you pay for it on every single query.
Flatten dimensions, normalise facts. That is the opposite instinct to transactional database design, and it is correct for analytical models.
Reshaping Without Rebuilding Every Report
The blocker is usually that existing reports reference columns on the flat table, so restructuring appears to mean rebuilding everything. It does not have to.
- Build the dimensions alongside the existing table, without removing anything. Nothing breaks yet.
- Create the relationships and set the fact table to hold keys.
- Recreate measures against the new structure, keeping the old ones live in parallel.
- Move one report at a time onto the new measures, validating each against its old output.
- Only once every report is migrated, remove the redundant columns from the fact table.
- Measure the model size before and after. It is the clearest evidence the work was worth doing.
This is slower than a clean rebuild and considerably less disruptive, which usually matters more. Reporting keeps working throughout.
One habit worth adopting during the migration: keep a running note of which reports you have moved and what broke when you moved them. The second and third reports are usually much faster than the first, because the same three or four surprises recur, and having written them down turns a repeated discovery into a checklist.
The Date Dimension Deserves Its Own Attention
Of all the dimensions, the date table is the one most often got wrong, and the errors are quiet rather than loud.
It must be contiguous — every single date between the first and last, with no gaps, including weekends and holidays you do not trade on. Time intelligence functions walk the table sequentially, and a gap does not raise an error. It produces a number, which is worse.
It must cover the full range of every fact table that uses it, extended to the end of the current fiscal year. A date table ending in December while transactions continue into January silently drops those rows from anything using time intelligence.
And it must be marked as a date table in the modelling ribbon. An unmarked date table mostly works, which is precisely the problem — it fails on specific functions in specific contexts rather than obviously.
Checks worth running on any model you inherit
Date Row Count = COUNTROWS( 'Date' )
Expected Days = DATEDIFF( MIN('Date'[Date]), MAX('Date'[Date]), DAY ) + 1
// These two must be equal. If not, you have gaps.
Max Fact Date = MAX( Sales[OrderDate] )
Max Date Table = MAX( 'Date'[Date] )
// The date table must extend at least as far.The Cases That Are Not Textbook
Role-playing dimensions. A sales fact has an order date, a ship date and an invoice date, all pointing at the same date dimension. Power BI allows one active relationship, so the other two need either inactive relationships activated in measures with USERELATIONSHIP, or separate date dimensions. Separate dimensions are usually clearer for report authors, at the cost of a few thousand extra rows.
Facts at different grains. Sales by transaction and budget by month do not belong in one table, and forcing them together produces a model where half the rows are meaningless for half the measures. Two fact tables sharing conformed dimensions is the right answer, and it is what the star schema is designed to support.
Degenerate dimensions. An order number has no attributes of its own but you still want to filter by it. Leaving it on the fact table is correct here — it is an exception to the no-descriptive-columns rule, and a well-known one.
Slowly changing dimensions. When a customer moves region, do historical sales follow them or stay with the old region? Both are defensible and they give different answers. This is a business decision that has to be made before the model is built, because retrofitting it means reprocessing history.
What We Would Do
We do exactly the migration above: build alongside, validate in parallel, move reports incrementally, then clean up. The deliverable includes before-and-after model size and query timings, because that is the evidence the restructuring paid for itself.
And where a flat model is genuinely fine — a small dataset, a one-off report, nothing that will grow — we say so. Restructuring a two-million-row model that performs acceptably is work for its own sake.
If your model is large, slow, or gives different answers in different reports, the shape is usually implicated. See data modeling, or read why dashboards go slow for the related failure modes.