Conformed dimensions

A conformed dimension is a dimension that has the same meaning to every fact with which it relates.  Conformed dimensions allow facts and measures to be categorized and described in the same way across multiple facts and/or data marts, ensuring consistent reporting across the enterprise.

A conformed dimension can exist as a single dimension table that relates to multiple fact tables within the same data warehouse, or as identical dimension tables in separate data marts.  Date is a common conformed dimension because its attributes (day, week, month, quarter, year, etc.) have the same meaning when joined to any fact table.  A conformed product dimension with product name, description, SKU, and other common attributes could exist in multiple data marts, each containing data for one store in a chain.

Using conformed dimensions makes the whole ETL process more efficient as it does not have to do multiple processes to process the same dimensions-related data (for example customer data) more than once.  It also makes dimensions extensible (add more attributes to a dimension): If there is one customer dimension, it makes the whole process of changing the dimensional attributes easier and less complex.  It will have minimal impact on the queries and associated meta-data, reports and views built on the dimension.  In short, the more different versions of a dimension, the more work one needs to do to handle any change.

There may be times when you have more than one fact table in a cube, and a user may want to compare measures in the fact tables on a scorecard.  You can only do this if there is a conformed dimension between the fact tables.

Keep conformed dimensions in mind when building a data warehouse.  It can save you a lot of trouble down the road.

About James Serra

James currently works for Microsoft specializing in big data and data warehousing using the Analytics Platform System (APS), a Massively Parallel Processing (MPP) architecture. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence/MDM architect and developer, specializing in the Microsoft BI stack. He is a SQL Server MVP with over 25 years of IT experience.
This entry was posted in Dimensions, SQLServerPedia Syndication, SSAS. Bookmark the permalink.

2 Responses to Conformed dimensions

  1. Pingback: Data Warehouse Architecture - Kimball and Inmon methodologies | James Serra's Blog

  2. Pingback: Exam 70-458 – Objective 4 – Design and implement a data warehouse | Tracy Boggiano's SQL Server Blog

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>