Dimensions correspond to master data and are critical building blocks of any Data Warehousing Environment. Irrespective of the modelling approach adopted such as Star or Snowflake schema, dimension conformity is implemented in order to maintain Data Quality and ensure Data Consistency.
In simple terms, a conformed dimension is any dimension that is shared across multiple fact tables or subject areas. The diagram below shows a simple conceptual model of conformed dimension.
Most frequently used example for conformed dimension is “Time“. If looked closely, the time dimension is common for any type of analytical scenario and follows the standard attributes Year, Quarter, Month, Week, Day and Timestamp pattern. On a sidenote, here are some posts that highlight complexities within “Time” dimension i.e. Different Flavours and Cheatsheet.
This same analogy can be extended to “Geography” dimension too to some extent. Characteristics of Geography data such as Continent, Country, State, Latitude and Longitude are common across organizations. Companies follow a different nomenclature of grouping that also vary vastly based on business needs. This trend is actually converging with map based analytics.
In today’s context, almost all DW / BI projects implement conformed dimensions. Most popular method used has the dimensional data populated in a single table and directly link with multiple facts as required. Other methods incorporate Views or Synonyms for conformed dimensions, which will have the same effect as a table. Choosing the database object is an architectural decision finalized during the blackboard phase of a project.
Advantages of conformed dimensions are as follows
- One central location to update by ETL jobs. All business rules are incorporated into a single job that is easy to maintain and grow in future.
- Easy to implement “Single Source of Truth” because the same copy of data is visible across organization
- Less data footprint due to removal of unnecessary copies of the same data.
- Implementing and Managing “Time travel” i.e. Slowly Changing Dimension is easy
- Improved Data Consistency and Quality
Some complexities attached to conformed dimensions
- Size grows in order to accommodate requirements from different subject areas. This can be seen as dimension tables with several additional columns used specifically for a single subject area.
- Addition of new attributes or changes to logic has impact across multiple teams or subject areas. Time taken to implement changes are high.
- Hierarchy re-organization is not easy to implement, which partially ties to the previous point.
MDM systems also play a major role in modelling approach that implements conformed dimensions. One main difference in a landscape with MDM systems is the role played by ETL tools are reduced to high degree in implementing conformity when compared to Non-MDM environment.
NOTE: In SAP BW, Reference Characteristic based info objects enable the same master data to be referred with different names e.g. CUSTOMER & DEBITOR, but essentially the base data is same irrespective of whichever object.