Using a FILTER Function Instead of CASE Statements

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:

• UAE:
  CASE WHEN Paint.Markets.Region = ‘UAE’ THEN Paint. SalesFacts.Dollars ELSE 0 END
• INDIA:
  CASE WHEN Paint.Markets.Region = ‘INDIA’ THEN Paint. SalesFacts.Dollars ELSE 0 END

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

• UAE:
  FILTER(Paint. SalesFacts.Dollars USING Paint.Markets.Region = ‘UAE’)
• INDIA:
  FILTER(Paint. SalesFacts.Dollars USING Paint.Markets.Region = ‘INDIA’)

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 Physical_yearr,
SUM(CASE WHEN Region = ‘UAE’ THEN Dollars),
SUM(CASE WHEN product = ‘Samsung’ THEN Dollars)
FROM physical_table
WHERE Region = ‘UAE’ OR Region = ‘INDIA’
GROUP BY Physical_year

The SQL generated from the CASE statements may look more like this:

SELECT Physical_year,
SUM(CASE WHEN Region = ‘UAE’ THEN Dollars ELSE 0),
SUM(CASE WHEN product = ‘Samsung’ THEN Dollars ELSE 0)
FROM physical_table
GROUP BY Physical_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.