Conformed dimensions are those dimensions which have been designed in such a way that the dimension can be used across many fact tables in different subject areas of the warehouse. It is imperative that the designer plan for these dimensions as they will provide reporting consistency across subject areas and reduce the development costs of those subject areas via reuse of existing dimensions. The date dimension is an excellent example of a conformed dimension. Most warehouses only have a single date dimension used throughout the warehouse.
Conformed Dimension – Challenges
These differences can usually be resolved easily but occasionally this can be difficult as the differences must be resolved and agreed to by the business owners of the source systems. Without resolving these issues workarounds will be implemented which, over time, will cause maintenance and reporting issues.
Note the challenges combining the two product tables below. In the PRODUCT_RETURN table, the primary key is PRODUCT_SKU, the PRODUCT_NAME field is defined as varchar(100), PRODUCT_DESCRIPTION is defined as varchar(100) and PRODUCT_PRICE is defined as number(5,2).
In the PRODUCT_SALES table, the PRODUCT_UPC is defined as the primary key. Luckily this field matches the PRODUCT_UPC field in the PRODUCT_RETURN table. The PROD_NAME field is defined as varchar(50), PROD_DESCRIPTION is defined as varchar(150) and PROD_PRICE is defined as number(7,2), all of which are different than that of the PRODUCT_RETURN table. Data profiling and data verification will be necessary to ensure the data between the two tables matches. The PRODUCT_SALES table does not have a column for PRODUCT_CATEGORY. One final difference is the PROD_TYPE, PROD_ACTIVE_FL, PROD_START_DT and PROD_END_DT. These fields are used to capture the change history of a product that is not captured by the PRODUCT_RETURN table.
To resolve these issues and conform the tables I would propose the following conformed product dimension.
The data in the PRODUCT_RETURN table appears to match that of the PRODUCT_SALES table with some notable exceptions. The product names do not always match between the two tables and the UPCs repeat in the PRODUCT_SALES table. The PRODUCT_SALES table does not have SKUs therefore, we need to ensure the SKUs in the PRODUCT_RETURN table is reliable historically and can be used as an attribute in the new dimension. Before moving forward these issues must be brought before the source system product owners for a mutually agreeable resolution to these data anomalies.
Conformed Dimension – Types
Conformed Dimension – Benefits
Conformed dimensions are the foundation upon which flexible and scalable dimensional models are built. A data modeler must plan for conformance in their design by ensuring that key dimensions of the enterprise are conformed dimensions that can be expanded to include new attributes over time. Dimensional models that lack conformed dimensions will only be able to answer simple questions for the particular data mart for which the dimension was built. When designed properly, conformed dimensions can and will be used by several data marts, providing the customer with insight into their data that exceeds their initial expectations.