EU procurement: a BI product end to end
Real public tender data, taken the whole way: mismatched source systems, a Data Vault absorbing entity resolution, dimensional marts, and a governed semantic model that publishes its own coverage and integrity alongside the figures. Every measure is defined in writing before any DAX exists, and regression tests pin each one to a known filter context.
Read the case study
Three numbers rendered perfectly and were wrong. Catching them is the work; the pipeline that produced them is commodity. Here is what each defect was, how it surfaced, and what the finished figures still cannot tell you.
A share that could only ever be zero
Framework Share was first measured against certified value — the total that excludes awards failing the data layer's quality rules. Every framework award fails those rules, because ambiguous framework treatment is precisely what makes a value uncertifiable. The measure therefore returned 0% in every filter context, permanently, and rendered as a clean and confident zero.
Measured against the total that includes unverified values, where framework awards are actually present, it reads 45.7%. The defect was not in the DAX. It was in the choice of basis, which is the kind of mistake a syntax check cannot see.
A budget total that added income to spending
The German federal budget file contains both halves of the budget. A budget balances, so summing every row returns roughly double: €993bn against an actual 2022 Bundeshaushalt of €495.8bn — a figure that would look entirely plausible on a card.
The first digit of the Gruppierungsplan code separates them: groups 0–3 are revenue and financing, groups 4–9 are expenditure. Each half now reconciles to €495.79bn for fiscal 2022, matching the published budget. The classification began as a calculated column in the semantic model and has since moved into the warehouse, where it is source-derived and every consumer inherits it rather than re-deriving it.
A ratio that divided one slice by a different slice
Framework Share filters the same column a reader can also slice on. Without KEEPFILTERS, the measure's inner filter replaces the reader's filter instead of narrowing it. Sliced to non-framework awards, it returned 84% — framework value divided by non-framework value. Not an error and not a blank: a plausible percentage that means nothing.
Every ratio in the model now wraps its numerator filter in KEEPFILTERS, and each carries a test asserting correct behaviour when sliced by the column it filters. Most DAX defects are filter-context defects, so the suite pins measures at several contexts rather than only at the grand total.
Definitions before DAX, including the contested ones
Every measure was defined in writing before any DAX existed. If a number is defined only by the code that calculates it, the code becomes the definition, and nobody can say whether it is right — only whether it runs.
The contract also records where more than one defensible definition exists. Single-Bidder Rate is the clear case: 55 of 351 awards do not record how many companies bid. Counting only awards with a known bid count gives 19.9%; counting all awards gives 16.8%. A 3.1-point gap is not rounding, and it changes how a reader reads the finding. The first is certified, the second is published beside it, and neither ever appears without the 84.3% bid-data coverage that limits both.
The join that failed, published as a finding
The original design compared procurement volume against departmental budgets. Only 1 of 104 buyers could be matched to a federal department. The cause is structural rather than tunable: the German buyers appearing in TED are mostly municipalities, regional states, hospitals and utilities, while the federal budget covers federal ministries only. The two sets barely overlap, so better matching cannot connect them.
The comparison was removed from the report. The failed join is reported in its place — two official datasets describing related government activity that cannot be linked at this scope. Doing it properly needs regional and municipal budget data, which is a later phase.