SAP HANA Join Pruning

[Jonathan_Haun]

I posted a question to the distinguished engineers area. Someone responded and requested that I also post the question here. Anyone from the SPA HANA product team have an answer to the following?

[justin_molenaur2]

Jon, I think that behavior you describe makes sense as implemented, whereas the inner join is executed when you apply a filter - the join cannot be pruned off.

By making the change to the XML (I make direct XML changes all the time for other purposes ), you would effectively be dropping the filter, is that correct? For example, if you had a filter applied in the attribute view just for "RED" products, I would expect only "RED" products to be in the output. In your case, if you made that XML parameter change, it sounds like the join would NOT be executed and therefore the filter would NOT be applied. This would likely not be the desired outcome when using the specific attribute view.

Are you saying that when you are comparing the native HANA view against the BW generated view for the same type of model, you are getting different results?

Regards and good to hear about this at SAPPHIRE

Justin

[Jonathan_Haun]

Justin,

ANAV=Analytic View

ATBV=Attribute View

You do not drop the filter used in the ATBV necessarily. The analytic view query currently prunes the join between the ANAV foundation table and the ATBV when your query does not reference any columns in the attribute view.  The behavior happens currently with LEFT OUTER JOINS and some REFERENTIAL joins. REFERENTIAL joins (ANAV -> ATBV) are not pruned if filters are setup in the ATBV foundation. However, exported BW models change the default behavior with the xml option mentioned. From my perspective I would like to have this option explained and possibly added to the GUI. There are cases where I want the join pruning to be activated for a referential join (even if I have a filter in the ATBV).

[justin_molenaur2]

I follow your explanation completely.

I agree it would be interesting to hear an answer on this. Did you by chance open an OSS message to get an answer?

Regards,

Justin

[ruediger_karl]

Hi,

it's like reversed engineering and I agree to Lars in the referenced SCN link. BW can use additional information to "guide" HANA for the model execution, as HANA simply trusts BW doing the right things. That cannot be assumed for the "default user".

Regards, Ruediger