Semantic Layer: One Revenue Definition, Not Three Tableau Workbooks

The CFO asked which Q2 revenue number was real. Tableau workbook A said $14.2M. Workbook B said $13.8M after a filter someone saved as the default. The Power BI dataset said $15.1M because it still counted a cancelled status the warehouse had stopped using in May. All three claimed “revenue.” None of them pointed at the same expression.

I did not need another chart. I needed one metric definition that BI tools could not fork in a calculated field named Revenue (final) (2).

A semantic layer is that definition: name, grain, dimensions it can slice by, and the SQL that produces it. It is not a product pitch. It is how you stop arguing about workbooks.

One name, one SQL

I do not care whether you type this in LookML, MetricFlow, a dbt Semantic Layer spec, or a Power BI dataset with locked measures. I care that analysts cannot ship a fourth revenue without changing a reviewed file.

-- The only revenue the scorecards may use.
-- Grain: one row per order_id in fct_orders.
-- Status rule: shipped or completed. Not cancelled.
SELECT
    o.order_id,
    o.order_date,
    o.region,
    o.amount_cents - o.discount_cents AS revenue_cents
FROM gold.fct_orders AS o
WHERE o.status IN ('shipped', 'completed')
  AND o.is_test = FALSE;

Workbook calculations that reimplement that CASE are bugs. If finance needs “booked” vs “recognized,” those are two metric names, not a hidden filter.

The tables underneath still need a model. A semantic layer on a swamp of extracts will faithfully serve three swamp definitions. Build gold first, the way star vs one-big-table describes, then bind metrics to those facts.

flowchart TD
  dbt["dbt gold facts"] --> sem["Semantic metrics"]
  sem --> looker["Looker / LookML"]
  sem --> pbi["Power BI dataset"]
  sem --> tab["Tableau published source"]
  wb["Workbook calc fields"] --> fork["Three revenues"]
  extract["Stale extract"] --> lie["Fresh UI, old numbers"]

Tools consume the metric. They do not each get a private CASE statement. Extracts still have a clock.

Extract lag is part of the metric

Tableau extracts and some Power BI import modes are batch copies. A dashboard that refreshes instantly from an extract that ran last night is advertising a freshness it does not have. I put the extract timestamp on the canvas and in the metric’s SLA: “revenue as of 06:00 UTC warehouse build,” not “live.”

Live query to the warehouse costs more and stays honest. Import mode is fine when the SLA says daily. Mixing them across three tools without saying so is how Q2 grew a $1.3M spread.

It does not replace dbt

The semantic layer does not test unique order_id, does not incrementalize fct_orders, and does not own source freshness. Those stay in dbt (or whatever builds gold). I have watched teams buy a metrics product and leave SELECT * extracts underneath. They got governed names on top of untested grain.

Use the layer to stop workbook forks. Use dbt to make the fact true. If you skip the second, the first is a glossary on fire.

I also do not put every ad-hoc column in the semantic layer on day one. Start with the ten metrics leadership already argues about. Grow when a second team needs the same name.

Pitfalls

Calculated fields that shadow a published metric. Ban them on certified sources, or they will win.

A LookML view that is SELECT * from a 200-column extract. You moved the swamp into YAML.

Calling the Power BI dataset the warehouse. It is a serving cache with a refresh schedule.

Ignoring cancelled and test orders in only one tool. That was the $15.1M.

Expecting the semantic layer to fix fan-out joins. It will scale the wrong grain faster.

What this means for your pipelines

I want one revenue expression, reviewed like application code, pointed at a tested fact table. LookML, MetricFlow, and Power BI are how that expression reaches people. They are not three places to reinvent it.

Put extract time on the glass. Keep dbt as the builder of gold. When the CFO asks which number is real, you should be able to open one file, not three workbooks and a Slack thread from May. That file is the semantic layer. The workbooks just render it.