Business Intelligence – Oracle

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.

About these ads

One Response to “Oracle BI EE 10.1.3.4 and Essbase Connectivity – Understanding Aggregations – Part 3”

  1. […] from Answers. For more details on each of these aggregations check my blog entries here, here, here and […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: