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

Dimensional Design Patterns for People and Organizations, Products and Services
179
Hierarchy Maps and Type 2 Slowly Changing Dimensions
The observant reader may have noticed three seemingly duplicate records in
COMPANY STRUCTURE (Figure 6-10) relating to Pomegranate and PicCzar
Movies as subsidiaries. These are revealed to be subtly distinct when you see the
surrogate key values in Figure 6-11 which show that the hierarchy map contains
each current parent key to historic subsidiary key combination. This is necessary
even though the hierarchy map is defined as CV (records the current hierarchy
shape only) so that the current hierarchy can still be used to roll up all the fact
history. To do this it must contain every historical subsidiary surrogate key so that
it can be joined to all the historical facts just like the Type 2 SCD Customer dimen-
sion that it is built from. This design requirement is documented by modeling the
SUBSIDIARY KEY as HV—even though the PARENT KEY and all other hierarchy
map attributes are CV.
CV hierarchy maps
must contain all the
HV surrogate key
values to join to
every historic fact,
even though they do
not track hierarchy
history
Figure 6-11
Hierarchy map with
Type 2 SCD
surrogate keys
When HV customer attributes change, their new surrogate key values must also
be inserted into the company ownership hierarchy map, as new SUBSIDIARY KEY
values even if their ownership remains unchanged.
Using a Hierarchy Map
Joining the customer dimension directly to a fact table in the normal way allows
queries to report the facts by the customers directly involved. To report the same
facts rolled up to parent customer levels you insert the hierarchy map between the
customer dimension and the fact table, and join on the parent and subsidiary keys
as shown in Figure 6-12. To make the business meaning of joining through the
hierarchy map more explicit you should create a role-playing view of the
CUSTOMER dimension called PARENT CUSTOMER and use that to define the
join path in a BI tool.
Parent totals are
queried by joining a
dimension to the
facts through a
hierarchy map
Figure 6-12
Using a hierarchy
map table to rollup
revenue to the
parent customer
level