Oracle Essbase 11.1.1.3 – Integration with Relational Sources and Applications

In the past i have covered a wide range of integration possibilities of Oracle Essbase with  relational sources. This is probably one area that will always evoke interest as from a reporting perspective it is not always possible to maintain the data in only one source(either be it Essbase or a relational database). For example, a typical use case of Essbase(or any OLAP product for that matter) is to pre-aggregate the data so that the aggregated data is always available for reporting. It is practically not possible to store every possible dimension within Essbase. For example, a big retail client cannot simply load all their possible (running to 50 to 100 million unique products) transactional product level data into Essbase unless and of course they have a very big server. Similarly, it is not possible to store every transactional data in a relational source for reporting due to obvious performance reasons. Generally a reporting strategy is arrived at in such a way that a compromise on what is stored in Essbase and what is stored in a relational source(reporting silos). This leads us to a obvious integration scenario as generally reports would have to be derived from both the sources. What kind of integration actually depends on the nature of reporting requirements. Today, i would basically summarize the various points of integration that i have covered before between Essbase and a relational source

1. Hybrid OLAP (HOLAP) – This is something that has been there right from very early releases of Essbase. In pre-11 releases, the Hybrid OLAP capability (drill through) was achieved through Essbase Integration Services and now in the 11 release it is achieved through Essbase Studio(one can do it even from EAS now). This integration basically provides a seamless drill through for an end user from Essbase to a SQL source. This is done at pre-defined cell intersections wherein the drill through reports are setup. Multiple drill through reports can be defined at a specific Essbase Cell and end users can choose the reports from the front-end. I have covered this integration in my blog entry here.

Advantages – This is very easy to setup. End users can get to choose the reports that they would like to drill through to. Supported both on ASO and BSO cubes.

Disadvantages – There are some disadvantages. Though this does provide a drill through, it does not provide a capability wherein a relational and an Essbase measure can be combined together (like Actuals from relational and Budget from Essbase). Also, it is not possible to build reports out of non-stored(or relational only) dimensional attributes directly. BI EE cannot leverage this yet and only tools like Smart-View, Web Analysis and Hyperion Financial Reporting to an extent can actually leverage the drill through.

2. Extended OLAP (XOLAP) – This is a new feature introduced with the EPM 11 release. This was primarily introduced to negate the disadvantages of HOLAP. This is something very unique as this was the first time, Essbase engine could actually fire SQL queries back to a relational database. And the SQL produced varies based on the dimension members chosen (not a static SQL like HOLAP wherein just the dimension members qualifying the cell intersection alone are passed as parameters).

Advantages – Provides both relational drill through as well as a means for comparing relational and Essbase specific measures in a single report. This is done through setting up a transparent partition between an XOLAP based cube and a normal BSO/ASO cube. I have covered this technique here.

Disadvantages – Though this is very good and offers both horizontal and vertical drill through, it simply does not seem to scale when the outline size increases beyond a point. It only supports Essbase Studio and it only creates an ASO cube with a duplicate member option enabled by default. The data source would have to be very good in data quality as even a small set of member kick-outs during a cube build can completely stop the build process. Incremental member addition from EAS is not supported and for every cube build, the database would have to be recreated again.

3. Integration through BI EE – With the introduction of Essbase connectivity in 10.1.3.3.2 release of BI EE and 3 major patch releases after that till 10.1.3.4.1, BI EE has come a long way in doing multi-dimensional reporting(and still a long way to go). The BI Server provides comprehensive capabilities which can be leveraged to do some complex reporting. It provides a drill capability between Essbase and a relational source. Both vertical and horizontal drills are supported.

Advantages – BI EE provides both Horizontal and Vertical Fragmentation. The details of the integration is in the paper that we presented in AIOUG and UKOUG BIRT SIG. It also provides integration in the form of a relational to a multi-dimensional drill through using Smart-View protocol. And 2 way drills are now possible between BI EE and other reporting tools like HFR and Web Analysis. With 11g, the integration should hopefully become even more seamless.

Disadvantages – BI EE still is not multi-dimensional aware. So, certain workarounds would have to be applied while creating certain kind of reports. The drill through capabilities are not supported/possible on ragged hierarchies.

4. Integration using Custom Programs/APIs – Though this is not commonly used, i have seen some customers using this extensively as well. This is a very robust method of integration wherein native APIs are used to access the data directly. For example, open source reporting toolsets like Jasper etc do not have native access to Essbase. So, in such cases custom reporting pages are custom built using JAPI(or any other API like C, VB etc) of Essbase and native Java drivers of the relational sources.

Advantages – This is probably the only method that gives complete control of the integration. During the time when BI EE did not have a connectivity to Essbase, JAPI was the only way to bring in Essbase data into BI EE. Also, it also provides native write-back capabilities from BI EE to Essbase.

Disadvantages – Though this provides complete control to the report developers, it is very important for the developers to have a good understanding of the various APIs. I have seen performances of the servers going down drastically when the JAPI code does not close sessions cleanly.

One more possible future integration scenario is to use Web Services instead of native APIs. Currently Web Service for Essbase is not available out of the box. One would have to hand code a custom report as a data service and expose it for other tools. But in future releases when the Web Service API of Essbase is available, it should open up even more integration possibilities. Along similar lines, in the case of BI EE which has a well document Web Service API, it still lacks a good Java API. I am not sure whether it is actually in the development pipeline to bring a full fledged Java API for BI EE. But a web service for Essbase and a Java API for BI EE really would open a lot of potential in achieving different kinds of integration.