RPDintermediate
Hierarchies, Levels & Drill-Down
Explain how dimensional hierarchies and levels enable both business-user drill-down and aggregate navigation in the RPD.
Hierarchies are the backbone of dimensional analysis in OBIEE — they define the drill path business users click through (Year → Quarter → Month → Day) and simultaneously define the vocabulary the BI Server uses internally to decide which aggregate table answers a query, tying this topic directly to aggregate navigation.
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
A hierarchy is built in the Dimension editor in the BMM layer on top of a logical dimension table, composed of ordered levels (e.g., Total > Year > Quarter > Month > Day for a Time dimension), each level associated with one or more logical columns that determine uniqueness at that level (the level key). Levels can have additional attributes attached (a Month level might carry both "Month Name" and "Month Number" as descriptor columns alongside the key). The topmost level is typically a Grand Total level representing the highest possible aggregation.
Dimension editorlevelslevel keyGrand Total
2
Hierarchies enable two related but distinct behaviors: (1) drill-down in Answers/Analyses, where a user clicks a value and OBIEE automatically generates a new query one level down the hierarchy, preserving other filters; and (2) as covered in aggregate navigation, content levels on fact LTSs are defined in terms of these hierarchy levels, so a well-designed hierarchy is a prerequisite for aggregate navigation to work at all.
drill-downcontent levels on fact LTSs are defined in terms of these hierarchy levels
3
Interviewers sometimes probe ragged or unbalanced hierarchies (e.g., an organizational hierarchy where some branches have more levels than others) and skip-level hierarchies, which OBIEE supports but which require more careful level configuration — a good candidate mentions these as edge cases rather than assuming every hierarchy is a clean balanced tree like Time typically is.
ragged or unbalanced hierarchiesskip-level hierarchies