The same data, modelled twice

The advice is so well worn nobody checks it any more. Model your data as a star schema. Do not put everything in one wide table. It will be faster.

I had a good opportunity to test that. The card transactions report is built on a proper star schema — ten tables, 1.68 million rows of card activity, every measure cross-checked against pandas. So I built the same rows a second time, the way most Power BI files are actually built, and measured the two against each other.

The flat model is six times the size. It is barely slower. And it answers one ordinary question wrong by a factor of seventeen.

The flat model is not a strawman

This only means anything if the bad model is one a competent person would actually build, so every decision in it is one I have seen in a production PBIX:

• Every dimension merged onto the fact, because that is what the merge button does.
• The transaction id kept, because someone might want to drill to a row.
• The timestamp kept to the minute, because we might need the time later.
• Keys left as text, because that is the type the merge produced.
• Year, Month and an approved flag added as calculated columns, because in a one-table model that is the obvious place to put them.
• Measures written as SUMX(FILTER(...)), because that reads like the sentence you were asked for: sum the amount where it was approved.

None of those is stupid. Each is defensible on its own. That is exactly why models end up like this.

What it costs: 30.8 MB against 190.4 MB

The star schema holds ninety-six columns across eighteen tables and takes a sixth of the memory of a single table holding thirty-two. More tables, more columns, far less memory — which is only a paradox if you think a model costs what it contains rather than how it is shaped.

Column	Size	Share of model
Transaction ID	91.2 MB	47.9%
Timestamp	58.1 MB	30.5%
Everything else (30 columns)	41.1 MB	21.6%

VertiPaq compresses a column by building a dictionary of its distinct values, so what a column costs tracks its cardinality, not the row count. Transaction ID has 1,682,640 distinct values in 1,682,640 rows — the worst case, one dictionary entry per row, nothing to compress. The timestamp, kept to the minute, has 1,365,222. Every other column in the file has fewer than twenty thousand.

The star schema contains neither. The transaction id was dropped because a row count is a measure, not a column. The timestamp became a date key and an integer hour, which is what the report actually asks for. That single pair of decisions is 149 MB.

What it costs in time: much less than you would think

Here is where I have to disagree with the article I set out to write. I ran ten queries against both models, five times each, cold and warm, and kept the median.

Query	Star, cold	Flat, cold	Star, warm	Flat, warm
Total spend	11.5 ms	11.4 ms	2.9 ms	5.1 ms
Spend by year	11.5 ms	18.6 ms	3.5 ms	5.5 ms
Spend by merchant category	16.5 ms	20.1 ms	7.7 ms	11.1 ms
Distinct cards and clients	12.0 ms	36.6 ms	3.4 ms	5.0 ms
Spend by card × year	880 ms	872 ms	795 ms	835 ms
Active cards by month	30.2 ms	51.7 ms	9.7 ms	12.3 ms

The flat model is slower on most queries. It is also slower by tens of milliseconds, and on the heaviest query in the set the two are indistinguishable. At 1.7 million rows, nobody opening this report would feel the difference.

I think it matters to say that plainly, because the usual version of this article claims a tenfold speed-up and does not show its timings. If the argument for good modelling rests on a speed claim that a reader can disprove on their own laptop in an afternoon, the argument deserves to lose.

The cost is not latency. It is that the model is six times larger than it needs to be, and size is what decides whether it fits inside a capacity, how long it takes to refresh, and how far it can grow before either becomes a crisis. A model that is 190 MB at 1.7 million rows is roughly 11 GB at a hundred million. The star schema is 1.8 GB. That is the difference between a licensing conversation and a rebuild.

And then the one that disagrees

Before comparing any timings I checked that both models actually answer the same questions. Nine of the ten queries return identical values — every year to 2018 agrees to thirteen decimal places, which is the strongest evidence I have that the two models really do hold the same rows.

The tenth is year-on-year spend, and it does not agree.

Year	Star: prior year	Star: YoY	Flat: prior year	Flat: YoY
2017	$7,636,708	−0.4%	$7,636,708	−0.4%
2018	$7,606,151	+0.3%	$7,606,151	+0.3%
2019	$6,347,500	−1.0%	$7,627,793	−17.6%

The star schema has a marked date table, so DATEADD shifts the visible ten months of 2019 back to the same ten months of 2018 and compares like with like. The flat model has no date table — there is only the one table — so year-on-year has to be done the only way left, arithmetic on a year column. It compares ten months of 2019 against twelve months of 2018.

The flat model reports that spending fell 17.6%. It fell 1.0%. Nothing is broken, no error appears, and the number is the kind that ends up in a board pack.

This is the part I did not expect to be writing, and it is the part that matters. The memory is recoverable in an afternoon: drop two columns nobody uses and the flat model falls from 190 MB to 41 MB without changing its shape at all. A number that is wrong by a factor of seventeen, in a report that nobody has any reason to doubt, is not recoverable at all — because nobody is looking for it.

What I would take from it

Model on paper before you model in the Power Query window. Not because the merge button is slow, but because the questions you will be asked in a year — how did this period compare with the same period last year? — need structure that the merge button does not create, and their absence does not announce itself.

And when you argue for that structure, argue for it honestly. Six times the memory is true and provable. A tenfold speed-up, at this scale, is not.

---

How it was measured

Both models were built from the same 160-client panel and measured on the same machine in the same Power BI Desktop session type.

• Storage comes from the engine's own DMVs. Attribute hierarchies arrive as pseudo-tables named H$<table>$<column> and are folded back onto the column they belong to, because they are part of what a column costs and they are largest exactly where the column is widest.
• Cold means the engine's caches were cleared immediately before the query; warm is the same query run straight after. Both matter: a page opened for the first time this morning is cold, the same page ten seconds later is warm.
• Each query ran five times in each state and the median is reported. A mean moves with one background process; a median does not.
• Every query pair was checked for identical results before its timing was used. A performance comparison between two models that disagree measures nothing.
• Differences under 8 ms are treated as the harness rather than the model — every query round-trips through ADOMD, which costs a few milliseconds on its own.

Two costs are missing, and both favour the flat model. Refresh time is not compared, because the flat model reads one 315 MB CSV and the star reads eleven small ones, which would measure the file layout rather than the model. And auto date/time was left enabled in the flat model, but Desktop does not generate its hidden date tables for a TMDL-authored model, so that cost — real in a Desktop-authored PBIX — is absent here.