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

178
Chapter 6
A hierarchy map
treats each
dimension member
as a parent and
records all its child,
grandchild etc.
relationships
Who and What
COMPANY STRUCTURE contains 11 rows where Pomegranate is the parent: one
for each subsidiary customer on the organization chart in Figure 6-9. Explicitly
storing a relationship between all Pomegranate subsidiaries and their topmost
parent makes it easy to answer any Pomegranate-related parent questions. If they
were the only questions, these would be the only rows needed in the map but to
support fully flexible ad-hoc reporting to any level of ownership, the map needs to
contain additional rows where each of the subsidiary customers is treated as the
parent of its own small hierarchy.
Table 6-1 A TTRIBUTE T YPE U SAGE
COMPANY Parent Key Surrogate Key Foreign key to the CUSTOMER dimension
playing the role of parent company. Part of the
primary key.
Subsidiary
Key Surrogate Key Foreign key to the CUSTOMER dimension
playing the role of subsidiary company. Part of the
primary key.
Company
Level Integer The level number of the subsidiary company.
Level 1 is the highest company in a hierarchy
Sequence
Number Integer Sort order used to display subsidiaries in the
correct hierarchical order.
Lowest
Subsidiary [Y/N] Flag Y indicates that the Subsidiary Key is the lowest
company in an ownership hierarchy, it is not the
owner of any other customer.
Highest
Parent [Y/N] Flag Y indicates that the Parent Key is the highest
company in an ownership hierarchy, it is not
owned by any other customer.
STRUCTURE
Attributes
PARENT KEY and SUBSIDIARY KEY in Figure 6-11 are documented as SK. They
contain company names for model readability (in true BEAM✲ fashion). The
physical database columns will contain integer surrogate keys.
If you know how
many members
there are at each
level you can
calculate the size
of a hierarchy
You can calculate the number of hierarchy map rows needed for a complete hierar-
chy by summing the number of members at each level times their level. For the
data shown on the organization chart in Figure 6-10 that would be 1×1 + 3×2 +
3×3 + 2×4 = 24 rows. COMPANY STRUCTURE has three more rows to handle
slowly changing customer descriptions for customers (Pomegranate and PicCzar
Movies) in the HV CUSTOMER dimension (Figure 6-8). They make the calcula-
tion 2×1 + 4×2 + 3×3 + 2×4 = 27 rows.
A quick estimate of the number of rows in a hierarchy map is:
dimension members × (max levels – 1)
For the 11 Pomegranate related dimension members (only 9 customers but there
are 2 additional versions of the slowly changed customers) with 4 levels the
estimate would be 33. This simple formula always gives you an overestimate,
which is good! You will be pleasantly surprised when the map is populated.