Business Intelligence – Oracle

Archive for April 9th, 2009

Oracle BI EE 10.1.3.4 and Essbase Connectivity – Understanding Aggregations – Part 3

Posted by Venkatakrishnan J on April 9, 2009

Lets look at one more interesting aggregation combination today which would result in different MDX queries and hence would result in different data being displayed. The combination that we would be using is given below

BMM: SUM and Physical Layer: SUM

Again to test this combination we shall use two forms of a similar report. The data for one report can be obtained from Essbase directly. The data for the other report would have to be derived from the existing Essbase data.

       

       

The MDX for this report is given below. As you see, this aggregation combination goes directly against the Essbase intersections.

With
set [Channel1] as '[Channel].Generations(1).members'
set [Product1] as '[Product].Generations(1).members'
select
{ [Measures].[Units] } on columns,
NON EMPTY {crossjoin ({[Channel1]},{[Product1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Global.Global]

This is very similar to the Aggr_External aggregation set to both the physical layer and the BMM. Now lets test a report wherein the data to be displayed would have to be derived.

       

       

If you look at the MDX below, you would notice that SUM function is shipped instead of AGGREGATE. So, what this means is that, if you use this combination you need to be sure your Essbase outline does not contain any Time Balance properties. If it contains Time balance properties then the numbers generated would be wrong, if the above combination is used.

With
set [Channel1] as '[Channel].Generations(1).members'
set [Product1] as '[Product].Generations(1).members'
set [Time2] as '{[Time].[1998], [Time].[1999], [Time].[2000]}'
member [Measures].[MS1] as 'SUM({[Time2]},Measures.[Units])'
select
{ [Measures].[MS1] } on columns,
NON EMPTY {crossjoin ({[Channel1]},{[Product1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Global.Global]

So far we have seen all the relevant combinations which would be commonly used. But again there are other combinations like Evaluate_Aggr, None etc which again can generate completely different set of MDX queries. I would not be covering these combinations as they are seldom useful. Neverthless, this wraps up my aggregation series. In the next blog entry i would summarize the observations so that anyone using BI EE on Essbase can refer to it while doing their implementation.

Posted in All Posts, EPM, Hyperion Essbase, OBI EE Plus | 1 Comment »