Conformed Dimensions & Enterprise Integration

Overview

A dimensional model becomes an enterprise data warehouse when dimensions are shared (conformed) across multiple fact tables, enabling consistent drill-across queries. Without conformed dimensions, each fact table becomes an isolated silo — users cannot compare sales with inventory with marketing in a single report.

Key Principle: Conformed dimensions are built once, used everywhere. They must have identical column names, data types, and domain values across all fact tables that reference them.

Anti-Pattern: Never join fact tables directly to each other. Instead, use conformed dimensions to "drill across" from one fact to another.

When to Use This Skill

• Planning which dimensions will be shared across multiple fact tables
• Building an enterprise data warehouse bus matrix
• Designing shrunken (rollup) dimensions for aggregate fact tables
• Enabling cross-process analytics (e.g., comparing sales with returns with inventory)
• Reviewing a dimensional model for consistency before ERD creation

The Bus Matrix

The bus matrix is the master planning document for a dimensional model. It maps business processes (fact tables) to the dimensions they use, revealing which dimensions must be conformed.

Bus Matrix Template

Business Process (Fact)	dim_date	dim_customer	dim_product	dim_store	dim_employee	dim_promotion
fact_sales_daily	X	X	X	X		X
fact_inventory_snapshot	X		X	X
fact_returns	X	X	X	X	X
fact_promotions	X		X	X		X

Reading the Bus Matrix

• Columns with multiple X's = conformed dimensions (shared across processes)
• dim_date, dim_product, dim_store have X in every row → must be conformed
• dim_employee only appears in returns → does not need conformance yet
• Rows represent individual star schemas (fact + its dimensions)

Key Rules

1. Build the bus matrix before creating ERDs — it is the blueprint for the entire model
2. Every X means the fact table has a foreign key to that dimension
3. Conformed dimensions must have identical column definitions across all references
4. The bus matrix drives domain grouping for ERD organization

Conformed Dimension Rules

What Makes a Dimension "Conformed"

Requirement	Detail
Same name	dim_product everywhere (not dim_product_sales and dim_product_inventory)
Same keys	product_key is the same surrogate key in all fact tables
Same attributes	Column names, types, and domain values are identical
Same grain	One row per product (not one row per product-store)
Single source	Built once, referenced by all fact tables

Common Conformance Violation

# ❌ BAD: Two separate "product" dimensions with different attributes
# fact_sales references dim_product_sales (has price columns)
# fact_inventory references dim_product_inventory (has warehouse columns)

# ✅ GOOD: One conformed dim_product with ALL attributes
table_name: dim_product
columns:
  - name: product_key
    type: BIGINT
  - name: product_name
    type: STRING
  - name: list_price        # Used by sales
    type: DOUBLE
  - name: warehouse_zone    # Used by inventory
    type: STRING

Shrunken (Rollup) Dimensions

When an aggregate fact table operates at a coarser grain than the base fact, it needs a shrunken dimension — a strict subset of rows from the conformed dimension.

Example

# Base dimension: one row per store
table_name: dim_store
columns:
  - name: store_key
    type: BIGINT
  - name: store_name
    type: STRING
  - name: district_name
    type: STRING
  - name: region_name
    type: STRING

# Shrunken dimension: one row per district (rollup of stores)
table_name: dim_district
description: "District rollup dimension. Business: Geographic districts. Technical: Shrunken dimension — subset of dim_store rolled up to district grain."
columns:
  - name: district_key
    type: BIGINT
  - name: district_name
    type: STRING
  - name: region_name
    type: STRING

Key Rules

• Shrunken dimensions must be strict subsets of the conformed dimension
• Column names and domain values must match the parent dimension exactly
• Shrunken dimensions are used with aggregate fact tables (pre-computed summaries)
• Always maintain a FK relationship between the base dimension and shrunken dimension

Conformed Facts

Just as dimensions must be conformed, facts (measures) that appear in multiple fact tables must use identical definitions.

Rule	Detail
Same name	revenue in fact_sales = revenue in fact_returns (not sales_revenue vs return_revenue)
Same calculation	quantity × unit_price in both tables
Same units	USD everywhere (not USD in sales, EUR in international)
Same data type	DOUBLE in all tables

If the same measure has different semantics in different processes, use different names (e.g., gross_revenue vs net_revenue).

Drill-Across Queries

What Is Drill-Across

Drill-across combines measures from multiple fact tables using conformed dimensions as the join key. This is the primary benefit of conformed dimensions.

Pattern

-- Drill-across: combine sales and inventory using conformed dimensions
WITH sales AS (
  SELECT date_key, product_key, store_key, SUM(revenue) AS total_revenue
  FROM fact_sales_daily
  GROUP BY date_key, product_key, store_key
),
inventory AS (
  SELECT date_key, product_key, store_key, on_hand_quantity
  FROM fact_inventory_snapshot
)
SELECT
  d.date_value,
  p.product_name,
  s.store_name,
  COALESCE(sales.total_revenue, 0) AS total_revenue,
  COALESCE(inventory.on_hand_quantity, 0) AS on_hand_quantity
FROM dim_date d
JOIN dim_product p ON ...
JOIN dim_store s ON ...
LEFT JOIN sales ON sales.date_key = d.date_key
  AND sales.product_key = p.product_key
  AND sales.store_key = s.store_key
LEFT JOIN inventory ON inventory.date_key = d.date_key
  AND inventory.product_key = p.product_key
  AND inventory.store_key = s.store_key

Critical Anti-Pattern: Direct Fact-to-Fact Joins

NEVER join one fact table directly to another fact table.

-- ❌ BAD: Direct fact-to-fact join
SELECT * FROM fact_sales s
JOIN fact_inventory i ON s.product_key = i.product_key AND s.date_key = i.date_key
-- This produces a Cartesian explosion if grains don't match!

Why: Fact tables at different grains produce incorrect Cartesian products. Always drill across through conformed dimensions using separate aggregated subqueries.

Validation Checklist (Design Phase)

Before finalizing the enterprise model:

• Bus matrix created mapping all fact tables to their dimensions
• Conformed dimensions identified (dimensions with X in multiple bus matrix rows)
• Conformed dimensions have identical column definitions everywhere
• No duplicate dimensions for the same business concept
• Shrunken dimensions documented as subsets of their parent dimension
• Conformed facts use identical names, calculations, and units
• No fact-to-fact joins planned in any query patterns

Reference Files

• Conformed Dimension Examples — Bus matrix template, shrunken dimension YAML examples, drill-across SQL patterns, and multi-star integration examples

Inputs

• From 01-grain-definition: Fact table grain definitions (determines which dimensions each fact needs)
• From 02-dimension-patterns: Dimension designs (reveals which dimensions exist and their attributes)
• From 03-fact-table-patterns: Fact table designs with measure classifications

Outputs

• Enterprise bus matrix (fact tables × dimensions)
• List of conformed dimensions with conformance requirements
• Shrunken dimension designs (if aggregate facts exist)
• Conformed fact definitions (measures that appear in multiple facts)

Design Notes to Carry Forward

After completing this skill, note:

• The bus matrix (which fact references which dimensions)
• Which dimensions are conformed (shared across multiple facts)
• Any shrunken dimensions needed for aggregate facts
• Drill-across query patterns planned for cross-process analytics

Next Step

Proceed to design-workers/05-erd-diagrams/SKILL.md to create ERD diagrams that visualize the dimensional model. The bus matrix from this step drives domain grouping and relationship documentation.

Related Skills

• Grain Definition: design-workers/01-grain-definition/SKILL.md — Grain types for fact tables
• Dimension Patterns: design-workers/02-dimension-patterns/SKILL.md — Individual dimension patterns
• Fact Table Patterns: design-workers/03-fact-table-patterns/SKILL.md — Fact table design patterns
• ERD Diagrams: design-workers/05-erd-diagrams/SKILL.md — Visualizing the model

References

• Kimball Dimensional Modeling Techniques
• AgentSkills.io Specification