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

Dimensional Design Patterns for Cause and Effect
Summary
Why dimensions are used to store direct and indirect causal reasons. Direct causal factors such
as price discounts are typically easier to implement and attribute to facts than indirect factors
because they are captured as part of a business event and do not need to be inferred from addi-
tional internal or external sources.
Unstructured why details are often captured as free text comments. These should be stored in a
COMMENT why dimension rather than as degenerate dimensions within fact tables.
Multi-valued (MV) bridge tables are used to resolve multiple causal factors and other multi-
valued dimension relationships. Bridge tables avoid having to change the natural atomic granu-
larity of a fact table and hard-coding fact allocations at ETL time. Using a bridge table allows BI
users to choose how to weight the facts at query time. They also avoid multi-valued issues alto-
gether when queries do not use the multi-value dimension.
Optional bridge tables and multi-level dimensions that share common surrogate keys can be
used to efficiently handle barely multi-valued dimensions. Queries that do not need to deal with
a multi-valued dimension level and its weighting factor can attach the multi-level dimension di-
rectly to the facts to rollup to single-valued hierarchy levels.
Pivoted dimensions (PD) are built by transposing row values into column flags or column
counts. They are used to simplify combination constraints that would otherwise be difficult to
place across multiple-rows. Pivoted dimensions are often implemented as swappable versions of
multi-valued bridge tables. For query flexibility it is useful to have both the row-oriented bridge
table for grouping and the column-oriented pivoted dimension for combination filtering. It is
also easier to build a pivoted dimension once the bridge table is in place.
Degenerate how dimension (DD) transaction IDs ensure that facts are traceable back to source
systems. They also provide unique event counts for use in multi-valued queries.
Physical how dimensions are typically non-conformed dimensions that are specific to a single
fact table. These miscellaneous dimensions provide a home for the unique combinations of de-
generate dimensions that are too numerous to leave in the fact table. They reduce the size of fact
tables and make it easier for users to browse the dimensional values combinations.
Range Band dimensions support the ad-hoc conversion of continuously variable facts and di-
mensional attributes into report-friendly discrete bands for grouping and filtering.
Step dimensions allow facts to be analyzed using their relative position within event sequences.
They enable BI users to discover events that closely follow or precede other significant cause
and effect events. The help the data warehouse to tell better stories.
Audit dimensions make ETL data lineage and data quality metadata available within star sche-
mas so that it can easily be used with BI reports.
283