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

of excluding from the analysis the members of nonleaf levels that do not
have an associated child. For instance, since in Fig. 4.4 a all measures are
associated with the ATM level, these measures will be aggregated into the
higher levels only for those agencies that have ATMs and, similarly, only
for those branches that have agencies. To avoid this problem, an unbalanced
hierarchy can be transformed into a balanced one using placeholders (marked
PH1 , PH2 , ... , PHn in Fig. 5.6 ) or null values in missing levels. Then, the
logical mapping for balanced hierarchies may be applied.

bank X

branch 1 branch 2 branch 3

agency 11 agency 12 PH1 agency 31 agency 32

ATM 111 ATM 121 PH2 PH3 PH4 PH5

Fig. 5.6 Transformation of the unbalanced hierarchy in Fig. 4.4 b into a balanced one
using placeholders

The above transformation has the following consequences. First, the fact
table contains measures belonging to all levels whose members can be a leaf
at the instance level. For example, measures for the ATM level and for the
Agency level will be included in the fact table for those members that do
not have an ATM. This has the problem that users must be aware that they
have to deal with fact data at several different granularities. Further, when
for a child member there are two or more consecutive parent levels missing,
measure values must be repeated for aggregation purposes. For example, this
would be the case for branch 2 in Fig. 5.6 since two placeholders are used for
two consecutive missing levels. In addition, the introduction of meaningless
values requires additional storage space. Finally, special interface must be
developed to hide placeholders from users.
Recall from Sect. 4.2.2 that parent-child hierarchies are a special case
of unbalanced hierarchies. Mapping these hierarchies to the relational model
yields tables containing all attributes of a level and an additional foreign
key relating child members to their corresponding parent. For example,
the table Employee in Fig. 5.4 shows the relational representation of the
parent-child hierarchy in Fig. 4.2 . Although such a table represents the
semantics of parent-child hierarchies, operations over it are more complex.
In particular, recursive queries are necessary for traversing a parent-child
hierarchy. Recursive queries are allowed both in SQL and in MDX.