BW on Hana - Composite Provider - Slow performance

[Former Member]

Hello experts,

I'm doing an implementation with BW on Hana ( 7.3 ). In our case we created many composite providers to join DSOs we have created.

I don't understand the inner structure of Composite providers but I can see in Hana that BW creates many views.

0BW:RST:BIP:ZMM_CP02.0BW:RST:BIP:ZMM_CP02

0BW:RST:BIP:ZMM_CP02.AGGR0001

0BW:RST:BIP:ZMM_CP02.AGGR0002

0BW:RST:BIP:ZMM_CP02.AGGR0003

0BW:RST:BIP:ZMM_CP02.AGGR0004

0BW:RST:BIP:ZMM_CP02.JOIN0001

0BW:RST:BIP:ZMM_CP02.JOIN0002

0BW:RST:BIP:ZMM_CP02.JOIN0003

0BW:RST:BIP:ZMM_CP02.UNION

If I create a SQL statement manually direct in Hana with necessary inner/outer joins, the query is extremely fast, even in table with tens of millions of records. But the composite provider does not have the same performance at all. I almost sure it's because of this many views BW creates.

Does anyone have already gone through this situation and how to tune it ?

Many thanks,

[Former Member]

Hi guys,

Thanks for the suggestions. We tried to put secondary indexes on all the join columns that weren't in the primary index. Unfortunately this didn't help. I believe in HANA DB secondary indexes only enhance performance when you have to select a very small subset of a table based on a non-key column. In our case we join the largest part of the tables in question.

I haven't had the time yet to remodel this CompositeProvider into a Analytical View and test this. If I will test this in the near future, I'll keep you informed.

Best regards,

André

[Former Member]

Hi André van der Heijden,

were you able to test and resolve your issue?

In my case I have n:m relationship, with around 8 million records in each of the 2 DSO.

Thanks in advance!

Best Regards, Vitali

[Former Member]

Hi Victor,

Did you already make some progress with this problem? We are facing the same issue, the CompositeProvider is really, really slow with large amounts of data. We are running SAP BW7.31 SP12 on HANA DB1.0.

Thanks in advance.

André

[Former Member]

Hi André,

We found out that some things that can improve performance, for example if you have joins with non-key columns and some of these columns have null or empty string, the query is extremely slow. Inner joins are better than left joins as well. It would be nice if we could see the exact query plan in Hana, but it seems that BW triggers a lot of small queries to the database.

But still, the composite provider is much slower than if I create direct joins in Hana using SQL. We haven't had time to test Analytic Views based on composite provides. If you do please let me know your results.

If you find anything please let me know

Victor

[Former Member]

Hi,

try to put indexes on the join columns.

Best martn

[Prabhith]

HI ,

I remember some setting/Flag in CP like “Unique Join Column”. Have you enabled that flag and checked the performance?

BR

Prabhith

[Former Member]

Hi Prabhith,

In fact many composite providers we cannot use this flag since it's a 1:N relationship. In the cases we have 1:1, we use this flag and it's a little bit faster.

But I though over Hana DB the composite provider would be much faster than it really is. Maybe there are some notes we need to apply.

We use:

BW 7.3SP8

Hana DB 1.0