Business Intelligence – Oracle

Oracle BI EE 10.1.3.3 – Support for Native database Functions and Aggregates

Posted by Venkatakrishnan J on September 10, 2007

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.

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

Advertisements

6 Responses to “Oracle BI EE 10.1.3.3 – Support for Native database Functions and Aggregates”

  1. Matt Bedin said

    Nice article.

    I suggest the highest value of this feature is when modeled in the enterprise semantic layer and transparent to end users.

    The Answers examples above largely address customers with single data source and end users have knowledge of the platform specific functions to more fully exploit it.

    A more pervasive model is where users are more fully abstracted from the physical models and syntaxes and interact with the enterprise semantic layer or business model.

    EVALUATE functions can be embedded as physical expressions in the business model’s logical table sources.

    End users would simply see the column name in the Answers picker and the BI Server would push down the appropriate function express ion the the various underlying data sources. A single presentation column could be mapped to many different physical sources with different expression syntaxes. End-users would be saved from this detail.

    Other interesting scenarios beyond leveraging database specific or custom written functions are using expressions to call Oracle Data Mining or OLAP expressions in Oracle OLAP.

  2. Venkatakrishnan J said

    Thanks Matt. Makes a lot of sense now. I believe it would be of greater use especially in olap since it can pass olap expressions on to the database as you said.

  3. Kurt Wolff said

    If used in the mappings in the logical table sources (i.e. with formulas involving physical columns) as Matt Bedin suggests, what classes of functions would be useable? Just class1 (scalar functions)? Are EVALUATE, EVALUATE_AGGR, and EVALUATE_PREDICATE only for Oracle or for all supported databases?

  4. Venkatakrishnan J said

    Hi Kurt,

    A very valid question. As per the docs this should work across databases (relational only for now) though i have not tried this out. I will update this blog entry once i try it on a sample mysql instance. With regard to the types of functions supported,

    EVALUATE – Supports scalar as well as analytic functions
    EVALUATE_AGGR – Supports aggregate functions
    EVALUATE_PREDICATE – Supports boolean return type functions

    Thanks,
    Venkat

  5. acc said

    Hi there,
    I’ve tried to use EVALUATE function in the Business Logic Layer, but when showed with Presentation Service, it raises error :
    “Cannot function ship the following expression: Evaluate( ..”

    Could u give me any idea how to solve this ?
    Thanks

    R’gards
    ACC

  6. Jagannath said

    I would like to use the EVALUATE_PREDICATE for FILTERS on Microsoft Analysis Cube that has been imported to OBIEE.
    The reason, on large cubes FILTERS are making the SQL server crash.
    The question: Can I use EVALUATE_PREDICATE in a filter against MSFT Analysis services cube ?

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: