Conceptual Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 100

Figure 4.2 shows a nonstrict hierarchy where an employee may be assigned
to several cities. Some instances of this hierarchy are shown in Fig. 4.13 . Here,
the employee Janet Leverling is assigned to three cities that belong to two
states. Therefore, since at the instance level a child member may have more
than one parent member, the members of the hierarchy form an acyclic graph.
Note the slight abuse of terminology. We use the term “nonstrict hierarchy”
to denote an acyclic classification graph. We use this term for several reasons.
Firstly, the term “hierarchy” conveys the notion that users need to analyze
measures at different levels of detail; the term “acyclic classification graph”
is less clear in this sense. Further, the term “hierarchy” is already used by
practitioners, and there are tools that support many-to-many parent-child
relationships. Finally, this notation is customary in data warehouse research.

a b

Sales by
employee

Aggregation
by city

Aggregation
by state

Sales by
employee

Aggregation
by city

Aggregation
by state

50
100
20
40
60
70
30

50
100
20
40
60
70
30

Atlanta
170

Georgia
170

Georgia
170

Atlanta
170

Orlando
100

Orlando
200

Florida
400

Florida
200

Tampa
100

Tampa
200

Fig. 4.14 Double-counting problem when aggregating a sales amount measure in
Fig. 4.13 .( a ) Strict hierarchy. ( b ) Nonstrict hierarchy

Nonstrict hierarchies induce the problem of double counting of measures
when a roll-up operation reaches a many-to-many relationship. Let us
consider the example in Fig. 4.14 , which illustrates sales by employees with
aggregations along City and State levels (defined in Fig. 4.13 ), and employee
Janet Leverling with total sales equal to 100. Figure 4.14 a shows a situation
where the employee has been assigned to Atlanta, in a strict hierarchy
scenario. The sum of sales by territory and by state can be calculated
straightforwardly, as the figure shows. Figure 4.14 bshowsanonstrict
hierarchy scenario, where the employee has been assigned the territories
Atlanta, Orlando, and Tampa. This approach causes incorrect aggregated
results, since the employee's sales are counted three times instead of only
once.
One solution to the double-counting problem consists in transforming a
nonstrict hierarchy into a strict one by creating a new member for each
set of parent members participating in a many-to-many relationship. In our
example, a new member that represents the three cities Atlanta, Orlando, and