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

194
Chapter 6
Who and What
Figure 6-24
HV hierarchy map
with effective dating
Effective dating must
be used to correctly
join the hierarchy
map to fact tables
Now the multiple historically correct versions of the hierarchical relationships
must be joined to the correct historical facts to avoid over-counting. This requires
a complex (or theta) join involving the hierarchy map effective dates and the
primary time dimension of the fact table:
Where Reporting_Structure.Employee_Key=Sales_Fact.Employee_Key
and
Sales_Date is Between Reporting_Structure.Effective_Date and
Reporting_Structure.End_Date
To avoid this, the
hierarchy map can
be given its own
surrogate key which
is then added to HR
fact tables
This creates a new
dependency: the
hierarchy map must
be built and updated
before any HR fact
tables
Alternatively, large
HR hierarchies could
be split into a
number of smaller
hierarchies that can
be tracked using
surrogate keys
This is likely to be a very expensive join. To get round this problem for HR fact
tables that must be constantly joined to the hierarchy map, a surrogate key must be
added to the hierarchy map such as HR HM KEY in Figure 6-24. This surrogate
key works like any other dimensional surrogate key to avoid effective dated joins. It
can be added to any specialist HR fact tables to simplify the join to:
Where Reporting_Structure.HR_HM_Key = Salary_Fact.HR_HM_Key
Implementing this surrogate key would require the REPORTING STRUCTURE
table to be built and updated ahead of the SALARY FACT table, like any normal
HR dimension, because the HR HM KEY values must be ready before the fact table
load begins. The simpler CV version of REPORTING STRUCTURE without its
specialist surrogate key does not require this dependency and can be maintained
independently of the facts.
An alternative approach, to avoid effective dating joins or a hierarchy map surro-
gate key, is to break the single organization hierarchy into a number of far smaller
departmental hierarchies by removing the executive level(s) from the hierarchy
map; in this case: Eve Tasks. The smaller hierarchies would be less susceptible to
macro change from above and its resulting ripple effect, which would enable the
employee surrogate key to be used to track all HV changes to employees and their
HR relationships.