FILTER function to improve performance of OBIEE reports

OBIEE allows developers to write expressions at several places like BMM,prompts, column expression builder etc. And most of the time we end up in writing CASE statements to achieve some or the other logic.
As we all know CASE statements actually are so popular in causing query performance issues and also increases the length of the logical and Physical queries.

Not in all cases we can avoid the CASE statements but thriugh this post i would like to show where we can. Let us know discuss on how these CASE statements can be avoided and use FILTER function to achieve the desired logic when filtering a measure for certain dimension filter.

Where to find

In the Column Expression Builder - Functions -- Display Functions -- Filter

How to write

FILTER( MEASURE_EXPRN  USING (EXPRN ))

Lets see how we can make use of this with an example

How to Use

Consider the below logical expression using CASE statement

CASE WHEN 
"Offices"."D1  Office"='Blue Bell Office' 
THEN 
"Base Facts"."1- Revenue" 
ELSE 0 
END

Now lets see how we can use the FILTER function to achieve the same

FILTER ("Base Facts"."1- Revenue"   USING("Offices"."D1  Office"='Blue Bell Office'  ))

Analyze the physical SQL for understanding the Query performance

What ever we do in Presentation, how best we may design the RPD,how good we may have set the performance metric parameters ,the ultimate thing that counts is the Physical SQL that hits the database.

Below will be the physical SQL generated by these both functions for above examples

The SQL generated from the CASE statement looks something like this as CASE is also supported at the Database level OBIEE will not convert functions that are supported by database as this will improve the formation and execution times :

SELECT
SUM(CASE WHEN Office= ‘Blue Bell Office’  THEN Revenue ELSE 0)
FROM PHY_TABLE1, PHY_TABLE2
ON CONDITION

Lets see the SQL generated by FILTER function,here the filter fucntion is not supported by database and hence OBIEE converts it into best possible way

SELECT
SUM(Revenue)
FROM PHY_TABLE1, PHY_TABLE2
ON CONDITION

WHERE Office= ‘Blue Bell Office’ 

When we observe the above two physical statements one can easily understand that SQL generated by FILTER is more effective when compared to CASE statements since it uses the conditions in WHERE clause.This is why we have to start using the FILTER where ever we can to avoid the CASE statements