analytic view joins with filters

[Former Member]

Hi,

I am developing a sales data analytic view of SAP ERP application. I need to get "Ship-To" customer at the header level of the document. I have created an attribute view of table VBPA with filters (on partner type and item number) to get the header level "Ship-To" customer. I have joined the attribute view to the data foundation with left outer join (cardinality 1:n). What I have observed in the visualize plan is that join is executed even for those queries that does not need "Ship-To" customer. Is there a way to prevent system from making unneeded joins at run time?

Regards,

Ramana

[Former Member]

This is no longer issue in the latest versions of HANA. One of the support packs for SP5 has fixed this issue.

Ramana

[former_member184768]

Hi Logi,

Ideally you could have used Referential join to ensure that the attribute view table is not queried when no column from the attribute view is selected. But since you have the filter on the attribute view, I think the join will be executed.

So from my perspective the join will be executed, but please try with referential join once again.

Please refer to the blog below for the details:

http://www.saphana.com/blogs/datamart/2011/10/17/sql-join-union--what-you-ever-wanted-to-know1

Regards,

Ravi

[Former Member]

Ravi,

Thanks for the quick reply. Clearly, my requirement is not unique and every HANA implementation with SAP ERP must have some how done it more efficiently. You must be very familiar with the situation below.

Table VBAP: (Data foundation)

ORDER     ITEM     MATERIAL

12343      000010    MAT123

Table VBPA: (Attribute view (filter on ITEM = '000000' PARTNER_TYPE = 'WE')

ORDER      ITEM     PARTNER_TYPE        PARTNER

12343      000000    WE                           CUST123

12343      000000    AG                           CUST124

The end result I want is (data foundation left outer join with attribute view on ORDER)

ORDER     ITEM     MATERIAL  PARNTER_WE

12343      000010    MAT123      CUST123

I was following the suggested best practices and created attribute view on VBPA instead of directly joining VBPA in the data foundation.  I was hoping that attribute view will not make a join if the query does not select PARTNER_WE dimension. I was thinking that I cannot use referential join. Irrespective of my understanding, please let me know, what is the optimum way to achieve what I want. Please note that I will have about 6 more joins of this nature in my model and collectively they deteriorate the performance.

Regards,

Ramana

[former_member184768]

Hi Ramana,

Can you please try with Referential join with Attribute view WITHOUT having the filter on attribute view. This way you would be able to avoid join on attribute view if no attribute from the view is selected. May be, you can add the filter in the Calc view. Secondly can you also check the mapping of the ORDER column. If it is selected from the Attribute view, then the join will be executed again. Please ensure that it is mapped to the Fact table and not to the attribute view.

As per the default behavior of the left outer join with the filter on the right hand side table (Attribute view in this case), the join will be executed even if you do not select any column from the table.

This issue has been raised with SAP and to my knowledge they are working on fixing this. The fix is expected in next or subsequent revision (52 or 53).

The behavior is even more evident when you generate the Analytic view based on BW DSO and all the P Tables with filter on OBJVERS are joined even if nothing is selected from the P tables. It has very high negative impact on the performance and I think your case is similar to that.

But can you please check with the Referential join and not apply the filters in the Attribute view.

Regards,

Ravi

[Former Member]

Ravi,

Once again, I am thankful for your quick response. I guess, in the end, I will have to wait for next SAP revision. However, I will try referential join and get back to you. As far as the ORDER dimension, the moment you link anything in the fact table to the attribute view, by default that dimension is read from the attribute view. In fact, the dimension is no longer highlighted in the fact table. I recognized this early on and I have added it to the output again. I got a new dimension ORDER1 that I was using.  In any case, I wanted to let you know that in the query I was not selecting even one dimension. I was just doing SUM(measure) to get just one row with one measure. In the visualize plan I could still see all the joins being performed.

Thanks already for the information you have provided. I will get back to you soon with results on the referential join.

-Ramana

[Former Member]

Ravi,

Just want to let you know that I tried the referential join. It did not help.

Regards,

Ramana

[former_member184768]

Hi Ramana,

That was anyway expected. Did you try with the filter on the attribute view in the view definition or added it later as the where clause.

You can check the 'Visualize plan' option on the right click on SQL statement and see how the query is being executed in the background, which step is taking longer time and which all joins are getting executed.

The only way I can think of reducing the join is to move the filter from the View definition to the where clause in the query (if really required to be filtered).

Regards,

Ravi