Database Functions (EVALUATE)
Users and administrators can create requests 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. Key uses for
these functions include the ability to pass through expressions to get advanced
calculations, as well as the ability to access custom written functions or
procedures on the underlying database.
Support for database functions does not currently extend across all multidimensional
sources. Also, you cannot use these functions with XML data sources.
By default, support for the EVALUATE family of database functions
is disabled. You must change the EVALUATE_SUPPORT_LEVEL parameter in NQSConfig.INI
to enable support for the EVALUATE* functions.
# 1: evaluate is supported for users with manageRepositories permssion
# 2: evaluate is supported for any user.
# other: evaluate is not supported if the value is anything else.
EVALUATE_SUPPORT_LEVEL = 2;
Setting EVALUATE_SUPPORT_LEVEL to 2 will make all users use it properly.
Functions include:
- EVALUATE
- EVALUATE_ANALYTIC
- EVALUATE_AGGR
- EVALUATE_PREDICATE
EVALUATE
This function passes the specified database function with optional referenced columns as parameters to the back-end data source for evaluation. This function is intended for scalar calculations, and is useful when you want to use a specialized database function that is not supported by the Oracle BI Server, but that is understood by the underlying data source.
The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.
Syntax
EVALUATE('db_function(%1...%N)' [AS data_type] [, column1, columnN])
Where:
db_function is any valid database function understood by the underlying data source.
data_type is an optional parameter that specifies the data type of the return result. Use this parameter whenever the return data type cannot be reliably predicted from the input arguments. However, do not use this parameter for type casting; if the function needs to return a particular data type, add an explicit cast. You can typically omit this parameter when the database-specific function has a return type not supported by the Oracle BI Server, but is used to generate an intermediate result that does not need to be returned to the Oracle BI Server.
column1 through columnN is an optional, comma-delimited list of columns.
Examples
This example shows an embedded database function.
SELECT EVALUATE('instr(%1, %2)', address, 'Foster City') FROM employees
The following examples use the
EVALUATE_AGGREGATE and EVALUATE functions. Note that expressions are
applied to columns in the logical table source that refers to the physical
cube.Use EVALUATE_AGGREGATE to implement custom aggregations. For example, you may want to compare
overall regional profit to profits for the top three products in the region.
You can define a new measure to represent the profits for top three products
resulting in the Logical SQL statement:SELECT Region, Profit, EVALUATE_AGGREGATE('SUM(TopCount(%1.members, 3, %2), %3)',
Products, Profit, Profit) Top_3_prod_Profit FROM SampleBasic
The Oracle BI Server generates the following expression for the custom aggregation:
member [Measures].[MS1] AS 'SUM(Topcount([Product].Generations(6).members,3,[Measures].[Profit]),[Measures].[Profit])'
Use the
EVALUATE function on projected dimensions to
implement scalar functions that are computed post-aggregation. EVALUATE may change the grain of the query, if its
definition makes explicit references to dimensions (or attributes) that are not
in the query.For example, if you would like to see the Profits for the top five products ranked by Sales sold in a Region, after creating the applicable measure, the resulting Logical SQL statement is as follows
SELECT Region, EVALUATE('TopCount(%1.members, 5, %2)' as VARCHAR(20), Products, Sales), Profits FROM SampleBasic
The Oracle BI Server generates the following expression to retrieve the top five products:
set [Evaluate0] as '{Topcount([Product].Generations(6).members,5,[Measures].[Sales]) }'
EVALUATE_ANALYTIC
This function passes the specified database analytic function with optional referenced columns as parameters to the back-end data source for evaluation.
The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.
Syntax
EVALUATE_ANALYTIC('db_function(%1...%N)' [AS data_type] [, column1, columnN])
Where:
db_function is any valid database analytic function understood by the underlying data source.
data_type is an optional parameter that specifies the data type of the return result. Use this parameter whenever the return data type cannot be reliably predicted from the input arguments. However, do not use this parameter for type casting; if the function needs to return a particular data type, add an explicit cast. You can typically omit this parameter when the database-specific analytic function has a return type not supported by the Oracle BI Server, but is used to generate an intermediate result that does not need to be returned to the Oracle BI Server.
column1 through columnN is an optional, comma-delimited list of columns.
Examples
This example shows an embedded database analytic function.
EVALUATE_ANALYTIC('dense_rank() over(order by %1 )' AS INT,sales.revenue)
If the preceding example needs to return a double, then an explicit cast should be added, as follows:
CAST(EVALUATE_ANALYTIC('Rank(%1.dimension.currentmember, %2.members)',
"Foodmart93"."Time"."Month" as Double)
EVALUATE_AGGR
This function passes the specified database function with optional referenced columns as parameters to the back-end data source for evaluation. This function is intended for aggregate functions with a
GROUP BY clause.The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.
Syntax
EVALUATE_AGGR('db_agg_function(%1...%N)' [AS data_type] [, column1, columnN)
Where:
db_agg_function is any valid aggregate database function understood by the underlying data source.
data_type is an optional parameter that specifies the data type of the return result. Use this parameter whenever the return data type cannot be reliably predicted from the input arguments. However, do not use this parameter for type casting; if the function needs to return a particular data type, add an explicit cast. You can typically omit this parameter when the database-specific function has a return type not supported by the Oracle BI Server, but is used to generate an intermediate result that does not need to be returned to the Oracle BI Server.
column1 through columnN is an optional, comma-delimited list of columns.
Example
EVALUATE_AGGR('REGR_SLOPE(%1, %2)', sales.quantity, market.marketkey)
EVALUATE_PREDICATE
This function passes the specified database function with optional referenced columns as parameters to the back-end data source for evaluation. This function is intended for functions with a return type of Boolean.
The embedded database function may require one or more columns. These columns are referenced by %1 ... %N within the function. The actual columns must be listed after the function.
Note that
EVALUATE_PREDICATE is not supported for use with Essbase
data sources.Syntax
EVALUATE_PREDICATE('db_function(%1...%N)', [, column1, columnN)
Where:
db_function is any valid database function with a return type of Boolean that is understood by the underlying data source.
column1 through columnN is an optional, comma-delimited list of columns.
If you want to model a database function for comparison purposes, you should not use
EVALUATE_PREDICATE. Instead, use EVALUATE and put the comparison outside the
function. For example, do not use EVALUATE_PREDICATE as follows:EVALUATE_PREDICATE('dense_rank() over (order by 1% ) < 5', sales.revenue)
Instead, use
EVALUATE, as follows:EVALUATE('dense_rank() over (order by 1% ) ', sales.revenue) < 5
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;
