6 Embedded Database Functions Feature

This new feature enables users and administrators to create more powerful reports by directly calling Database functions from either Oracle BI Answers or by using a Logical column (in the Logical Table source) within the Metadata (repository).

The Oracle BI server now supports the capability to directly call functions defined within the Database from either the Answers interface or using a Logical column (in the Logical Table source) within the Metadata (repository). This feature is restricted to support SQL sources in this release.

Note: MDX or MOLAP data sources are not supported in this release.

The following new database functions are supported:

• EVALUATE

  For more information, see Section 6.1, "EVALUATE Function".

• EVALUATE_AGGR

  For more information, see Section 6.2, "EVALUATE_AGGR Function".

• EVALUATE_PREDICATE

  For more information, see Section 6.3, "EVALUATE_PREDICATE Function".

6.1 EVALUATE Function

This function is intended for scalar and analytic calculations.

Syntax: EVALUATE('DB_Function(%1)', <Comma separated Expression>)

Example: SELECT e.lastname,sales.revenue,EVALUATE('dense_rank() over(order by %1 )',sales.revenue) FROM sales s, employee e;

6.2 EVALUATE_AGGR Function

This function is intended for aggregate functions with group by clause.

Syntax: EVALUATE_AGGR('DB_Aggregate_Function(%1)', <comma separated Expression)

Example: SELECT year.year, sales.qtysold, EVALUATE_AGGR('sum(%1)', sales.quantity) From SnowFlakeSales;

6.3 EVALUATE_PREDICATE Function

This function is intended for functions with a return type of boolean.

Syntax: EVALUATE_PREDICATE('DB_Function(%1)', <comma separated Expression)

Example: SELECT year, Sales as DOUBLE,CAST(EVALUATE('OLAP_EXPRESSION(%1,''LAG(units_cube_sales, 1, time, time LEVELREL time_levelrel)'')', OLAP_CALC) AS DOUBLE) FROM "Global".Time, "Global"."Facts - sales" WHERE EVALUATE_PREDICATE('OLAP_CONDITION(%1, ''LIMIT time KEEP ''''1'''', ''''2'''', ''''3'''', ''''4'''' '') =1', OLAP_CALC) order by year;