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

272
Chapter 9
Adding multiple
levels to the bridge
table increases
reporting flexibility
Why and How
TEAM contains an additional attribute MEMBERSHIP TYPE to describe these
“Team Split” and “Employee” records. It also records a third membership type of
“Team” for some of the 100% records (highlighted in bold in Figure 9-12). These
records allow the bridge to join facts to the team level records (e.g. “Holmes &
Watson”) in EMPLOYEE as well as normal employee records. This makes TEAM
[HV, MV, ML] a multi-level (ML) as well as multi-valued bridge table, enabling it
to be used to flexibly query both team sales and employee sales in a single pass. For
example, consider the following query:
Select Employee_Name, Sum(Revenue)
From Employee E, Team T, Sale_Fact S
where E.Employee_Key = T.Member_Key and
T.Team_key = S.Employee_Key
Group by Employee_Name
Bridge table levels
must be carefully
filtered to avoid
double-counting
This returns both team sales and employee total sales (including their team sales)
— a very useful report — but care must be taken not to add a grand total because it
would double-count team sales. Filtering on MEMBERSHIP TYPE removes this
limitation and makes the following additive reports available:
employee individual sales (excluding their team sales):
Where Membership_Type = "Employee"
employee team sales (excluding their individual sales)
Where Membership_Type = "Team Split"
employee total sales
Where Membership_Type in ("Employee", "Team Split")
team sales:
Where Membership_Type = "Team"
teams sales and employees individual sales (excluding their team sales):
Where Membership_Type <> "Team Split"
This last filter is the equivalent of the shortcut join that avoids the bridge table.
Consequences
Multi-level bridge
tables are complex
to build and
complex to use
correctly
Multi-level bridge tables are complex. Including the multiple levels provides
complete reporting flexibility, but at a price. Queries must filter the bridge table
correctly to avoid double-counting or misinterpreting the results. Keeping the
multiple levels in the dimension and bridge synchronized also requires significant
additional ETL processing. For example, one change to Watson’s location requires
2 new rows in EMPLOYEE and 4 new rows in TEAM to keep Watson and the
Holmes & Watson team in sync. All these new rows have been marked (2) in
Figures 9-9 and 9-12 to highlight the second versions of Watson, his team, and his
team splits in the two tables created by his return from Afghanistan to London.