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

Division Section Employee

Payroll

DivisionName
Type
Responsible
...

SectionName
Description
Activity
...

EmployeeId
EmployeeName
Position
...

Salary

Fig. 4.16 Transforming a nonstrict hierarchy into a strict hierarchy with an
additional dimension

Janet Leverling is associated, one-third of the value of the measure will be
accounted for each city.
Figure 4.16 shows another solution to the problem of Fig. 4.15 where we
transformed a nonstrict hierarchy into independent dimensions. However, this
solution corresponds to a different conceptual schema, where the focus of
analysis has been changed from employees' salaries to employees' salaries
by section. Note that this solution can only be applied when the exact
distribution of the measures is known, for instance, when the amounts
of salary paid for working in the different sections are known. It cannot
be applied to nonstrict hierarchies without a distributing attribute, as in
Fig. 4.13 .

a b

Employee Section No. employees

by section

Section Division No. employees

by division

E1
E2
E3
E4
E5

3 S1

S1

2

S2 S2 D1 7

S3

S3

2

Fig. 4.17 Double-counting problem for a nonstrict hierarchy

Nevertheless, although the solution in Fig. 4.16 aggregates correctly the
Salary measure when applying the roll-up operation from the Section to the
Division level, the problem of double counting of the same employee will
occur. Suppose that we want to use the schema in Fig. 4.16 to calculate the
number of employees by section or by division; this value can be calculated
by counting the instances of employees in the fact. The example in Fig. 4.17 a
considers five employees who are assigned to various sections. Counting the
number of employees who work in each section gives correct results. However,
the aggregated values for each section cannot be reused for calculating the
number of employees in every division, since some employees ( E1 and E2 in
Fig. 4.17 a) will be counted twice and the total result will give a value equal
to7(Fig. 4.17 b) instead of 5.