Analytics & BI Architecture

Analytics and BI architecture is the layer that turns governed, integrated data into consumable, trustworthy insight — semantic models, metrics layers, and self-service reporting.

At a glance

• What it is: A semantic or metrics layer that defines business metrics once, sitting between raw storage and the BI tools/dashboards people actually use.
• Why it matters: Dashboards that can’t be trusted get ignored — the entire investment in data infrastructure is wasted if nobody believes the numbers on the screen.
• For developers: reuse the semantic layer’s existing metric definitions instead of writing your own aggregation SQL for a new report — that’s exactly how “three different revenue numbers” incidents start.
• When to use it: Whenever more than one report or dashboard needs to reference the same metric.
• When not to use it: For a genuinely one-off, throwaway analysis that won’t be repeated or reused, a direct query may be simpler than formalizing a metric.
• Prerequisites: Data Governance & Stewardship and Data Storage Architectures.

Mental model

graph LR
    Store["Warehouse / Lakehouse"] --> Semantic["Semantic / metrics layer\n(metric defined once)"]
    Semantic --> BI["BI tools & dashboards"]
    BI --> Consumers["Consumers\n(marketing, finance, product)"]

The semantic layer is the single place a metric like “active customer” is defined — every dashboard downstream reads that definition rather than re-deriving its own version of the same idea.

Step-by-step walkthrough

1. Identify the core business metrics that matter across teams (e.g. active customers, revenue, churn).
2. Define each metric exactly once in a semantic/metrics layer, agreed with the data owner and steward.
3. Connect BI tools to the semantic layer rather than to raw warehouse tables directly.
4. Enable self-service reporting within guardrails — analysts build dashboards, but reuse governed metric definitions.
5. Monitor dashboard and metric usage, and deprecate stale or duplicate dashboards over time.

Worked example

Following the governance process that resolved Northwind’s conflicting “active customer” definitions, marketing, finance, and product still each had their own dashboard querying raw tables independently.

• Input: one agreed definition of “active customer” published in the business glossary, but three dashboards each with their own hand-written SQL.
• Process: implement active_customer once as a metric in the semantic layer, then repoint all three teams’ BI tools to query that metric instead of writing their own aggregation logic.
• Output: marketing, finance, and product dashboards now always show the same number for “active customers,” and future dashboards can reuse the same metric without re-deriving it. On Azure/Power BI, this is a single measure (Active Customers) defined once in a shared Power BI semantic model (or Azure Analysis Services model), which all three teams’ reports connect to instead of writing their own DAX or SQL aggregation.

Common use cases

Use case	When to use	Notes
Executive dashboards	Leadership needs a single trusted view of key metrics	Highest cost of inconsistency — prioritize these first
Self-service BI rollout	Analysts need to build their own reports without engineering bottlenecks	Guardrails come from the semantic layer, not from blocking access
Embedded analytics	Metrics surfaced inside a product or customer-facing app	Same semantic layer should back both internal and embedded views
Semantic layer as source of truth	Multiple teams currently define the same metric differently	Directly addresses metric-drift incidents

Advanced details

• Metric layer tooling: dedicated metrics-layer/headless BI tooling lets a metric be defined once and queried consistently from multiple BI frontends.
• Governance of self-service: define who can create a new metric versus who can only consume existing ones, to avoid self-service turning into uncontrolled metric proliferation.
• Performance: pre-aggregation and caching in the semantic layer keep self-service queries fast without every dashboard hitting raw data at full scale.
• Dashboard sprawl: track which dashboards are actually used and retire duplicates — an unmaintained dashboard showing stale logic is a recurring source of “wrong number” incidents. Track actual usage (e.g. Power BI usage metrics, or Application Insights events emitted by an embedded-analytics front end) rather than guessing which dashboards are still read.

Quick reference

Term	Meaning
Semantic layer / metrics layer	The layer where a business metric is defined once and reused everywhere
Self-service BI	Letting non-engineering users build their own reports/dashboards
Single source of truth	One agreed, authoritative definition for a given metric
Dashboard sprawl	Accumulation of duplicate or unmaintained dashboards over time

Troubleshooting

Symptom	Likely cause	Fix
Two dashboards show different numbers for the same metric	Each dashboard has its own hand-written aggregation logic	Migrate both to a single metric defined in the semantic layer
Self-service BI produces conflicting new metrics constantly	No guardrails on who can define new metrics	Separate “can consume” from “can define” permissions in the semantic layer
Dashboards slow to load	BI tools querying raw tables directly instead of a curated/pre-aggregated layer	Route queries through the semantic layer with appropriate pre-aggregation/caching

Check your understanding

• Why does pointing three dashboards at one semantic-layer metric prevent the same conflict from recurring later?
• For a metric you rely on regularly, do you know whether it’s defined once centrally, or hand-computed independently by whoever built the dashboard?

Related pages

Part of Introduction to Data Architecture. See also Data Governance & Stewardship for how metric definitions get agreed in the first place, Data Storage Architectures for what the semantic layer sits on top of, and the Glossary for term definitions.