In this lesson, you will learn how to use conformed dimensions to ensure consistency and enable "drill-across" reporting when your organization operates multiple data marts.
The Problem: Siloed Data Marts
Imagine your company has a Sales data mart and a Support data mart. If the Sales team defines a customer by their email and the Support team defines the same customer by their customer_id, your reports will fail to align. When you try to answer a question like, "Do customers who submit many support tickets spend more on our products?", you hit a wall because the dimensions aren't speaking the same language.
The Solution: Conformed Dimensions
A conformed dimension is a dimension table that is designed to be shared across multiple fact tables or different data marts. By using the exact same dimension table—or at least an identical set of attributes and keys—you create a "common language" for your data.
When you use a conformed Dim_Customer table, your architecture looks like this:
-- Shared Dimension
CREATE TABLE Dim_Customer (
customer_sk INT PRIMARY KEY, -- Surrogate Key
customer_natural_id VARCHAR(50),
name VARCHAR(100)
);
-- Fact table in Sales Mart
SELECT s.sales_amount, c.name
FROM Fact_Sales s
JOIN Dim_Customer c ON s.customer_sk = c.customer_sk;
-- Fact table in Support Mart
SELECT t.ticket_priority, c.name
FROM Fact_Support_Tickets t
JOIN Dim_Customer c ON t.customer_sk = c.customer_sk;
Why This Matters
Because both fact tables reference the same customer_sk, you can perform a drill-across. You can join or aggregate data from both the Sales mart and the Support mart using the Dim_Customer table as a bridge. Without conforming these dimensions, the business would constantly receive conflicting reports, and you would be forced to perform messy, manual data cleanup every time an executive asked a cross-departmental question.
By standardizing dimensions, you provide a single version of the truth, allowing different business processes to be analyzed as part of one cohesive analytical ecosystem.
Key Takeaway: Conformed dimensions act as a shared vocabulary that allows different data marts to be integrated, enabling seamless drill-across reporting and consistent business metrics.