Dimensional Design Techniques

There are various principles regarding dimensional warehouse model design. These topics share our ideas based on customer experiences.

For the sake of simplicity, the number of logical design principles that are applied in the IBM Banking and Financial Markets Data Warehouse dimensional warehouse models is limited. However, by definition of a design model, many alternatives, and other considerations can exist, such as physical constraints, which you cannot always be aware of.

These topics serve as an opportunity to remember basic dimensional design principles based on our interpretation of Ralph Kimball's dimensions principles, but also as a means to generate ideas based on actual customer deployments. The ultimate purpose is to keep improving the usability and quality of the DWM.

Specialized and generalized dimensions
When you are designing a dimension entity, the challenge is often to decide the level of specialization or generalization at which it should be defined.
Shrunken conformed dimensions
A shrunken dimension entity is a perfect subset of a more detailed, granular dimension entity. In this case, the attributes that are common to both the detailed and shrunken subset dimension have the same attribute names, definitions, and domain values.
Dimension keys
Natural keys are the business keys, operational keys, or production keys that are used in source systems and consideration needs to be given as to how they can be used in dimension entities.
Variable-depth hierarchies
Some dimensions can be subject to a hierarchical structure on which one wants to aggregate.
Outrigger entities
In addition to the specific case of the Calendar Period Outrigger and Time Of Day Period Outrigger entities delivered with DWM, other circumstances can also make an outrigger entity permissible.
Bridge and factless fact entities
When a data modeler faces a many-to-many relationship between two dimensions, the dilemma is how to best support it in the DWM. Below are some of the different design options that can be considered.
Slowly changing facts
In the Industry Models, all dimensions are modeled as Slowly Changing Dimension (SCD) Type 2, which means a new row is added to the dimension table whenever the tiniest change occurs in any attribute value.

Parent topic: Dimensional Warehouse Model