Join optimization

You can optimize joins by applying filters to them.

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. Joins across different relational data sources can be performed through local query execution.

In IBM® Cognos® Analytics, you can optimize how these joins are executed by using filters. The query that is driving the join is executed, and a 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) to the data source, the amount of local data processing that the join must perform is reduced. As a result, performance is improved significantly.

The following join filters are available:

No filtering

The optimization is turned off.

Unique values

Values from the table with 1 cardinality are used to filter values from the table with many cardinality. The filter uses a single IN expression. For a 1-to-1 relationship, the filtering is applied to the second table.

Range of values

Values from the table with 1 cardinality are used to filter values from the table with many cardinality. The filter uses a single BETWEEN expression, using the minimum and maximum values. For a 1-to-1 relationship, the filtering is applied to the second table.

Unique values in a subquery

Values from the table with 1 cardinality are used to filter values from the table with many cardinality. The filter is generated in a subquery. For a 1-to-1 relationship, the filtering is applied to the second table.

Unique or range of values

The cardinality is ignored, and values from the table on the left are used to filter values from the table on the right. This filter uses either IN or BETWEEN predicate. An error massage is displayed if the optimization can't be applied.

If the data server types are different, ensure that the data types of the matched columns are compatible. Otherwise, you might need to edit the join expression to explicitly cast the data types.

In Cognos Analytics versions 11.1.6 and 11.1.7, a filter optimization error might occur. For more information, see XQE-PLN-0355 filter join optimization error.