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

Dimensional Design Patterns for People and Organizations, Products and Services
185
Figure 6-16
Recursive key
ripple effect
Ripple Effect Benefits
Using surrogate keys to track every type of change ensures that HV hierarchy maps
will correctly join the correct historical facts to the correct historical hierarchies
using simple SQL. Each new surrogate key will automatically join the historical
facts to the correct historical parent version through the correct hierarchical path
using simple inner joins with no additional date logic—just like a normal slowly
changing dimension. So although the HV hierarchy map requires additional rows
to record each historical version of a hierarchy, thanks to the ripple effect, its
structure and usage remains the same.
The surrogate key
ripple effect keeps
the correct HV joins
between parent,
hierarchy map and
fact table, simple and
efficient
Small and relatively stable variable-depth hierarchies can be tracked using an HV
recursive key, just like any other HV attribute. While the HV recursive key will
cause some additional growth in the dimension, it keeps the joins between hierar-
chy maps and fact tables simple and efficient.
Ripple Effect Problems
Unfortunately some variable-depth hierarchies are too large or too volatile to be
tracked using an HV recursive key. A human resources hierarchy is a classic
example of this, because it is one single hierarchy which contains all the employees
in the employee dimension. A minor HV change at the highest level would result
in the entire active employee population being issued with new recursive keys as
the change ripples down to the ‘shop floor’.
Avoid tracking history for large or volatile variable-depth hierarchies by using an
HV recursive key, because it will cause explosive growth in the dimension. In-
stead, track hierarchy history outside of the dimension by adding effective dating
attributes to hierarchy maps. See the HV MV HM pattern shortly.
HR hierarchies are
too large and volatile
for their HV recursive
keys to be stored in
employee dimensions