Business Intelligence – Oracle

Oracle BI EE 10.1.3.4 and Essbase Connectivity – Differences between EVALUATE and EVALUATE_AGGR – Creating Custom Measures

Posted by Venkatakrishnan J on February 4, 2009

One of the frequent questions which i am getting recently on the Essbase and BI EE connectivity is about the fundamental difference between EVALUATE and EVALUATE_AGGR. If you had looked at my blog entries here and here, i would shown you 2 variations of using EVALUATE. The former shows how to use EVALUATE for producing dynamic members. The latter shows how to use EVALUATE to produce custom measures. Since both of them are possible using EVALUATE itself, the question was “What is the use of EVALUATE_AGGR?”. Well, there are 2 major differences in the way Essbase uses EVALUATE and EVALUATE_AGGR

1. When used in Answers
2. When used in the Repository

In the first case, what happens is that the MDX query is determined by the BI Server. But since we do not have the option of specifying whether the EVALUATE column is a dimension or a fact measure from the UI, we are sometimes at the mercy of the BI Server determining the MDX for us. That is where my series of blog entries here would be helpful in understanding how BI Server generates these MDX queries. But in the second case, it is straightforward to understand.

1. When used in a repository, EVALUATE should be used for generating members or one to one numeric values like Rank in Dimensions. This accepts dimension generations as its parameter.
2. When used in a repository, EVALUATE_AGGR should be used for generating fact related custom measures. This accepts a static measure (Account dimension) members as its parameter. It can also accept dimension generations as its parameter. But the static measure member is a must.

Lets understand this with an example. We shall use the same example that we used here. So, basically our aim is to create a measure in the repository that would find the variance between Actual and Budget measures. From Answers, it is not possible to pass the measure values to EVALUATE as it supports only dimension generations as input. Hence, if you notice, we had hardcoded the measure values within the EVALUATE function. Instead of that let us go to the repository and create a new measure column as shown below

       

This custom measure is defined manually in the repository using the Evaluate function shown below.

EVALUATE_AGGR('%1 - %2', "localhost"."Demo".""."Basic"."Actual", "localhost"."Demo".""."Basic"."Budget")

       

Ensure that this measure has Evaluate_Aggr as its aggregation property.

       

Now, lets go to Answers and create a report out of this custom measure.

       

If you look at the MDX query, you can notice that the custom measure has been correctly translated into a custom measure in the MDX itself.

       

With
  set [Market3]  as '[Market].Generations(3).members'
  member [Scenario].[MS1] as '[Scenario].[Actual] - [Scenario].[Budget]', SOLVE_ORDER = 101
select
  { [Scenario].[Actual],
    [Scenario].[Budget],
    [Scenario].[MS1]
  } on columns,
  NON EMPTY {{[Market3]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

So for creating any custom measures in the repository EVALUATE_AGGR is used.

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

 
%d bloggers like this: