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

82
Chapter 3
To test that SUBCATEGORY belongs above BRAND ask:
Can a SUBCATEGORY have more than one BRAND?
then:
Can a BRAND belong to more than one SUBCATEGORY?
If you finish with “M” above the child (BRAND) and “1” below the candidate
(SUBCATEGORY) (YES, NO answers) you have the right M:1 child relationship,
the candidate is in the correct position and you can move on to the search for
another level in the hierarchy.
Hierarchy Attributes at the Same Level
A 1:1 relationship
means that
attributes are at the
same level
If you have two “1”s between levels this indicates a one-to-one (1:1) relationship
which means that the candidate is at the same level as the existing hierarchical
attribute. As such it adds no additional drill-down functionality to the hierarchy,
and you should not insert it. It may, however, replace the existing attribute, if it
proves to be a better report label. For example, CATEGORY NAME is at the same
level as CATEGORY CODE, but would be a more useful descriptive label on a
drill-down report than the cryptic code. Remember that even if an attribute is not
added to a hierarchy it will still be available in the dimension as an alternative for
report formatting, so excluding CATEGORY CODE from the hierarchy does not
preclude its use in custom drill-down reporting, just its use in default drilling.
Hierarchy Attributes that Don’t Belong
M:M indicates that
attributes do not
belong in the same
hierarchy
If you get two “M”s between levels this indicates a M:M relationship which means
that the two levels do not belong in the same (balanced) hierarchy. If stakeholders
want to define a hierarchy containing the candidate it must be a separate parallel
hierarchy to the current one, just as Week and Month are in different time hierar-
chies in Figure 3-10b.
Hierarchy Attributes at the Wrong Level
Reverse
relationships mean
that attributes need
repositioning on the
hierarchy chart
If you finish with the reverse relationship from the one you are looking for the
candidate is at the wrong level. If you get a 1:M with a parent (you’re looking for an
M:1), the candidate is too low and you should move it up a level. If you get an M:1
with a child (you’re looking for a 1:M), the candidate is too high and you should
move it down a level. After you move it, be sure to retest it against its new parent
and child levels.
Follow each hierarchy level name with a few example values as a bracketed list as
in the Figure 3-14 chart. This is especially useful if you are modelstorming a
hierarchy chart with limited whiteboard space and stakeholders cannot see a copy
of the dimension example data table.