Unlevel Hierarchies: a Quick Workaround

Sometimes, especially when using the Essbase Wizard to expose Essbase cubes to OBIEE, the technical team presents unlevel hierarchies without defaulting to outer join. The result is that when they are selected down to the last level, whichever levels do not have members at the last level do not get into the result set. This is due to the hierarchy being unlevel, or not having a detail value down to the last level being selected. This is called an unlevel hierarchy. Also the term ragged hierarchy has been used to describe such a hierarchy.

Here is a simple workaround. Let’s say there are 5 levels. Make a union query by selecting all columns and repeating it five times as a union of five queries. Then one query at a time, modify one or more of the columns to include a blank instead of the column. One query goes to level 5, one goes to level 4 with blank in level 5, one goes to 3 with blank in levels 4 and 5, one goes to 2 with blanks in levels 3, 4 and 5, and one goes to level one with blanks in levels 2, 3, 4 and 5. This when rendered will produce a hierarchical visualization of the unlevel hierarchy, with all the members visible.

I have used this at clients with much success. Since the Essbase cube had multiple hierarchies, we were able to combine this technique with putting a dashboard prompt at the top of the page where the user selects which hierarchy to view. For example, two hierarchies might be Company before Merger and Company after Merger. The selection which hierarchy to choose would be done for Level 1, and this would affect all the union by putting Is Prompted for Level 1 in the filter criteria.