Tuesday, 24 December 2013

OBIEE 11g Release 11.1.1.7 –Oracle Analytical Functions and OBIEE Evaluate Option


Oracle has SQL's analytical processing capabilities which use analytic SQL functions. These analytical functions enable us to calculate Rankings and percentiles, moving window calculations, Lag/Lead analysis, First/Last analysis and linear regression statistics.

Can we leverage these advance analytical processing capabilities to add value to the existing OBIEE reporting stack?  I believe using analytical function to answer questions around financial analytics or spend and procurement analytics can improve the overall offering by tenfold.

 This blog is an attempt to introduce analytical function and list down different options to include them in our offerings.

Analytical Functions

Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.

The analytical function can be illustrated as below; the description of the analytical function is also included below.

 



analytic_function([ arguments ]) OVER (analytic_clause)

The analytic clause includes query partition clause, order by clause or windowing clause as illustrated below.



The description of analytics clause is as below
[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]

SQL for Analysis and Reporting

The following is the list of analysis type and its usage supported by Oracle Analytical Functions. 

Type
Used For
Ranking
Calculating ranks, percentiles, and n-tiles of the values in a result set.
Windowing
Calculating cumulative and moving aggregates. Works with these functions: SUM, AVG, MIN, MAX, COUNT, VARIANCE, STDDEV, FIRST_VALUE, LAST_VALUE, and new statistical functions. Note that the DISTINCT keyword is not supported in windowing functions except for MAX and MIN.
Reporting
Calculating shares, for example, market share. Works with these functions: SUM, AVG, MIN, MAX, COUNT (with/without DISTINCT), VARIANCE, STDDEV, RATIO_TO_REPORT, and new statistical functions. Note that the DISTINCT keyword may be used in those reporting functions that support DISTINCT in aggregate mode.
LAG/LEAD
Finding a value in a row a specified number of rows from a current row.
FIRST/LAST
First or last value in an ordered group.
Linear Regression
Calculating linear regression and other statistics (slope, intercept, and so on).
Inverse Percentile
The value in a data set that corresponds to a specified percentile.
Hypothetical Rank and Distribution
The rank or percentile that a row would have if inserted into a specified data set.

OBIEE 11g Solution

To enable access to extend physical source capabilities, as set of following functions are introduced as part of the BI Server API grammar.

EVALUATE
Used to implement scalar functions and are computed post-aggregation.
Syntax
EVALUATE(‘expression(%1,%2,…)’ as TYPE,parm1, parm2,…)
Example
EVALUATE(‘dense_rank()over(order by %1)’,rev) – calculates dense rank for column rev

EVALUATE_ANALYTIC
This function passes the specified database analytic function with optional referenced columns as parameters to the back-end data source for evaluation.
Syntax
EVALUATE_ANALYTIC('db_function(%1...%N)' [AS data_type] [, parm1, parm2,…])
Example
EVALUATE_ANALYTIC('dense_rank() over(order by %1 )' AS INT,sales.revenue)
Or/And
CAST(EVALUATE_ANALYTIC('Rank(%1.dimension.currentmember, %2.members)',
"Foodmart93"."Time"."Month" as Double)

EVALUATE_AGGR
Used to implement custom aggregate not supported by standard aggregation function, it is computed at the grain of query
Syntax
EVALUATE_AGGR(‘db_function(%1,%2,…)’ as TYPE,parm1, parm2,…)
Example
EVALUATE_AGGR(‘REGR_SLOPE(%1,%2)’ as Double,quantity_sold,list_price) – returns thee slope component of a linear regression line of quanity_sold over list_price


EVALUATE_PREDICATE
Used to model native Boolean types for relational systems. Data sources which utilize such expressions include Oracle Spatial filters and Oracle Analytic Workspaces. It is used as filter clause within OBIEE query.
Syntax
EVALUATE_PREDICATE('db_function(%1...%N)', [, parm1, parm2,..)
Example
EVALUATE_PREDICATE('length(%1)>6',"A - Sample Sales"."Products"."P4 Brand") – requests that brand values be greater than 6 characters.

Note -
The ability to use some of these functions is disabled by default. To enable support for this function, change the EVALUATE_SUPPORT_LEVEL parameter in NQSConfig.INI.

I hope you this collected information around analytical function useful.

No comments:

Post a Comment