conformed dimension

What is a conformed dimension?

In data warehousing, 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 or data marts, ensuring consistent reporting across the enterprise.

A conformed dimension can be referenced by multiple fact tables in a data warehouse. The dimension is implemented as either a single dimension table that is referenced by multiple fact tables or as multiple dimension tables that are referenced by fact tables in separate data marts within the warehouse. It is also possible for multiple fact tables within a single data mart to reference that same conformed dimension table as a fact table in other data marts.

When implemented as a single dimension table, two or more fact tables reference the dimension table, making it possible for them to share the same data. The data within the dimension table has the same meaning to all the referencing fact tables. In this way, a report can reliably incorporate data from multiple fact tables without concern that the data has different meanings within the context that it's used.

A single dimension table reduces the amount of data that needs to be stored, as well as the overhead that comes with extract, transform and load (ETL) operations. Figure 1 shows a simple data warehouse that includes two conformed dimension tables: one for products (dimProduct) and one for dates (dimDate). The data warehouse also includes two fact tables -- factSales and factInventory -- both of which reference the two conformed dimensions.

The product dimension can be used as a conformed dimension because its attributes mean the same thing to both the sales and inventory fact tables. A report that incorporates both fact tables would be using the product information consistently no matter how the sales and inventory data were combined.

The date dimension also makes a good candidate for a conformed dimension because its attributes (date, week, month, quarter, year, etc.) have the same meaning for either of the two fact tables. This would be similar to two people looking at the same calendar and being able to extract the same date, month and year. From a single date key, a query can extract the various levels of the date hierarchy, and those levels would have the same meaning to each fact table. Date dimensions such as the one in this example often make good candidates for conformed dimensions.

A conformed dimension can also be implemented as multiple tables in two or more data marts. For the tables to be considered conformed, they must have identical columns, keys and content. However, one table can be a subset of the other, as long as the remaining components are still identical. As with a single dimension table, data from the different data marts can be reliably combined into a single report because the data has the same meaning to each fact table.

If a conformed dimension is implemented across multiple data marts, the table names can be different, as long as the columns and content are the same. In fact, data marts created in the same SQL database must often have different names unless they can be logically separated, such as putting them into different schemas, which is possible in some database management systems.

Figure 2 shows two data marts that share two conformed dimensions: one for products and one for dates. The date dimension tables are identical in both data marts, except for the different table names. However, the product dimension table in the Inventory data mart contains only a subset of the attributes of the product dimension table in the Sales data mart. The remaining attributes are identical, although the tables have different names.

When a conformed dimension is implemented as multiple dimension tables, it does not reduce the amount of data in the warehouse like a single dimension table, but it still reduces the ETL overhead because the same operations can be used to populate all instances of the dimension tables.

Further explore the differences between data warehouse vs. data mart. Read about 8 data integration challenges and how to overcome them and see how to develop an enterprise data strategy in 10 steps.