Tuesday, 24 December 2013

OBIEE 11g Release –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. 

Used For
Calculating ranks, percentiles, and n-tiles of the values in a result set.
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.
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.
Finding a value in a row a specified number of rows from a current row.
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.

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

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

Used to implement custom aggregate not supported by standard aggregation function, it is computed at the grain of query
EVALUATE_AGGR(‘db_function(%1,%2,…)’ as TYPE,parm1, parm2,…)
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

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.
EVALUATE_PREDICATE('db_function(%1...%N)', [, parm1, parm2,..)
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