Three ways to check the push-down of filters
In the following three different ways are described how to verify whether or not a filter is “pushed-down”. With “pushed-down” it is meant that the filter is applied at the lowest level possible – e.g., already when reading data from a table instead of filtering later in the data flow. This post will provide you with methods to check it but what leads to push-down is not in the current scope. If you are interested in how you can enforce the push-down of filters in certain situations you can find more information here
The three options to analyze the filter push-down: Explain Plan, Visualize Plan/Analyze SQL, and Debug View will be shown for the following scenario
We use a Calculation View named S2EX1_SALES_CUSTOMER in the example.
Using the “Column Lineage” tool (button is highlighted in screenshot below) on this model shows that the column “PHONE_NUMBER” originates from table BusinessPartnerContacts.
Model used in example. Column lineage shows that Field “PHONE_NUMBER” is already available in node “Proj_BPC” (table “BusinessPartnerContacts”)
Therefore, if a query is send with a filter on “PHONE_NUMBER” the filter could be applied to this table directly.
Here is an example query that puts a filter on PHONE_NUMBER:
SELECT "PartnerId", "TITLE", "LAST_NAME", "FIRST_NAME", "GENDER", SUM("NetAmount") AS "NetAmount" FROM <...>::S2EX1_SALES_CUSTOMER" WHERE PHONE_NUMBER='7412697340' GROUP BY "PartnerId", "TITLE", "LAST_NAME", "FIRST_NAME", "GENDER"
Example Query to test filter on field “PHONE_NUMBER”
The following examples use this scenario to illustrate how a check can be done whether the filter ‘7412697340’ on PHONE_NUMBER is already used when reading from table “BusinessPartnerContacts”
How to check filter-push down
a) Check filter push-down with Explain Plan
c) Check filter push-down with Debug View