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