Hyperion Essbase 11.1.1.2 – XOLAP – MDX to SQL Conversion – Part 1

Venkatakrishnan J

Jul 6, 2009 • 4 min read

One of the interesting aspects of XOLAP is the fact that it exposes Essbase as a dynamic relational aggregation engine. XOLAP to an extent is pretty good in determining what kind of sql should be pushed back to the relational sources and what requires custom in-memory calculations. For example, lets first start with a simple XOLAP cube that we built the other day using the Global Schema.

tmp1A

Lets import this XOLAP cube into BI EE and without changing any default aggregation settings.

tmp1B

After this import lets first create a very simple report containing the Generation 1 of Channel and Product dimensions.

tmp1D

tmp1E

To generate the above report BI EE basically generates an MDX that is fired back to Essbase.

With
  set [Channel Hierarchy1]  as '[Channel Hierarchy].Generations(1).members'
  set [Product Hierarchy1]  as '[Product Hierarchy].Generations(1).members'
select
  { [Units Hierarchy].[UNITS]
  } on columns,
  NON EMPTY {crossjoin ({[Channel Hierarchy1]},{[Product Hierarchy1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [GlobXola.GlobXola]

MDX is used to tell Essbase on what is needed in the report. Then Essbase converts the MDX to the physical sql. Unfortunately, XOLAP does not give you an automated way of finding the physical sql fired back to Essbase. The main reason for this is, it just does not fire the sql alone in some kind of reports. But for the above report, to determine the sql query, i had to use the v$sql views to find out what was getting fired by XOLAP(i would recommend DBMS_MONITOR package if you are on 10g). The converted sql from MDX looked like the one shown below

SELECT
cp_1108."TOTAL_CHANNEL_DSC",
cp_2106."TOTAL_PRODUCT_DSC",
SUM(cp_107."UNITS"),
count(*)
FROM
(("GLOBAL"."CHANNEL_DIM" cp_1108 join "GLOBAL"."PRODUCT_DIM" cp_2106 on (1=1))
join "GLOBAL"."UNITS_FACT" cp_107 on (cp_1108."CHANNEL_ID" = cp_107."CHANNEL_ID")
AND
(cp_2106."ITEM_ID" = cp_107."ITEM_ID"))
WHERE
( (cp_1108."TOTAL_CHANNEL_DSC" IN ('All Channels')))
AND
( (cp_2106."TOTAL_PRODUCT_DSC" IN ('Total Product')))
GROUP BY cp_1108."TOTAL_CHANNEL_DSC", cp_2106."TOTAL_PRODUCT_DSC"

The sql above is pretty straight forward. But one interesting point to note is the filters on the topmost node of the outline (though i have not explicitly applied the filters). So, to an extent XOLAP simply is not a conversion engine. It is an outline aware SQL conversion/aggregation engine. To put this under more tests, lets now include one more column in the report. But this column would contain an EVALUATE MDX expression to pull in all the Time dimension members.

EVALUATE('%1.dimension.members',"Time Hierarchy"."Gen1,Time Hierarchy")

tmp20

tmp21

Again, XOLAP has basically converted our BI EE report into its corresponding MDX given below

With
  set [Channel Hierarchy2]  as '[Channel Hierarchy].Generations(2).members'
  set [Product Hierarchy2]  as '[Product Hierarchy].Generations(2).members'
  set [Evaluate0]  as '{[Time Hierarchy].Generations(1).dimension.members }'
select
  { [Units Hierarchy].[UNITS]
  } on columns,
  NON EMPTY {crossjoin ({[Channel Hierarchy2]},crossjoin ({[Product Hierarchy2]},{[Evaluate0]}))} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [GlobXola.GlobXola]

But the corresponding sql is what would be of interest to us as the above report cannot be produced using a normal sql. Either it would have to be a union all request or it should use the MODEL clause. But its actually neither of them.

SELECT
cp_1108."TOTAL_CHANNEL_DSC",
cp_2106."TOTAL_PRODUCT_DSC",
cp_3109."MONTH_DSC",
SUM(cp_107."UNITS"),
count(*)
FROM
((("GLOBAL"."CHANNEL_DIM" cp_1108 join "GLOBAL"."PRODUCT_DIM" cp_2106 on (1=1))
join "GLOBAL"."TIME_DIM" cp_3109 on (1=1))
join "GLOBAL"."UNITS_FACT" cp_107 on  (cp_1108."CHANNEL_ID" = cp_107."CHANNEL_ID")
AND  (cp_2106."ITEM_ID" = cp_107."ITEM_ID") AND
(cp_3109."MONTH_ID" = cp_107."MONTH_ID"))
WHERE ( (cp_1108."TOTAL_CHANNEL_DSC" IN ('All Channels')))
AND ( (cp_2106."TOTAL_PRODUCT_DSC" IN ('Total Product')))
GROUP BY cp_1108."TOTAL_CHANNEL_DSC", cp_2106."TOTAL_PRODUCT_DSC", cp_3109."MONTH_DSC"

All that the sql does is, it produces level-0 data at the month level. Every other outline based calculation/aggregation is done by the Essbase at run-time. The next question would be, how are we sure that Essbase is doing the Aggregation instead of the BI Server. In order to eliminate this possibility, lets fire the MDX directly in Essbase.

tmp22

As you see, the MDX itself has produced the year and quarter level data even though the sql fired got the month level. So, every outline based operation is done at the Essbase layer using the Essbase aggregation engine. This opens up a lot of possibilities for reporting. We now have 3 levels of aggregation for relational sources.

Relational Database itself.
Essbase
BI Server

The major advantage of having this flexibility is that we can determine what is best depending on the system scalability etc. Also, it provides Essbase specific calculation capabilities to relational sources. I would cover more advanced reporting use cases in the coming blog entries.

Sign up for more like this.