Tuesday, 31 December 2013

OBIEE 11g Release 11.1.1.7 – Linear Regression SLOPE, INTERCEPT, REGR_R2, REGR_COUNT, REGR_AVGX, REGR_AVGY




Statistics functions are used to compute covariance, correlation, and linear regression statistics. Each function operates on an unordered set. They also can be used as windowing and reporting functions.

Linear Regression

This example computes an ordinary least-squares regression line that expresses the Avg Order Size as a linear function of its Revenue. The following functions are used:

SLOPE - slope of determination of the regression line
INTERCEPT - intercept of determination of the regression line
REGR_R2 - coefficient of determination of the regression line
REGR_COUNT - number of items
REGR_AVGX - averageY
REGR_AVGY - averageX

Calculation Example
Description
This example computes an ordinary least-squares regression line that expresses the Avg Order Size as a linear function of its Revenue
Selected Items
"Time"."Per Name Month","Base Facts"."1- Revenue", "Simple Calculations"."25  Avg Order Size"
Sort Order
"Time"."Per Name Month"
Condition
"Time"."Per Name Year"  IN ( '2010',’2011’.’2012’)
Calculation Name
Slope, Intercept  Coefficient,m Average, Average2 and Reg Line
Database Calculation
Slope = REGR_SLOPE(Avg Order Size, Revenue) OVER(ORDER BY Avg Order Size)
Intercept = REGR_INTERCEPT(Avg Order Size, Revenue) OVER(ORDER BY Avg Order Size)
Coefficient = REGR_R2(Avg Order Size, Revenue) OVER(ORDER BY Avg Order Size)
Average = REGR_AVGX(Avg Order Size, Revenue) OVER(ORDER BY Avg Order Size)
Average 2 = REGR_AVGY(Avg Order Size, Revenue) OVER(ORDER BY Avg Order Size)
Calculation for Analytics Using Evaluate Function
Intercept
EVALUATE_AGGR( 'REGR_INTERCEPT(%1, %2)', "Base Facts"."1- Revenue","Simple Calculations"."25  Avg Order Size")
Slope
EVALUATE_AGGR( 'REGR_SLOPE(%1, %2)', "Base Facts"."1- Revenue","Simple Calculations"."25  Avg Order Size")
Coefficient
EVALUATE_R2( 'REGR_SLOPE(%1, %2)', "Base Facts"."1- Revenue","Simple Calculations"."25  Avg Order Size")
Average
EVALUATE_ AVGX( 'REGR_SLOPE(%1, %2)', "Base Facts"."1- Revenue","Simple Calculations"."25  Avg Order Size")
Average 2
EVALUATE_ AVGY( 'REGR_SLOPE(%1, %2)', "Base Facts"."1- Revenue","Simple Calculations"."25  Avg Order Size")
Reg Line
AGGREGATE(EVALUATE_AGGR( 'REGR_SLOPE(%1, %2)', "Base Facts"."1- Revenue","Simple Calculations"."25  Avg Order Size") BY 1)* AGGREGATE("Simple Calculations"."25  Avg Order Size" BY "Time"."Per Name Month")+EVALUATE_AGGR( 'REGR_INTERCEPT(%1, %2)', "Base Facts"."1- Revenue","Simple Calculations"."25  Avg Order Size")
Notes
EVALUATE_AGGR is used for shipping function to database for intercept, slope and Reg Line
Result – Table View/Graph View

I hope you find this information useful.

OBIEE 11g Release 11.1.1.7 – Evaluate Statistical Function –Linear Regression



Statistics functions are used to compute covariance, correlation, and linear regression statistics. Each function operates on an unordered set. They also can be used as windowing and reporting functions.

Linear Regression

This example computes an ordinary least-squares regression line that expresses the Avg Order Size as a linear function of its Revenue. The following functions are used:

SLOPE - slope of determination of the regression line

INTERCEPT - intercept of determination of the regression line

REG LINE – Line co-ordinates based on slope and intercept

Calculation Example

Description

This example computes an ordinary least-squares regression line that expresses the Avg Order Size as a linear function of its Revenue

Selected Items

"Time"."Per Name Month","Base Facts"."1- Revenue", "Simple Calculations"."25  Avg Order Size"

Sort Order

"Time"."Per Name Month"

Condition

"Time"."Per Name Year"  IN ( '2010',’2011’.’2012’)

Calculation Name

Slope, Intercept and Reg Line

Database Calculation

Slope = REGR_SLOPE(Avg Order Size, Revenue) OVER(ORDER BY Avg Order Size)

Intercept = REGR_INTERCEPT(Avg Order Size, Revenue) OVER(ORDER BY Avg Order Size)

Calculation for Analytics Using Evaluate Function

Intercept
EVALUATE_AGGR( 'REGR_INTERCEPT(%1, %2)', "Base Facts"."1- Revenue","Simple Calculations"."25  Avg Order Size")

Slope
EVALUATE_AGGR( 'REGR_SLOPE(%1, %2)', "Base Facts"."1- Revenue","Simple Calculations"."25  Avg Order Size")

Reg Line
AGGREGATE(EVALUATE_AGGR( 'REGR_SLOPE(%1, %2)', "Base Facts"."1- Revenue","Simple Calculations"."25  Avg Order Size") BY 1)* AGGREGATE("Simple Calculations"."25  Avg Order Size" BY "Time"."Per Name Month")+EVALUATE_AGGR( 'REGR_INTERCEPT(%1, %2)', "Base Facts"."1- Revenue","Simple Calculations"."25  Avg Order Size")

Notes

EVALUATE_AGGR is used for shipping function to database for intercept, slope and Reg Line

Result – Table View/Graph View


This example is taken from the Sample Apps. I hope you find this copy/pasted information useful.