What is a semantic layer?

Every business has somebody who decides what things mean. It’s rarely in their job description. Ask them for last month’s sales and they don’t recite a figure, they ask a question back. Which date do you want: order, invoice or posting? Does that include intercompany? Are credits netted off, and if so, credits raised last month or credits against last month’s invoices?

Most people find that mildly irritating. It’s the most valuable thing happening in your reporting. That person is holding the questions behind the numbers and answering them consistently, from memory, one conversation at a time. When they are on leave the answers drift a little. When they leave, the answers go with them, and the reports carry on running as though nothing happened.

A semantic layer is that job written down in a form your software can read. That’s the short definition. The longer one matters, because the reason the job exists at all is specific to ERP data.

So which date did you mean?

Take a question as ordinary as on-time delivery. In Microsoft Dynamics 365 Business Central, to take one system, a sales order line carries a requested delivery date, a promised delivery date, a planned shipment date and a shipment date. Each is a defensible basis for the measure, and they answer different questions. Measure against what you first promised and the number is hard to live with. Measure against the most recent confirmation and, if your process rewrites that date when an order is rescheduled, the figure drifts upward until it stops telling you much at all.

The system holds all four fields, and each is legitimate. What it can’t hold is which of them your business treats as the promise. Whoever built the report picked one, probably years ago, for a reason that was never recorded.

That’s the shape of the whole problem, and it’s better stated plainly. Your ERP contains several perfectly valid fields. It does not decide which one represents the definition you meant to report.

The field names change from one system to the next. The problem doesn’t. Whether you are on Syspro, Sage, SAP Business One, Dynamics or anything else in that class, the system carries choices about dates, classifications, costs, status and structure that it has no way of resolving on your behalf. It was built to run the business, not to arbitrate how the business describes itself.

None of this is really a software problem, and it predates software. The bridge that opened at Laufenburg in 2004 crosses the Rhine between Germany and Switzerland, and it was built from both banks at once. Each side set its heights against sea level. Germany measures sea level from the North Sea, Switzerland from the Mediterranean, and the two differ by 27 centimetres. Everybody involved knew that. Someone applied the correction in the wrong direction, so as the two halves approached each other they were 54 centimetres apart. Nothing had been miscalculated. One word meant two things, and both of them were right.

What does available to sell mean?

Ask what is available to sell and the same thing happens. SAP Business One tracks quantity on hand, committed and ordered, and does surface an available figure derived from them. Whether that’s the figure your salespeople should quote depends on how the business treats stock allocated to an unshipped order, goods in transit, and quantities held against a forecast. The warehouse manager, the planner and the salesperson can each read the system correctly and give three different answers.

Why does the textbook explanation come apart?

This is where the textbook explanation of a semantic layer starts to come apart. Most writing on the subject assumes the modelling problem upstream is solved, that the entities are clean and the remaining argument is about metric syntax. An ERP is configured, not bought, and the configuration carries meaning the schema never states. In Sage X3 the analytical dimension types are defined per company, up to twenty of them with nine available on a given ledger, so what the first dimension actually represents is a setup decision each entity made separately. Department 410 can mean field service in the company you started with and warehousing in the one you acquired, because when the charts were consolidated the accounts were mapped and the dimensions were left alone. A group report by department is then arithmetically perfect and factually wrong.

None of this announces itself, which is why it survives. A report built on a definition nobody declared doesn’t come back with an error. It returns a number, it foots, it ties to the ledger, and it circulates for four years.

Why doesn't your P&L have this problem?

Put the same kind of question to your ledger. Ask for gross margin and you probably don’t get asked which one you meant, because the ledger has a chart of accounts and a statement mapping sitting over it, and every posting is forced through them. That mapping isn’t a description of how transactions get classified. It’s the structure the transactions go through, and the statements inherit it whether or not anybody has read it.

It also computes rather than merely labels. Deciding what nets against what, which way the signs run, what rolls into cost of sales and what sits below the line, all of that is derivation. Gross margin is a derived line, not a caption. Hand the same ledger to five people with no chart and you get five statements that all foot and none of which agree.

So you already own a semantic layer. You built it for the ledger, you defend it every year, and you have probably never thought of it as optional. The question worth sitting with is why you only ever built one.

Part of the answer is that somebody made you. The ledger is the corner of the business where meaning had to be written down and defended, because auditors and accounting standards required it. Some of that discipline does extend past the ledger, inventory valuation policy being the obvious case. What has never been required is a written answer to which date counts as the promise, or what available means when a customer is on the phone. So those definitions got settled the way they always get settled, one report at a time, by whoever happened to be building that report.

None of which requires your chart to be elegant. Plenty aren’t. Most businesses that have made an acquisition are carrying two charts half merged, and a great many have a 999 department quietly absorbing whatever nobody wanted to code, which by now is one of the larger lines in any dimensional report. A governed structure that has drifted still beats no structure, partly because drift is visible at all only when there’s something to compare against.

A semantic layer, then, is a chart of accounts for everything outside the ledger. The place where a measure is defined once, so that reports, spreadsheets and anything else reading your data inherit the same answer rather than each deriving its own.

What actually changes when the definitions are governed

The argument so far has been about a problem. The other side of it is easy to underestimate, so it’s better set out concretely.

Today the same measure can exist in several places with no clear owner. Finance, sales and operations each carry a version that’s defensible on its own terms, and most of the knowledge that reconciles them sits with one or two people. A good part of the analyst’s week goes on reconciling outputs rather than looking at them. Anything pointed at that estate, an assistant included, inherits every one of those decisions without being told they were decisions.

Govern the definitions and the arithmetic does not change. The ownership does. Revenue, margin, customer, product, period and hierarchy have an agreed meaning, recorded once. Power BI, Excel and anything else reading through the model inherit it rather than deriving their own. A change is made in one place instead of being reproduced across a dozen reports, most of which nobody will remember to update.

The part worth noticing is what happens to disagreement. It doesn’t disappear, and it shouldn’t. What changes is that it surfaces as a visible decision about which measure the business runs on, instead of staying buried in a filter somebody set four years ago. That’s the difference between an argument you can settle once and one that returns every quarter.

The practical effects are unglamorous and they add up. Less of the month spent reconciling. Less exposure when the person who knows how it works is on leave. Fewer meetings that stall on whose number is right before anyone gets to what the number means. Less logic living in workbooks that break when a column moves. And when you do point an assistant at any of it, it’s working from definitions your business agreed rather than ones it inferred on the spot.

Isn't that what the warehouse is for?

A fair objection, and it deserves a proper answer, because plenty of businesses reading this have built one and it’s working.

The warehouse settles what happened. Transactions reconciled to the source, kept over time even where the ERP overwrites itself, structured so a query can run without going near the operational system at month end. That’s real work, the job data warehouse automation exists to do, and it’s a prerequisite for everything above it.

The semantic layer settles what those records mean. It’s the difference between holding the delivery dates and holding a decision about which one is the promise. You can have a healthy warehouse and still hold two reports that both say sales and disagree by a few percent. Nothing is broken. The warehouse did its job. The disagreement sits above it, and no amount of additional data resolves it.

The multi-system version of this is sharper still. In the ERP, sales might mean invoiced revenue in base currency. In the CRM it means won deals. In a spreadsheet it means bookings, or cash received. Each is correct in its own context. Reconciling them at the moment somebody asks a question is the worst possible time to be doing it, because that’s exactly when the definitions and the joins matter most.

Microsoft recommends a star schema for Power BI models, and getting ERP data into that shape on every refresh is warehouse work. Once it’s there, the calculations and hierarchies defined over it are inherited by everything downstream, Excel included. Excel isn’t the villain here. The reason it produces contradictory numbers isn’t the tool but that each workbook carries its own definitions with nowhere common to draw them from.

Where your definitions live today

Worth doing literally rather than in the abstract. Pick one measure and find every place it’s defined. A representative answer looks something like this.

• A saved report in the ERP's own report writer, whose filter set is the definition.
• A Crystal report on a share drive, built by somebody who has left.
• A measure inside one Power BI file, copied into three others and then edited in two of them.
• A spreadsheet column with a hard-coded exclusion for two account codes and a comment explaining why.
• A SQL view called vw_Sales_Final_v3.

Five definitions, no owner, and nothing you can compare. In ledger terms, five mappings and no chart. The argument in the meeting is usually downstream of this, which is why it never quite resolves. Nobody is disagreeing about the number. They are disagreeing about a question that was never asked out loud.

Where this goes next

That’s the definition, and for a lot of readers it’s enough: a semantic layer is the place a measure is defined once, so everything downstream inherits it rather than deriving its own.

The comparison with a chart of accounts has limits though, and they are the interesting part. A chart handles ambiguous words. It does nothing for the reports that are arithmetically wrong without a single word being used loosely, and nothing for the fact that ERP master data describes the present while your reports describe the past.

Those two problems, and what changes when software rather than a person starts answering the questions, are covered in part two.