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.