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

Dimensional Design Patterns for People and Organizations, Products and Services
181
Figure 6-13
Using a hierarchy
map to browse a
customer hierarchy
and report facts at
the subsidiary level
Hierarchy Sequence
Sorting on company name would destroy the hierarchical order. But sorting by
hierarchy level is no better, because this would display all the level 1 customers,
followed by all level 2 customers, then all level 3 customers, and so on. You would
not be able to tell which level 2 customer owns which level 3 customers. To solve
this problem the hierarchy map needs a Sequence Number attribute that sorts the
nodes in the hierarchy correctly “top to bottom before left to right” as shown in
Figure 6-14. The Sequence Number can then be used to sort the decedents of a
customer (top to bottom) ahead of the next customer (left to right) at the same
level; i.e., ensures that all the level 3 subsidiaries of a level 2 customer will be
displayed before the next level 2 customer is displayed.
To display a
hierarchy correctly
the hierarchy map
must contain a
hierarchy sequence
number that sorts
top to bottom before
left to right
Figure 6-14
Hierarchy
sequence
numbers
The report in Figure 6-15 shows how you use SEQUENCE NUMBER with
COMPANY LEVEL to display the hierarchy, by sorting down the page on
SEQUENCE NUMBER, and indenting across the page on COMPANY LEVEL.
The following snippet of Oracle SQL shows how an indenting Company Name
could be defined in a BI tool:
LPAD( ' ', 3*(Company_Level-1)) || Customer_Name
This will indent each level 2 customer name by three spaces, each level 2 by six
spaces, and so on. A level 1 customer would display on the left margin (indented by
zero spaces).
To allow new nodes to be added to a hierarchy without updating the existing
sequence numbers you can create the initial sequence numbers in increments of
10 or 100 depending on the growth you expect.
Sort hierarchy
reports by sequence
number and indent
using level