RPDintermediate
Hierarchies, Levels & Drill-Down
Explain how dimensional hierarchies and levels enable both business-user drill-down and aggregate navigation in the RPD.
A hierarchy does two jobs at once: it defines the path a user drills down, and it gives aggregate navigation its vocabulary.
It's like a set of nested Russian dolls (matryoshka) — Year contains Quarters, Quarters contain Months, Months contain Days — and clicking to "open" one doll (drill down) reveals the next size down, in a fixed, well-defined order.
Key Concepts
1
Total
Year
Quarter
Month
Day2
Levels have keys. Each level names the logical column that makes a member unique at that level — the
level key.
3
A Month level keyed on month number alone is wrong, because March exists in every year. It needs
year-and-month, or the level collapses years together and every aggregate above it is incorrect.
4
That is the most common hierarchy defect, and it produces plausible wrong numbers rather than an error.
5
Levels carry descriptors too. A Month level may hold Month Name and Month Number alongside the key, so
drilling shows "March" and sorts by 3.
6
The two things a hierarchy powers.Drill-down. A user clicks a year in an analysis and OBIEE generates the next query one level down,
keeping the existing filters. No report configuration — the drill path is the hierarchy.
7
Aggregate navigation. A fact LTS declares its grain as "the Month level of Time", which only means
something if that level exists.
8
So a hierarchy is not optional decoration. Without one, aggregates cannot be registered at all.
9
The edge cases worth naming.Ragged hierarchies, where branches have different depths — an organisation where one division has four
management levels and another has two. The shallow branch has no member at some levels.
10
Skip-level hierarchies, where a member's parent is not at the immediately higher level — a city reporting
directly to a country, with no state in between.
11
OBIEE supports both, with more careful configuration. Mentioning them separates someone who has modelled a
real organisational hierarchy from someone who has only modelled Time, which is always a clean balanced
tree.
12
The practical check. Drill from the top to the bottom and confirm the totals match at every level. A
hierarchy with a wrong level key gives different totals at different levels, and that comparison finds it
immediately.
13
What the interviewer is probing.1. "What does defining a hierarchy enable?" Probing: the value. Stalls: "Drilling." *Moves
up:* drill-down in analyses, level-based measures, and aggregate navigation — it is not only a UI
feature.
14
2. "What is the difference between a level-based and a parent-child hierarchy?" Probing: the
distinction. Stalls: "Nothing." Moves up: level-based has a fixed number of named levels;
parent-child is ragged and self-referencing, like an org chart of unknown depth.
15
3. "What is a level key for?" Probing: the detail. Stalls: "Identification." Moves up:
uniquely identifying a member at that level, which the server needs for correct aggregation and
drill paths.
16
4. "What is a skipped level and why does it matter?" Probing: the depth. Stalls: "Nothing."
Moves up: a member with no parent at an intermediate level, which breaks naive rollups unless the
hierarchy is declared ragged.