Whether Variable pushes filter to Data Foundation level

[former_member199763]

Hi,

We use a Calculation View that consumes four analytical views (through Union).

We would like to know whether creating a Variable on the Calculation View level will push the filter to Analytical View level also.

We tried creating a Variable in the underlying Analytical Views and try to propagate to upper level Calculation View, but HANA does not recognize the Variables from underlying Analytical Views.

HANA however recognizes the Input Parameters created in underlying Analytical Views.

Our queries here are:

1. Whether Variable pushes the filter to the Data Foundation level of a View
2. Whether Variable created in a Calculation View pushes the filter further down to the Data Foundation levels of Analytical Views that it is referencing

Thanks and Regards,

Ram

[Former Member · Accepted solution]

My suspicion is that variables can only be mapped to output fields of a particular view (i.e. in this case just the CalcView output fields). Why? Because variables only generate WHERE clauses - which are only valid for output field of a table/view.

My suggestion - ensure all of your fields are mapped from source Analytic Views to your output field in the CalcView, and create a variable on those output fields.

Generally speaking, HANA should be "smart enough" at runtime to push the filter condition all the way down to the base table(s). Factors such as model complexity, calculations etc - can influence this.

You can investigate by running a plan visualization against a particularly query and tracing at what point the filter gets executed.

Cheers,

Jody

[justin_molenaur2 · Accepted solution]

Wow, this sounds strikingly familiar to another conversation I just had with Jody Hesch... funny how these topics weave throughout your day

I would be interested to get an opinion/feedback from since he is the king of small tricks to get better performance.

What I found to be true, is just as Jody said - for a filter/variable to truly be pushed down, it needs to be clearly mapped all the way from the source and NOT derived from a join AFTER the union. In the case of the AN as a source, the same should be true - just make sure you are mapping the columns to the unions consistently.

For example, consider two scenarios, each will require a mandatory variable on the column "OWNER" to ensure smaller datasets.

Case #1 - two tables are unioned and then joined to another table to derive OWNER.

In the Vizplan, we can see that even though we specified something in the where clause/variable, we are getting a lot of records (~5million) at the lowest level, and the filter kicks in after the join. Execution is about 1.1s. This shows me that the ENTIRE union needs to be generated for the filter on the joined column OWNER to be properly evaluated.

Case #2 - two tables are joined THEN unioned, bringing the desired filter column OWNER through the union and into the result set.

In the Vizplan, we can see that at the lowest level the filter is being pushed down, only 9 records are returned after the joins are evaluated. Execution is about 100ms, 10x faster than the previous example. Extrapolated over a large dataset, this would be a much more significant number.

Happy HANA,

Justin

[lbreddemann · Accepted solution]

Hey Justin

not sure what to add to this conversation. Looks like I am done with starting up performance analysis in the SCN HANA community you guys clearly are on top of it by now.

Concerning the filter push down through a JOIN - well that's difficult and IMO not really possible for different filters for different source tables (at least not for the general case).

I think with the change in the model you provided a good way to circumvent the problem.

- Lars

[former_member182302 · Accepted solution]

The Variable adds the "WHERE" clause at the run time and it does push downs the filter condition to the referenced analytic views in the calculation views.

Adding to the already detailed discussion that took place here, have a look on the below link as well including the comments in that blog

Regards,

Krishna Tangudu

[abanip · Accepted solution]

To extend this conversation, the variable (WHERE clauses) will be pushed down to underlying views.

a. IF you don't have a filter condition on a calculated attribute (in the calc view)

b. IF you have a JOIN node and all filter conditions exists on both side of the join.

Regards

Abani

[former_member199763]

Thank you Jody, Justin, Lars, Krishna for the help!

Unfortunately SCN allowed me to mark only one Correct answer!

Never stuck me that adding a Join/Calculation on the Calculation View will make Variable filter not to be pushed down underlying Analytical Views.

Thanks and Regards,

Ram