Oracle BI EE 10.1.3.4.1 & Essbase Connectivity – Time Based Aggregation and Multiple Queries

A good question came up on our OOW session the other day on BI EE & Essbase connectivity for a means of doing Time Based aggregation on Essbase cubes. Time Based aggregation is a pretty common requirement while doing an Essbase implementation. For more details on what this means, refer my blog entry here. The question was, how do we push certain Time Based aggregation metrics to BI EE rather than setting them up in Essbase. Though it is possible to do this in the current release of BI EE, i would recommend not to do this in production (or even in dev) as the MDX queries fired by BI EE can easily consume the entire memory allocation for Essbase.

To start with lets take a simple example of the Demo Basic cube. The idea is to make Actuals a Time Based First measure. Every other measure would aggregate normally across all the dimensions. To do this, we start with changing the aggregation of the Actuals measure from Aggr_External to First only for the Time dimension

I have highlighted the disadvantages of converting the aggregation from Aggr_External to SUM in a prior blog entry here. So lets take a look at the MDX query when we create a report with the Time dimension

With
  set [Accounts4]  as '[Accounts].Generations(4).members'
  set [Market3]  as '[Market].Generations(3).members'
  set [Product3]  as '[Product].Generations(3).members'
  set [Year3]  as '[Year].Generations(3).members'
  set [Axis1Set] as 'crossjoin ({[Accounts4]},crossjoin ({[Market3]},crossjoin ({[Product3]},{[Year3]})))'
select
  {[Scenario].[Actual]} on columns,
  NON EMPTY  {[Axis1Set]} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

As you see, what this does is it does a level-0 cross join to get to all level-0 intersections and then it rolls it up in its own memory. I expected the BI Server to be level aware for atleast the chosen dimensions. Unfortunately, thats not the case. But what is good though is, it is knowledgeable enough to figure out that there is no FIRST aggregation in the MDX grammar and then does the manipulation in its own memory. To use MDX AGGREGATE function (Which takes care of Time Based aggregation), we need the TB properties to be set in the Essbase outline which we do not have. To make this more interesting lets, include the Budget measure which has the Aggr_External aggregation.

If we look at the MDX for the above query, you would notice 2 different MDX queries being fired simultaneously. The first query would be to fetch the Actual values (Same as the one above) and the other would be for Budget. The Budget MDX would hit the right intersections and its MDX is given below

With
  set [Year2]  as '[Year].Generations(2).members'
select
  { [Scenario].[Budget]
  } on columns,
  NON EMPTY {{[Year2]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

This is good. This is slightly different from relational models since, in all the cases, if we bring in multiple measures from the same fact table, BI Server always produces only one sql query. But in the case of Essbase sources, measures with different aggregation(only if you use based on dimensions aggregation) are treated as measures from multiple fact logical tables and hence multiple MDX queries are fired(and are joined together using the in-memory stitch join on Conforming dimensions). To take this further, lets create one more Budget measure called Budget – SUM and make its aggregation to be SUM and then include it in the report.

If you look at the report, you would notice, 2 MDX queries alone. This is good as well as the BI Server knows that there is an Aggr_External measure already and it requires only 2 queries to accomplish the final report.

With
  set [Year2]  as '[Year].Generations(2).members'
select
  { [Scenario].[Budget],
    [Scenario].[Budget]
  } on columns,
  NON EMPTY {{[Year2]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

This is really good again. So, what we have is a very efficient reporting engine that can achieve quite a few complex requirements. Though part of the above cannot be used in a prod environment due to obvious performance issues, we can derive certain conclusions based on the above and then use them for performance tuning. I will cover them in a future blog entry.