Oracle EPM 11.1.1.3 & Oracle OLAP 11g – Reporting on Oracle OLAP using Essbase Excel Add-in/Smartview – XOLAP

April 19th, 2010 by

This is the week of Colloborate 2010 conference. Mark, myself, Pete and Stewart were scheduled to present this year but due to the European Airspace closure, myself and Pete haven’t been able to actually travel this year. Its a real shame as we all really looked forward to this conference in Vegas. The topic that i was supposed to present on was the various applications of using XOLAP in a full life-cycle implementation. I have already blogged about some of the potential use cases of XOLAP before here and here. The other use case that i was planning to cover in the conference was the ability to use Essbase Smart-View/Classic Excel Add-in directly on Oracle OLAP 11g. Before the acquisition of Hyperion by Oracle, there were quite a few customer instances where Oracle OLAP lost to Essbase primarily because of the Excel front-end. Essbase integration with Excel goes a long way back and Oracle OLAP never really had a very efficient multi-dimensional UI (apart from D4O which is not being enhanced due to the advent of BI EE). But now though both these MOLAP tools scale very well, they are positioned differently and very rightly so. Even then, there is always the need to use Excel based Analysis even on Oracle OLAP.

To bridge this, Simba Technologies came out with a MDX-ODBC bridge which can basically convert MDX to SQL. Oracle did endorse this as can be inferred from this press release here. This is really good as one can easily access Oracle OLAP metadata using MDX without rewriting any of the front-end applications that use MDX. At about the same time, when EPM 11 release came out, Oracle released a new technology called XOLAP which essentially did the same thing i.e exposing Essbase Cubes using the inherent outline structure but by leveraging the SQL at runtime for data. I am not sure whether there is any public XSD based mappings to convert MDX to SQL, but the XOLAP does the conversion extremely well. In fact, the same methodology that i will be showing below can be used for BI EE as well.

What i will be showcasing today is quite simple and can be understood by the high level architecture diagram below

Picture 1

I have used Oracle OLAP 11g R2 since a significant number of bugs have been fixed in this release. The hardest part is in actually locating the 11g version of the Analytic Workspace Manager. The link in OTN is broken and it took me a while in getting the latest version. The latest version of AWM compatible with 11gR2 of Oracle database can be downloaded here. I shall be using the GLOBAL schema for demonstrating this. I start with first building a simple cube from AWM as shown below

Picture 2

Picture 3

There are 2 main things to be aware of before actually starting to the build the XOLAP cube from this Oracle OLAP cube.

1. Each cube built using Oracle OLAP 11g, will have at-least one dimension view per dimension and one cube view. So, the joins between the dimension views and cube views will be similar to joins between a dimension table and fact table. A sample screenshot of a dimension and cube view is given below.

Picture 4

In 11g, there is no necessity for creating the views separately (unlike 10g). And in 11g, the joins across the views perform very efficiently (like a normal table join) whereas in 10g, it is not recommended to make joins between the dimension and cube views.

2. Oracle OLAP is similar to an ASO cube in some respects. The Oracle OLAP 11g SQL views exposed are similar to a write-back partitioned ASO cube. For example, lets consider a simple hierarchy shown below

Picture 5

When we load data and aggregate the cube, the hierarchy stored within Oracle OLAP will be as shown below

Picture 6

Similar to Essbase ASO cube(to enable write-back partitions), this structure for Oracle OLAP provides it a flexibility to store upper level values and also a flexibility to join with the main cube view to get at the data. For example, a normal SQL query on relational data to get the value of member E in the above hierarchy will look as shown below (Assuming each column for each level is named as LEVEL_N)

But in OLAP since the value of member E is already stored, we should not be doing a SUM aggregation at run-time. Rather we should fire a SQL query to get at the stored value itself. That is done using the SQL query shown below

Understanding this is the key in knowing why we need a transparent partition even within Essbase. So, once the Oracle OLAP Cube is built, we log into Essbase Studio and then start building our XOLAP cube. The structure of the XOLAP cube will consist of all the dimensions but with a flat member list. All of these will be built using the OLAP 11g Views.

Picture 7

For example, lets take the Channel and Time Dimension for the XOLAP cube. Both these dimensions will have a flat hierarchy list as shown below

Picture 8

Picture 9

Once the hierarchies and measure hierarchies have been created within Essbase Studio, we need to build an Essbase Cube Schema and then deploy that as a XOLAP cube.

Picture 10

Picture 11

Open the XOLAP cube outline from within EAS. You should basically see a flat outline containing all dimension members.

Picture 12

It is not necessary that the XOLAP cube should have a flat structure. We just need a XOLAP cube where every dimension member in the Oracle OLAP 11g can be mapped to a level-0 member in the target XOLAP cube. In order to get the hierarchical structure for reporting, we again should build another Block Storage cube with no data but with just the hierarchical structure. I will build that here again using Essbase Studio as shown below

Picture 13

Picture 14

While building the cube we need to enable Duplicate member support as the XOLAP cube by default has that enabled. So for transparent partition to work the target Block Storage cube should also have this enabled. After enabling this deploy this block storage cube. This is the cube that will be used for reporting.

Picture 15

The final step in the process will be is to create the transparent partition between both the cubes. Since the member names match for all the dimensions, every time the cube is rebuilt, we can easily rebuild the partition using MaxL.

Picture 16

Picture 17

Now we can easily use the Hyperion Smart View or the classic Excel-add in to report on the OLAP data. Every time a report is run, Smart view will fire an MDX query against the Block Storage cube. Essbase will internally source it from the XOLAP cube through the transparent partition. XOLAP will then convert the MDX to SQL and generate the data. This will all be fast as direct reporting again Oracle OLAP, since Essbase will go only against pre-summarized OLAP intersections. No aggregations will be performed on Oracle OLAP unless necessary.

Picture 18

The same concept can be used using BI EE as well for reporting on Oracle OLAP data. Even in Oracle OLAP 11g, though the integration between BI EE and Oracle OLAP is much easier now, BI EE still does not fire the desired queries unless we model the repository using a complex multi-level assignment technique to go at the desired Oracle OLAP intersections. In the case of using XOLAP, that is not necessary.

Comments

  1. Bud Endress Says:

    A very interesting technical exercise, but it looks like a pretty complex alternative to using the MDX Provider for Oracle OLAP or the Oracle BI Spreadsheet Add-in (available for Oracle 10.2 now, soon for Oracle 11.x).

    Note that the URL to AWM for Oracle 11.2 is incorrect (the link in the post points to AWM 11.1). AWM 11.2 is available at http://www.oracle.com/technology/products/bi/olap/olap_downloads.html#software .

    There are a few comments that might leave the reader with the impression that aggregate level data is always pre-computed and stored in the Oracle OLAP cube. E.g.,

    “But in OLAP since the value of member E is already stored, we should not be doing a SUM aggregation at run-time. Rather we should fire a SQL query to get at the stored value itself.”

    While the cube presents all aggregate level data as if it is pre-computed in the relational view of the cube (eliminating the need for SUM … GROOUP BY in SQL), most aggregate level data is computed at runtime. Typically, only a small amount of data is pre-computed and stored in the cube.

    The following paragraph might leave readers with the impression that mapping OBIEE to an Oracle OLAP cube is complex:

    “The same concept can be used using BI EE as well for reporting on Oracle OLAP data. Even in Oracle OLAP 11g, though the integration between BI EE and Oracle OLAP is much easier now, BI EE still does not fire the desired queries unless we model the repository using a complex multi-level assignment technique to go at the desired Oracle OLAP intersections. In the case of using XOLAP, that is not necessary.”

    Readers should be aware that AWM (using the OBIEE Plug-in for AWM) can automatically create OBIEE repositories for querying OLAP cubes, thus making this an extremely simple process. The OBIEE Plug-in for AWM is available on the same OTN page (link above) where AWM can be downloaded from.
    Again, a very interesting exercise and post.

  2. Venkatakrishnan J Says:

    Hi Bud,

    Thanks for the comments & the updated link.

    1. With regard to the comment on run-time aggregations, what i actually meant was, run-time SUM within the OLAP engine is always desired rather than a run-time SUM outside of the OLAP engine. True, if we have skip level aggregations etc, OLAP will do the aggregations at run-time. But again i believe this is within the OLAP kernel/memory and i was actually pointing to the run-time SUM within the database SQL engine as opposed to the OLAP engine(for example, doing SUM(measure) in the SQL to get at upper intersection values as against filtering the member directly). But thanks again for the comments.

    2. Thanks for the AMW plug-in link. Actually, i have a follow post to this in draft form which was for covering the details of what the plug-in does. Will post it soon. The OLAP integration is much better now & easier because of the Plug-in.

    Thanks,
    Venkat

Website Design & Build: tymedia.co.uk