OBIEE 11G Support for Ragged, Skip-Level & Value-Based Hierarchies

listened in on the webcast, you’ll have heard Paul Rodwick talk about support for ragged and skip-level hierarchies whilst he demonstrated the 11g version of the BI Administration tool. If like me you spend most of your development time in OBIEE working with the BI Server repository (the RPD), this was interesting news as hierarchies in the 10g release were fairly limited, and were always level-based and balanced. So how does this new feature work?
The 11gR1 release includes a number of enhancements to dimension handling, including:

• Support for parent-child (value-based) hierarchies
• Support for ragged level-based hierarchies
• Support for skip-level level-based hierarchies

The support for parent-child hierarchies has implications for Essbase and Oracle OLAP-based sources, but we’ll leave this to a later blog post. For now, I’m going to look at how these new features support more complex relational-based hierarchies.
To take an initial example, suppose we have a dimension that organizes stores into regions, stores and concessions:

If this was a simple, level-based balanced hierarchy, all would be straightforward. However imagine that this hierarchy was in fact ragged, with only certain stores having concessions. In this case, the leaf levels for the hierarchy wouldn’t be at the same level, something that would have caused problems for RPD modellers working with OBIEE 10g.

Now in 10g this would have been tricky, as OBIEE would have expected each leaf member to be at the same (in this case, concession) level, and if they weren’t, you’d need to fudge the data a bit, for example by adding dummy concession members so that each leaf was at the same level.
Going back to our hierarchy, another interesting thing you often get in real life is “skip levels”. Imagine that some of our concessions are so important that they report directly to the regional office, with no store in between. In the diagram below, the SFO concession reports directly to the North CA regional office, and again in 10g you’d need to create a dummy store parent for this concession to make it all work.

OBIEE 11g can handle this though with the new ragged and skip-level support for level-based hierarchies. It does this by detecting NULLs in either leaf levels (for ragged hierarchies) or other levels (for skip-level hierarchies) and use this to modify how the new hierarchical column type in Answers handles the missing levels. To create a ragged and/or skip-level hierarchy, you would therefore import your source schema into the OBIEE 11g physical layer as normal, and then create the logical model in the business model and mapping layer, like this:

Then, and this is a new feature in 11g, you choose whether to create your hierarchy as level-based, or parent-child. In this instance, the hierarchy is still level-based, and this option should then be selected.

You now get the opportunity to specify whether the hierarchy is ragged, skip-level and/or for a time dimension.

The hierarchy itself is then created as normal, with levels for All Stores, Region, Store and Concession, going down to a common primary key level so that each row in the source table has an ID that can be used to reference it.

This logical model is then copied across to the presentation layer along with the hierarchy, with the primary key level then being deleted so that users don’t drill down to it (it’s just there for internal purposes, not for displaying the user).

With the RPD then complete, I can then switch over to Answers and display the hierarchy. As you can see from below, it’s handled the ragged elements (some stores having concessions, some not) and the skip-level (for the SFO concession) correctly.

Another new feature in 11g is the ability to designate hierarchies as parent-child. Taking our example further, imagine that our stores had staff associated with them, and these staff had managers, with staff being stored in the underlying relational database as a table with a manager ID associated with them. In this case, when defining the hierarchy you can specify this to be the case.

This then brings up a dialog where I can name the dimension, and where I pick the logical column that provides the parent ID for the dimension member, in this case the Manager Name.

The way that 11g supports parent-child hierarchies is to create what’s referred to as a “closure table” behind your source data, that takes each of the individual members and explodes-out the hierarchy. This is done through a wizard that runs SQL against your data set, creates and populates the table and then maps it into your physical layer metadata, creating a simple link between the data in your fact table and this parent-child hierarchy. You’ll need to refresh this table every time the parent-child hierarchy changes, using a script provided by the wizard.

Taking a look at the final results in Answers, you can see the parent-child, ragged nature of the hierarchy at work, in the screenshot below showing sales for each of the salespeople arranged into the hierarchy that we just built.

So there you go. In the next posting, we’ll move away from the back-end and take a look at the hierarchical columns feature in 11g, and also see how this has led to enhancements in the pivot table views that we use in Answers.