DQM Filter Type Property in Cognos Report Studio

When we join two queries in the report studio, the “Filter Type” property of a join condition is used to improve the performance by filtering data of the many side query (1:n or 0:n) with the values retrieved by the other side of the query (1:1 or 0:1).

In the above example, Query1 and Query2 may contain columns either from the same data source (or) from different data sources. If the data sources are heterogeneous, we need to make sure that the data types of the join column are compatible. Otherwise, we need to explicitly apply casting between the joined columns.

• By using "Filter Type" option, we can minimize the amount of data retrieved by the Cognos server, particularly when querying data from heterogeneous sources where one side of the join has less number of rows than the other side. 

• IBM also proved that, using “Filter Type” property improved performance of the reports from 10 to 100 times.

• The filter join optimization reduces the size of the set of rows by applying a filter to the many side. The join filter is based on the join key values retrieved from the one-side operand. 

There are '3' types of Filters:

IN:

IN the generated filter is an IN predicate comprised of constant values of the join keys from the one-side join query.

BETWEEN:

The generated filter is a BETWEEN predicate comprised of the minimum and maximum of the join key values from the one-side operand.

TABLE:

TABLE the generated filter is a table value constructor form of the IN predicate.