Query optimization for relative date measures

Starting with Cognos® Analytics 11.2.3, a detail filter is automatically generated for a query that includes relative date measures, based on the filtered measures that are used in the query.

With this additional filter, a data server might be able to further optimize how it executes the query. Without this filter, more rows of data are passed to each relative date measure.

Relative date measures are defined by using the Lookup reference (split definition) property. This property links the measure to a table, such as the Gregorian Calendar or Fiscal Calendar sample table. The filter definitions in the tables are based on macro expressions, and generate SQL fragments, such as in the following example:

SUM(
        CASE 
            WHEN 
                ORDER_HEADER0.ORDER_DATE >= DATE '2006-10-01' AND
                ORDER_HEADER0.ORDER_DATE <= DATE '2006-12-13'
                THEN
                    ORDER_DETAILS0.QUANTITY
        END) AS QTD__Quantity_,

This feature is implemented in a query transformation that under the right conditions adds a detail filter to the query in the WHERE-clause.

The following governors control the aspects of this feature:

optimizeFilteredMeasures
The following values are supported:
  • enabled (default) - apply the optimization with all the conditions, as specified in the Conditions section in this document
  • disabled - the optimization is disabled
  • enabled_verbose - apply the optimization and add warning or info messages in the validation mode
optimizeFilteredMeasuresBetween
Values: enabled, disabled (default)
optimizeFilteredMeasuresPredicates
Values: single, multi (default)

No governors are exposed in data modules.

In Cognos Analytics Reporting 11.2.4 and later versions, you can configure this functionality through advanced query hints in the following way:

  1. Open the report in edit mode.
  2. From the Report menu, click Queries, and select the query that you want to configure.
  3. Open the Properties panel.
  4. Under QUERY HINTS, click the Advanced hints property, and then click its context menu Context menu icon.

    The Advanced hints dialog box is opened.

  5. Click the plus icon to add a query hint.

    Adding advanced query hints

  6. Specify the hint name and value, and click OK.

For more information, see Optimizing query performance with query hints.

Conditions

By default, 2 expression patterns are recognized:
  • Case when expr1 (> | >=) literal1 AND expr1 (< | <= ) literal2 then expr2 end
  • Case when expr1 (> | = | < | >= | <= ) literal3 then expr2 end

When setting the governor optimizeFilteredMeasuresBetween=enabled, the case when expr1 between literal1 AND literal2 then expr2 end is also recognized.

The following rules apply:

  1. expr1 occurrences must be the same (applies only to the first pattern).
  2. The whole case expression must be a child of a summary operation.
  3. The items referenced in expr1 must be from a data module and must have a split definition (Lookup reference).
  4. Adjoining and overlapping ranges based on the same expr1 values are reduced to a single predicate. Adjoining considers the < and <= operands to be the same, as well as > and >=. This may lead to the inclusion of the = literal value for a range that ends with < literal value, and the next range that starts with > literal value.
  5. If the computed range is negative-infinity to positive-infinity, the where clause predicate is not generated at all. For example, exp1 >= 2022-01-01 OR exp1 <= 2022-01-01.
  6. No additional consideration is given to null values. Using a >, >=, <, <= operation with a null operand results in an error. Using a null literal in a between predicate is accepted, but results only in null. A NULL is a value expression, not a literal.
  7. Setting the governor optimizeFilteredMeasuresPredicates=single creates a single, all-encompassing range predicate, causing overfetching for the price of a simpler predicate.
  8. The generated predicates are not compared to existing where-clause predicates, which might result in duplicate predicates if filters were specified in other places.
  9. The optimization affects the query of each fact-stream.
  10. When other measures in the query are not filtered, the transformation is not applied.

When to disable the optimization

You could disable the optimization if you have a date range for which the categorical values are not present. When the optimization is disabled, the category would be part of the output, with a null value for the filtered measure. When the optimization is enabled, the category would not be part of the output.

For example, in the following situation the optimization can be disabled (there is no 'Mountaineering Equipment' for '2004'):

Product Line En Quantity
Camping equipment 181,822
Golf Equipment 99,400
Mountaineering Equipment 301,958
Outdoor Protection 557,854
Personal Accessories 389,908

There's no filtered measure optimization on the between predicate in the case statement.

Product Line En Quantity
Camping equipment 181,822
Golf Equipment 18,912
Mountaineering Equipment
Outdoor Protection 274,650
Personal Accessories 86,322

The operands >= and <= trigger filtered measure optimization.

Product Line En Quantity
Camping equipment 181,822
Golf Equipment 18,912
Outdoor Protection 274,650
Personal Accessories 86,322