OBIEE 11gR1 : Enhancements to Essbase Support
July 14th, 2010 by Mark Rittman
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.
July 14th, 2010 at 6:54 am
Great series of posts, thank you Mark.
But you badly need to brush up your German ;-)
have a nice day
@lex
August 5th, 2010 at 10:33 pm
Thanks. OBIEE sounds more stable than Essbase Studio.
August 24th, 2010 at 8:53 pm
Now this I couldn’t wait for because it was pretty frustrating attempting to get Cube users to adopt Answers while they cant do simple hierarchy drills. Das ist sehr gut Mark!! Danke! I’d be curious to test MSAS and SAP cubes as soon I install mine test env.
September 17th, 2010 at 9:55 am
hi:
I have some question to ask.
after installed essbase and BIEE 11G
in 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.
connect fail it will be appear.
please give me tips
thanks
September 21st, 2010 at 2:18 pm
Mark,
Today currently in 10.1.3.4 while federating between essbase relational we are kind of limited and forced to follow the OLAP design in implementing the cube. To elaborate what I said
Leys say the measures dim has two members Dollars and Units. The span dim has three members QTD, MTD and YTD. The currencies have USD and GBP for now (it can increase potentially)
But in datawarehouse in relational we do not have the model as mentioned above. All we have is measures multiplied across different combinations. In a sense we have something like this
Dollars_MTD_US, Dollars_QTD_Us, Dollars_YTD_US,
Dollars_MTD_GBP, Dollars_QTD_GBP, dollars_QTD_GBP,
Units_MTD_US, Units_QTD_US, Units_YTD_US,
Units_MTD_GBP, Units_QTD_GBP, Units_YTD_GBP
Potentially the combination of measures will increase as long as the currecies increase.
So we kind of filpped in essbase to be more user friendly by grouping them under SPAN and Currency dimension with maintaining only base measures (dollars and units)
It works fine for essbase. When drilling to relational details from essbase, OBIEE essbase logical column mapping require to map something equivalent of relational warehouse columns.
In this case when user picks YTD from SPAN, US from Currency and Base measure as Dollars, It should automatically understand that it should map the combination to Dollars_YTD_US relational column.
Is that something we can work in the BMM to do the comparsion check against a matrix or something. (Ideally i can only map one column at at a time for a single logical column coming from the same table)
I just dont want to import all the measures in essbase and mapp them to relational seperately or flip the table to match the essbase paradigm (which means repeat the rows and increase the depth of table)
Do you think some thing like this has a more convenient way of handling in 11g?
October 12th, 2010 at 7:36 pm
Thanks you so much for this post. But I am facing issue with Alias Name. I follow same above steps but still . Here is details below.
we are facing an issue with alias name for a gen6, Marketcode.
When we import a Revenue cube, I selected the check box of Import UDA’s and Complete Sturcture and cube got imported without any issues and i just drag the into another two layers. When i was trying to create a report in 11g answers with Gen6, Market code column its display only the Member name, but the requirement is to show member name and alias name.
Ex: :
Gen6, Member Name= XYZ, but the requirement is XYZ-Alias Name
How to aviod this with showing Member name and Alias Names.
October 12th, 2010 at 8:37 pm
Kiran, did you complete the step to import the Alias columns into the physical model? This should then make the aliases available as columns that you can then bring into the business model and then presentation subject area.
regards, Mark
March 17th, 2011 at 11:40 pm
The selection criteria seems to be buggy. Even in your screenshots you can see that when you “keep only children of Total Expenses” the screenshot still shows Profit, and other items above the line. That’s not how it should work. It should just keep items under that portion of the hierarchy. In playing with this today, I noticed the same issues with other essbase outlines. I have a feeling there may be a bug with the resulting query that is being built for Essbase.
March 20th, 2011 at 10:34 am
Sandy, I think you may be right. I’ve seen some odd behavior with this feature, though I’ve not looked into it too close. I’m working with the feature tomorrow, so I’ll try and take a look, and confirm this.
March 22nd, 2011 at 2:42 pm
Mark,
Thanks for the post on this!
Based on what you demoed here Why would we choose anything other than ‘Value’ as the hierarchy against Essbase seeing how OBIEE handles changes to the outline? I can see where you might have additional Generations added for others Dims as well.
What Adv/DisAdv would you get using UnBalanced, Ragged Balanced, Network, etc? Are those more for creating reports against OTHER sources than Essbase?
Thanks again Mark!
March 23rd, 2011 at 2:53 am
Hi Robert,
If your analyses were just going to be using Essbase data, I’d agree that setting the hierarchies to value-based would make sense, to protect against outline changes. The major limitation comes though if you wish to integrate (federate) your Essbase data with relational data sets; setting your Essbase-derived hierarchies to value-based removes the “levels” from them, and you need defined levels to be able to integrate with relational data sets, which are also generally level-based. See this blog posting for details:
http://www.rittmanmead.com/2010/10/oracle-bi-ee-11g-vertical-fragmentation-on-parent-child-hierarchies-drills-from-essbase-to-oracle-database/
If you only incoming data is Essbase-derived though, I’d agree, value-based hierarchies make sense.
Mark
April 20th, 2011 at 10:43 am
Hi,
Thanks for this post, it´s really useful.
I´m working with OBIEE 10.1.3.4 importing ESSBASE cubes. The Cubes are changing sometimes and I have to re-import the entire source in the phisical layer and change the entire BusinessModel (each time). I have read in the post that this is well-know by the users community. Do you know if there is an Oracle SR about this?
I´m working in a workaround for just re-import the model and re-map all the columns so the changes don´t impact in the reports or dashboards…
Thanks!!
Gabriel
April 20th, 2011 at 2:21 pm
Gabriel,
This (the fact you can’t re-import, or refresh, OLAP-derived physical databases in the OBIEE repository) is still the issue with OBIEE 11g; however what you can now do is set the incoming physical hierarchies from level-based to parent-child, which stops OBIEE creating separate physical cube columns for each generation in the Essbase database. This allows you to therefore add generations/levels to your Essbase dimension hierarchy without requiring a full re-import; there are still some “gotchas” though – you can’t easily do vertical federation (combining Essbase and relational data sources at a particular level) with parent-child hierarchies, and you also need particular versions of Essbase for this to work – at my last check, you needed Essbase 9.3.3 or 11.1.2, and 11.1.1.3 wasn’t supported yet (though this may have changed recently).
regards, Mark
May 13th, 2011 at 9:27 am
Mark, do you have some information about filtering a Answers request on a column that is not included in the request itself? In obiee 10g and essbase this combination was very very slow in execution as opposed to when we were filtering on a column that was also contained in the request as a column?
thx
May 26th, 2011 at 6:19 am
Is there a way to use an APS cluster name to connect OBIEE 11 to an Essbase cube?
The 2 main drivers for this would be:
1) You have to do regular rebuilds of your cube during period end using a bait and switch strategy
2) You just need to load balance for scalability
Any suggestions?
Thanks,
Dave
July 7th, 2011 at 7:50 pm
I am going to integrate the OBIEE and Essbase.
This contents was very useful to understand the integration
Thanks you
Anbuvel
August 2nd, 2011 at 10:09 pm
Hi Mark, Thank you for the article. How do you handle multi dimensional security integrating OBIEE and Essbase. Imagine i have restrition on a certain region and restriction to view only a part of my organisation. Similarly there are security restrictions on few other dimensions as well. Will you keep this info outside the Essbase cube in a table. If it can be done, then can you join this table and cube in OBIEE to see restricted data in dashboard/answers. I would highly appreciate your response.
Thanks
Rajesh