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.

Let us know discuss on how these CASE statements can be avoided and use FILTER function to achieve the desired logic.

Where to find

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

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 

Paint.Markets.Country= ‘INDIA’ 

THEN 

Paint. SalesFacts. Quantity 

ELSE 

0 

END

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

FILTER (Paint. SalesFacts. Quantity  USING(Paint.Markets.Country= ‘INDIA’ ))

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.

Lets see the physical SQL generated by these both functions for above examples

The SQL generated from the CASE statement looks something like this:

SELECT Year,
SUM(CASE WHEN Country= ‘INDIA’  THEN Sales ELSE 0)
FROM PHY_TABLE
GROUP BY year

Lets see the SQL generated by FILTER function 

SELECT Year,
SUM(Sales)
FROM PHY_TABLE

WHERE Country= ‘INDIA’ 
GROUP BY year

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