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

Modeling Business Dimensions
73
Dimensional Hierarchies
Hierarchies provide a mechanism for dealing with details that are too numerous or
small to work with individually. Dimensional hierarchies describe sequences of
successively lower cardinality attributes. They allow individual business events to
be consistently rolled up to higher reporting levels, and subsequently drilled-down
on, to explore progressively more detail. Without hierarchies, BI reporting would
be overwhelmed by detail. Dimensional
So how much detail is too much? In everyday life we all seem to agree that 365 (or
366) are too many! Too many days to always deal with individually — too short a
period of time to complete large tasks and activities or see trends. So we group our
days into longer periods: weeks, months, quarters, terms, semesters, seasons etc. so
that we can plan bigger things and see patterns and trends in our lives. Figure 3-6
shows how the days in the first quarter of 2012 can be grouped hierarchically.
Organizations naturally do this with the many fiscal time periods, geographic
locations, people, products, and services that they work with. The clue is in the
name: organizations organize things and if there are enough things (their 7Ws)
they typically organize them hierarchically. The majority of the 7Ws that describe a
business will have de-facto hierarchies in place, and it is vitally important that they
are standardized and made available in the dimensions in the data warehouse. When dealing with
hierarchies support
BI drill-down
analysis
lots of things, we
naturally tend to
organize them
hierarchically into
fewer and fewer
groups
Figure 3-6
The calendar: a
balanced hierarchy
Why Are Hierarchies Important?
Explicit hierarchies are not strictly necessary for drill-down or drill-up reporting.
So long as dimensions contain the attributes that BI users want to drill on they can
effectively drill-down by adding them to existing reports and drill-up by removing
them. However, there are a number of significant modeling and implementation
benefits in making at least one hierarchy in each dimension explicit:
Agile dimensional
modelers take
advantage of
hierarchies for a
number of reasons