Join Pruning - Referential Joins

[Jonathan_Haun]

So I think we have discovered why I sometime see inconsistent results with SAP HANA information views generated by SAP BW vs SAP HANA information views generated manual within SAP HANA Studio. There has been much contention on the subject of referential joins in an Analytic View and join pruning when they are executed. For those generated in SAP HANA studio, I have observed that join pruning is negated when there is a filter in the attribute view data foundation. Effectively SAP HANA executes the "Inner Join" because it noticed a filter defined within the attribute view. Every test I conducted concluded in the same result when I reviewed it with the plan visualization tool. In contrast, when testing the information views generated by SAP BW, I was not seeing the same behavior.

We then started reviewing the information view in XML view and noticed something new. They include an attribute called “forced” and it’s set to false in the models generated by BW 2 HANA import. I went back and created a filter on an attribute view using the HANA studio GUI and it does not include that option and it does not appear that there is any way to set that option in the GUI.  So it seems there are more configuration options in the models than what is being exposed in the GUI, and the BW import process is taking advantage of some of them.

Example filter xml… the first is generated by the import process from BW the second manually created in SAP HANA Studio.

<columnFilter columnName="OBJVERS" forced="false">

  <valueFilter xsi:type="AccessControl:SingleValueFilter" including="true" value="A"/>

</columnFilter>

<columnFilter columnName="CITY_2">

  <valueFilter xsi:type="AccessControl:SingleValueFilter" including="true" value="TEST"/>

</columnFilter>

If it is true that this forced="false" setting changes how SAP HANA determines join pruning, why is this not exposed in the SAP HANA Studio GUI?

[lbreddemann]

Hi Jonathan,

maybe it would be a good idea to post such questions in the HANA space instead of the HDE space. I am not sure how many people from SAP product development/management read in the HDE area.

To your question: there may be options that aren't exposed in the GUI for sure.

Some of those options may only work under specific conditions (e.g. under the constraints of the SAP BW application), which might be a reason to not expose them in the GUI.

Of course things that affect actual execution behavior and performance should be explained to the user in the documentation - again something that needs to be addressed with the product development team.

Generally though, the XML (and with it the whole of possible options for a column view) are not documented and/or supported for outside-SAP development.

So, if you see that you have an issue with the filter-set->join-no-pruned behavior and you believe that the filter-force=FALSE setting gives you an advantage here, I recommend to open a support incident to have this checked out.

- Lars

[brenden_kennedy]

Jonathan,

Interesting find, thanks for sharing!

Have you tried manually adding this in the XML from the Studio/GUI, importing back in to HANA, and confirming the difference in behavior from the GUI version? Logically yes but I am wondering if you were able to fully confirm that in your testing?

Thanks

[Jonathan_Haun]

By manually manipulating the XML for filters in Attribute Views and adding the forced="false"> produces the desired results when visualizing the execution plan. However, without any documentation I am not 100% sure of the ramifications of this change. My assumption is that if it ok for BW to add this option, it should be ok for custom analytic solutions to use the same. However, I am still hesitant to officially recommend it without fully understanding what it is doing.

[lbreddemann]

"My assumption is that if it ok for BW to add this option, it should be ok for custom analytic solutions to use the same. "

Why would that be the case? SAP BW is developed in very close cooperation with the SAP HANA development. That way the BW data manager developers know very well when to use what functions and the SAP HANA developers know the range of use cases that will be produced by SAP BW.

For custom DB designs this is not at all the case.

Besides that, manually changing XML files is the same as using HINTS in your DB development: a short term fix and a long term maintenance issue.

Surely not a problem when building a show-and-burn-PoC system, but big NO NO for real systems.

- Lars

[Jonathan_Haun]

Lars,

The next sentence stated:

"However, I am still hesitant to officially recommend it without fully understanding what it is doing."

Because I don't work for SAP or have access to the development teams, I use SCN to pose such questions and to gain further insight. I did not recommend that anyone make this change in the posting or reply. I am just trying to understand if the setting can be utilized outside of BW specific implementations. Empirical evidence would suggest that it can be. The whole fact the filters in an attribute view negates the join pruning mechanism (for referential joins) can be problematic to performance in specific use cases. One could assume that this specific setting was added to address join pruning issues for BW models exported to SAP HANA. IE.. SAP BusinessObjects Explorer access exported BW models. However, without "insider information" or official documentation, those on the outside are relegated to using "black box" techniques to understand undocumented settings.

To address your concerns related to customizing the XML: In the real world you typically use whatever mechanism is available to you to successfully implement the solution. If a best practices concern originates from your design, you address it. The great part about building custom solutions is that you can always devise another method to eliminate someones concern. For example, we have developed an XS engine application that will help re-apply such customizations to an information view's XML. SAP HANA is also a development environment with an application server which makes it ripe for customizing. In this case we are able to removed the maintenance issue until it is exposed in the SAP HANA studio GUI. However, we are still stuck with the fact that we are using an undocumented setting. That is where I need information from SAP. In short, what does the setting do to the execution of the model? Is it safe to use? What is the impact of the setting?

[lbreddemann]

To get your question to an audience that would at least in theory be able to address these questions, I hinted before that the question may be better located in the main HANA forum.

Here in the dusty HDE corner I don't expect anyone from product management/development to look into any requests.

I am growing tired of asking for better documentation. Maybe it's more effective when somebody from the "real world" as you coined it requests that.

[Jonathan_Haun]

Thanks... I posted a link on the SAP HANA main page.

I am not sure where else I could post this. SAP Ideas Place does not appear to have a section for SAP HANA https://ideas.sap.com. Hopefully the produce team reviews the above mentioned site.