OBIEE 11gR1 : Support for Ragged, Skip-Level & Value-Based Hierarchies
July 12th, 2010 by Mark Rittman
If you were at the London launch of OBIEE 11g last week, or 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.
July 12th, 2010 at 3:43 pm
Hi Mark
Is ‘Closure Table’ a behind the scenes work or is it more of an intermediate table created in the physical layer of the database like an Opaque View? Can we look at this table that is being created?
Kris
July 12th, 2010 at 3:48 pm
@Kris
The closure table is a physical table, defined by a script created using the parent-child hierarchy wizard. You can look at the table contents, and you run another script to update its contents as your underlying data changes (to pick up changes in member parentage).
regards, Mark
July 13th, 2010 at 3:18 pm
Mark,
Thanks for the response.
Would it be nice to start off this using this wizard, get the DDL script, create your closure table and ‘trigger’ the update script whenever there are changes on the base tables? Or is this what it is doing in this methodology already behind the scenes?
Kris
July 13th, 2010 at 7:53 pm
@Kris
Well you get two scripts, one for the DDL and one for the DML, so certainly this would be possible. Depending on how big the source table is for the dimension though, running the DML script as a trigger might cause excessive load on the server. I would probably consider this feature more suited for DW-style source data, where the dimensional data changes less frequently, for example during an ETL process.
regards, Mark
October 8th, 2010 at 5:09 am
Hi Mark,
We are using OBIEE 11g Value Hierarchy feature to display GL Segment Hierarchies.
- We have set the hierarchies to be Ragged and Skipped Levels in the RPD Business Model.
- We have dragged the hierarchies from Business Model to Presentation Layer.
- When selecting the hierarchies during Analysis, the first hierarchy that is browsed appears correctly. The next hierarchy when browsed just hangs.
For example:
Step 1: Browse Hierarchy1 (Block). The values show correctly.
Step 2: Browse Hierarchy2 (Account). The hierarchy does not open with a blinking circle remaining forever.
Alternatively;
If we selected, Hierarchy 2(Account) first, the hierarchy shows correctly, but when selecting Hierarchy1 (Block) a blinking circle appears and remains for ever without opening the hierarchy.
The nqquery.log shows correct result. My suspicion is that the issue is on the front end presentation services/javascript side.
Is there any additional setup/configuration required to open multiple value hierarchies during analysis.
Thanks and Regards,
Sasi
January 24th, 2011 at 9:55 am
Hi,
Can any one tell me where (in which schema) can i find these tables given in above example.
If they were given in previous post?
January 25th, 2011 at 1:29 pm
Mastan,
This data comes from example data we use in our courses, etc. It’s also being used in the Oracle Press book we’re currently working on. As such, it’s not available for general download, however it will be made available with the book (early in 2012 probably).
Mark
January 26th, 2011 at 11:26 pm
Nice intro to these features, thanks. I’m working through the ragged & skip-level case now, and wondering if there’s really any downside to just checking both of these options, or at least the skip-level option, as defaults.
If I don’t enable the skip-level option and my dimension table has a skipped level, I get a “Bad xml instance!” error in my results view at query time when I try to expand a null intermediate node.
And (I think), if neither leaf nor intermediate nodes are null, the checkboxes will make no difference.
Any recommendations on this? Performance ramifications?
Thanks, Eric
January 27th, 2011 at 8:46 am
Hi Eric,
Yes, there can be a performance hit from this. Basically, the BI Server has to “peek” down multiple levels to find out if there are skip-level members, for example, which usually leads to it generating SQL that requires GROUPING_IDs and GROUPING SETS to retrieve multiple sets of aggregations for a given SQL query. This can happen on normal, balanced level-based hierarchies, but it’s much more prevalent when you mark hierarchies as being ragged or skip-level.
This article by Venkat explains some of the technicalities:
http://www.rittmanmead.com/2010/10/oracle-bi-ee-11g-skipped-ragged-hierarchies-grouping-sets-grouping-ids/
regards
Mark
January 31st, 2011 at 4:04 pm
Hi Mark,
I am facing issue with keeping a dimension(non-measure) column in the pivot table hierarchy view. i have a hierarchy of catalog and associated measure columns. i need to put a flag column in the report and when the flag value is ‘Y’ then the measure columns should be displayed as Bold and in italics. In conditions tab i created this condition and it works fine. the issue is that due to adding of this flag column my report is not getting drilled down. e.g. lets say i have a hierarchy as A–>A1, A–>A2, B–>B1. A has 2 children which have further children and B has only one child and B1 will have more children. Now when i drill down on B then it gets opened but when i drill down on A then it does not get drilled. This scenario is for all the non measure columns even when i do not put any formating condition. I have also tried to change(increase) the “No of elements at this level” property of the hierarchy in the BMM layer but no sucess.issue in short is only single child hierarchy is opening and not multi child. Kindly let me know what is causing this error.
March 21st, 2011 at 11:29 am
Mark,
Nice article. I noticed a very minor typo.
“with staff being stores” should read “with staff being stored” in one of the sentences above
March 21st, 2011 at 11:39 am
Hari
Well spotted, typo fixed now.
Mark
March 22nd, 2011 at 12:20 pm
That was very quick! Just wanted to know if you guys train in Hyderabad too
May 10th, 2011 at 6:02 am
Hello Mark,
Can u provide me some information on applying data level security for valu based hierarchy.
Regards
Kumar
May 11th, 2011 at 12:44 am
Mark,
I have a reporting requirement where in, we need to traverse through PeopleSoft Accounting Tree to get the required GL Node. But we do not which level exactly this node is stored.(only identifier is the description of the node!) and the max levels of tree can also change over a period of time.
Any guidance, how to proceed for this problem (both from DB structure & OBIEE) will be appreciated.
PS: We do not have Essbase at the moment.
Cheer
Ajit Hegde
May 11th, 2011 at 7:05 am
Hi Ajit
Thanks for the comment. However really this is getting into the area of “free consulting” as it’s not something I can just answer in a two second response. If you’d like some help on this, our Bangalore office has a team of developers who’d be happy to provide consulting support on a chargeable basis for you or your client. Their contact details are on our home page.
regards, Mark
May 11th, 2011 at 7:06 am
Hi Malvika
Thanks for the comment. However really this is getting into the area of “free consulting” as it’s not something I can just answer in a two second response. If you’d like some help on this, our Bangalore office has a team of developers who’d be happy to provide consulting support on a chargeable basis for you or your client. Their contact details are on our home page.
regards, Mark
May 12th, 2011 at 11:38 am
Mark,
Can u pls provide me info on how to apply data level security to valu-based hierarchy
Thanks,
Kumar
June 2nd, 2011 at 9:26 am
I have DDL and DML script.
I add an employee in employee table. I run DDL and DML script again.
But I can not see New Employee in BI web.
Help me pls!