Let me ask you something while the tea cools. Suppose you join a company and in your first week you are handed three dashboards: one from sales, one from finance, one from marketing. Each has a number labelled “March revenue”. They are ₹12,40,000, about ₹10,50,000 and ₹10,00,000. Which one do you quote in the meeting?
The honest answer is: it depends on what each one counted, and nobody wrote that down. That, in small, is the problem a semantic layer exists to solve.
Where three numbers come from
Take the same month, same orders, in an invented online business. Customers were billed ₹12,40,000 in total, and that amount includes 18% GST. During the month, ₹60,000 (again including GST) was refunded.
- The sales dashboard sums the invoice amounts as billed: ₹12,40,000. It includes tax and ignores refunds.
- The marketing dashboard strips out GST but forgets refunds: 12,40,000 divided by 1.18 is about ₹10,50,847.
- The finance dashboard removes GST and subtracts refunds: (12,40,000 minus 60,000) divided by 1.18 comes to exactly ₹10,00,000.
I did these sums in a script so you don’t have to trust my mental arithmetic. All three are defensible for some purpose. GST is collected on behalf of the government and is not the company’s income, so finance is right to exclude it. Sales might want billed value because their targets are written that way. The failure is that they all call it “revenue”.
Why the usual fix does not last
The first instinct is a meeting. Everyone agrees on a definition, someone updates their report, and three months later a new analyst writes a fresh query for a new dashboard, copying the formula from an old one, or from memory. The definition lives in people’s heads and in scattered query text, so it drifts.
The structural answer is to write the logic once and make every tool pull from that single place. That place is the semantic layer, sometimes called a metrics layer. It sits between the warehouse tables and the people (or tools) asking questions. In it you define things like:
- Measures: net revenue means invoice amount excluding tax, minus refunds, and counted on invoice date.
- Dimensions: the ways you may slice it, such as month, region, product, with their agreed names and hierarchies.
- Relationships: how tables join, so a person does not have to rediscover which key links orders to customers, or accidentally multiply rows in a many-to-many join.
- Rules: which cancelled or test orders to exclude, which date counts, whose currency conversion applies.
Now a dashboard user drags “net revenue” and “month” into a chart, and the layer writes the underlying query. Nobody retypes the GST logic.
Where it lives
There is no single product. The semantic model inside Power BI (the dataset with its DAX measures), a LookML model in Looker, a universe in older enterprise BI tools, or a standalone metrics layer from a data-transformation vendor are all versions of the idea. Their trade-offs differ and I won’t pretend to rank them for you.
One real consideration: if the logic lives inside a single BI tool, only that tool benefits. The Excel user and the data scientist’s notebook go back to their own formulas. Layers that expose the metric to many tools, through an API or generated SQL, spread the benefit wider but need more engineering effort. Pick based on how many different consumers you actually have.
What a layer can’t fix
You still have to decide the definitions, and this is the hard, political part. Sales and finance might each legitimately want a different number. A good layer does not force one; it names both. “Billed value” and “net revenue” are separate measures with separate descriptions, and the word “revenue” alone is reserved for one of them.
It also needs an owner. A definition without a named person who approves changes becomes stale like any wiki page. When a business rule changes, say the company starts recognising revenue differently, someone must update the measure, record the change date, and decide whether history is restated. Version notes beside the measure save a lot of arguments later.
Lastly, a layer does not repair bad source data. If refunds are recorded late, net revenue will still shift after the fact. That’s a separate problem, connected to late-arriving data and data quality checks.
A small test for your own company
Pick your five most-quoted metrics. For each, ask two people in different teams to write the formula on a sheet without looking anything up. If the answers differ, you have found the places where a layer would pay for itself first. Start with those five, not with the whole warehouse. Small and agreed beats large and half-trusted.
Figures and names in the examples are illustrative.
Leave a Reply