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

184
Chapter 6
Who and What
example, when an employee reports to a different manager—is handled as a logical
deletion and a new relationship.
Child history is
tracked by adding
every HV child key
value to the hierarchy
map. This should be
the default for most
hierarchy maps
Child History: tracking changes to HV attributes of a child level; for example, the
location of a subsidiary company or an employee’s marital status changes. This
involves populating the hierarchy map with every surrogate key value for a child
and adding new rows with the new child key value for every parent level above it,
every time a child’s HV attribute changes. For a hierarchy map built from an HV
dimension, such as COMPANY STRUCTURE, it must at least track child history
to correctly join to child level facts and rollup all their history.
Tracking the historical version (HV) of a variable-depth hierarchy is a particularly
vexing design challenge, not to be undertaken simply because all other attributes
of a dimension have been defined as HV.
To track full history a
recursive key in the
dimension must be
defined as HV
HV RK attributes
cause a ripple effect.
A change to a parent
HV attribute will
cause all its
descendants to be
changed and new
records to be added
to the hierarchy map
Ripple effect growth
can be manageable if
a dimension contains
a small number of
small hierarchies
Historical Value Recursive Keys
If you want to expand COMPANY STRUCTURE to track full hierarchy history so
that the historically correct ownership hierarchies could be rolled up or filtered
using historically correct parent company values, the PARENT KEY [RK] in the
CUSTOMER dimension must first be redefined as HV and the dimension popu-
lated accordingly with the historically correct recursive data.
The Recursive Key Ripple Effect
Figure 6-16 shows what happens to the CUSTOMER dimension when a change
occurs to a high level parent company, such as Pomegranate if PARENT KEY is
defined HV RK. When Pomegranate is upgraded from “Good” to “Great” a new
record—with a surrogate key value of 106—is created to record the change to the
HV attribute CUSTOMER CATEGORY. This new CUSTOMER KEY value of 106
must be reflected in the PARENT KEY of all of Pomegranate’s children. As
PARENT KEY is also HV, new child customer records must also be created to
preserve its history. This causes a ripple effect as each new CUSTOMER KEY must
in turn be reflected in the PARENT KEY of its children right down to the bottom
of the ownership hierarchy. What would have been a micro-level change to a single
customer (Pomegranate) becomes a macro-level change to 9 customers in total—
Pomegranate and all of its 8 subsidiaries. This is startling enough, but these 9 new
rows in the Customer dimension translate to 25 new rows in the COMPANY
STRUCTURE hierarchy map.
Using an HV recursive key to track every parent or child change will cause a
dimension to grow more quickly, but the technique is still viable if hierarchies
make up a small amount of the data. For example, if only a small percentage of
customers are owned by another customer (PARENT KEY is mainly NULL) and
ownership hierarchies are typically only a couple of levels deep, the resulting
additional growth would be manageable.