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

190
Chapter 6
Who and What
Multi-Valued Hierarchy Map Pattern
Problem/Requirement
HR hierarchies with
dotted-line
relationships must
rollup employee
activity to multiple
managers
The Pomegranate organization chart, in Figure 6-19, shows one of the main com-
plexities of HR hierarchies: employees can report to more than one manager.
When employee activity is rolled up to a manager or department level these multi-
ple relationships needs to be taken into account. The problem is illustrated by
James Bond, who reports to M, but also has a dotted-line relationship with George
Smiley. This dotted-line represents a temporary or part-time posting with a full-
time equivalence (FTE) of 20%. One must therefore assume, as Smiley might say,
that M receives 80% of Bonds efforts. The dotted-line makes this a multi-parent
variable-depth hierarchy as defined in Chapter 3, with the possibility of multiple
immediate parents (managers) for any child (employee).
Figure 6-19
HR hierarchy
with a dotted line
relationship
A multi-parent
hierarchy is
represented by a
M:M recursive
relationship
A multi-parent hierarchy is another example of a variable-depth hierarchy that can
be represented in a source system by a recursive relationship, only this time it is a
many-to-many (M:M) recursive relationship, as shown in Figure 6-20. The M:M
relationship requires an additional association table containing a pair of employee
foreign keys.
Figure 6-20
M:M recursive
relationship
Solution
M:M recursive relationships can be recorded in a multi-valued hierarchy map (MV,
HM) simply by storing additional rows for the multiple parent relationships but
will require additional attributes (Role Type and FTE) to describe the meaning and
value of each parent-descendent relationship correctly. Figure 6-21 shows the
multi-valued hierarchy map REPORTING STRUCTURE [CV, MV, HM] popu-
lated with all the employee relationships documented on the Figure 6-19 organiza-
tion chart. The first notable thing about this hierarchy map is the number of Bond