Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

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.


No comments.

Leave a Comment

Please register or log in to leave a comment.