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

In summary, nonstrict hierarchies can be handled in several ways:
￿ Transforming a nonstrict hierarchy into a strict one:
- Creating a new parent member for each group of parent members linked
to a single child member in a many-to-many relationship.
- Choosing one parent member as the primary member and ignoring the
existence of other parent members.
- Transforming the nonstrict hierarchy into two independent dimensions.
￿ Including a distributing attribute.
￿ Calculating approximate values of a distributing attribute.
Since each solution has its advantages and disadvantages and requires
special aggregation procedures, the designer must select the appropriate
solution according to the situation at hand and the users' requirements.
4.3 Advanced Modeling Aspects
In this section, we discuss particular modeling issues, namely, facts with
multiple granularities and many-to-many dimensions, and show how they
can be represented in the MultiDim model.
4.3.1 Facts with Multiple Granularities
Sometimes, it is the case that measures are captured at multiple granular-
ities . An example is given in Fig. 4.18 , where, for instance, sales for the USA
might be reported per state, while European sales might be reported per
city. As another example, consider a medical data warehouse for analyzing
patients, where there is a diagnosis dimension with levels diagnosis, diagnosis
family, and diagnosis group. A patient may be related to a diagnosis at
the lowest granularity but may also have (more imprecise) diagnoses at the
diagnosis family and diagnosis group levels.
As can be seen in Fig. 4.18 , this situation can be modeled using exclusive
relationships between the various granularity levels. Obviously, the issue in
this case is to get correct analysis results when fact data are registered at
multiple granularities.
4.3.2 Many-to-Many Dimensions
In a many-to-many dimension , several members of the dimension partic-
ipate in the same fact member. An example is shown in Fig. 4.19 .Sincean