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.

What I write down for each metric

A metric without these five lines will fork again in the next quarter:

1. Name — revenue_recognized, not revenue.
2. Grain — one row per order_id in fct_orders.
3. Status rule — which statuses count, in SQL.
4. Owner — a team, not “analytics.”
5. Freshness — warehouse build time, plus extract lag if the tool imports.

# Example MetricFlow-shaped contract. The file format
# is less important than the five fields staying in git.
metrics:
  - name: revenue_recognized
    description: Shipped or completed orders, test rows excluded.
    type: simple
    label: Recognized revenue
    type_params:
      measure: revenue_cents
    filter: |
      {{ Dimension('order__status') }} in ('shipped', 'completed')
      and {{ Dimension('order__is_test') }} = false

If finance needs booked vs recognized, that is a second name. A hidden workbook filter is how you got $15.1M.

Certified sources in Tableau / Power BI should expose measures, not a 200-column extract with “create calc” still enabled for everyone.

How this sits next to dbt

source  →  dbt staging  →  dbt gold fact  →  semantic metric  →  BI
                tests           unique key         one SQL

dbt owns grain and tests. The layer owns the name the scorecard calls. I will not put LookML sql: that re-joins three staging tables the warehouse already modeled. That is a second transform pipeline you cannot dbt test.

More on that split: dbt testing in production and the BI hub.

FAQ

Do I need Looker if I have dbt Semantic Layer? You need one place the metric is defined. Two semantic products is two forks with nicer YAML.

Can Power BI be the semantic layer? Yes, if the dataset is locked, refreshed on a known schedule, and measures are not copied into fifty reports as local DAX. Governance is the habit, not the logo.

What about Cube / AtScale / vendor metrics stores? Same test: one name, one grain, one SQL, one owner. If the vendor sits on untested extracts, you bought a glossary.

The dashboard is live but the number is yesterday. Extract lag. Print the build timestamp. Or go live-query and pay the warehouse.

Who approves a new metric? The owner of the fact table, in the same PR that adds the YAML. Slack “can we add revenue_2” is how you get three revenues.

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.