Modeling For Consumption: The BI Handoff

Chapters 1 through 23 made a model correct and made it survive production load. This chapter is about the moment after that: someone opens a BI tool, drags three fields onto a canvas, and gets a number. They never see your DDL, never read your SCD policy, and never open the query they just ran.

There are three parties in that moment, not two: your model, the BI tool’s semantic or metadata layer sitting between you and them, and the person dragging the fields. Naming the middle party matters — it turns "the reader misused the data" from an excuse into a design failure. Your model does not answer the question. It constrains the set of SQL the tool is allowed to generate on your behalf.

Meet Marchwood Plant & Tool Hire: a group with a handful of depots, hire contracts, delivery and collection legs, and kit brought in from other suppliers when a depot is short (cross-hire). Every widget in this chapter runs on Marchwood, and every number in it is one you can check by hand.

The question this chapter keeps asking, from nine angles, is the same one: what could a consumer do to this model that you did not authorise — and which of those can you make impossible, rather than merely discouraged?

semantic layer

The layer between a model and a BI tool that turns a drag-and-drop click into SQL — the umbrella term used throughout this chapter.

query generator

The part of a BI tool that compiles a report’s fields and filters into the SQL it actually runs against the warehouse.

consumption boundary

The point where your model stops being read by people who know its rules and starts being read by a tool that only knows its structure.

A BI tool meets your model in exactly one of three shapes. First: a star with the joins declared inside the tool’s own semantic model — it knows fact_hire_charges joins to dim_contract on contract_id, and it will never invent a different path. Second: one big table, built by resolving every join and every grain decision before the table is queried — "pre-joined" and "One Big Table" are the same idea wearing two names, not two options. Third: a raw star exposed with no declared joins at all, where every report author invents their own path through dim_contract.

The first is the answer. Microsoft’s own Power BI guidance recommends it as the default over a single flattened table — citing model size, the ambiguity of an implicit SUM, and how a relationship propagates a filter — with a single table framed as an acceptable exception, not the rule.

The third shape is where a chasm trap lives: join two independent facts at different grains through a dimension they both share, and every measure fans out by the other fact’s row count — fact_hire_charges and fact_delivery_legs, chained through the dim_contract they both hang off, is exactly this shape. A fan trap is the same failure from a different direction — a sequential header-to-line chain, a coarse row joined to its own finer children (a contract to its own charge lines) without aggregating first, and the coarse measure repeats once per child. Neither throws an error. Both multiply an additive measure into a plausible-looking, wrong number. Run Contract C-101 through all three shapes below and watch the same join return £4,080, £4,080, or £8,160 depending only on which shape it is.

Chapter 14 already covers One Big Table as a serving materialisation — recap in one line: an OBT that flattens a single star keeps the grain; the failure is when it quietly becomes a union of more than one business process and the first shared measure double-counts. This chapter is about the boundary where a tool, not a person, decides which of the three shapes it is looking at.

fan trap

Joining a header table to its own line table — a sequential one-to-many chain — without aggregating first, so the header’s measure is duplicated once for every line row.

chasm trap

Joining two independent facts, at different grains, through a dimension they share — so a measure is duplicated once per row on the other side of the join. Contract C-101 below is this shape, not a fan trap.

symmetric aggregate

A query technique some engines support that computes a correct sum across a fan-out join by keying the aggregation to the row's own primary key instead of the join result.

# semantic model — pseudo-config, not any one vendor's syntax
fields:
  hire_charge_amount:
    label: "Hire charge (£)"       # business name, not the column name
    visible: true
  contract_id:
    visible: false                 # surrogate/technical key, never dragged in
measures:
  total_hire_charge:
    field: hire_charge_amount
    aggregation: sum                # declared once, not re-guessed per report

A metric definition has exactly four possible homes — a materialised column on the fact table, a rollup in a mart model, a definition in the semantic layer, or a calculation local to one BI tool. Each is a different answer to the same question: how expensive is it to change your mind later, and who has to inherit the change? The comparison table below spells out all four; the one distinction worth stating in prose is the semantic layer’s asterisk.

A semantic-layer definition can be changed in one place, with every consumer inheriting it on their next query — but only if the layer compiles that definition to SQL at query time. One that instead precomputes and stores a value behaves like a materialised column wearing a nicer name, with all of a materialised column’s cost. Before routing a number anywhere, work down this ladder, in order, before touching a single WHERE clause:

"Four dashboards disagree, therefore there are four definitions" is a false biconditional, and it teaches a debugging habit that fails on the hardest real case. Two dashboards with byte-identical definitions will disagree if one runs a live query and the other reads a 06:00 extract that a 07:00 arrival missed. Diff the definitions first. If they match, the disagreement is a boundary in time, not a difference in meaning — and a semantic layer, which fixes every divergence on the ladder above, is completely blind to this one.

Marchwood asks a simple question every month: what was our revenue? Below are four honest answers from four different people, all computed from the same underlying rows. Work through naming, then routing, then time, in the widget below.

• Which rows count — the filter.
• Which entity gets credited — scope and attribution.
• Which date column, and which definition of "belongs to a period" — time basis.
• Whether a join fanned two grains together.
• Whether the number is shown gross or net of tax, discounts, or an agent-vs-principal split — presentation.
• Which exchange rate, and which day it was struck on — currency/FX, invisible, and it can make two correct definitions differ.
• Whether two dashboards are even reading the same snapshot of the data.

metric definition

The full set of choices — filter, grain, time basis, presentation — that turns a column into a number a dashboard can show.

snapshot divergence

Two dashboards disagreeing not because their definitions differ, but because they read the data at different refresh boundaries.

Every measure has exactly one correct aggregation for a given axis, and a BI tool will pick one whether or not you told it to. Chapter 10 names the vocabulary for time — additive, semi-additive, non-additive — worth one sentence here: semi-additive facts sum safely across every dimension except time, where the correct read is a point-in-time value, an average, or a last value, never a sum.

That trio doesn't cover everything a drag-and-drop tool will happily get wrong. A distinct count is non-summable across slices — count(distinct customer) per depot does not add up to the company total, because the same customer can appear in more than one slice. An average of averages weights every group equally regardless of its size. And a ratio has two honest ways to compute it: sum the numerator and the denominator separately and divide once (ratio-of-aggregates), or compute the ratio per row and average those (average-of-ratios) — the two are rarely equal, and neither looks obviously wrong on its own.

You cannot stop someone from dragging SUM onto a percentage column with documentation. What stops it is enforcement: a declared default aggregation on the measure, a semantic layer that types a ratio as a metric rather than exposing raw numerator and denominator columns a report could sum by accident, and — where the SQL dialect supports it — a query generator using symmetric aggregates so a fan-out join cannot silently double-count in the first place. Work through all three measures in the truth table below — units on hire, distinct customers, utilisation — and watch the tool return a different, wrong number for each without ever raising an error.

semi-additive

Sums safely across every dimension except time, where it needs a point-in-time read, an average, or a last value instead.

non-summable

A measure — most often a distinct count — with no safe axis to add across at all, because the same member can appear in more than one slice.

ratio-of-aggregates

sum(numerator) / sum(denominator), computed once over the whole group — the honest way to average a rate.

-- ratio-of-aggregates: sum the parts, divide once. cast() is used here,
-- not ::numeric, so this reads the same on Postgres, Snowflake or BigQuery.
select sum(units_on_hire) * 100.0 / sum(fleet_size) as utilisation_pct
from depot_day where day = 'Day 2';

-- average-of-ratios: average the per-depot percentages
select avg(cast(units_on_hire as numeric) * 100.0 / fleet_size) as utilisation_pct
from depot_day where day = 'Day 2';

Everything in this section is ordered by how hard it is to notice, not by what causes it — because in production, detectability is the only thing that decides how long a wrong number survives. A duplicate row during a load window is at least sometimes visible, if someone happens to query at the wrong second. A number that silently shrinks, or a report that gives a different answer in August than it gave in April for the exact same month, is not.

"Current" sounds like a fact about an asset. It is really a join decision, and Chapter 11 already covers the mechanics of how a Type 2 dimension is built — this chapter is about what happens when a BI tool’s query generator makes that join decision for someone who never thought about it. The six ways it goes wrong below all come from the same root: is_current is a property of the dimension row at query time, not a property of the fact, and two configurations that look identical in a drag-and-drop UI resolve it differently.

A new dimension member is the other side of the same coin. Marchwood opening a fourth depot is a completely ordinary business event, and it is enough to silently break a hardcoded filter, an access rule, a colour palette, an axis range, and a pivoted extract — all without a single failed job.

as-was vs as-is

Kimball's terms for a Type 2 join resolved at the fact's own event date (as-was) versus resolved to today's current row regardless of when the fact happened (as-is).

fails closed / fails open

An access rule keyed to an enumerated allow-list silently excludes a new member (fails closed); one keyed to a deny-list silently includes it (fails open).

"Put RLS in the model, not the dashboard" is not unconditionally true. The honest rule is narrower: define the policy once, and enforce it at the lowest layer that can actually see the end user’s identity. A warehouse can enforce row-level security only if it knows who is asking — with per-user credentials or identity passthrough, it can; behind a single shared service-account connection, every query runs as the same principal, and warehouse-level RLS collapses to all-or-nothing. Enforcement then has to move up, into the BI tool’s own semantic model.

Even where RLS works, it has real, tool-specific costs. Power BI's RLS restricts anyone without edit permission on the semantic model — an Admin, Member or Contributor holds edit permission and bypasses it entirely, seeing every row regardless of the rule. A Viewer does not hold edit permission and is restricted by RLS, and that includes a Viewer granted Build permission: Build is enough to explore the model or open it in Excel, but it is not edit permission, so RLS still applies — the boundary is edit access, not the Viewer/Build distinction it is easy to assume. Snowflake's row access policies need Enterprise Edition or higher. BigQuery's row-level access policies work correctly, but disable BI Engine acceleration on any table they're applied to — a securely filtered query runs at standard speed, not the accelerated one.

An extract is not just a cache — it is a new security boundary. Whatever rows the extracting identity could see get materialised, and everyone who opens that extract sees exactly those rows, with the warehouse's own RLS completely out of the picture from that point on.

RLS on rows does not imply privacy on aggregates. A filter that legitimately restricts someone to their own rows can still resolve to a group of one — an average, a max, or a count over a single-row group re-identifies that value, with no row-level rule broken anywhere. Use the router below to test every combination of connection identity and tool against Marchwood’s own entitlements.

connection identity

Whether a BI tool's connection to the warehouse carries the actual signed-in user's identity (per-user credentials, OAuth passthrough) or one shared identity for everyone (a service account).

aggregate disclosure

An aggregate query resolving to a group small enough — often one — that it reveals an individual row's value even though every row-level rule was respected.

Chapter 21 owns the producer-consumer contract handshake generically — compatibility modes, expand-and-contract, and the rule that a "zero reads" figure is a signal to investigate, not a calendar date to act on. This chapter’s angle is narrower and harder: you cannot expand-and-contract a human. Chapter 21’s pattern assumes a consumer with a deploy pipeline who can absorb a deprecation window; a person who built a dashboard eighteen months ago and moved teams has no such pipeline, and the contract has to be enforced by the shape of what you expose to them, not by a notice they will never read.

Deprecation telemetry lies by omission. Zero interactive queries against a dataset does not mean zero consumers — a scheduled email subscription, an embedded tile refreshed by a service account, and a downstream extract all read the data without ever registering as a user query against it. The headline metric answers a narrower question than the one you are actually asking. Reveal each evidence source in the panel below and watch the true consumer count climb while the headline "0 reads" figure never moves.

Certification is not a verb — it is Microsoft’s specific ladder (Promoted, by any user with write permission; Certified, by admin-designated reviewers; Master data, for core organisational data). Teach the underlying concept instead: a durable, visible, revocable claim about fitness for use, made by a named party. Tableau’s own data-labels model makes the revocation half concrete — its four data-quality warning types are Warning, Deprecated, Stale data, and Under maintenance (Sensitive Data moved out to become a separate sensitivity label in 2023). A Deprecated warning anyone can see and act on does more real governance work than a Certified badge nobody has revisited since the person who granted it left.

The only real lever is rebuilding a dashboard for its owner, and knowing which few of the forty are actually worth rebuilding. A dashboard’s "built on" relationship counts everything that ever referenced a dataset; an activity log of who actually opened it in the last quarter is a completely different, much shorter list.

deprecation telemetry

Query-log evidence of who reads a dataset — reliable for interactive BI-tool queries, and silent about subscriptions, embedded tiles and downstream extracts.

endorsement

A durable, visible, revocable claim about a dataset's fitness for use, made by a named party — the concept behind vendor-specific ladders like Promoted/Certified/Master data.

"Talk to your stakeholders" is not a plan — it produces nothing you can point to when a number is questioned six months later. What you need instead is a consumption contract: a written, specific answer to what grain a dataset is at, which of several defensible filters and scopes it uses, what time basis it recognises against, what currency and FX convention it applies, how often it refreshes and what "stale" means for it, who owns it, and what happens the day it needs to change.

This is not the same document as Chapter 21’s data contract. That one is a producer-consumer handshake about schema and compatibility, aimed at a consumer with a pipeline and a deploy process. A consumption contract is aimed at the person dragging fields onto a canvas — it has to answer questions in business language, before the dashboard exists, not in a schema registry the reader will never open.

Write it down before the first dashboard is built, not after the third one disagrees with the second. The definitions this chapter has spent eight sections adjudicating — where a number lives, which aggregation is safe, who is allowed to see which rows, when a dataset is safe to retire — are exactly the fields a consumption contract has to answer explicitly, in one place, for a person who will never read your model directly.

consumption contract

A written, business-language agreement about a dataset's grain, scope, time basis, currency, refresh and ownership — aimed at the person consuming it through a BI tool, not at a downstream pipeline.

refresh boundary

The point at which a dataset’s snapshot last updated — the thing two identically-defined dashboards can disagree across.

# consumption_contract.yml
dataset: mart_depot_hire_output
grain: one row per depot per day
definition:
  measure: depot_hire_output_gbp
  scope: "hire charge only — excludes delivery, waivers, consumables, cross-hire"
  time_basis: "booked on hire start day, not accrued"
  currency: "GBP, no FX conversion (single-currency operation)"
refresh:
  schedule: "nightly, 02:00 UTC"
  staleness_sla: "6 hours"
owner: fleet-ops-data@marchwood
access: "row-level, by depot — see rls-enforcement-router"
endorsement:
  status: promoted
  reviewed_by: null      # not yet certified — no reviewer assigned
deprecation_policy: >
  60 days' notice to registered consumers; owner rebuilds the
  top-6-by-opens replacement before removal

This chapter asked one question from nine angles: what can a consumer do to your model that you did not authorise, and which of those can you make impossible rather than merely discouraged? A query generator only has three shapes to work with; a metric has exactly four homes and four different costs of changing your mind; an aggregation is either declared or guessed; "current" is a join decision with six ways to go wrong; a new dimension member breaks five ordinary artifacts silently; a row-level policy is only as strong as the identity behind the query; and a dataset forty dashboards depend on cannot be retired the way an API is deprecated.

None of this is exotic. It is the same discipline as every earlier chapter — name the decision, write it down, and make the wrong answer structurally harder to produce than the right one — applied to a boundary where the person on the other end will never read anything you wrote.

Five design scenarios for this chapter, on the ERD canvas. Each one is graded against a real rubric, seeded with real rows, and each has one fault planted in the data rather than in the diagram.

Build them with the chapter closed. If one goes wrong, come back to the section it belongs to rather than re-reading the whole thing.