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

Dimensional Design Patterns for People and Organizations, Products and Services
175
Figure 6-8
BEAM✲ recursive
relationship
Recursion is a very efficient way of storing parent-child relationships. However
merely storing the information is only a fraction of the DW/BI story—stakeholders
have to be able to use it for analysis. Because a parent customer can in turn be
owned by another customer and that in turn owned by another, the recursive
relationship can represent a variable-depth hierarchy. Wherever a hierarchy exists
stakeholders will invariably want to use it to explore the facts. An ownership
hierarchy offers the possibility of rolling up individual customer activity to the
topmost owners to see who becomes significant when all their indirect sales reve-
nue is consolidated. This would be quickly followed by drill down analysis on
ownership to see a breakdown of sales revenue by subsidiaries.
Recursive
relationships are
often used to
represent variable-
depth hierarchies
Variable-Depth Hierarchies
Customer ownership is a classic example of a variable-depth hierarchy. Some
business customers will be self-contained or privately-owned companies, repre-
senting a hierarchy of only a single level. But other customers may be the top,
middle, or bottom of a deep hierarchy of corporate ownership (stretching all the
way to Liechtenstein or Delaware!). The Figure 6-9 organization chart reveals that
all the Figure 6-8 examples customers are ultimately owned by Pomegranate.
Customer
ownership is a
classic example of a
variable-depth
hierarchy, best
illustrated by an
organization chart
If you have difficulty giving meaningful names to the levels in a hierarchy, it is a
strong clue that you need to model it as a variable-depth hierarchy. The levels in a
balanced or ragged hierarchy normally have distinct names because they repre-
sent different things or concepts; for example, day and month in a balanced time
hierarchy or street and country in a ragged geographic hierarchy. The levels in a
variable-depth hierarchy do not have distinct names, because they typically
represent things of the same type; for example, each level in a variable-depth
customer ownership hierarchy is a customer.