Almost every slow, unwieldy Power BI report I've been asked to fix traces back to the same root cause: the model was built as one wide flat table (or a handful of loosely joined ones) instead of a proper dimensional model. It works fine with a sample dataset. It falls over once real volume and real users show up.
What a flat table actually costs you
A single denormalized table repeats dimension attributes — customer name, region, product category — on every transaction row. That redundancy does three things quietly, in the background, until it doesn't feel quiet anymore:
- It inflates the in-memory model size, since VertiPaq (Power BI's storage engine) compresses columns far less efficiently when the same dimension text is scattered across millions of duplicated rows instead of stored once.
- It makes relationships and filter propagation ambiguous, so DAX measures start needing workarounds instead of clean, reusable logic.
- It makes the model brittle: adding a new dimension attribute means touching a giant query instead of a small table.
The star schema alternative
A star schema separates facts (transactions, events, measures — the numbers) from dimensions (customer, product, date, region — the context). Facts hold foreign keys to dimensions; dimensions hold the descriptive attributes once each. The shape, drawn out, looks like a star: one fact table in the middle, dimension tables radiating out.
The goal isn't elegance for its own sake — it's giving the storage engine a shape it can compress well and giving DAX a shape it can filter through predictably.
What changes in practice
- Model size drops. Dimension text is stored once per dimension table, not once per fact row — often a 60–90% reduction on wide, high-cardinality datasets.
- DAX gets simpler. Filters propagate along well-defined one-to-many relationships instead of relying on fragile bidirectional joins across a flat table.
- Slowly changing dimensions become possible. You can track "the customer's region as of the transaction date" instead of only ever seeing the current value.
When a flat table is genuinely fine
Small, single-purpose reports with a handful of thousand rows and no plans to scale don't need the ceremony of a full warehouse. The line I use: if more than one stakeholder relies on the numbers, or the dataset will grow past a few hundred thousand rows, model it properly from the start. Retrofitting a star schema onto a live, trusted report is far more expensive than building it right the first time.
A practical starting checklist
- Identify your grain — what does one row in the fact table represent?
- Pull out anything descriptive-but-repeating into its own dimension table.
- Build a proper date dimension; don't rely on native date hierarchies for real reporting.
- Keep relationships single-direction and one-to-many wherever possible.
- Measure model size and query times before and after — the improvement is usually dramatic enough to justify the refactor on its own.
If your dashboard is starting to lag as it grows, this is almost always the first thing worth checking — before adding more hardware, more caching, or more DAX patches on top of a structure that was never going to scale.