The same data, modelled twice
1,682,640 transactions, loaded two ways: as the star schema in the card transactions build, and as one flat table with everything merged in — the way most Power BI files are actually built. Both hold identical rows. Nine of the ten benchmark queries return identical numbers. The tenth is where it gets interesting.
Six times the memory, for fewer columns
Two columns are 78% of the flat model
VertiPaq compresses a column by building a dictionary of its distinct values, so what a column costs tracks its cardinality, not the row count. The transaction id has one distinct value per row — the worst case, nothing to compress. The timestamp, kept to the minute, has 1,365,222. Every other column in the file has fewer than 20,000. Neither has ever been on a visual.
It is barely slower, and that matters
| Query | Star, cold | Flat, cold | Star, warm | Flat, warm |
|---|
Median of five runs each. Cold means the engine’s caches were cleared immediately before the query; warm is the same query straight after. Differences under 8 ms are the harness — every query round-trips through ADOMD, which costs a few milliseconds on its own.
And then the one that disagrees
Nine queries return identical values from both models — every year to 2018 agrees to thirteen decimal places. Year-on-year spend does not, because 2019 is a partial year: the data stops on 31 October.
| Year | Spend | Star: prior year | Star: YoY | Flat: prior year | Flat: YoY |
|---|
What this does and does not show
-
The flat model is not a strawman. Every decision in it is one I have
seen in a production PBIX, and each is defensible alone: merge the dimensions in
because that is what the merge button does; keep the id for drill-through; keep the
timestamp because we might need the time; leave the keys as the text the merge
produced; put Year and the approved flag in calculated columns; write
SUMX(FILTER(...))because it reads like the sentence you were asked for. None of them is stupid. Together they cost six times the memory and one materially wrong number. - Speed is not the argument, and I am not going to pretend it is. The usual version of this piece claims a tenfold speed-up and does not show its timings. At 1.7 million rows the flat model is slower on most queries by tens of milliseconds, and on the heaviest query in the set the two are indistinguishable. Nobody would feel the difference. The cost is size, and size is what decides whether a model fits its capacity, how long it refreshes, and how far it can grow before any of that becomes a crisis.
- Refresh time is deliberately not compared. The flat model reads one 315 MB CSV and the star reads eleven small ones, so a refresh comparison would measure the file layout rather than the model.
- Two costs are missing, and both favour the flat model. Auto date/time was left on in it, but Desktop does not generate the hidden date tables for a TMDL-authored model, so that cost — real in a Desktop-authored PBIX — is absent here. And the engine build in Desktop 2.157 does not expose the hierarchy DMV directly; the attribute hierarchies were recovered from the segment data instead, which is why they can be shown at all.
- The memory is recoverable in an afternoon. Dropping the two columns nobody uses takes the flat model from 190 MB to 41 MB without touching the shape of it. The wrong year-on-year figure is not recoverable that way — it needs a date table, which means it needs a dimension, which means it needs the model it did not have.