My first Publication Agile-Data-Warehouse-Design-eBook | Page 222

202
Chapter 6
Who and What
Figure 6-31
Component
Analysis
Consequences
Don’t try to use the PARTS EXPLOSION hierarchy map pattern to describe the bill
of materials for anything as complex as a real car, submarine or aircraft—unless
you are prepared for a very large table.
Summary
Mini-dimensions track historic values for very large volatile dimensions, like CUSTOMER, that
cannot use the Type 2 SCD technique. Volatile HV attributes are moved to a separate mini-
dimension to keep the size of the main dimension under control and the historical values are
related back to the main dimension via fact table relationships. Mini-dimensions typically band
high cardinality values to control their size and volatility and to provide better report row
headers and filters.
Snowflaking makes sense for very large dimensions when a large set of lower-cardinality,
seldom used attributes can be normalized into outriggers. The calendar dimension can be a
particularly useful outrigger for any dimension that contains embedded dates.
Swappable dimensions (SD) are used to break up large mixed type dimensions into specialist
subsets that are easier to use and faster to query. Swappable dimensions can be swapped into a
star schema in place of one another because they share a common surrogate key.
Hybrid SCD requirements for current value and historical value reporting are best handled by
creating separate hot swappable CV versions of HV dimensions. These CV dimensions can be
created as material views using simple self-joins of HV dimensions.
Hierarchy maps (HM) are used to store variable-depth hierarchies in a report-friendly format
and avoid recursive structures that cannot easily be queried by BI tools.
Multi-valued hierarchy maps (MV HM) are used to represent multi-parent hierarchies that are
typically stored in source systems as M:M recursive relationships.
Multi-level dimensions (ML) describe business events that vary in their level of dimensional
detail. A multi-level dimension will contain additional special value members that represent
higher levels in the dimension’s hierarchy.