OBIEE 11g for Hyperion Users - Are We There Yet?

In a couple of weeks time it's the OUG Conference Series EPM & Hyperion 2011 Conference in the UK (now there's a mouthful), and along with such Hyperion luminaries and Oracle ACE Directors as Edward Roske and Eric Helmer, I'm presenting a session entitled "OBIEE 11g for Hyperion Customers / Developers - Are We There Yet?". The idea behind this session is to take a look at OBIEE 11g, and see whether it finally provides an integrated BI/EPM platform that users of tools such as SmartView, Financial Reporting, Web Analysis and Interactive Reporting can migrate to. Now I've got an extended 90 minute session to present my thoughts on this, but at a high level, here's where I am now.

OBIEE 10g offered a lot of promise for Hyperion customers, in that you could connect Essbase to the Oracle BI Repository and create rich, interactive dashboards and analyses using Essbase data, relational data, or a combination of the two. The BI Server translated the incoming "logical SQL" queries from the Presentation Server into MDX queries that Essbase understood, allowing you to create reports, graphs and other visualizations that worked the same way whether your source data came from an Oracle Database, an Essbase cube, or any other sort of supported data source. You could also integrate the OBIEE dashboard with Hyperion Workspace, so that users signed on once through Shared Services and could then log in to OBIEE using the same credentials, inside a unified query environment, as detailed in this blog post from 2009.

But this integration came with limitations. The 10g version of OBIEE didn't really have a proper OLAP-style query tool, such that when you drilled-down from one hierarchy level to another, it kept adding columns to the report and placing filters on the column you've drilled on, and there was no proper hierarchical selection tool, meaning that Essbase users moving from tools such as SmartView or WebAnalysis found OBIEE 10g to be a very "relational" query environment. In addition, only level-based hierarchies were supported within the Oracle BI Repository, and whilst OBIEE did a good job in "unraveling" the parent-child hierarchies in Essbase into corresponding level-based hierarchy when you imported an Essbase outline into the repository, changes to the hierarchy - particularly adding new levels or generations into the Essbase hierarchy - usually meant that you had to re-import the whole Essbase cube back into the repository, losing any integration work that you'd done beforehand.

So OBIEE 11g offered a bunch of improvements that were particularly aimed at Essbase, and other OLAP server, customers; the new hierarchical columns feature brought proper OLAP analysis to OBIEE, and coupled with OLAP-oriented features such as hierarchical prompts and selection steps, made OBIEE 11g a much more conducive environment for Essbase users.

There's much better support for Essbase-native features such as value-based hierarchies, alternate hierarchies, multiple alias tables, UDAs and other Essbase artifacts, and the value-based hierarchy feature in particular is very welcome as it can avoid the need to completely re-import your Essbase database if your outline gains additional hierarchy levels.

So coupled with the new and improved visualization capabilities in OBIEE 11g, including maps, scorecards, improved dashboard controls, the whole new UI, improved BI Publisher and so on, there's a lot to recommend OBIEE 11g to users of the "legacy" Hyperion tools looking for a bit of a refresh of their BI environment.

Some of the new capabilities in OBIEE 11g, such as the action framework, may not immediately obvious to Hyperion users as useful to them, but they actually bring the ability to create additional integration between the platforms. One thing that's still lacking, for example, is the ability to write-back from the dashboard to Essbase (you can write back to relational sources, but not Essbase, yet), and you can also use it to call actions that, for example, run a calc script or perform an allocation routine. If you take a look at the new 11.1.1.5 version of the SampleApp (due out in a couple of weeks), there's some working examples "from the field" that perform this function, and there's a posting on our blog from last year that show how you can use the Action Framework, and the Credential Store within Oracle Fusion Middleware, to provide integration between the OBIEE toolset and the Hyperion toolset.

As you'll also be on the OBIEE platform, Oracle's "strategic" BI direction, you'll also be able to take advantage of features such as Oracle Enterprise Manager, Oracle Platform Security Services, and all the other middleware features that come with 11g. And you'll be able to also integrate with the Oracle Business Intelligence Applications, giving you the ability to reach through to an integrated data warehouse sourcing data from Oracle E-Business Suite, SAP, Siebel and Peoplesoft.

But with every new feature, you're going to expect to hit some new issues. Some things haven't changed in this release compared to 10g; if you do need to make changes to the Essbase database metadata in your Oracle BI Repository and you can't get around it by switching to value-based hierarchies, you still need to re-import the whole outline as there's no "sync" or "incremental import" feature in OBIEE 11g. The Oracle BI Server still function-ships very few OBIEE functions down to MDX functions, so you'll either have to have the BI Server perform analytic-style functions for you, or you'll need to use the EVALUATE function to pass them down yourself. And we've actually lost the integration between Hyperion Workspace and the OBIEE dashboard, so if you want both OBIEE and the Hyperion tools to use the same directory, you'll need to connect them both to an external LDAP server, and use some other SSO solution over both products rather than the "point-to-point" solution that worked with OBIEE 10g.

Some new features, such as value-based hierarchies, come with one or more "gotchas" that may well catch you out by causing one problem when you've just fixed another. If, for example, you've moved your Essbase hierarchies in the Oracle BI Repository physical layer to value-based ones, your users benefit from more efficient searches across the hierarchy and you won't need to re-import the outline if you add a new level or generation. But where you'll come unstuck now is if you're planning on setting up drill-to-detail reports, where Essbase provides the aggregated data but the detail comes from a relational database, such as Oracle. In this case, you'll find it hard to set up the "vertical federation" that's required as there are no "levels" to designate the granularity of the Essbase and relational sources. There are a number of workarounds (we covered two approaches, here and here, towards the end of 2010), but neither of them are pretty and you'll have a "facepalm"-type moment when you realise this limitation of value-based hierarchies.

Other, possibly more serious issue, is around the way that OBIEE generates the logical SQL, and physical MDX, for pivot-table style queries involving hierarchical columns. Take for example the following simple pivot-table report with a single product hierarchy and revenue measure, where the user has drilled-down into the data to produce some totals and sub-totals.

Now if this analysis was being generated against a relational source, the BI Server would generate three or four logical SQL requests for each measure "grouping", which in turn would normally be pushed-down into a single SQL statement that used sub-query factoring (the WITH clause). Again, not pretty, but databases such as Oracle 10gR2 onwards can handle this pretty well.

Where it gets tricky though is when you've got an equivalent Essbase source under the covers. In this case, these logical SQLs all get translated into one or more physical MDX statements, each one of which is returning the aggregated values for these sets of grouped measures. If we were using an "MDX-native" tool such as SmartView or WebAnalysis, we'd only need the one MDX query, but in this case our query has generated five of them.

Now where it gets really interesting is if we add a second, or third hierarchical column to the report, and in particular, when we pivot the dimensions around so that one of them runs along the columns, rather than just the rows, of the pivot table, like this:

In this case, what would appear to be a simple pivot table query with a couple of dimensions drilled-in to asymmetrically, ends up generating around fifty-eight separate MDX queries, each of which is relatively simple, but multiply that by a number of concurrent users, and you've got a seriously big load on your Essbase server.

Now in practice, there are workarounds, and the new subquery-caching feature in OBIEE 11g means that these MDX sub-queries are often retrieved from cache rather than sent on a round-trip to the Essbase server. You can also limit the effect by keeping all of the dimensions on the rows, rather than rows and columns, of your pivot table, and in practice most Essbase systems aren't all that heavily loaded, so you may well get away with it - but it's something to bear in mind.

Another new feature that can also add to this problem is selection steps. Selection steps are very interesting to Essbase customers as they allow you to build up queries that use just certain members from the dimension, combine them together, use them to filter data and do other "OLAP"-style filtering and selections. To take the example below, we've used the Sample.Basic cube to initially return the grand total figure, then used selection steps to keep just the Q1 and Q2 figures, add a calculated member for H1, then do something simular for the regions.

All good so far, and light-years ahead of what we could do with OBIEE 10g. The problem comes though with the way that the selection step sub-queries are translated from the logical SQL that the BI Server uses down to the physical MDX queries that Essbase uses. From the tests that Venkat has carried out, the number of logical SQLs that selection steps generates comes to the product of all the individual selection steps, so if you have three dimensions that contain 2, 3 and 3 selection steps each, you're going to end up with 2 x 3 x 3 = 12 logical SQLs, each one of which translates into 1 or 2 physical MDX queries. Once you add in the usual grand total at the top that brings back the entire cube, and any hierarchical column drills you might add in, you can see how you might end up with a very large number of MDX statements being sent to the Essbase server for what appears to be a very straightforward report.

Now in practice you tend to have workarounds for these issues; we tend to suggest keeping the number of hierarchical columns to a minimum in an analysis, and when you do use them, limit how far down you drill into them. If you use selection steps, try and keep them to one or two per dimension maximum, and overall, size your system to expect large numbers of relatively simple MDX statements.

Another aspect to consider is where your users current make use of the Excel Add-in, or SmartView, to do their analysis. Typically, they'll use Excel and the "query-by-example" feature of the Excel Add-in to write-in the names of the dimension members they want in the X and Y axis, and then do a retrieval based on the selected members. Using this approach, end-users can create pretty complex analyses where there's very little limitation on the combination of dimension members they use (selecting budget profit for this year, alongside the same figure for last year, together with actual inventory and forecast margin, for example). Creating analyses using these sorts of member selections is certainly possible using OBIEE 11g, but you'll need to learn a new set of skills to do it properly.

For example, if you want to create a dynamic time-series reference such as the value of a measure as at this time last year, or over the past three months, or year-to-date, you can use one of the OBIEE time-series functions (AGO, PERIODROLLING, TODATE) to provide an offset value for a measure.

Similarly, OBIEE 11g now by default creates a single measure within your logical fact table which is then dimensioned by the measure (accounts) and scenario dimension, if present. If you want to display on the screen your "budget profit" figure using this approach, you can use the FILTER function in OBIEE to filter the measure by these two dimension member names, an approach you can use to display any arbitrary measure and scenario combination in an analysis.

Another technique that's useful is being able to make use of native MDX function names through EVALUATE and EVALUATE_AGGR. As I mentioned earlier, one limitation with this current release is that very few OBIEE functions are function-shipped down to MDX, but if there's an MDX function you want to use, you can easily access it through EVALUATE (for scalar functions, such as TOPCOUNT) or EVALUATE_AGGR (for functions that return an aggregated value, such as STDDEV).

So there's two things to take away from this last point; firstly, don't think of OBIEE 11g as a like-for-like replacement for Excel and the Excel-Addin, think of it instead as a complementary tool that covers, say, 60% of its functionality but then provides a whole raft of features that Excel alone can't provide. Also, be prepared to roll your sleeves up and learn how OBIEE analyses are created, and expect your non-trivial reports to require a bit of Essbase knowledge an understanding of the more exotic features of Answers and OBIEE.

There's more to the story that I'll go into in the session, including what's now possible for Hyperion Planning and Hyperion Financial Management users, and I'll also run through some detail on how the actions integration works between the OBIEE dashboard and Essbase writeback and calc scripts, and I'll also go into more detail on how you set up vertical and horizonal federation between Essbase and relational sources, and why you might want to use the new LOOKUP function to do some of this instead. So without jumping the gun too much, it's a bit of a "qualified Yes", but I'll go into more detail on the day.

My session at the OUG Conference Series EPM & Hyperion 2011 is at 9.40am - 11.10am on Day 2 (15th June 2011), and full details on the event, including how UKOUG members can get in free, are on the event website. I'll also post the slides on our Articles page once the event is over. Hopefully I'll see you there.