Introduction

This article is part of a Best Practice series about Oracle Analytics Cloud (OAC) and Oracle Analytics Server (OAS). This article describes ways to improve performance through the use of enhanced formulations of case statements. It’s intended only for technical developers writing formulas for reporting metrics.

Scenarios

 You can define case statements in the column formula of reports or in the Oracle Analytics repository (RPD) file.

Case statements defined on variables

This scenario can almost always be rewritten to improve performance. The case logic can be used within an INDEXCOL function, enabling Oracle Analytics to simplify the execution of the case statement so that only the result is queried. INDEXCOL is a conversion function having this syntax: INDEXCOL ([integer literal], [expr1] [, [expr2], ?-]). The first argument must resolve to a number from 0–N, followed by a comma-delimited set of expressions corresponding to the number. Note that a case statement is used to define the integer literal.

Case statement with a presentation variable

An example of a case statement with a presentation variable

Presentation Variable

Original Statement

CASE '@{Metric}{Units}' WHEN 'Revenue'
THEN "Base Facts"."1- Revenue"
WHEN 'Units'
THEN "Base Facts"."2- Billed Quantity"
WHEN 'Paid Amount'
THEN "Base Facts"."4- Paid Amount"
WHEN 'Booked Amount'
THEN "Base Facts"."8- Booked Amount" end

INDEXCOL Version

INDEXCOL (CASE '@{Metric}{Units}' WHEN 'Revenue' THEN 0
WHEN 'Units'
THEN 1
WHEN 'Paid Amount'
THEN 2
WHEN 'Booked Amount'
THEN 3 end,
"Base Facts"."1- Revenue", "Base Facts"."2- Billed Quantity", "Base Facts"."4- Paid Amount", "Base Facts"."8- Booked Amount")

Session Variable

Original Statement

CASE VALUEOF (NQ_SESSION.PREFERRED_CURRENCY) WHEN 'USD'
THEN "Base Facts"."83 - Dollars"
WHEN 'EURO' THEN "Base Facts"."84 - Euros"
WHEN 'YEN' THEN "Base Facts"."85 - Yen" end

INDEXCOL Version

INDEXCOL (CASE VALUEOF (NQ_SESSION.PREFERRED_CURRENCY) WHEN 'USD'
THEN 0
WHEN 'EURO'
THEN 1
WHEN 'YEN' THEN 2 else 0 end,
"Base Facts"."83 - Dollars", "Base Facts"."84 - Euros", "Base Facts"."85 - Yen")

Repository Variable

Original Statement

CASE VALUEOF (REPORTED_CURRENCY) WHEN 'USD'
THEN "Base Facts"."83a - Reported Dollars"
WHEN 'EURO'
THEN "Base Facts"."84a - Reported Euros"
WHEN 'YEN'
THEN "Base Facts"."85a - Reported Yen" end

INDEXCOL Version

INDEXCOL (CASE VALUEOF (REPORTED_CURRENCY) WHEN 'USD'
THEN 0
WHEN 'EURO'
THEN 1
WHEN 'YEN'
THEN 2 else 0 end,
"Base Facts"."83a - Reported Dollars", "Base Facts"."84a - Reported Euros", "Base Facts"."85a - Reported Yen")

Case statements defined on data values

Case statements defined on data values with a single expression

This scenario is complex and can vary for each row returned from the query. The FILTER function replaces the case statement in this scenario. The FILTER function computes an aggregate expression with the given pre-aggregate filter. The syntax is: FILTER (measure USING filter expression).

Original Statement

CASE WHEN "Orders"."R1  Order Status" in ('1-Booked', '2-Fulfilled', '3-Shipped') and "Orders"."R2  Order Type" = 'Express'
THEN "Base Facts"."1- Revenue" else 0 END

FILTER Version

IFNULL (
FILTER ("Base Facts"."1- Revenue"
using "Orders"."R1  Order Status" in ('1-Booked', '2-Fulfilled', '3-Shipped') and "Orders"."R2  Order Type" = 'Express'), 0)
Note: The FILTER function returns a null value for rows not meeting the filter criteria, so consider embedding the function inside an IFNULL clause.

Case Statements defined on data values for multiple expressions

This scenario is the most complex, because multiple FILTER functions must be added to get the desired results.

Original Statement

CASE WHEN "Orders"."R1  Order Status" in ('1-Booked', '2-Fulfilled', '3-Shipped') and "Orders"."R2  Order Type" = 'Express'
THEN "Base Facts"."1- Revenue" WHEN "Orders"."R1  Order Status" in ('6-Cancelled', '9-On Hold')  
THEN "Base Facts"."2- Billed Quantity" WHEN "Orders"."R2  Order Type"='Secure' THEN "Base Facts"."7- Shipped Amount" ELSE 0 END

Equivalent Version Using Filters

Note: It’s necessary to carry through the impact of the previous data filters because the filters are combined. 
IFNULL (FILTER ("Base Facts"."1- Revenue"
using "Orders"."R1  Order Status" in ('1-Booked', '2-Fulfilled', '3-Shipped') and "Orders"."R2  Order Type" = 'Express'), 0)
+ IFNULL (FILTER ("Base Facts"."2- Billed Quantity"
using ("Orders"."R1  Order Status" in ('1-Booked', '2-Fulfilled', '3-Shipped') and "Orders"."R2  Order Type" <> 'Express') or "Orders"."R1  Order Status" in ('6-Cancelled', '9-On Hold', '2-Fulfilled')), 0)
+ IFNULL (FILTER ("Base Facts"."7- Shipped Amount" using
"Orders"."R2  Order Type" = 'Secure' and "Orders"."R1  Order Status" not in ('6-Cancelled', '9-On Hold', '2-Fulfilled')), 0)

Call to action

To ensure your report formulas result in the best performance, review how case statements are used and consider: replacing case statements with the INDEXCOL function, which improves query performance; and replacing case statements with FILTER functions, which often improves query performance, but not always.

Use these methods to find reports using case functions:

  • Use Catalog Manager and search for CASE in catalog objects.
  • Use usage tracking and search for CASE in reports and queries.

For additional information on usage tracking, check here for OAC; here for OAS; and here for Catalog Manager.

To learn more about Oracle Analytics, visit oracle.com/analytics, follow us on twitter@OracleAnalytics, and connect with us on LinkedIn.