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.
Pingback:Data Warehouse Architecture - Kimball and Inmon methodologies | James Serra's Blog
Pingback:Exam 70-458 – Objective 4 – Design and implement a data warehouse | Tracy Boggiano's SQL Server Blog
If you have a conformed dimension that is used in multiple data marts and those data marts reside on different servers, what is a best practice to keep that dimension synched? In our scenario, those servers are actually on separate HA Availability Groups and the dba team won’t allow cross-db joins.
Pingback:Conformed Dimensions Bus Architecture | Easy Architecture Fan