My first Publication Agile-Data-Warehouse-Design-eBook | Page 96

Modeling Business Dimensions
75
Hierarchy Types
Data warehouses and BI applications have to deal with three types of hierarchies:
balanced, ragged (unbalanced) and variable depth (recursive). Each of these can
come in two flavors: simple single parent and more complex multi-parent. Of the
six varieties shown in Figure 3-7, single parent balanced hierarchies are the easiest
to implement and use and should be the main focus of your initial modeling
efforts.
There are three
hierarchy types:
balanced, ragged
and variable depth
Figure 3-7
Hierarchy types
Balanced Hierarchies
Balanced hierarchies have a fixed (known) number of levels, each with a unique
level name. Time (when) is an example of a balanced hierarchy, as the example
calendar data in Figure 3-6 shows. This example has four levels: day, month,
quarter, and year. The hierarchy is balanced because there are always four levels;
days always roll up to months, months to quarters, and quarters to years — there are
no exceptional dates that do not belong to a month and only belong to a quarter or
a year. Balanced
Being balanced has nothing to do with the number of members (unique data
values) at each hierarchy level. For example, even though the number of days in a
month varies from 28 to 31, and days in a year can be 365 or 366, the calendar
hierarchy is still balanced in depth. Figure 3-6 is not the only time hierarchy;
alternative hierarchies of day → fiscal period → fiscal quarter → fiscal year and
day → week → year may all exist in the same calendar dimension. Each of these is
a separate balanced hierarchy. The number of
A hierarchy is implemented in a dimension by adding an attribute for each of its
levels. For a balanced hierarchy each of its fixed levels must be a mandatory attrib-
ute with a strict M:1 relationship with the parent attribute one level above it and a
1:M relationship with the child levels below it. Balanced hierarchy
hierarchies have
fixed numbers of
levels
members at each
level can vary
levels are
mandatory attributes