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

Modeling Business Dimensions
83
Completing a Hierarchy
After you have found the correct position for the new level — or discarded it if it
does not belong — you continue to find more levels by pointing at the existing ones
and asking stakeholders whether any other levels exist above or below them. When
they have finished providing new levels you should ask one more hierarchy related
question:
Check for “hot”
planning levels
before finishing
each hierarchy
Do you have plans, budgets, forecasts, or targets associated
with [dimension]s? If so at what level(s) are they set?
If stakeholders identify planning levels, mark these with an asterisk (*) to denote
that they are “hot”, i.e., likely to be particularly important levels for many BI
comparisons. You may need to design aggregates or OLAP cubes at these levels to
improve query performance (see Chapter 8). You should definitely model the
planning events themselves along with any additional hot level rollup dimensions
they require. E.g., Month is typically a hot level in the when hierarchy that is
implemented as a rollup dimension (a separate physical dimension derived from
the base calendar dimension) to match the granularity of plan and aggregate facts.
(See Chapters 4 and 8.) Mark hot levels with
Hot levels often appear at the points where different W-type hierarchies logically or
physically intersect. For example, Category and Department are hot levels in the
Product and Employee hierarchies (as denoted in Figure 3-12) because this is
notionally where a what hierarchy of things (products) intersects with a who
hierarchy of people (employees). At that point, a de facto 1:1 relationship exists
between the HR and product hierarchies: a single employee (a product sales man-
ager) responsible for a single group of employees (a department) is also responsible
for a single group of products (a category). He or she will want many reports
summarized to these levels. Hot levels exist
When you have finished modeling a hierarchy, check that each level is a mandatory
attribute. If some are not mandatory then you may have a ragged hierarchy instead
of your preferred balanced one. If data profiling confirms that certain level attrib-
utes contain nulls, then update the hierarchy chart to document the missing levels
by putting brackets around their names (as in Figure 3-12a) prior to resolving the
issue with stakeholders. It is especially important that hot levels are mandatory for
successful cross-process analysis. Check all levels are
When you have completed all the hierarchy charts for a dimension, rearrange the
level attributes in hierarchy order in the dimension table with the low level attrib-
utes first followed by higher level attributes (reading left to right). Hierarchical
column order increases readability and helps to roughly document the hierarchi-
cal relationships within physical dimension tables.
an * and be
prepared to model
their additional
matching events
and rollup
dimensions
where different W-
type hierarchies
intersect
mandatory to avoid
ragged hierarchies