report query slow on PARENTH2

[Former Member]

Dear experts;

after creating a second hierarchy, data retrieval on this second hierarchy is very slow. Before we created this second hierarchy, all data was duplicated in default logic on dummy accounts. We now removed that script statement and adjusted dimension formula's. So, we can retrieve the correct data again using the second hierarchy and without duplication of data... however- - all reports using this second hierarchy are twice as slow as before, especially for a first data retrieval (second comes from memory).

We have a BPC MS 10 sp15 server installation and client EPM addin SP 20.

What can we do to speed up things - and restoring the old method doesn't sound like a best practise to me..

many thanks for your suggestions,

christophe

[former_member810148]

Hi Christophe.

Did you manage to solve the issue with data retrieval being slow when adding a second hierarchy?

The reason I ask is because we seem to have the same problem and I haven't been able to find similar postings elsewhere. However, it only seems to be an issue when we have formula calculations in both account hierarchies. If we have calculations in only hierarchy 1 or hierarchy 2, no performance issue. We run BPC MS sp21 on the server and sp25.1 on the EPM addin.

br Søren (DFDS A/S)
.

[former_member186498]

Hi Christophe,

have you done a full optimize and a Modify Model with Process Model and Recreate Index after the described operations?

Regards

     Roberto

[Former Member]

Hi Roberto,

I selected all possible optimize, defragment, recreate index options 🙂

[Former Member]

We ran a SQL profiler test and we could clearly see that adding extra dimension changes the query and subcube traces. In fact, a lot more attributes are taken into account (I guess, a second hierarchy duplicates the dimension attributes). So, in nature, this is a structural SAP BPC design problem.

I deleted the additional hierarchies ( in fact i created two additional ones) and this brings the report query time back from 8 to 0.3 seconds. So, it is far better to duplicate your data in default logic, allowing the use of a single hierarchy, then creating additional hierarchies if performance is key success factor.

So, my advice would be to be really carefull with creating additional hierarchies in the MS version (especially in dimensions with a large number of properties).

Unless a BPC performance expert can contradict this statement and really point towards a solution in which we can keep the addtional hierarchies whilst not impacting performance that much - which is what the customer and I really want to obtain - we will keep on duplicating data on dummy members..

cheers,

c

[former_member186498]

Hi Christophe,

very strange that performances decrease with just a second hierarchy, reasons could be related with hardware or IIS (maxthreads, etc.) or DB settings. In consolidation I work with 5 hierarchies on account dimension but I don't have bad per formances.

Have you maybe a lot properties with "inapp" option checked? If so leave the inapp option only where necessary and retry.

see please BPC 7.5 and BPC 10 Best Practice Performance Tuning

Regards

     Roberto