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

270
Chapter 9
Why and How
Optional Bridge Pattern
Problem/Requirement
A bridge table over-
complicates queries
that rollup a barely
multi-valued
dimension to a
single-valued level
Causal (why) dimensions are not the only multi-valued dimensions. Frequently the
who dimension for a single fact can be multi-valued. For example, multiple doctors
can perform a surgical procedure and multiple customers can purchase a joint
policy. Multi-valued uses of who dimensions can be implemented by building
bridge tables as in the previous pattern. However, using bridge tables in every
query can be excessive when a multi-valued who dimension is only barely multi-
valued and the majority of queries want to rollup the facts past the multiple indi-
viduals to a single-valued who hierarchy level. For example, most product sales are
made by a single employee but a small percentage are made by teams of two
employees working together. Employee level sales reports need to split any team
sales facts between employees based on their seniority or role within their teams,
but most reports only need to total sales to the branch level or above—ignoring the
members of a team by using the branch where the team is based.
Solution
Use a multi-level
(ML) dimension to
avoid joining
through a bridge
table
When a dimension is barely multi-valued, a bridge table can be avoided by making
the dimension multi-leveled (ML) so that it contains additional records for the
small number of multi-valued groups needed. For example, the multi-level
EMPLOYEE [HV, ML] dimension, in Figure 9-9, holds normal employee records
for sales consultants and additional records for sales teams made up of two or more
consultants. It contains example dimension members for two employees Holmes
and Watson, and handles facts where they have worked together (when the game is
afoot) by treating their team “Holmes & Watson” as a pseudo-employee. This
allows EMPLOYEE to join directly to the sales fact table (as in Figure 9-10) and
rollup all their individual and joint sales to the appropriate branch at the time of
sale. For example, Watson’s individual sales will be rolled up to Afghanistan or
London depending on when they occurred. His joint sales with Sherlock Holmes
will always be rolled up to London.
Figure 9-9
Multi-level
EMPLOYEE
dimension
containing additional
team rows
All SK column examples values will be replaced by integer surrogate keys in the
physical model. The (2) records show the effect of an HV attribute change for
employee John Watson (moving back to London).