EVALUATE in OBIEE with parameter

Last week I had a challange that I solved with Oracle BI EVALUATE function. Because I never used this “advanced” feature before I wrote this quick How-To.

Business example:

Think of a fact table like this one

AMOUNT | CUSTOMER_ID | DATE
200,00 | 1           | 01.09.2013
200,00 | 1           | 01.10.2013
200,00 | 1           | 01.11.2013
117,00 | 2           | 01.09.2013
119,00 | 2           | 01.10.2013

The business question is “how much pays a customer each month in average”. The result should be CUSTOMER_ID = 1 pays 200,00 each month ((200+200+200) / 3). CUSTOMER_ID = 2 pays an average of 118,00 each month ((117+119) / 2).

Of course you can solve this with Oracle BI standard features but my challange was a little bit more complex. But I broke it down to an easier example here!
I wrote an Oracle function that returns the average payment for the customer given as parameter. The function spec is

F_GET_AVG_AMOUNT(P_CUSTOMER_ID NUMBER) RETURN NUMBER;

-- Test
SELECT F_GET_AVG_AMOUNT(1) FROM DUAL; => 200,00
SELECT F_GET_AVG_AMOUNT(2) FROM DUAL; => 118,00

The function body is some SELECT with AVG(),  WHERE clause and GROUP BY and so on. I would not focus on it.

Now I would include this function call in Oracle BI. Therefore I created a new logical column within a logical fact table.

The column is derived from physical mapping with the following formula:

EVALUATE('F_GET_AVG_AMOUNT(%1)' as Double,  "ORCL".""."MY_SCHEMA"."FACT"."CUSTOMER_ID" )

The EVALUATE function passes the call down to the database. So the function call is executed within the database. %1 is a placeholder for a parameter that is added to the string. My parameter is the logical column CUSTOMER_ID. Of course you can add further parameter with %2, %3 and so on if your functions expect these. Add the values to the end of the function call seperated by commas. As Double indicates the returned data type.

The aggregation was set to NONE because the function does it already. Then I dropped this logical column to presentation layer and created a new analyse. The result is shown in the following picture. The column AVERAGE AMOUNT is filled by function call. The analyse is filtered to CUSTOMER_ID=1.

 You will find further information here:

http://docs.oracle.com/cd/E14571_01/bi.1111/e10540/sqlref.htm#BIEMG675
http://know2obiee.blogspot.de/2013/03/evaluate-function-in-obiee-to-embedded.html
http://obieetutorialguide.blogspot.de/2012/03/database-evaluate-function.html
http://gerardnico.com/wiki/dat/obiee/evaluate

 

 

This entry was posted in General, OBIEE. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>