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

presence of the complex hierarchies studied in this chapter. The first
multidimensional normal form (1MNF) requires each measure to be uniquely
identified by the set of associated leaf levels. The 1MNF is the basis for correct
schema design. To analyze the schema in Fig. 4.19 in terms of the 1MNF,
we need to find out the functional dependencies that exist between the leaf
levels and the measures. Since the balance depends on the specific account
and the time when it is considered, the account and the time determine the
balance. Therefore, the schema in Fig. 4.19 does not satisfy the 1MNF, since
the measure is not determined by all leaf levels, and thus the fact must be
decomposed.

Time Account Client Balance

T1
T1
T1
T1
T1

A1
A1
A1
A2
A2

C1
C2
C3
C1
C2

100
100
100
500
500

Fig. 4.20 An example of double-counting problem in a many-to-many dimension

Let us recall the notion of multivalued dependency we have seen in Chap. 2 .
There are two possible ways in which the Balance fact in Fig. 4.19 can be
decomposed. In the first one, the same joint account may have different clients
assigned to it during different periods of time, and thus the time and the
account multidetermine the clients. This situation leads to the solution shown
in Fig. 4.21 a, where the original fact is decomposed into two facts, that is,
AccountHolders and Balance . If the joint account holders do not change over
time, clients are multidetermined just by the accounts (but not the time).
In this case, the link relating the Time level and the AccountHolders fact can
be eliminated. Alternatively, this situation can be modeled with a nonstrict
hierarchy as shown in Fig. 4.21 b.
Even though the solutions proposed in Fig. 4.21 eliminate the double-
counting problem, the two schemas in Fig. 4.21 require programming effort for
queries that ask for information about individual clients. The difference lies in
the fact that in Fig. 4.21 a, a drill-across operation (see Sect. 3.2 ) between the
two facts is needed, while in Fig. 4.21 b, special procedures for aggregation in
nonstrict hierarchies must be applied. In the case of Fig. 4.21 a, since the two
facts represent different granularities, queries with drill-across operations are
complex, demanding a conversion either from a finer to a coarser granularity
(e.g., grouping clients to know who holds a specific balance in an account)
or vice versa (e.g., distributing a balance between different account holders).
Note also that the two schemas in Fig. 4.21 could represent the information
about the percentage of ownership of accounts by customers (if this is