IBM Support

DQM filter join optimization for Report Studio



The purpose of the filter join optimization is to improve performance of joins by filtering one side of the join using the values retrieved by the other side. This can help you minimize the amount of data retrieved by the Cognos BI server, particularly when querying heterogenous data sources where one side of the join has orders of magnitude less rows than the other side. Applying filter joins in such cases allowed reports to run between 10 and 100 times faster in IBM Lab testing.


As of 10.2.1 Fix Pack 3 you can initiate a filter join through Report Studio, by setting the Filter Type property of a join operation.

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

The filter join optimization introduced in 10.2.1 Fix Pack 3 reduces the size of the set of rows for the “many” operand by applying a filter to the many side. The join filter is based on the join key values retrieved from the one-side operand. You can specify three types of filters:
1. IN the generated filter is an IN predicate comprised of constant values of the join keys from the one-side join operand
2. BETWEEN the generated filter is a BETWEEN predicate comprised of the minimum and maximum of the join key values from the one-side operand
3. TABLE the generated filter is a table value constructor form of the IN predicate

In Report Studio, the optimization can be enabled by setting the Filter Type property of a join operation as depicted below.

In the above example, Query1 or Query2 can get data from the same data source or from different data sources. Either one of them can also get data from the external data feature of Report Studio. In cases where the data sources are different, care must be taken to ensure that the data types are compatible. Otherwise, the join expression may need to be edited to do explicit casting between query items being joined.

[{"Product":{"code":"SSEP7J","label":"Cognos Business Intelligence"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"--","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"10.2.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
15 June 2018