Three dashboards report different numbers for "active users" and each is defensible. What is the actual problem, and what does a semantic layer have to provide to fix it?

The actual problem

It is not a data quality problem; it is a definition problem. Each dashboard computes something reasonable and different: active in the last 28 days versus 30; sessions versus authenticated users; excluding internal accounts or not; counting a user in each region they appeared in or once globally; using event time or ingestion time.

Every number is correct for its definition, and the definitions were never written down — they exist as SQL in three places, authored at different times by people solving different problems.

The cost is trust rather than accuracy. Once leadership has seen three numbers for one metric, every number from the data platform is questioned, and meetings are spent reconciling figures rather than acting on them. That is the real damage and it is difficult to reverse.

What a semantic layer must provide

• One definition per metric, in one place, expressed as code and version-controlled — with the SQL generated from it rather than written per consumer.
• The definition consumed by every tool: dashboards, notebooks, ad hoc queries, exports. A semantic layer that only the BI tool uses does not solve the problem, because the notebook and the ad hoc query are where the divergent numbers come from.
• Documented semantics alongside the calculation: what it means, what it excludes, its grain, its time basis, and what question it is intended to answer — which is what stops the metric being used for a question it does not answer.
• A named owner who decides what the metric means and approves changes.
• Versioning with change history, so "this number changed" has an answer — a metric definition changing silently is as damaging as three definitions coexisting.
• Consistent dimensions — the same definition of region, segment, channel and time period — since metrics agreeing while their dimensions differ produces disagreement that is far harder to diagnose.
• Certification levels, distinguishing governed metrics from exploratory ones, so a self-service culture is preserved without every ad hoc calculation carrying the same authority.

Why these efforts usually fail

• Started as a definitions catalogue with no enforcement, so the documented definition and the queries people run diverge immediately.
• Only covering one tool, leaving the others as sources of divergence.
• No owner, so contested definitions are never resolved and the layer records the disagreement rather than settling it.
• Trying to define everything at once, which never completes — start with the ten metrics that appear in leadership reporting, where the cost of disagreement is highest and the ownership is clearest.
• Treating it as a technology purchase. The tooling is straightforward; the work is agreeing what things mean, and that is an organisational negotiation the tool cannot perform.