At Rittman Mead, one of the major areas that we've been asked to help customers in the past is in the integration between OBIEE 10g and Essbase. OBIEE supports Essbase as a data source, but it's had a few well-known limitations in the 10g release of OBIEE that have made it hard for certain types of Essbase customer to migrate to this new platform. In particular:
- Lack of proper support for ragged, unbalanced and value-based hierarchies in Essbase outlines
- Inflexibility around changes in the Essbase outline
- Very few OBIEE SQL functions were function-shipped to the equivalent Essbase MDX functions
- No out-of-the-box support for UDAs
- No way to access non-default member aliases
- The accounts hierarchy was flattened during import, losing the hierarchy of accounts (particularly important for financial applications)
- Limitations on how hierarchies could be drilled into, using Answers, and
- Limitations around how members could be browsed and selected
So it was not surprising then that most Essbase customers are looking forward to the 11gR1 release of OBIEE to address some of these shortcomings. So how does this new release stack up, does it address all of these issues, and does it go beyond traditional Essbase front-end tools such as Web Analysis and Interactive Reporting?
To test this out, I'm taking the Demo.Basic Essbase BSO database that comes with the 11.1.2 release of Essbase Server, to which I've added an additional alias table that describes members in the Products dimension in German, as well as the default English. Here's how the outline looks, and note the ragged hierarchy in the Accounts dimension.
Note also the UDAs I've added to the Product dimension.
I now turn to the OBIEE 11g BI Administration tool, and select File > Import Metadata. I choose Local Machine from the Import Type drop-down, select Essbase as the Connection Type, and enter the connection details and login for the Essbase server.
The next page of the Import Metadata wizard lets me select the Essbase database outline to import.
Notice the option at the bottom to import UDAs? I then press Finish to complete the import.
Looking inside the physical layer of my semantic model, I can see the new Essbase database that I've imported. The most obvious change is that the Accounts dimension has now been brought in properly, and I now have a single measure in my database, that will be dimensioned by this accounts dimension. In the screenshot below, you can see the Product dimension hierarchy expanded, and there are a number of "columns" imported that are new to 11g.
As with OBIEE 10g, the generations in the Essbase outline are converted to columns in the physical model. A new feature however in the 11g version of OBIEE is the ability to create additional columns for any other alias tables that might be used in your outline. I right-click on the Product hierarchy, select Create Columns for Alias Table...
This brings up a dialog that lets me select the alias table to use. I select the German alias and press the Create button.
If I take a look at the Product hierarchy now, I can see the extra column that will expose the German aliases for these member names.
At this stage, if I take a look at the Accounts dimension, it has this same set of levels based on the generation names in my Essbase outline. But this isn't what I really want, as the Accounts dimension is ragged and would really suit a parent-child, or value-based hierarchy instead. So, using a new feature in the 11g release, I right-click on the Accounts hierarchy and select Properties. Then, I change the Hierarchy Type from Unbalanced to Value, a new option with 11g.
If I then take a look at the hierarchy under my Accounts dimension, it's now got just a single level, the Member Key column is still there but now there's a Parent Key column as well.
So now the physical model is prepared, as with the previous 10g release of OBIEE I just need to drag and drop it into the business model and mapping layer, and then drag the business model into the presentation layer, to make it ready for analysis. Taking a look at the business model, I can see the parent-child hierarchy set up for the Accounts dimension, and the single measure under the fact table. To display particular accounts (equivalent to measures in regular relational OBIEE) I need to make sure I select an account name from the accounts dimension.
So, switching now to Oracle BI Answers 11g, I create a simple request showing the balances under each account.Notice the value-based ragged hierarchy used by the Accounts dimension.
And, as we're working with a hierarchical column, we can use the Selection Steps feature to filter down the list of accounts used (regardless of their generation). I start by adding those accounts that are the children of the Total Expenses account, a feature that Essbase users are used to using in tools such as Hyperion WebAnalysis.
I then add another selection step, this time only keeping those accounts whose balance is over 100,000.
This leaves me with just the following selection, and the list of accounts shown in the request.
I can also bring in other dimensions and hierarchies, creating a pivot table that looks like this.
The aliases that I brought in earlier can be accessed by their own columns. In the example below, I've brought the German aliases in as an attribute column in the analysis (apologies Christian and @lex for the cod-German translations...).
Remember the UDA that I brought in earlier on? If I want to filter so that I only include those products with a UDA of Digital, I find the attribute column that references this particular UDA value and set the filter to '1'.
Now I could actually have set up the accounts hierarchy as a level-based, unbalanced hierarchy (which is the default when it comes in from the Essbase outline), but changing it to Value-based has a significant advantage if your outline is likely to change in the future. The 10g release of OBIEE always brought Essbase hierarchies in as level-based ones, creating columns for each of the generations in the hierarchy, which translated into columns in the OBIEE logical model. Apart from making it tricky to filter against hierarchies where members were held at arbitrary generations, the other problem this caused was when additional members were added to the outline that caused additional generation to be created.
As there is no "refresh" feature when importing outlines, the only way to add these new members was to try and create new columns in the physical model to represent the new outline generation, and somehow get these into the business model afterwards. In practical terms this was more or less impossible, meaning that any change in the outline of this nature meant you had to completely re-import the Essbase outline into the RPD, and rebuild the business model and presentation models afterwards, something that wasn't really practical when the outline changed frequently.
OBIEE 11g changes this though by allowing you to define an Essbase outline hierarchy as value-based, something we did earlier on with the Accounts dimension. When you do this, there is only one "generation "in the physical layer and the hierarchy is instead defined by the Member key within it, and the Parent key that creates the link with the dimension member's parent. By doing this, I can then go back into Essbase Administration Services and add two new members under an existing leaf level, effectively creating an additional generation in the outline. In this case, I've added Direct Marketing and Advertising members under the Marketing member, as shown below.
In OBIEE 10g, the only way to bring these new members into the repository (or more precisely, the generation that contained them) would be to re-import the outline into the RPD. Now though, these new members come through automatically with no need to recreate the RPD entries, as they are just part of this value-based hierarchy.
So there's a brief intro into the new Essbase capabilities in OBIEE 11g. Keep an eye on this blog for more postings on this subject by Venkat nearer to the GA date, and also keep an eye on Metalink for details of compatibility between OBIEE 11g and the various Essbase releases. In the meantime though, we'll move on from looking at RPD enhancements as part of this series, and tomorrow take a look at some of the new visualizations and controls that are available in the OBIEE 11g dashboard.