Conformed Dimensions
One calendar, one customer, everywhere
Conformed dimensions are shared, standardized dimension tables used across multiple fact tables and processes — the mechanism that makes every star in the warehouse speak the same language.
What you will learn
- Explain what conforming a dimension means and why it matters
- Apply a date dimension and customer hierarchy across stars
- Avoid the un-conformed-attribute trap in cross-process reports
What is this?
A conformed dimension is a single, standardized dimension shared by multiple fact tables — the same dim_date, dim_customer or dim_region used by sales, marketing and finance stars. Conforming means agreeing on attributes, keys and definitions, so two reports from different processes can be joined or compared ('drill across') and trust that 'region' means the same thing.
Why do you need it?
- Non-conformed dims are why two departments' dashboards disagree on 'region'.
- Conforming makes cross-process analysis legitimate without copy-paste.
- One certified dimension is easier to maintain and audit than N near-copies.
- It is the core of enterprise consistency — the difference between a BI zoo and a BI platform.
Mental model
One national calendar and timezone rulebook
Conformed dimensions
Every business agrees on which dates are holidays and what 'quarter' means, so a meeting booked in Osaka and one in Ohio reference the same calendar. Conformed dims are that rulebook for every report in the company.
How it works
- A dimension is built once, owned centrally, using standard keys and attributes.
- All fact tables reference the same conformed dim via surrogate keys.
- Attribute hierarchies (region → country → continent) are consistent everywhere.
- Cross-star queries join facts through the shared dimension to compare processes legitimately.
- Certification documents the canonical definition so deviating copies are visible as risks.
One dim serves many stars.
In production
- Finance and sales stars share dim_date and dim_customer, so quarterly revenue reconciles department-by-department.
- A retail chain's store dim has region/country/market attributes; every fact (sales, stock, labor) joins the same rows.
- Marketing's campaign dim conforms to the central calendar, enabling campaign-to-sales attribution joins.
Best practices
- Build conformed dims centrally and certify their attribute definitions.
- Reuse the same surrogate-key scheme; never re-key per process.
- Keep conformed attributes lean and unambiguous; stack special-case attrs in process-specific dims.
- Document the conformed dimension grid: which facts share which dims.
Common mistakes
- Each team building 'their own customer dim' with re-keyed rows — the fork that breaks everything.
- Conforming 'region' in one process to mean country and in another to mean country-group.
- Copying dim values instead of referencing rows, so derived tables drift.
- Forgetting history: conformed type-2 dims become filters too.
Common issues
Marketing and sales 'US' totals differEach star has a private region attribute with different rows
Replace with one conformed dim_region referenced by both facts.
Certified dim changed and broke a starConsumers assumed no attribute edits
Version conformed dim releases; consumers opt in with review.
Debugging tips
- Cross-team totals differ? Compare the surrogate keys and attribute definitions of their dims.
- A customer with two keys across stars won't 'drill across' — re-key alignment is the fix.
- If regions diverge, check the conformed dim's certification change log.
Interview checkpoint
Quick quiz
The main purpose of a conformed dimension is to:
Two stars that each define their own 'region' are effectively conformed.