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

the Categories hierarchy in Fig. 4.2 yields a snowflake structure with tables
Product and Category shown in Fig. 5.5 a.
Nevertheless, if star schemas are required, it is necessary to represent
hierarchies using flat tables, where the key and the attributes of all levels
forming a hierarchy are included in a single table. This structure can be
obtained by denormalizing the tables that represent several hierarchy levels.
As an example, the Time dimension of Fig. 4.2 can be represented in a single
table containing all attributes, as shown in Fig. 5.5 b.

a b

Product Category Time

ProductKey
ProductName
QuantityPerUnit
UnitPrice
Discontinued
CategoryKey

CategoryKey
CategoryName
Description

TimeKey
Date
...
MonthNumber
MonthName
Quarter
Semester
Year

Fig. 5.5 Relations for a balanced hierarchy. ( a ) Snowflake structure. ( b ) Flat table

As we have seen in Sect. 5.2 , snowflake schemas better represent
hierarchical structures than star schemas, since every level can be easily
distinguished and, further, levels can be reused between different hierarchies.
Additionally, in this representation, specific attributes can be included in
the different levels of a hierarchy. For example, the Product and Category
tables in Fig. 5.5 a have specific attributes. However, snowflake schemas are
less performant for querying due to the joins that are needed for combining
the data scattered in the various tables composing a hierarchy.
On the other hand, star schemas facilitate query formulation since
fewer joins are needed for expressing queries, owing to denormalization.
Additionally, much research has been done to improve system performance
for processing star queries. However, star schemas have some drawbacks.
For example, they do not model hierarchies adequately since the hierarchy
structure is not clear. For example, for the Store dimension in Fig. 5.1 ,itisnot
clear which attributes can be used for hierarchies. As can also be seen in the
figure, it is dicult to clearly associate attributes with their corresponding
levels, making the hierarchy structure dicult to understand.

5.5.2 Unbalanced Hierarchies

Since unbalanced hierarchies do not satisfy the summarizability conditions
(see Sect. 3.1.2 ), the mapping described in Sect. 5.3 may lead to the problem