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

Dimensional Design Patterns for People and Organizations, Products and Services
193
Figure 6-23
Updating a
hierarchy map
Historical Multi-Valued Hierarchy Maps
Earlier, we described how the customer ownership hierarchy map was able to track
full hierarchy history without schema modification if it was populated from an HV
version of its recursive key held in the customer dimension. This is not an option
for the multi-parent HR hierarchy because multiple MANAGER KEYs cannot be
stored in an employee dimension. Even if HR was a single parent hierarchy, it is
too large and too volatile for an HV recursive key to be viable—the ripple effect
would cause uncontrollable growth in the dimension. Instead the HR hierarchy
map must be modified to use effective dating to track history. To track history, large
Figure 6-24 shows an HV version of the REPORTING STRUCTURE hierarchy
map that includes the effective dating attributes EFFECTIVE DATE, END DATE
and CURRENT typically found in HV dimensions. These attributes allow BI Users
to browse both the current hierarchy (Where CURRENT =‘Y’) and any point in
time hierarchy (e.g., where ‘31/3/2011’ Between EFFECTIVE_DATE and
END_DATE). To understand how this hierarchy map records changes to an
employee, a manager and their relationship, take a look at the timelines in Figure
6-25 for Bond, M and Bond’s HR relationships during the first six months of 2011. Effective date, end
Consequences
With its effective dated relationships, the REPORTING STRUCTURE [HV] map
can be used to rollup employee facts using historically correct hierarchy descrip-
tions, but its join to fact tables is more complex than before. When the hierarchy
map did not contain historical relationships the join to SALES FACT was simply:
Where Reporting_Structure.Employee_Key = Sales_Fact.Employee_Key
volatile, multi-valued
hierarchy maps
cannot rely on HV
recursive keys.
Instead effective
dating must be used
date and a current
flag should be added
to HV, MV, HM tables