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

EmplSection

EmployeeKey
SectionKey
Percentage

Payroll Employee Section Division

EmployeeKey
...
Salary

EmployeeKey
EmployeeId
EmployeeName
Position
...

SectionKey
SectionName
Description
DivisionKey
...

DivisionKey
DivisionName
Type
...

Fig. 5.11 Relations for the nonstrict hierarchy in Fig. 4.15

as shown in Fig. 4.16 . Then, the corresponding mapping for a strict hierarchy
can be applied. The choice between the two solutions may depend on various
factors, namely,

￿ Data structure and size: Bridge tables require less space than creating
additional dimensions. In the latter case, the fact table grows if child
members are related to many parent members. The additional foreign key
in the fact table also increases the space required. In addition, for bridge
tables, information about the parent-child relationship and distributing
attribute (if it exists) must be stored separately.
￿ Performance and applications: For bridge tables, join operations, calcula-
tions, and programming effort are needed to aggregate measures correctly,
while in the case of additional dimensions, measures in the fact table
are ready for aggregation along the hierarchy. Bridge tables are thus
appropriate for applications that have a few nonstrict hierarchies. They
are also adequate when the information about measure distribution does
not change with time. On the contrary, additional dimensions can easily
represent changes in time of measure distribution.

Finally, still another option consists in transforming the many-to-many
relationship into a one-to-many relationship by defining a “primary” rela-
tionship, that is, to convert the nonstrict hierarchy into a strict one, to which
the corresponding mapping for simple hierarchies is applied (as explained in
Sect. 4.3.2 ).

5.6 Advanced Modeling Aspects

In this section, we discuss how facts with multiple granularities and many-
to-many dimensions can be represented in the relational model.