Example Flag "Allow Filter Push Down"

[jan_zwickel]

This example is referenced in my other post about flags to enforce the push-down of filters. Please review the other post to get a better understanding of the context for this example

 

 

Example

This example is using the data of table "rankingExample" as source data. The records in table "rankingExample" are shown below.

 



Data of table "rankingExample" used as input in the Example

 

These data are fed into a Rank node that partitions the ranking on field "ARTICLE" and orders by field "AMOUNT". As output only the two highest values are requested (see screenshot below for the parameters):

 



Definintion of Ranking in the Rank node

 

The output of the Rank node is used in a Projection node that filters on field "COLOR" for "red". The definition is shown below.

 



Filter definition that only accepts "red" articles.

 

The rest of the model are the default nodes of a dimensional Calculation View so that the complete model looks like the following:

 


Model used in Example

 

Default behavior (push-down is not enforced)

Given this model per default the filter that is defined in the Projection node will not be pushed below the rank node as it is not defined on the partitioning field "ARTICLE". This means filtering on field "COLOR" for red items will only happen after ranking has completed.

In the Rank node the records with the 2 highest values in field "AMOUNT" will be selected for each distinct ARTICLE. These records are shown below.

 



Highest two values for each article type if no filter is applied. These values will be delivered by the rank node to the projection node

 

In the default case the filter is not pushed below the Rank node and therefore the blue items are not filtered out before ranking and will be delivered as the highest values by the Rank node. In the example, the filter in the succeeding Projection node will remove these records with entry "blue" that have been output by the rank node for ARTICLE "shirt". This has the consequence that no values will be delivered for ARTICLE "shirt". The final output of the model is shown below.



The output shows articles with the highest "amount" values after the records that contain "blue" articles have been removed

 

Behavior with enforced push-down

If the "Allow filter push down" flag is set for the Rank node the filter will be pushed below the Rank node and the "blue" colored articles will already be filtered out before reaching the Rank node.

Given that the blue values have been filtered out before the Rank node these records will not be considered in the Rank node. Therefore, the Rank node does not compute the highest records of ARTICLE "shirt" irrespective of color but only computes the highest records of ARTICLE "shirt" that are not blue. This has the consequence that it will output the two highest values for shirts that are not of color "blue". These records will be delivered to the succeeding nodes on which no further filters are defined and therefore reach the output in the current example. The output is shown below.

 



Records with the two highest amounts per article type when ranking is done after filtering out "blue" articles. These records will be delivered by the rank node to the projection node

 

Thus, different results are obtained by setting the flag and it is up to the developer to decide which behavior is intended.

 

 

This example is referenced in my other post about flags to enforce the push-down of filters. Please review the other post to get a better understanding of the context for this example