I was going through the new features guide in OBI EE 10.1.3.3. I happened to notice one other killer feature that everyone would like(me included). OBI EE 10.1.3.3 now natively supports the use of database functions directly from answers or from Metadata Repository. It supports the use of both one-to-one functions and also aggregate database functions. So if you want to use database specific functions directly from within answers, you can do that to your heart’s content now 
. Let’s see how to go about doing that here in this article. There are 3 types of functions that you can call within a database.
. Let’s see how to go about doing that here in this article. There are 3 types of functions that you can call within a database.1. Functions that just do some data modification. These are one to one functions. For example, substr, instr etc are all one to one functions.
In order to call these functions from BI Answers, a new function called EVALUATE has been added to the function dictionary of OBI EE. This EVALUATE function would take your database specific function as its argument and can also pass specific columns into the function. Lets see the syntax of EVALUATE(from the docs).
Syntax: EVALUATE(‘DB_Function(%1)’, <Comma seperated Expressions>)
As you see above, the columns are passed into the database specific functions like parameters in DOS. If you have more than 1 parameter to your function then you can use arguments like %2, %3 etc depending on how many parameters that you pass. But i believe currently it does not support parameters of different data types.
In above report, i am creating a new column called dense rank that would basically do a dense rank and order by the Amount.
As you see above, FACT.Amount would be passed as %1 to the dense rank function of the oracle database.
One can also call user-defined functions directly from the database. For example, i have created a very simple function test1 that would basically do a summation of the Amount and Effort.
2. Functions that do some aggregation. For example, one can call functions to do a group by on the resultant query like sum, count etc.
Syntax: EVALUATE_AGGR(‘DB_Aggregate_Function(%1)’, <comma separated Expressions>)
3. Functions that return a boolean value.
Syntax: EVALUATE_PREDICATE(‘DB_Function(%1)’, <comma separated Expressions>)
These functions are used in the where clause of a function. These are evaluated and help in filtering the resultant recordset.
But be aware that this supports only sql functions. It still does not support MDX or MOLAP data sources. For more information one can get the details here
Posts
