Oracle OLAP 11g - Reporting in Excel using Simba MDX OLE-DB Provider
If you had looked at my blog entry here, i would have shown a way of reporting on Oracle OLAP 11g using the newly introduced Essbase XOLAP. As mentioned there, one of the biggest advantages of using Essbase is its tight integration with Excel through Smart View. Unfortunately, in the case Oracle OLAP, the excel add-ins were based on the BI Beans technology which is more or less deprecated now. Also the excel add-ins of Oracle OLAP were not as powerful as the Smart View add-in. In my previous blog entry i had shown how XOLAP interpreted the MDX fired from Visual Explorer/Smart-View and then converted them back to the corresponding SQL calls to Oracle OLAP. The SQL's generated by XOLAP were OLAP aware i.e multiple SQL's were generated to hit the correct pre-aggregated intersections rather than doing aggregations through SQL. There are 2 biggest drawbacks with this approach. They are
- It required an Essbase License
- Any change to the OLAP metadata required an XOLAP cube rebuild within Essbase
Some time last year, Simba Technologies announced an MDX OLE-DB provider for Oracle OLAP. So far i did not get an opportunity to test this though it looked promising. Couple of weeks back we got an evaluation copy from Simba to test the driver (i will have to thank Simba and their Oracle OLAP - MDX provider team for providing us with an evaluation copy). This driver basically provides an ability for Excel users to leverage the power of Oracle OLAP using the Excel Pivot Tables/Charts etc. At a high level this driver does the following
- End users can use the native Excel functionality to create charts/pivot tables etc
- The charts/Pivot tables generate MDX (standard OLE-DB based microsoft MDX)
- Simba driver then converts the MDX to one or more SQL calls to the Oracle OLAP
In this blog entry we will basically see how this driver works. The install process is quite straightforward where we are taken through a set of steps that will setup the OLE-DB driver. This driver will work only for Oracle OLAP versions 11.1.0.7 or above. Then we start off with setting up a DSN to connect to the Oracle OLAP database. Ensure that the client driver of Oracle used in the DSN is atleast of the 11.1.0.7 version.
Once this is setup, from Excel use the Data Connection Wizard to setup a OLE-DB connection through the Simba MDX driver.
This should automatically connect us to the Oracle OLAP schemas.
As you see, we can save a connection to a cube and then the same connection can be reused later for creating more reports. Lets start with creating a simple pivot table report (using native Microsoft Excel Pivot tables)
After this if you notice, we will now be having metadata of Oracle OLAP exposed within the Pivot Table member selection panels. This is very similar to Hyperion Visual Explorer where we are shown all the levels in a dimension and all the hierarchies as well. We can pick and choose either a specific level or we can choose members from multiple levels by applying proper filters. Lets create a very simple report as shown below
As you see, we now have the ability to drill using the native MS pivot table functionality. Lets look at the MDX fired to generate the above query.
SELECT
{[MEASURES].[SALES],[MEASURES].[SALES_YTD]}
DIMENSION PROPERTIES
PARENT_UNIQUE_NAME ON COLUMNS ,
NON EMPTY
CrossJoin(Hierarchize({DrilldownLevel({[TIME].[CALENDAR].[ALL_YEARS].[ALL_YEARS]})}),
Hierarchize({DrilldownLevel({[PRODUCT].[STANDARD].[ALL_PRODUCTS].[ALL_PRODUCTS]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,
[TIME].[CALENDAR].[CALENDAR_YEAR].[CALENDAR_YEAR_END_DATE],
[TIME].[CALENDAR].[CALENDAR_YEAR].[CALENDAR_YEAR_TIME_SPAN],
[TIME].[CALENDAR].[CALENDAR_YEAR].[CALENDAR_YEAR_LONG_DESCR],
[TIME].[CALENDAR].[CALENDAR_YEAR].[CALENDAR_YEAR_SHORT_DESC],
[TIME].[CALENDAR].[CALENDAR_YEAR].[END_DATE],
[TIME].[CALENDAR].[CALENDAR_YEAR].[TIME_SPAN],
[TIME].[CALENDAR].[CALENDAR_YEAR].[LONG_DESCRIPTION],
[PRODUCT].[STANDARD].[DEPARTMENT].[DEPARTMENT_LONG_DESCRIPT],
[PRODUCT].[STANDARD].[DEPARTMENT].[DEPARTMENT_SHORT_DESCRIP],
[PRODUCT].[STANDARD].[DEPARTMENT].[LONG_DESCRIPTION] ON ROWS
FROM
[SALES_CUBE] CELL PROPERTIES VALUE, FORMAT_STRING,
LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS,2
As you see the MDX retrieves all the necessary Oracle OLAP level properties as MDX intrinsic properties. This is very interesting. The MDX driver basically does a metadata level mapping between MDX and Oracle OLAP. I am not sure how much of this is documented(in terms of MDX to SQL conversion calls) but again this looks very promising. Now lets look at the SQL that is fired back to Oracle OLAP. The driver can generate multiple SQL Queries for a single MDX call. This is very similar to BI EE 11g (which i shall be covering later once BI EE 11g is GA) where while doing a drill to multiple levels we will see multiple SQL calls being generated.
The first 2 SQL's generated(for this report) will be for constructing the metadata or the member list for all the dimensions that are part of the query
SELECT 'OLAPTRAIN' AS CATALOG_NAME, 'SALES_CUBE' AS CUBE_NAME, members.DEPTH AS LEVEL_NUMBER, members.HIER_ORDER AS MEMBER_ORDINAL, members.DIM_KEY AS MEMBER_NAME, 1 AS MEMBER_TYPE, SHORT_DESCRIPTION AS MEMBER_CAPTION, 1 AS CHILDREN_CARDINALITY, CASE WHEN (members.PARENT IS NULL) THEN NULL ELSE members.DEPTH-1 END AS PARENT_LEVEL, CASE WHEN members."CALENDAR_QUARTER" IS NOT NULL AND members.LEVEL_NAME != 'CALENDAR_QUARTER' THEN '[TIME].[CALENDAR].[CALENDAR_QUARTER].[' || members.PARENT || ']' WHEN members."CALENDAR_YEAR" IS NOT NULL AND members.LEVEL_NAME != 'CALENDAR_YEAR' THEN '[TIME].[CALENDAR].[CALENDAR_YEAR].[' || members.PARENT || ']' WHEN members."ALL_YEARS" IS NOT NULL AND members.LEVEL_NAME != 'ALL_YEARS' THEN '[TIME].[CALENDAR].[ALL_YEARS].[' || members.PARENT || ']' ELSE (CAST (NULL AS VARCHAR2(1))) END AS PARENT_UNIQUE_NAME, (CAST (NULL AS VARCHAR2(1))) AS DESCRIPTION , members.CALENDAR_YEAR_END_DATE AS PROPERTY_4 , members.CALENDAR_YEAR_TIME_SPAN AS PROPERTY_5 , members.CALENDAR_YEAR_LONG_DESCR AS PROPERTY_6 , members.CALENDAR_YEAR_SHORT_DESC AS PROPERTY_7 , members.END_DATE AS PROPERTY_20 , members.TIME_SPAN AS PROPERTY_21 , members.LONG_DESCRIPTION AS PROPERTY_22 , 'TIME' AS DIMENSION_NAME , 'CALENDAR' AS HIERARCHY_NAME , members.LEVEL_NAME AS LEVEL_NAME FROM "OLAPTRAIN".TIME_CALENDAR_VIEW members WHERE members.LEVEL_NAME = 'CALENDAR_YEAR' ORDER BY MEMBER_ORDINAL, PARENT_UNIQUE_NAME, MEMBER_NAME
SELECT 'OLAPTRAIN' AS CATALOG_NAME, 'SALES_CUBE' AS CUBE_NAME, members.DEPTH AS LEVEL_NUMBER, members.HIER_ORDER AS MEMBER_ORDINAL, members.DIM_KEY AS MEMBER_NAME, 1 AS MEMBER_TYPE, SHORT_DESCRIPTION AS MEMBER_CAPTION, 1 AS CHILDREN_CARDINALITY, CASE WHEN (members.PARENT IS NULL) THEN NULL ELSE members.DEPTH-1 END AS PARENT_LEVEL, CASE WHEN members."COUNTRY" IS NOT NULL AND members.LEVEL_NAME != 'COUNTRY' THEN '[GEOGRAPHY].[REGIONAL].[COUNTRY].[' || members.PARENT || ']' WHEN members."REGION" IS NOT NULL AND members.LEVEL_NAME != 'REGION' THEN '[GEOGRAPHY].[REGIONAL].[REGION].[' || members.PARENT || ']' WHEN members."ALL_REGIONS" IS NOT NULL AND members.LEVEL_NAME != 'ALL_REGIONS' THEN '[GEOGRAPHY].[REGIONAL].[ALL_REGIONS].[' || members.PARENT || ']' ELSE (CAST (NULL AS VARCHAR2(1))) END AS PARENT_UNIQUE_NAME, (CAST (NULL AS VARCHAR2(1))) AS DESCRIPTION , members.ALL_REGIONS_SHORT_DESCRI AS PROPERTY_9 , members.ALL_REGIONS_LONG_DESCRIP AS PROPERTY_10 , members.LONG_DESCRIPTION AS PROPERTY_12 , 'GEOGRAPHY' AS DIMENSION_NAME , 'REGIONAL' AS HIERARCHY_NAME , members.LEVEL_NAME AS LEVEL_NAME FROM "OLAPTRAIN".GEOGRAPHY_REGIONAL_VIEW members WHERE members.LEVEL_NAME = 'ALL_REGIONS' ORDER BY MEMBER_ORDINAL, PARENT_UNIQUE_NAME, MEMBER_NAME
Then the final query will be for generating the measure values.
SELECT
SALES_CUBE_VIEW.SALES, SALES_CUBE_VIEW.TIME,
SALES_CUBE_VIEW.PRODUCT
FROM
"OLAPTRAIN".SALES_CUBE_VIEW SALES_CUBE_VIEW
WHERE
SALES_CUBE_VIEW.TIME IN ('ALL_YEARS', 'CY2008', 'CY2010','CY2007','CY2009' )
AND SALES_CUBE_VIEW.PRODUCT IN ('ALL_PRODUCTS', '-518', '-519', '-520' )
AND SALES_CUBE_VIEW.CHANNEL = 'ALL_CHANNELS'
AND SALES_CUBE_VIEW.GEOGRAPHY = 'ALL_REGIONS'
If you look at all the queries, they are all OLAP aware i.e. default member filters are applied properly and there is no additional aggregation that is pushed through SQL. This is very interesting and if there are customers using Oracle OLAP, this is one driver that can potentially be put to good use for Excel based reporting.
Currently looks like there is no way to fire custom MDX queries through the Excel 2007 that i have. So, i am not sure how the driver will behave/work when we push custom MDX aggregations like AGGREGATE, SUM etc. Also, i am not sure whether a mapping for all MDX functions(like intersect, union etc) to corresponding OLAP SQL calls exist. But I was told that custom MDX functions should also work well. It is just a case of Excel 2007 not supporting custom MDX queries for the native Pivot Tables.