Filter propagation
Description
Description of how FILTERs are propagated to other tables in the data model.
Filters are not immediately propagated. They remain with the table where they were calculated. The filters on tables that are connected in the data model are propagated to this table, but only when it is queried. We merge all filter conditions together using a logical AND.
When a filter is propagated to another table where rows do not have a join partner, those rows are always discarded from the result.
[1] Simple example of two filter statements on the activity table. Both filters are merged with a logical AND. As we query the activity table, no propagation to another table is happening: | ||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||
|
[2] In this example, the activity filter from the previous example is propagated to the case table, because the case table is queried. As Case 1 is eliminated by the filter on the activity table, it is also filtered out in the case table. As there are still rows left from Case 2 after applying the filter, Case 2 is contained in the result: | ||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||
|
[3] This example shows that filters are applied after other calculations. First, the VARIANT function is evaluated, and the result is added to the case table. The filter on the activity table is then propagated to the case table, which eliminates Case 1. Case 2 with its full variant string is contained in the result: | ||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||
|
[4] In this example, the last row of the activity table (caseID 3) does not have a join partner in the case table. We filter on case 2 in the case table and query the activity table. Case 1 is filtered out by the propagated filter, and case 3 does not have a join partner and is therefore discarded from the result as well. As a consequence, only rows of case 2 are in the result: | ||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||
|