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

Dimensional Design Patterns for Cause and Effect
271
Figure 9-10
Joining the multi-
level dimension
directly to the facts
For most queries that need to total sales the efficient direct join will be ideal, but
those queries that calculate team sales splits will still need to treat EMPLOYEE as
multi-valued. They can do so by joining through an optional bridge table, such as
TEAM shown in Figure 9-11, that provides the team split percentage (the equiva-
lent of a weighting factor) for each team member. The presence of the optional
bridge effectively makes the direct join a shortcut that can be used whenever
PERCENTAGE is not needed.
A bridge table will
still be needed for
queries that must
use a multi-valued
weighting factor
Figure 9-11
Joining through the
TEAM optional
bridge table
To be able to optionally join through a bridge, or directly to the facts, both the
optional bridge table and the ML dimension must use the same surrogate keys,
effectively making them swappable dimensions. For example, the Figure 9-12
BEAM ✲ diagram for TEAM shows that the bridge table key TEAM KEY is actually
a foreign key role of EMPLOYEE KEY. TEAM uses the special pseudo-employee
key values, shown in Figure 9-9, to record the members and percentage splits for
each team. It also uses normal employee key values on the records where TEAM
KEY and MEMBER KEY are the same and PERCENTAGE is 100. These act like
teams of one, allowing the bridge table to join employees to 100% of their individ-
ual sales facts—the equivalent of a direct join.
An optional bridge
table must use the
same surrogate key
values as its multi-
level dimension
Figure 9-12
Multi-valued and
Multi-leveled
bridge table