A conformed dimension is a set of data attributes that have been physically implemented in multiple database tables using the same structure, attributes, domain values, definitions and concepts in each implementation. In below article we will discuss conformed dimension in details.
Conformed Dimension Kimball definition
Dimension tables conform when attributes in separate dimension tables have the same column names and domain contents. Information from separate fact tables can be combined in a single report by using conformed dimension attributes that are associated with each fact table. When a conformed attribute is used as the row header (that is, the grouping column in the SQL query), the results from the separate fact tables can be aligned on the same rows in a drill-across report. This is the essence of integration in an enterprise DW/ BI system. Conformed dimensions, deﬁned once in collaboration with the business’s data governance representatives, are reused across fact tables; they deliver both analytic consistency and reduced future development costs because the wheel is not repeatedly re-created.
Check here for Degenerate Dimension in Data warehouse
What is conformed Dimension ?
In data warehouse , conformed Dimension is the dimension which has the same meaning and content when being referred from different fact tables. A conformed dimension can refer to multiple tables in multiple data marts within the same organization. For example : Time is a common conformed dimension because its attributes (day, week, month, quarter, year, etc.) have the same meaning when joined to any fact table. Similarly Customer dimension will have the same meaning irrespective of which FACT table we are referring to.
Conformed dimensions allow facts and its measures to be accessed in the same way across multiple facts, ensuring consistent reporting across the enterprise.
Check here for Junk Dimension in Data warehouse
For two dimension tables to be considered as conformed, they must either be identical or one must be a subset of another. There cannot be any other type of difference between the two tables. For example, two dimension tables that are exactly the same except for the primary key are not considered conformed dimensions.
Why are Conformed Dimensions Important?
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.
Advantages of Conformed Dimensions
- Independent data marts become a part of fully integrated data warehouse.
- Deliver a consistent view across the business.
- Data should never be defined for a specific function or department. Good data is one, which is widely shareable and conformed dimensions help in doing that.
Defining Conformed Dimension
Conformed dimensions should be defined at the most granular level so that each record in these tables corresponds to a single record in the fact able.
Points to consider while standardizing dimensions
- A set of dimensions (customers, products or geography) can be similar for Business Unit1 and Business Unit 2, but their hierarchical arrangements can be different.
- Hierarchies may change over a period of time, business unit re-org or product re-categorization.
Check here for Role Playing Dimension in Data warehouse
Having conformed dimensions, with above points considered:
- The first thing is to arrive at a basic set of dimensions across as many data marts/business units as possible.
- Compile a combined, de-duplicated, complete set of members for each dimension across all data marts.
- Compile the linkages between members with each dimension by hierarchy.
- All hierarchies are part of the enterprise but there are certain hierarchies, which belong to specific data mart/business unit. This would require an entity, which would store the information of hierarchy and the owner.
So in above data warehouse tutorial ,we have gone through some basic concept of conformed dimension , along with importance and its advantages.