DQM filter join optimization for Report Studio

Content

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.