Foundation05 BI & semantics · 2 of 3

Where the semantics live

What the Databricks layer will not do, and therefore the choice: two credible places to define a measure, and one popular third option that is not a compromise but a defect.

Stand 29.07.2026

#What a metric view will not do

What

Five limits decide whether a metric view can carry your BI layer at all. None is a defect; all of them are things a client discovers in week eight if you do not say them in week two.

Databricks-only

The definition is evaluated by the Databricks SQL engine. Any consumer reading a copy of the data — an Excel extract, a Tableau extract, a Fabric model over a shortcut — is outside the semantic layer for good.

Joins inside the definition are constrained

They are LEFT OUTER with a declared cardinality, many_to_one by default as in gold.mv_sales. Under one_to_many — and only there — an aggregate expression may reference columns from one source only, so a measure multiplying source.quantity by a joined column must be pre-computed in gold.fct_order_line. Do not quote that as a blanket rule: it is not documented for many_to_one. Joined tables cannot carry MAP columns.

The consuming query may not join at all

A metric view cannot be joined to a table or to another metric view; any query that joins one fails. This is the reverse of how it is usually read — joins are allowed in the definition and forbidden in the tool, so whatever a report needs to join gets modelled into the YAML.

Every time grain is explicit

There is no free date hierarchy. month exists because it is written above; quarter and week do not exist until someone writes them. Filtering on month filters the truncated value, not order_ts.

Measures are not columns

You cannot GROUP BY one, put one in a slicer, or reference one without MEASURE(). A measure in a GROUP BY returns METRIC_VIEW_MEASURE_IN_GROUP_BY.

The last limit pays for itself. average_order_value is non-additive — the AOV of Germany plus the AOV of Austria is not the AOV of both. Because it is a measure and not a column, nothing can sum it: the engine re-evaluates SUM(amount_eur) / COUNT(DISTINCT order_id) at whatever grain was asked for. Materialise that figure into a table and the protection is gone.

Why

Stated early, the limits are design inputs. 'Every grain is explicit' sounds like a footnote until a controller asks for a rolling 13-week view and discovers that week was never defined — at which point the semantic layer looks unfinished rather than deliberate.

The Databricks-only limit decides architecture. If the finance team's real reporting surface is an Excel workbook refreshed from an Import model, the metric view can be perfect and not be in the path of a single number anyone reads.

Doing it

Define every time grain the business asks for by name, and refuse to add one that has no named consumer
Model every join a report needs inside the metric view definition — that is where they are legalCheck that no consumer is trying to join the view to something else.
Establish what the actual reporting surface is before committing to metric viewsIf it is an extract, write that into the concept rather than discovering it at UAT.
Benchmark the slowest realistic dashboard query before promising response timesMaterialization is a preview and cannot be assumed.

Embedding it

Give the team the four limits and ask which one bites this client firstThe answer tells you where the risk is, and it comes from them.
Have someone try to sum average_order_value and watch the engine refuseA limit that has been felt is remembered; a limit that was read is not.
When a request needs a grain that does not exist, make the team add the field rather than adding it for themIt is a four-line pull request and it establishes that the semantic layer is theirs to extend.

Defaults

Non-additive figures — ratios, averages, distinct counts — exist only as measures, never as columns in a table
Time grains are declared explicitly and reviewed once a quarter against what reports actually use
Joins belong inside the metric view definition; the consuming query joins nothing

Gotchas

Materialising a ratio into gold.agg_revenue_month so a dashboard is fasterSomeone shows it across four regions and the tile sums four averages: plausible, always wrong, and wrong by more the more rows are on screen.
Assuming a date hierarchy existsA BI tool that offers drill-down on a date column offers nothing on a metric view field called month — there is no year above it unless you defined one.
Removing joins from the definition to 'make it BI-compatible'That is the restriction read backwards, and it costs you the country and segment fields for nothing: METRIC_VIEW_JOIN_NOT_SUPPORTED comes from the query that joins the metric view, not from the view's own joins. The documented remedy is the opposite move — model the join in the metric view definition.
A BI tool joining the metric view without saying soIn Tableau a data-source relationship is the obvious case, but a Top-N filter, a set, and many LOD expressions all compile to a join too — so the workbook fails on a filter nobody thinks of as a join, and the error names a restriction the author never knowingly used.
Quoting materialization in a sizing estimateIt is Public Preview; if it is not enabled on the client's workspace, the dashboard you promised at two seconds runs at twelve.

Depends on

#Metric view, BI model, or both

What

The three-way decision

	UC metric view	Power BI semantic model	Both
Definition lives in	gold.mv_sales — YAML in the repo	DAX, in a model file or workspace	two places
Changed by	a pull request	whoever opens the file	whoever gets there first
Reaches	SQL, notebooks, AI/BI dashboards, Genie, any tool that sends SQL to a warehouse	Power BI reports and Excel pivots on that model	everything, inconsistently
Tested by	a SQL oracle in CI	nothing, in practice	the half that has a test
Cost of a new measure	one PR, live for every consumer at once	one file, live for that model's consumers	twice — and the second one is forgotten
Breaks when	a tool cannot emit MEASURE()	a second tool needs the same number	the two numbers differ and nothing can arbitrate

Both is not the safe middle. It is the only option with no answer to 'which one is right'. Two definitions agree on the day they are written and diverge on the first exception — a returns rule, a change to how cancellations are dated, a new country. Six weeks later finance reports 4.31 M, the AI/BI dashboard says 4.29 M, and nobody knows which is correct because there is no third definition to check them against.

Genie reads the metric view and cannot read DAX

The moment revenue is defined in the BI model, every natural-language question is answered from a different definition than the reports use — fluently, in a sentence, with no SQL for anyone to review.

Power BI's own natural-language surface is mid-migration

The legacy Q&A was announced for deprecation in December 2025 and retires in December 2026, with Copilot as its replacement. A client whose self-service story rests on Q&A visuals is defending a dated dependency — the question is not whether their natural-language layer changes, but which definition it reads once it has.

The one legitimate ‘both’: a passthrough with zero measures

The BI model defines zero measures and exists only for connectivity, not semantics. Write that into the developer concept in those words, because the drift always starts with one harmless local measure.

Why

The choice is usually made by default and downward. Nobody decides, so the first report author defines revenue in DAX because that is the tool in front of them, and by the time anyone asks there are forty measures and a migration nobody will fund — DAX does not translate mechanically into SQL. Deciding explicitly in week two costs one meeting, and it determines whether the warehouse is the source of truth or merely the source of data.