Optimizing joins by applying filters

A report might need a query that requires a relational join across multiple data sources. For example, a transaction database might be used to locate a set of customer details that are then joined to a corporate sales warehouse. With the dynamic query mode, joins across different relational data sources can be performed through local query execution.

The dynamic query mode optimizes how these joins are executed by using a filter join. The query that is driving the join is executed, and the set of key values is gathered and then added to the query that is executed against the other data source. By extending the predicates (filter criteria) sent to the data source, the amount of local data processing that the join must perform is reduced. As a result, performance can be improved by several orders of magnitude.

For every join, a cardinality must be specified on each side. The cardinality can be one of: [0..1], [1..1], [0..n], or [1..n]. The side on which the cardinality is specified as [0..1] or [1..1] is commonly referred to as the one-side. The side on which the cardinality is specified as [0..n] or [1..n] is commonly referred to as the many side. The "one-side" operand often has rows fewer rows than the "many" side by orders of magnitude.

Applying a filter to the join reduces the size of the set of rows for the "many" operand by applying a filter to the "many" side. The filter is based on the join key values that are retrieved from the "one-side" operand. When the number of values that are retrieved from the "one-side" operand is higher than 10000, the filter optimization error might occur. For more information, see XQE-PLN filter join optimization error.

Either of the queries can also retrieve data from an external data source that is accessed in Cognos® Analytics - Reporting. If the types of the data sources are different, ensure that the data types are compatible. Otherwise, you might have to edit the join expression to explicitly cast between the query items that are being joined.

About this task

You can apply a filter to a join in IBM® Cognos Analytics - Reporting or in IBM Cognos Framework Manager by setting the Filter Type property of a join.

For information on how to apply the filter in Cognos Analytics - Reporting, see Create a Join Relationship.

Procedure

  1. To apply the filter in Cognos Framework Manager, click Tools > Launch Context Explorer.
  2. Select the join that you want to optimize and set the Filter Type property.

    Choose one of the following values:

    In
    The generated filter is an IN predicate that is composed of constant values of the join keys from the "one-side" join operand.
    Between
    The generated filter is a BETWEEN predicate that is composed of the minimum and maximum of the join key values from the "one-side" operand.
    This is the default value.
    Table
    The generated filter is a table value constructor form of the IN predicate.

    Many data sources support the IN and BETWEEN predicates in their SQL syntax, while table or row constructors are less common. Do not select the Table option if the data source does not support it. The dynamic query mode would be forced to decompose the query and perform local query processing, which is not faster than when filter joins are not used.

    If a report is creating a large list of values in an IN predicate, performance might be impacted by the methods that the database vendor uses to optimize or rewrite the IN predicate. This also increases the size of the SQL statement that is sent to the data source. If possible, use the BETWEEN predicate instead.

    Tip: A report author can use the validate feature in Cognos Analytics - Reporting to see a message that explains why a filter join cannot be pushed to the database.