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

180
Chapter 6
Who and What
Once PARENT CUSTOMER, COMPANY STRUCTURE and BILLING FACT are
correctly joined, it becomes simple for stakeholders to ask:
What is the total revenue for Pomegranate,
including all of its subsidiaries?
Rolling up
descendent facts is
straightforward once
the hierarchy map is
joined correctly
Descendent levels
can be filtered using
!
By constraining a query on Parent_Customer = ‘Pomegranate’, one row
from PARENT CUSTOMER joins to the hierarchy map, finding 11 matching
subsidiary records. These 11 subsidiary keys are then presented to the fact table,
and their revenue is aggregated accordingly. Thanks to the parent-parent rows,
where both the parent and subsidiary keys represent Pomegranate, the total reve-
nue automatically includes any work done for Pomegranate directly. To exclude
this from the total you simply add Company_Level <> 1 to the query con-
straint, and only nine keys will be presented to the fact table.
Queries can be further refined using COMPANY LEVEL and LOWEST
SUBSIDIARY. For example:
the LEVEL and
LOWEST columns
in the hierarchy map
To get the total revenue just for customers that are directly owned by
Pomegranate, change the constraint to Company_Level = 2
To get the total revenue for only the Pomegranate companies that do
not own other customers, add Lowest_Subsidiary = ‘Y’
One of the strengths of the hierarchy map is that all of these questions can be
answered without knowing (or caring) how many subsidiaries or levels there are.
A CV hierarchy map such as COMPANY STRUCTURE that does not track parent
history is not symmetrical for query purposes if its matching dimension contains
Type 2 SCD surrogate keys. You cannot reverse the joins in Figure 6-12 and use it
to roll up all the historical revenue for the parents of a selected subsidiary, be-
cause the map only contains the current surrogate key values for each parent. If
there is a requirement to roll up historical parent facts using current subsidiary
descriptions a different version of the hierarchy map must be built that contains
the full history of parent surrogate keys.
Displaying a Hierarchy
A hierarchy map
can be used to
display a hierarchy
by joining a parent
view of a dimension
to the dimension
The example queries described so far use the hierarchy map to aggregate facts to
the parent level. But the hierarchy map can also be used to display all the levels of a
hierarchy on a report. To do this you join the customer dimension to the parent
customer view through the hierarchy map, as shown in Figure 6-13. This gives you
both a parent customer name and a (subsidiary) customer name to group on and
display in your reports—allowing reports to display facts for each level in the
hierarchy. However, to make sense of the hierarchy itself on such reports, the
subsidiaries have to be displayed in the correct hierarchy sequence.