Another area I'm looking to cover in my Oracle Open World session on OBIEE data modeling is support for ragged, skip-level and unbalanced hierarchies. If you've worked with OBIEE for a while and you're from an OLAP background, one of the first things you would have noticed was that OBIEE, like most relational query tools, only really supports level-based hierarchies, where for example a product hierarchy breaks down into product classes, product families and items, and every item has a family, every family has a class and so on. This works well in your typical sales-type reporting environment, but when you are looking to report on organizations, account hierarchies and so on, this can be a bit limiting, as you may well end up working with a hierarchy that looks like the following:
Unfortunately in this current release of OBIEE there is no out-of-the-box support for these types of hierarchies, but if you have to report on hierarchies like this there are three main ways in which you can do this.
The first and most common way of handling this sort of data is to "flatten" it out so that all routes down the hierarchy have the same number of levels. If we took the organization chart above, this would mean that you'd need to create a dimension table, that you'll then load into OBIEE, that looks something like this (note the fact that you need to make up names for the various dimension levels that you've created, and that you need to "copy-down" the lowest level dimension member down to the bottom level, to ensure that each route down the hierarchy has a bottom level to roll up from).
Depending on whether your hierarchy is ragged (has varying depths down the hierarchy) or skip-level (has levels missing on various routes up the hierarchy, sometimes referred to as unbalanced) you end up filling in values at various points in the table, your measures can only be recorded at the lowest level in the flattened hierarchy, and OBIEE ends up repeating data where levels are missing when you drill down the hierarchy. This route does however have the virtue of simplicity and if your hierarchy doesn't change much (most importantly, doesn't increase the maximum depth of the longest route down the hierarchy) you can usually get away with this.
Another method that I've seen used, and in fact I documented in this blog post a couple of years ago, is to an approach put together by Joe Celko where you generate a table that sets out, for each member in the dimension, the member IDs that are it's descendants.
The idea here is that you take the parent-child relationship in your source table and break it out into this "stack" table, where each dimension member has its "children" listed out and you can then navigate through it, in the case of the example using the report navigation feature in Oracle BI Answers, to list out the results. This approach has the advantage of allowing you to record facts against any member in the dimension, not just those at the lowest (flattened hierarchy) level, but it's a bit tricky to set up and maintaining the stack table is quite cumbersome. If you look around the internet you'll see other variations on this, others I've seen include descendent tables and a variation where each route down the hierarchy has a guaranteed lowest level but then varying levels of aggregation up the hierarchy.
The final way that I've seen ragged and unbalanced hierarchies handled in OBIEE is to use Essbase as a data source. Essbase (like Oracle OLAP) inherently stores its dimension hierarchies in parent-child format and I was intrigued to notice that, when you bring in an Essbase dimension into the physical layer of your semantic model, there's an option to set the dimension to balanced, unbalanced, ragged balanced and network. Now what can this be for?
Normally, if you try and import a ragged hierarchy into the OBIEE seminar model using the BI Administrator tool, the hierarchy gets rejected and only the balanced ones import correctly. This was certainly the case for SAP B/W and Microsoft Analysis Services, but since the BI Administrator tool started using the Essbase C API rather than XML/A to read in it's OLAP metadata (from the 10.1.3.3.2 release onwards, i.e. the first production release to support Essbase) you can now actually import most kinds of Essbase hierarchies in, and you use this setting after the import to tell OBIEE what sort of hierarchy it is (this is then used by the MDX generator within the BI Server). So what happens when you import a ragged hierarchy in, such as the one in this Essbase outline?
Well, what actually happens is that the import routine scans the Essbase Outline and works out the maximum depth of the hierarchy, and then generates "columns" for each of the hiearchy levels, so that you end up with a physical model that looks like this for my organization chart:
and a logical model that looks like this:
So what's happened here is that, like the conversion I did earlier on with the flattened table I set up, the import routine has generated a flattened relational representation of the data for the hierarchy as it stands at the moment. If I run a report in Oracle BI Answers and take a look at the resulting hierarchy, it looks like this:
Which isn't a bad representation, when you think about it, although this method does have one major drawback in that you'd need to re-import your Essbase dimension into the physical layer, and regenerate the logical business model of it, if the maximum depth of your ragged hierarchy increased.
So, that's where things are in terms of ragged hierarchy support in OBIEE. At present, it's not something that's fully supported and you have to jump through various hoops to create a flattened, relational version of your hierarchy. The Essbase support is interesting (especially as with other multi-dimensional data sources, you can't actually import ragged hierarchies in at all), and it'll be interesting to see what functionality Oracle add to OBIEE 11g around this area, especially as Answers+ is being positioned as a replacement for the ragged-hierarchy capable Hyperion Web Analysis.