Using a FILTER Function Instead of CASE Statements

Let’s face it; CASE statements are notorious for causing poor query performance. For certain kinds of simple CASE statements, there may be a more performance-friendly OBIEE alternative – the FILTER function. Like CASE statements, you can use the FILTER function to build a logical column expression. In the Expression Builder, this function can be found under Functions > Display Functions > Filter. Here is an example of how to use it:
Suppose you have 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

Instead of using CASE statements, try using the following equivalent expressions involving the FILTER function:

• 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’)

The SQL generated by the FILTER expressions will typically perform better than the SQL generated from the CASE statements. The FILTER SQL may look something like this (pretending all the columns come from the same physical table):

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 SQL generated from the CASE statements may look more like this:

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

The major difference is that the FILTER SQL includes the criteria in the WHERE clause. In most cases, this means that the WHERE clause would run first, constraining the result set before the CASE statements are run, hence the improvement in performance.

Article From: http://www.biconsultinggroup.com/obiee-tips-and-tricks/using-a-filter-function-instead-of-case-statements.html