Error while Passing Session Variables to Essbase in Evaluate Expression

We can make use of native Essbase Functions using the Evaluate and Evaluate_Aggr Expressions in OBIEE.

Let’s first see how we can call an Essbase Function TOPCOUNT from within OBIEE.

The TOPCOUNT function returns a set of n elements ordered from largest to smallest, optionally based on an evaluation. For example:

To Find the top 3 Products ranked  by Sales I would use the following statement:

EVALUATE(‘TopCount(%1.members, 3, %2)’ as VARCHAR(20), Products,
Sales)

Where 3 is the INDEX of the Topcount function.

In the above statement the index n which is equal to 3 is passed to the Essbase Cube in order to retrieve the TOP 3 products ranked by sales.

In order to Call this function from within the OBIEE RPD, I have created a new logical column in the BMM layer called Top3 and in  the Expression Builder  I use the following statement.

EVALUATE(‘TopCount(%1.members, 3, %2)’ AS  VARCHAR ( 20 ), “universi-vlz1vh.oracle.com”.”Sales”.””.”Sales_db”.”Gen4,PRODUCT_D”, “universi-vlz1vh.oracle.com”.”Sales”.””.”Sales_db”.”SALE_AMOUNT”)

Image-0002

Image-0000

When entering the expression I would recommend inserting the required columns from the Expression Builder window rather than typing it. You will be less prone to make syntax errors this way.

Now when you drag this new column into the Presentation Layer and run a report in Answers you will see that the Top3 column does in fact give you the Top3 products ranked by Sales.

Image-0005Image-0006

Now let us see if we can make the index in the topcount (3 in the above case) function dynamic. Basically what I would like to do is to populate the index using a variable instead of hard-coding it in the Expression like we did earlier.

Let us see if the Evaluate Expression will work if we use a sessionVariable as the index.

I have created a session variable “N” which will return a numeric value. Then I created a new logical column and in the expression builder entered the following:

EVALUATE(‘TopCount(%1.members, VALUEOF(NQ_SESSION.N)  , %2)’ AS  VARCHAR ( 20 ), “universi-vlz1vh.oracle.com”.”Sales”.””.”Sales_db”.”Gen4,PRODUCT_D”, “universi-vlz1vh.oracle.com”.”Sales”.””.”Sales_db”.”SALE_AMOUNT”)

You can see that I have replaced the index with the session Variable Value.

Now let us run the report and see what we get.

Image-0004Image-0003

You can see that an error is generated. If you look at the MDX being generated by OBIEE then you find that the the session Variable value is not being passed into Essbase and so we get the Unknown Member used in Query Error. So to my knowledge it is not possible to dynamically pass the value of a OBIEE session variable to Essbase as an index value.

With
set [Evaluate0]  as ‘{TopCount([PRODUCT_D].Generations(4).members, VALUEOF(NQ_SESSION.N) , [Accounts].[SALE_AMOUNT]) }’
set [Axis1Set] as ‘{[Evaluate0]}’
select
{} on columns,
{[Axis1Set]} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Sales.Sales_db]

This is a significant handicap that puts limitations on the use of Evaluate Expressions to leverage the native Essbase Functions.

Advertisements

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: