Every organization that reaches a certain scale of data maturity encounters the same crisis: two dashboards answering the same question produce different numbers. The CFO’s revenue report says Q1 was $12.3M. The VP of Sales’s pipeline dashboard says Q1 was $11.8M. Both are technically correct, but they’re using different definitions of “revenue,” different date boundaries for “Q1,” and different inclusion criteria for “active customers.”
This isn’t a data quality problem. The underlying data is the same. It’s a semantic consistency problem. Multiple teams have independently defined business metrics in their own dashboards, and those definitions have silently diverged.
The solution is a semantic layer: a single, governed abstraction that defines business metrics exactly once and serves those definitions to every consumption tool.
What a Semantic Layer Actually Is
A semantic layer sits between the raw data warehouse and the consumption tools (dashboards, reports, ad-hoc queries). It provides:
- Metric definitions:
Revenue = SUM(sales.amount) WHERE sales.status = 'completed' AND sales.is_refund = false - Dimension hierarchies:
Date → Quarter → Month → Week → Day - Access controls: Which users can see which metrics and dimensions
- Query optimization: Pre-aggregations and caching for frequently accessed combinations
The critical property: metrics are defined in one place. Every dashboard that shows “Revenue” pulls from the same definition. If the definition changes (e.g., refunds are now excluded differently), every consumer automatically reflects the update.
Where It Lives in the Stack
┌──────────────────────────────────────────┐
│ Consumption Layer │
│ (Power BI, Superset, Flutter App, SQL) │
├──────────────────────────────────────────┤
│ Semantic Layer │
│ (Metric definitions, dimensions, ACLs) │
├──────────────────────────────────────────┤
│ Data Warehouse │
│ (ClickHouse, PostgreSQL, Synapse) │
├──────────────────────────────────────────┤
│ Ingestion Layer │
│ (Celery + Redis, Mage.ai pipelines) │
├──────────────────────────────────────────┤
│ Source Systems │
│ (APIs, databases, SaaS platforms) │
└──────────────────────────────────────────┘The Power BI Implementation: Shared Datasets
In the Microsoft ecosystem, the semantic layer is implemented through shared datasets (now called “semantic models” in Fabric terminology). A shared dataset is a published Power BI model that multiple reports connect to.
The Anti-Pattern: One Dataset Per Report
Most organizations start with each report containing its own dataset. This is the fastest way to build but creates the metric divergence problem:
Report A: Dataset A → Revenue = SUM(Sales[Amount])
Report B: Dataset B → Revenue = SUM(Sales[Amount]) - SUM(Sales[Tax])
Report C: Dataset C → Revenue = SUM(Sales[Net_Amount])Three reports, three revenue definitions, three different numbers on the executive dashboard.
The Pattern: Centralized Semantic Models
We restructure the architecture into shared models:
Semantic Model: "Corporate Metrics"
├── Revenue = SUM(Sales[Amount]) WHERE Status = "Completed"
├── Net Revenue = [Revenue] - SUM(Sales[Tax]) - SUM(Returns[Amount])
├── Active Customers = DISTINCTCOUNT(Sales[CustomerID])
└── ... (all official metric definitions)
Report A → connects to "Corporate Metrics" → uses [Revenue]
Report B → connects to "Corporate Metrics" → uses [Net Revenue]
Report C → connects to "Corporate Metrics" → uses [Revenue]Now there is exactly one definition of Revenue. Report C’s developer wanted net revenue but was using the wrong field name—the shared model makes the correct metric available with clear naming.
Implementation Mechanics
Creating a shared semantic model requires discipline in several areas:
1. Star Schema Design
The model must follow strict dimensional modeling. Fact tables contain measures. Dimension tables contain attributes. Relationships are clearly defined:
Dim_Date ──────┐
│
Dim_Product ───┤
├── Fact_Sales
Dim_Customer ──┤
│
Dim_Geography ─┘Every dimension table has a surrogate key. Every fact table references dimensions through foreign keys. No snowflaking unless the dimension genuinely has a hierarchical structure that warrants it.
2. Measure Documentation
Every DAX measure in the model includes its business definition, owner, and last review date:
// [Revenue]
// Business Definition: Total completed sales amount excluding refunds
// Owner: Finance Team
// Last Reviewed: 2026-03-15
// Spec Reference: DOC_ID 0210341ab
Revenue =
CALCULATE(
SUM(Fact_Sales[Amount]),
Fact_Sales[Status] = "Completed",
Fact_Sales[IsRefund] = FALSE
)This documentation lives inside the DAX model itself, visible to anyone who inspects the measure. When a business user asks “what does Revenue include?”, the answer is embedded in the metric definition—not in someone’s Confluence page from 2023.
3. Row-Level Security (RLS)
Shared models must implement row-level security so that the same model serves different business units:
// RLS role: Regional Managers
[Region] = USERPRINCIPALNAME() -- Simplified; actual implementation
-- maps UPN to region through a security tableA regional manager in Brazil sees only Brazilian data. A global executive sees everything. Same model, same metrics, different visibility.
Beyond Power BI: The Headless Semantic Layer
For organizations that consume data from multiple tools (Power BI for executives, Superset for analysts, Flutter apps for operations), a Power BI-native semantic layer creates vendor lock-in. The metrics are trapped inside a proprietary format.
The headless approach uses a dedicated metrics engine—such as Cube.dev—that sits between the warehouse and all consumption tools:
# Cube.dev metric definition
cubes:
- name: sales
sql_table: analytics.fact_sales
measures:
- name: revenue
type: sum
sql: amount
filters:
- sql: "{CUBE}.status = 'completed'"
- sql: "{CUBE}.is_refund = false"
description: "Total completed sales amount excluding refunds"
- name: net_revenue
type: number
sql: "{revenue} - {tax_total} - {refund_total}"
dimensions:
- name: order_date
type: time
sql: created_at
- name: region
type: string
sql: regionAny tool that can issue SQL or REST queries can consume these metrics. Power BI connects via SQL endpoint. Superset connects via SQL endpoint. The Flutter app connects via REST API. All three tools show identical numbers because they’re hitting the same metric definitions.
Pre-Aggregations for Performance
Cube.dev supports pre-aggregations that dramatically reduce query latency:
pre_aggregations:
- name: daily_revenue
measures: [revenue, net_revenue]
dimensions: [region]
time_dimension: order_date
granularity: day
refresh_key:
every: 1 hourInstead of scanning millions of rows in ClickHouse for every dashboard load, pre-aggregated results are served from a materialized cache. Sub-second response times for any combination of the pre-aggregated dimensions.
The Organizational Challenge
The technical implementation of a semantic layer is straightforward. The organizational challenge is harder: convincing teams to give up their independently maintained datasets and adopt centralized definitions.
Common Resistance Patterns
“Our metrics are different because our business unit is different.”
Sometimes true. More often, the difference is an inconsistency that hasn’t been surfaced. The resolution is a formal metric review meeting where both teams present their definitions and the business decides which is canonical.
“Centralized models are too slow to update.”
Valid concern. Mitigate by establishing a clear SLA for metric changes: new metrics are published within 48 hours of approval. If the central team can’t meet this SLA, the model governance is understaffed.
“We need flexibility to experiment.”
Acceptable. Create a sandbox tier where teams can build experimental datasets. But any metric that reaches production reporting must go through the shared model.
The Governance Model
We recommend a three-tier governance structure:
| Tier | Purpose | Governance Level |
|---|---|---|
| Gold | Official metrics for executive reporting | Full review, signed off by Finance/Ops |
| Silver | Validated metrics for team-level reporting | Team lead approval, documented definitions |
| Bronze | Exploratory/sandbox metrics | Self-service, not for production decisions |
Metrics graduate from Bronze → Silver → Gold as they prove their value and get formally defined. This provides the experimentation flexibility teams need while maintaining the consistency executives require.
Implementation Roadmap
For organizations starting from zero semantic layer:
Month 1: Audit all existing datasets. Catalog every metric definition. Identify the top 20 metrics by usage frequency.
Month 2: Build the shared model with the top 20 metrics. Implement star schema design. Define RLS roles.
Month 3: Migrate 3-5 high-visibility reports to use the shared model. Validate numbers match. Fix discrepancies.
Month 4-6: Migrate remaining reports. Deprecate independent datasets. Establish ongoing governance process.
The payoff is permanent: once the semantic layer is established, the “numbers don’t match” meetings stop. Analyst time shifts from reconciliation to actual analysis. And every new report starts from a foundation of trusted, consistent metrics instead of reinventing definitions from scratch.