Thursday, September 5, 2013

[OBIEE 11g] Using FILTER Function Instead Of Case Statements



 



OBIEE 11g Using FILTER Function Instead of CASE Statements


 

It is well-known that CASE statements are causing low query performance and an alternative performance-friendly function is FILTER, can be used instead. In the Expression Builder, this function can be found under Functions > Display Functions > Filter. Here is an example of how to use it:


Assuming there are two Logical Columns derived from the following expressions:


  • Southern Region Units: 
    CASE WHEN Paint.Markets.Region = ‘SOUTHERN REGION’ THEN Paint. SalesFacts.Units ELSE 0 END
  • Western Region Units:
    CASE WHEN Paint.Markets.Region = ‘WESTERN REGION’ THEN Paint. SalesFacts.Units ELSE 0 END



An equivalent FILTER expression:


  • Southern Region Units:
    FILTER(Paint. SalesFacts.Units USING Paint.Markets.Region = ‘SOUTHERN REGION’)
  • Western Region Units:
    FILTER(Paint. SalesFacts.Units USING Paint.Markets.Region = ‘WESTERN REGION’)




COMPARISON:


CASE generated query (assuming all columns come from the same table): 

SELECT Year,
SUM(CASE WHEN Region = ‘SOUTHERN REGION’ THEN Units ELSE 0),
SUM(CASE WHEN product = ‘WESTERN REGION’ THEN Units ELSE 0)
FROM physical_table
GROUP BY year


FILTER generated query:

SELECT Year,
SUM(CASE WHEN Region = ‘SOUTHERN REGION’ THEN Units),
SUM(CASE WHEN product = ‘WESTERN REGION’ THEN Units)
FROM physical_table
WHERE Region = ‘SOUTHERN REGION’ OR Region = ‘WESTERN REGION’
GROUP BY year



The major difference is in FILTER query's WHERE clause, which gets executed first at the database level; where for CASE query, it executes line-by-line, which impacts the performance.





No comments:

Post a Comment