Business Intelligence – Oracle

Archive for February 4th, 2009

Oracle BI EE 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.


  set [Market3]  as '[Market].Generations(3).members'
  member [Scenario].[MS1] as '[Scenario].[Actual] - [Scenario].[Budget]', SOLVE_ORDER = 101
  { [Scenario].[Actual],
  } 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.

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