OBIEE 11g: Using FILTER Function Instead of CASE Statements

It is well-known that CASE statements are notorious for causing poor query performance; and an alternative performance-friendly function, 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 dramatically.

Hope this helped!