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.
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