What Is a Semantic Model? The Layer Between Data and Business Users

A semantic model is an abstraction layer that sits between a data warehouse's technical schema and the business users who query it. It translates technical database structures — tables, joins, column names, SQL — into business-friendly terms: metrics, dimensions, measures, and calculated fields that reflect how the business thinks about its data rather than how the database stores it.

Without a semantic layer, every analyst who wants to compute "monthly recurring revenue" writes their own SQL — and often writes slightly different SQL, using slightly different business rules, producing slightly different numbers. The semantic layer is where "monthly recurring revenue" is defined once, correctly, in a way that any query tool can use.

The Problem the Semantic Layer Solves

In organizations without a governed semantic layer, inconsistency is the default state. Three analysts computing "revenue" may each use different source tables, different filters for what counts as recognized revenue, different exchange rate assumptions, and different date logic for the reporting period. They produce different numbers. The business does not know which one to trust.

This is not primarily a technology problem — it is an organizational problem. But a well-implemented semantic layer solves it technically: metrics are defined once by authorized data owners, and all consumers of those metrics use the same definition. Inconsistency is not possible because there is only one definition.

How Semantic Models Work

A semantic model maps physical database objects to logical business objects:

**Measures** (or metrics) are numerical calculations: revenue, order count, active users, churn rate. The semantic model defines the SQL expression that computes each measure, along with aggregation rules (SUM, COUNT, AVERAGE), filters, and dimensional context.

**Dimensions** are the attributes used to filter and group measures: date, region, customer segment, product category. The semantic model defines which dimensions are available for each measure and how they join to the underlying tables.

**Hierarchies** define the roll-up structure within a dimension: day → week → month → quarter → year. The semantic model enables BI tools to drill down and roll up through hierarchies without custom SQL.

**Calculated fields** are derived values: profit margin (revenue minus cost divided by revenue), year-over-year growth, rolling 30-day average. These are computed by the semantic layer rather than stored in the warehouse.

Semantic Layer Tools

**Looker (LookML)** — Looker pioneered the modern semantic layer for analytics. LookML is a YAML-based modeling language that defines explores (which tables are queryable and how they join), dimensions (attributes and how they map to database columns), measures (metrics and their SQL expressions), and derived tables (pre-computed SQL). Looker generates SQL at query time based on what the user selects, ensuring consistent metric definitions. Google acquired Looker in 2020; it is now available standalone and integrated into BigQuery.

**Power BI (Tabular Model / DAX)** — Power BI's semantic model is called an Analysis Services Tabular model. It defines tables, relationships, measures in DAX (Data Analysis Expressions), and hierarchies. The model is deployed to the Power BI Service, where all reports in a workspace share the same model definitions. Power BI shared datasets and certified datasets enable metric consistency across reports in an organization.

**Tableau (Data Model and Calculations)** — Tableau's semantic layer is less formalized than Looker or Power BI's. Tableau's logical layer (introduced in Tableau 2020) enables multi-table data source definitions with relationships. Calculated fields in published data sources provide shared metric definitions. For strict governance, published certified data sources with documented, reviewed calculations are the Tableau equivalent of a governed semantic layer.

**dbt Metrics / MetricFlow** — dbt's MetricFlow framework defines metrics as YAML configuration in the dbt project. Each metric specifies the measure, dimensions, granularities, and filters. Integrated tools (dbt Cloud, Looker, Lightdash) can query metrics through MetricFlow, ensuring the metric definition in dbt is the authoritative source rather than being duplicated in BI tools.

**Cube** — open-source semantic layer platform (formerly Cube.js). Defines data models in JavaScript/YAML, generates SQL for any underlying database, and provides APIs for BI tools and custom applications. Positions itself as a headless BI semantic layer that can serve multiple front ends.

**AtScale** — enterprise semantic layer platform designed to sit above a cloud data warehouse and serve multiple BI tools (Tableau, Power BI, Excel, and others) from a single model definition. Useful in organizations with multiple BI tools that need consistent metric definitions across all of them.

The Universal Semantic Layer Problem

The "universal semantic layer" — a single model that serves all BI tools consistently — is a valuable but difficult goal. The challenge: each BI tool has its own query model and its own strengths. Forcing all tools to query through a single semantic layer may reduce each tool to its lowest common denominator capabilities, preventing analysts from using the advanced features of their preferred tool.

The practical middle ground for most organizations:

- Implement a semantic layer in the primary BI tool (Looker, Power BI, or a dbt + Lightdash combination)

- Ensure core business metrics are defined in the warehouse transformation layer (dbt) rather than only in BI tool calculations

- Accept that secondary tools (Excel, ad-hoc SQL) will have some inconsistency and governance it through process rather than technology

The most important metric definitions — the ones that appear in board reports, investor communications, and cross-functional discussions — should be defined in a governed layer. The long tail of ad-hoc metrics can tolerate more variation.

Governed Semantic Layers and Data Teams

Implementing a governed semantic layer requires answering: who owns metric definitions? Who approves changes? What process do analysts follow when they need a new metric or believe an existing metric is wrong?

Technical implementation without governance process produces a semantic model that is authoritative in theory but inconsistent in practice — analysts work around it when it is inconvenient, defeating the purpose. The governance process (metric ownership, change management, documentation standards) matters as much as the technology.

Our BI strategy and Tableau consulting practices design semantic layer and metric governance frameworks — contact us to discuss governed analytics architecture for your organization.

A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.