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

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

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

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

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.

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.

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

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

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)

SELECT LEVEL_2, SUM(MEASURE) FROM DIMENSION
WHERE LEVEL_2 = 'E'
GROUP BY LEVEL_3

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

SELECT LEVEL_2, MEASURE FROM OLAP_FACTDIMENSION_VIEW
 WHERE LEVEL_2 = 'E'

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.

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

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.

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

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

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.

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.

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.

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.