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.
CASE WHEN
"Offices"."D1 Office"='Blue Bell Office'
THEN
"Base Facts"."1- Revenue"
ELSE 0
END
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
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 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
"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 :
SUM(CASE WHEN Office= ‘Blue Bell Office’ THEN Revenue ELSE 0)
FROM PHY_TABLE1, PHY_TABLE2
ON CONDITION
SELECT
SUM(Revenue)
FROM PHY_TABLE1, PHY_TABLE2
ON CONDITION
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
