Logical Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 131

City

CityKey
CityName
CityPopulation
CityArea
...

Employee

State

EmployeeKey
EmployeeID
FirstName
LastName
Title
BirthDate
...

StateKey
StateName
EnglishStateName
StateType
...

Territory

TerritoryKey
TerritoryName
Description

Fig. 5.10 Relations for the parallel dependent hierarchies in Fig. 4.12

Note that shared levels in parallel dependent hierarchies are represented in
one table ( State , in this example). Since these levels play different roles in each
hierarchy, we can create views in order to facilitate queries and visualization.
For example, in Fig. 5.10 ,table States contains all states where an employee
lives, works, or both. Therefore, aggregating along the path Employee → City
→ State will yield states where no employee lives. If we do not want these
states in the result, we can create a view named StateLives containing only
the states where at least one employee lives.
Finally, note also that both alternative and parallel dependent hierarchies
can be easily distinguished at the conceptual level (Figs. 4.9 and 4.12 );
however, their logical-level representations (Figs. 5.9 and 5.10 ) look similar
in spite of several characteristics that differentiate them, as explained in
Sect. 4.2.5 .

5.5.6 Nonstrict Hierarchies

The mapping rules specified in Sect. 5.3 , applied to nonstrict hierarchies,
creates relations representing the levels and an additional relation (called
a bridge table ) representing the many-to-many relationship between them.
An example for the hierarchy in Fig. 4.15 is given in Fig. 5.11 , where the bridge
table EmplSection represents the many-to-many relationship. If the parent-
child relationship has a distributing attribute (as in Fig. 4.15 ), the bridge
table will include an additional attribute for storing the values required for
measure distribution. However, in order to aggregate measures correctly, a
special aggregation procedure that uses this distributing attribute must be
implemented.
Recall from Sect. 4.2.6 that another solution is to transform a nonstrict
hierarchy into a strict one by including an additional dimension in the fact,