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

192
Chapter 6
Who and What
All multi-parent hierarchies—even balanced ones with a fixed number of named
levels—require hierarchy maps to store their multiple parent relationships, to-
gether with their appropriate weighting factors.
Handling Multiple Weighting Factors
Weighting factors
become more
complex when there
are multiple dotted-
line relationships
The weighting factors in Figure 6-21 are relatively straightforward, because there is
only one part-time employee (Bond) and he does not manage anyone else. How-
ever, if circumstances were to change, the allocations become more complicated.
For example, if Moneypenny permanently divides her time equally between M and
Bond, a new solid-line needs to be drawn between her and Bond worth 50% FTE,
as shown in Figure 6-22.
Figure 6-22
Rolling up
weighting factors
Distant relationship
weighting factors
are calculated by
multiplying the
weighting factors
of the intermediate
direct relationships
In the recursive source data this change would create one new association record in
the EMPLOYEE_EMPLOYEE table (Figure 6-20) for Bond-Moneypenny, and
update the existing M-Moneypenny record to 50% FTE. In the unraveled hierarchy
map more work is required, because records for each new distant relationship need
to be inserted and all the existing distant relationships need to be updated with the
appropriate weighting factors. Figure 6-22 shows how the new direct Bond-
Moneypenny permanent relationship also creates an distant temporary relationship
between Smiley and Moneypenny, with a weighting factor of 10%. This is calcu-
lated by multiplying all the weighting factors of the direct relationships between
the two distant employees: 0.2 × 0.5 = 0.1 or 10%.
Updating a Hierarchy Map
If hierarchy history is
not required, it is
often easier to drop
and rebuild hierarchy
maps rather than
update them
Figure 6-23 shows 6 new rows and 9 updated rows in the REPORTING
STRUCTURE hierarchy map after all the necessary processing has been per-
formed. Given that the table was initially 18 rows, dealing with only one new
relationship has updated 50% of the original table, and caused it to grow by 33%.
Because seemingly small changes can cause a large number of complex updates to a
hierarchy map, dropping and rebuilding the map is often easier than updating it—
if you do not need to track history.