Jaco van der Laan

Universal Star Schema as the Foundation for Our Delivery Layer

17/09/2025  ·  Data Architecture  ·  Data Engineering  ·  Data Modeling

article base/exposure-visualisation-addendum
‹ ›

How the Universal Star Schema brings clarity, flexibility, and scalability to the consumption layer of our metadata-driven architecture — with Power BI as the front-end accelerator.

Summary

In our architecture, we have already defined source models, business-friendly mappings, and historized integration models.

The missing piece is a consistent way to model the delivery / consumption layer (also known as the exposure layer).

The Universal Star Schema (USS) provides a standardized way to integrate multiple facts and dimensions into a clean, extensible model.

In Power BI, the USS eliminates inactive relationships, looping joins, and ad hoc complexity.

Combined with DuckDB (metadata-as-data), Python (SQL generation), and Notion (business context), the USS becomes the blueprint for delivering data marts in a consistent, governed way.

Introduction

Our metadata-driven methodology ensures that data moves from sources through mappings into historized integration layers, giving us trust and consistency.

But when we reach the delivery layer — the place where analysts, business users, and BI tools consume data — complexity often creeps in. Without structure, consumption models in Power BI or Tableau quickly become inconsistent, overloaded with inactive relationships, and difficult to extend.

This is where the Universal Star Schema (USS) comes into play. It gives us a disciplined, scalable approach for the delivery (exposure) layer, perfectly complementing the foundation we’ve built for sources and business layers.

The Challenge of the Delivery Layer

Traditional star schemas are effective when modeling a single fact (e.g., Orders).
But in reality we deal with multiple facts: Orders, Shipments, Invoices, Payments. Challenges emerge:

  • Multiple relationships between facts and dimensions.
  • Inactive relationships in Power BI.
  • Role-playing dimensions (e.g., multiple employees linked to an order).
  • Each new requirement introducing additional fragility.

The delivery layer is where these issues are felt most acutely. If not addressed, business users face inconsistent metrics and a growing reliance on workarounds.

The Universal Star Schema Explained

The USS, developed by Bill Inmon and Francesco Puppini, simplifies consumption modeling by introducing a bridge table:

  • All facts and dimensions connect through the bridge.
  • Each fact contributes keys for its related dimensions.
  • Each dimension contributes its keys to the bridge.
  • The bridge becomes the single integration point.

For Power BI this means:

  • Every fact and dimension has exactly one relationship — to the bridge.
  • No inactive relationships.
  • A clean, extensible model for analysts.

How the USS Fits into Our Architecture

1. Source & Integration Layers (Already in Place)

  • Logical source models reverse-engineered from physical databases.
  • Business-friendly mappings expressed in Notion and persisted in DuckDB.
  • Dual SCD2 historization for temporal integrity and point-in-time access.

2. Delivery Layer with USS

  • USS defines how facts and dimensions are exposed to BI tools.
  • We register USS entities in DuckDB (meta.fact, meta.dimension, meta.bridge).
  • Python scripts generate:
  • Bridge table SQL.
  • Simplified fact tables with only measures and surrogate keys.

3. BI Consumption with Power BI

  • Power BI connects to the USS bridge, facts, and dimensions.
  • Users create reports without juggling inactive relationships or custom DAX fixes.
  • The bridge leverages Power BI’s storage engine by standardizing joins.

4. Governance

  • Notion documents facts, dimensions, and measures in business-friendly language.
  • OpenMetadata (if adopted) integrates lineage and glossary terms.
  • DuckDB acts as the central metadata warehouse, ensuring all USS definitions remain queryable and governed.

Benefits of USS in Our Architecture

  • Clarity: one consistent way to link facts and dimensions.
  • Scalability: new facts or dimensions require only a column in the bridge.
  • Simplicity: Power BI models are clean and predictable.
  • Governance: relationships are explicit and centrally defined.
  • Performance: Power BI’s VertiPaq engine optimizes the bridge design.

Example Flow

  1. Define facts and dimensions in DuckDB metadata.
  2. Generate bridge table SQL with Python.
  3. Expose the USS (bridge + facts + dimensions) to Power BI.
  4. Analysts build reports directly on the USS model.
  5. Governance teams validate measures and mappings in Notion.
  6. Lineage tools (e.g., OpenMetadata) trace USS entities back to historized integration models.

When to Use USS

  • When multiple facts (orders, shipments, invoices, payments) need to be analyzed together.
  • When Power BI datasets are becoming too complex with inactive relationships.
  • When we need a standardized delivery layer across different domains and data marts.

References