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

Dimensional Design Patterns for People and Organizations, Products and Services
177
Solution
A hierarchy map is an additional table that resolves a recursive relationship by
storing all the distant parent-descendent relationships it represents. Recursive
relationships record only immediate parent-child relationships, whereas a hierar-
chy map stores every parent-parent, parent-child, parent-grandchild, parent-great-
grandchild relationship, and so on, no matter how distant. Its structure is best
explained by looking at the BEAM ✲ diagram Figure 6-10 which shows
COMPANY STRUCTURE, a hierarchy map for the Figure 6-9 Customer owner-
ship hierarchy. It is documented as CV, HM to denote that it is a current value
hierarchy map: it records only the current ownership hierarchy because it is based
on the CV definition of PARENT COMPANY in Figure 6-5.
Hierarchy maps
store variable-depth
hierarchies in a
BI-friendly format
Figure 6-10
Hierarchy map table
The first thing you notice about COMPANY STRUCTURE is that it contains far
more rows than the original CUSTOMER dimension. This may explain why the
technique is sometimes referred to as a hierarchy explosion. But don’t worry—it’s
not a very big bang! The row count is rarely an order of magnitude higher, and
hierarchy maps are quite narrow—made up of a pair of surrogate keys and just a
few useful counters and flags. Table 6-1 describes these attributes for COMPANY
STRUCTURE.
Hierarchy maps
explode all the
hierarchical
relationships
(it’s not a big bang)