In Data Warehouse parlance, dimensions are entities that serve as a mechanism to slice and dice measures stored in fact tables. Data Warehouse tables are often arranged as set of data marts, each catering to a specific domain or problem. This leads to a silo architecture where each domain is distinct and similar dimensions are represented differently in each data mart making cross-domain analysis very difficult if not impossible.
SHR solves this issue by enforcing conformance across a set of standard dimensions. Dimensions are conformed when they are either exactly the same or one is a subset of the other. These dimensions serve as an entry point or mechanism to slice and dice data across several data marts. SHR implements several design patterns for Dimension Conformance.
The following figure depicts a pattern where the same dimension is used across data marts.
In the above example, a user analyzing Fact1 in the context of Dim1 can link to Fact2 via the ConfDim; here ConfDim acts as a bridge between the two fact tables, hence enabling cross analysis between the two. The above pattern is used to implement SHR’s DateTime dimension.
The pattern depicted below achieves conformance by treating one dimension as a subset of another “master” conformed dimension.
This pattern is used extensively in SHR. The rows of ConfDim, are a mathematical subset of MastConf and share the same keys; while the ConfDim may have several additional columns to the ones contained in MastConf, it will always contain the columns present in MastConf. A typical example of this in SHR is the modeling of Configuration Items (CI) defined in RtSM. This pattern allows MastConf dimension to be restricted to common CI attributes and not be burdened with domain/CIType specific attributes. All type specific attributes are relegated to the appropriate ConfDim tables.
Two dimensions can be conformed by replicating them across data marts as shown below.
The above pattern demands that both the ConfDim dimension tables be kept absolutely the same at all times. This pattern is not used in SHR today.
Conformed dimensions are indeed the secret sauce in SHR architecture and enable all the cross domain goodness it has to offer. It is a powerful design pattern that requires a fairly sophisticated ETL layer but yields an elegant and easy to use schema for advanced in-depth analysis.
References: “The Data Warehouse Toolkit” by Ralph Kimball and Margy Ross
Product Marketing Manager for HP Application Performance Management suite of software products. Before this role, I worked in the HP StorageWorks Division working as both a Product Marketing Manager overseeing enterprise hardware and software, as well as working as Business Development Manager for the Enterprise Services channel.