A Power BI report you can review

Ask a bank how it controls a change to a payment engine and you will get an answer involving version control, code review, automated tests and a release process. Ask the same bank how it controls a change to the report the board reads, and the answer is usually that someone opened a file, dragged something, and saved it.

That gap is not because BI people are careless. It is because the tooling defaults to a binary. A PBIX cannot be diffed, so it cannot be reviewed; if it cannot be reviewed there is nothing to approve; and if there is nothing to approve, the control is a screenshot in a change ticket.

Power BI has supported a plain-text project format for a while now. Almost nobody uses it as a place to put a build.

What the builds actually are

Every report in the portfolio is produced by four scripts. Nothing in the model or the report layout is hand-authored.

• The ETL reads the source and writes a star schema as CSVs, along with a quality_report.json recording everything it found on the way through — row counts, dropped columns, distinct values, the anomalies.
• The model generator writes TMDL: the tables, columns, relationships and every measure, with the DAX and the documentation for each one sitting together in a Python list you can read top to bottom like a schema.
• The report generator writes PBIR — one JSON file per visual, with page layout expressed as coordinates and helper functions rather than a thousand lines of hand-typed envelope.
• The checks parse the model before Power BI ever opens it, refresh it, query it, and compare every number it produces against the same number computed independently in pandas.

Why generate rather than click

Because a diff becomes possible

When the model is text, a change to a measure is three lines in a pull request instead of a new binary. Someone can disagree with it before it ships. That is the whole of what “auditable” means, and it is unavailable in the default workflow.

Because identity has to be stable

Every object in a TMDL model carries a lineage tag. Generate those randomly and every rebuild churns the whole file, which destroys the diff you just bought. Here each tag is a UUID derived from the object’s own path, so a table called Transactions gets the same tag on every run forever, and a diff only ever shows what actually changed.

Because deleting is harder than adding

PBIR keeps one directory per visual. Rename a visual by hand and the old directory stays on disk — and still renders, as an empty box on the page. The generator wipes the pages folder and rebuilds it every run, then sweeps any directory left without a definition and fails loudly if it cannot. You cannot leave a ghost behind if the ghost is deleted every time.

Because the same decision gets made once

Five pages, sixty-seven visuals, one function that draws a panel. Change the way a chart title is rendered and it changes in all sixty-seven. In the click-and-drag workflow that is sixty-seven opportunities to be slightly inconsistent, and the inconsistency is what makes a report look homemade.

Checking it before Power BI sees it

Malformed TMDL has an unhelpful failure mode: Power BI Desktop opens a blank window with no error dialog at all, which is indistinguishable from a slow load. The first time I hit that I spent fifteen minutes waiting for a file that was never going to open.

So the model is parsed first, with the same serialiser Desktop itself uses, which gives a document and a line number. It takes about a second, and it turns a fifteen-minute mystery into a compiler error.

And then checking the numbers, twice

This is the part that matters most, and it is the part most BI work skips entirely.

The model is the thing being checked, so the check cannot use the model. Every headline measure is recomputed from the source CSVs in pandas — no DAX involved, written independently — and diffed against what the live model returns over its XMLA endpoint. On the card transactions build that is ten measures at four grains: 300 comparisons, and the build fails if any of them disagree.

It has been worth it every time. On the Superstore build it caught a measure counting customers active in every year, which returned 793 when the answer was 293. The DAX used DISTINCTCOUNT on the date table, and a fact table cannot filter its own dimension — so every customer appeared to have been active in every year. On screen it looked completely plausible. Nothing but a second independent calculation was ever going to find it.

What the checks did not catch

I want to be straight about this, because a piece about rigour that claims the rigour worked perfectly is not worth reading.

On the most recent build, two bugs went through all of it — the TMDL parser, the PBIR validator and all 300 cross-checks — and were only visible when I looked at a rendered page.

• Every chart subtitle was silently dropped. A helper took its arguments in the order (title, transparent, subtitle), and fourteen call sites passed (title, subtitle). The caption went into transparent, which is truthy, so the subtitle was never written. The theme repainted the card background anyway, so the pages looked right and half the analysis simply was not there.
• A credit-limit measure returned the whole book on every row. Summing a column on the card table cannot see a filter applied to the client table — filters travel from the one side to the many, so the filter reached the fact and stopped. The table showed an identical figure five times over, and the ratio built on it read 317% for one segment.

Neither is exotic. Both are the kind of thing a reviewer spots in four seconds and an assertion never will, because an assertion can only check the thing you thought to check.

Automate what a machine can verify. Then look at the thing with your own eyes, because some errors are only visible once they are drawn.

What it costs

More up front, obviously. Writing a generator for a five-page report takes longer than building a five-page report. The break-even is not the first build; it is the fourth change request, the first handover, or the first time somebody asks why a number moved.

It also gets cheaper each time. The generators in these builds are largely the same file with a different schema at the top, so the fifth report cost a fraction of the first. That is the ordinary economics of tooling, and it applies to BI exactly as it applies to everything else — we have just been unusually willing to pretend otherwise.

Where I would not do this

A one-off analysis that answers a question and is then thrown away should be built in whatever way is fastest. The argument here is about reports that live for years, get handed between people, and end up in front of a regulator or a board. Those are software, whether or not anybody has decided to treat them that way.