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

Tampa will be created. However, in this case, a new member must also be
created in the state level, since the three cities belong to two states. Another
solution would be to ignore the existence of several parent members and to
choose one of them as the primary member. For example, we may choose the
city of Atlanta. However, neither of these solutions correspond to the users'
analysis requirements, since in the former, artificial categories are introduced,
and in the latter, some pertinent analysis scenarios are ignored.

Division

DivisionName
Type
Responsible
...

Employee Section

Payroll

EmployeeId
EmployeeName
Position
...

SectionName
Description
Activity
...

Salary

percentage ÷

Fig. 4.15 A nonstrict hierarchy with a distributing attribute

An alternative approach to the double-counting problem would be to
indicate how measures are distributed between several parent members
for many-to-many relationships. For example, Fig. 4.15 shows a nonstrict
hierarchy where employees may work in several sections. The schema includes
a measure that represents an employee's overall salary, that is, the sum of the
salaries paid in each section. Suppose that an attribute stores the percentage
of time for which an employee works in each section. In this case, we depict
this attribute in the relationship with an additional symbol '

', indicating
that it is a distributing attribute determining how measures are divided
between several parent members in a many-to-many relationship.
Choosing an appropriate distributing attribute is important in order to
avoid approximate results when aggregating measures. For example, suppose
that in Fig. 4.15 , the distributing attribute represents the percentage of time
that an employee works in a specific section. If the employee has a higher
position in one section and although she works less time in that section,
she may earn a higher salary. Thus, applying the percentage of time as a
distributing attribute for measures representing an employee's overall salary
may not give an exact result. Note also that in cases where the distributing
attribute is unknown, it can be approximated by considering the total
number of parent members with which the child member is associated. In
the example of Fig. 4.14 , since we have three cities with which the employee

÷