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

9
W HY AND H OW
Dimensional Design Patterns for Cause and Effect
There is occasions and causes why and wherefore in all things.
— William Shakespeare (1564–1616), "King Henry V", Act 5, scene 1
How am I doing?
— Ed Koch, Mayor of New York 1978–1989
Some of the most valuable dimensions in a data warehouse attempt to explain why
and how events occur. Why dimensions are used to describe direct and indirect
causal factors. They are often closely linked to the how dimensions that provide all
the remaining event descriptions that are not related to the major who, what, when
and where dimension types. Together why and how represent cause and effect and
complete the 7W dimensional description of a business event. Why and how
In our final chapter we cover dimensional design patterns for describing how
events occur and why facts vary. We focus particularly on bridge table patterns for
representing multiple causal factors and multi-valued dimensions in general. We
describe how bridge table weighting factors are used to preserve atomic fact granu-
larity and avoid ETL time fact allocations. We also describe how bridge tables can
be augmented with multi-level dimensions and pivoted dimensions to efficiently
handle barely multi-valued reporting and complex combination constraints. We
conclude with step, range band and audit dimension techniques for analyzing
sequential events, grouping by facts and handling ETL metadata. This chapter
Direct and indirect causal factors
dimensions are
closely linked: they
describe cause and
effect
describes why and
how dimension
design patterns
Chapter 9 Design
Attributing multiple causes to a fact Challenges
Dealing with barely multi-valued dimensions efficiently At a Glance
Handling complex combination constraints
Understanding sequential behavior
Range band reporting
Tracking data quality and lineage
261