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

74
Chapter 3
Hierarchy definition
provides a hook for
catching additional
attributes
Hierarchies help
expose “informal”
stakeholder
maintained data
sources that can
greatly enrich
dimensions
Hierarchies help you
discover planning
processes
BI users and BI
tools require default
hierarchies to
enable simple drill-
down
Hierarchies are
used to optimize
query performance
Hierarchies provide a necessary hook to catch dimensional attributes. When
you ask stakeholders about hierarchies you are asking them how they (would
like to) organize their data. Discussing this activity is one technique for adding
some otherwise missing narrative to dimension stories and prompting stake-
holders for the BI friendly attributes you need to model good dimensions.
When stakeholders think about their 7Ws hierarchically, they describe low
level attributes that can be used as discriminators for similar dimensional
members, and higher level attributes that can group together many dimen-
sional members.
When stakeholders describe their favorite hierarchy levels they will frequently
provide you with additional “informal“ data sources (spreadsheets, personal
databases) they own that contain this categorical information. These stake-
holder maintained sources often contain hierarchy definitions, vital to BI, that
are missing from “formal” OLTP databases because they are nonessential for
operational activity. Many operational applications happily perform their func-
tion at the bottom of each hierarchy with no knowledge of the higher level
groupings that are imposed upon their raw transactions for reporting purposes.
For example, orders can be processed day in, day out without the order proc-
essing system knowing how a single date is rolled into a fiscal period. Similarly,
items can be shipped to the correct street number/postal code without knowing
how the business currently organizes sales geographically (or how they might
have been organized differently last year).
Hierarchies exist so that organizations can plan. Discussing hierarchies with
stakeholders will get them thinking about their planning processes, and will
likely help you discover additional events and data sources that represent budg-
ets, targets or forecasts. You must make sure the dimensions you design con-
tain the common levels needed to roll up actual event measures for comparison
against these plans.
BI users like default hierarchies and BI tool “click to drill” functionality that
allows them to quickly drill-down on an attribute without having to manually
decide each time what to show next. For example, if users drill on “Quarter”
they usually want to see monthly detailed data by default. Explicit hierarchies
establish predictable analytical workflows that are very helpful to (new) BI us-
ers exploring the data for the first time. “Clicking to drill” is less laborious and
error prone than manually adding and removing report row headers.
Everyone wants common drill-down and drill-up requests to happen quickly.
Explicit hierarchies are needed to define efficient data aggregation strategies in
the data warehouse. On-Line Analytical Processing (OLAP) cubes in multidi-
mensional databases, aggregate navigation /query rewrite optimization in rela-
tional databases and prefetched micro cubes in BI tools, all take advantage of
hierarchy definitions to maximize query performance.