SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


No comments.

Leave a Comment

Please register or log in to leave a comment.