Monday, 30 December 2013

OBIEE 11g Release 11.1.1.7 – Banding Function – Case Statement > Equi-Width Band Example


Banding is a type of ranking that divides a list of values in a partition into a specified number of groups called Bands (also known as buckets) and assigns each value to a Band.
There are two types of branding – Branding by Value and Branding by Rank
Equi-width Brands – Branding by Value
Branding divides values into groups according to their value; it is also called as equi-width bands. The function takes the largest value minus the lowest value and divides the result by the number of bands required. This value defines the range of each Band. Values are then assigned to bands according to which range they fall into. Therefore, the number of values in each Band may differ. For example, if we have 100 values and divide them into four equi-width bands, each band may contain different numbers of values.


Equi-Height Brands - Branding by Rank
Branding by Rank divides values into groups according to their rank; it is also called as equi-height bands. The function divides the number of values in the partition by the number of bands, which gives the number of values in each band. An equal number of values are then placed in each band. For example, if we have 100 values and divide them into four equi-height bands, each band contains 25 values.

There are two different ways to produce equi-width bands based on value, one is using GREATEST function or the other is CASE function in Oracle. 

Equi-width Brands – Case Statement

This blog is an attempt to demonstrate OBIEE 11g’s ability to achieve equi-width band calculations by using CASE Statements

Calculation Example

Description

This example covers sales figure across time

Selected Items

"Time"."T05 Per Name Year", "Offices"."D2  Department", "Base Facts"."1- Revenue"

Sort Order

"Time"."T05 Per Name Year"

Condition

"Time"."Per Name Year" = '2010'

Calculation Name

Band

Database Calculation

MAX Sales = MAX(Sales SUM) OVER(PARTITION BY Year)

MIN Sales = MIN(Sales SUM) OVER(PARTITION BY Year)

Range = (MAX Sales - MIN Sales)/4

Q1 = MIN Sales + Range

Q2 = MIN Sales + (Range*2)

Q3 = MAX Sales - Range

CASE WHEN Sales SUM < Q1 THEN 4 WHEN Sales SUM < Q2 THEN 3 WHEN Sales SUM < Q3 THEN 2 WHEN Sales SUM >= Q3 THEN 1 END

Calculation for Analytics  Using Functions

Revenue Max - MAX("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year")

Revenue Min - MIN("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year")
Range - (MAX("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") - MIN("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") )/4
Q1  - MIN("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") + (MAX("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") - MIN("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") )/4 
Q2  - MIN("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") + ( (MAX("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") - MIN("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") )/4)*2

Q3 - MAX("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") - (MAX("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") - MIN("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") )/4

Band - CASE WHEN "Base Facts"."1- Revenue" < MIN("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") + (MAX("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") - MIN("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") )/4  THEN 4 WHEN "Base Facts"."1- Revenue"<MIN("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") + ( (MAX("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") - MIN("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") )/4)*2 THEN 3 WHEN "Base Facts"."1- Revenue" < MAX("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") - (MAX("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") - MIN("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") )/4 THEN 2 WHEN "Base Facts"."1- Revenue" >=MAX("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") - (MAX("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") - MIN("Base Facts"."1- Revenue" BY "Time"."T05 Per Name Year") )/4 THEN 1 END

Notes

All calculations are done on analytics only, no database functions are being used.

Result – Table View/Graph View

I hope you find this information useful.

No comments:

Post a Comment