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

80
Chapter 3
Discovering Hierarchical Attributes and Levels
You discover hierarchies by asking stakeholders how they organize the members of
a dimension. You could begin by drawing a hierarchy chart with PRODUCT at the
bottom and asking:
How do you organize products (hierarchically)?
It’s a leading question if you add “hierarchically”, but stakeholders generally have a
good idea about the hierarchies they need, and will usually offer you candidate
levels in hierarchical order — which helps. If any are new attributes check that they
belong in the dimension (have a M:1 relationship with it) and ask for examples
before considering them as candidates.
Introduce stakeholders to hierarchy charts by drawing a simple version of the
time hierarchy (Figure 3-10) on the whiteboard. Try using relative spacing for this
well known hierarchy to get stakeholders thinking generally about additional
useful levels that fill large hierarchical gaps. At some point, you will want to use
this chart to model any custom calendar levels and design an explicit CALENDAR
dimension (see Chapter 7).
Ask stakeholders to
add their new levels
to the hierarchy
chart
Ask stakeholders where they think a candidate attribute sits on the hierarchy chart
bar and add it to your diagram where they suggest, or better still get them to add it.
Figure 3-14 shows SUBCATEGORY added to a PRODUCT hierarchy chart be-
tween BRAND and CATEGORY.
Figure 3-14
Adding
SUBCATEGORY to
the PRODUCT
hierarchy at the
correct level
Check that each
candidate level has
the correct parent
child relationships
As each new candidate is added, you need to check that it is in the right position
relative to the existing levels. It must have a M:1 relationship with its parent (if
present) and a 1:M relationship with its child. If you or any stakeholders are
unsure, you can check the relationship by methodically asking the following