BI IP - bad performance in BEX workbooks

[Former Member]

Hi,

we use BI IP and have workbooks with 5 to sometimes 10 planqueries (located on individual worksheets). We notice that the performance in BEX is bad. The planning functions itself are performant ( less that 5 seconds in the modeler). However in the excell workbook everything is slow;

opening: 30 seconds - 3 minutes.

simple save: at least 30 seconds

planning function: more or less the same as the save.

=> when performing a trace with ST05 (when clicking on save for example) we don't see much happening. Also in SM50 we don't see much happening.

Does anyone has experienced the same (or the opposite?) Is this problem caused by SAP or by Microsoft Excell?? Are the figures above normal???

D

[Former Member]

Hi Dries,

How's your performance now? We're also working with the analyzer and I also have problems with performance. I'm still in development but the data set is so small the performance can't be bad. And I only have one query in the workbook. I also see, looking at SM50, that the planning functionalities are very fast, but the response in excel is slow.

If you heard anything from SAP, could you post this?

Thanks.

J.

[Former Member]

Hi,

we created aggregates which decreased the response time significantly. But even if we have no data or it is quickly selected, it takes for ex 10 - 20 seconds to do something (saving, simple function) in case of workbooks with 4 - 8 queries. For queries with one query, this is faster, like 3 - 8 seconds to do something.

I think planning in BEX is slow (it is 4 - 20 times slower than web according to SAP studies!), but the effort we should do now to increase the speed would not weigh up against the benefits ...

could you do a trace with ST05 and let me know which statements take the longest time? What is your reaction time in your query. How hearvy is your query? Do you have pictures or graphs included? do you use data slices or characteristic relationships? you can also email to further discuss.

D

[Former Member]

Hi Dries,

Do you, by chance, have any heavy characteristic relationships involved?

[Former Member]

Hi Manyak,

we don't have use any characteristic relationships. Do you have similar workbooks (10 sheets) and how long does it take when you save, or open the workbook.

D

[Former Member]

Hi Dries,

Unfortunately not. I dont have any such live example with me similar to yours, to be referred. I did have such a problem (long time for saving), long back which was solved after we created an OSS case but that time we were on SPS 8.

I dont know if you would want to create an OSS case for this?

[Former Member]

I will get in contact with SAP, but first we are going to spend some effort on database tuning itself.

I just was wondering whether someone else has these kind of workbooks operational ...

grtz

D

[gregor_dieckmann]

Hi Dries,

I think the run time can easily explained since you have so many queries in the workbook:

- Start up time: All queries will be started

- Manual change + transfer or start of a planning function: Triggers a roundtrip,

write the changes to the deltabuffer and refresh all queries contained in the

workbook

I suggest to start the queries on request only (if the queries are independent).

Regards,

Gregor

[Former Member]

are you referring to the flag automatic refresh when opening the workbook?

regards

Dries

[gregor_dieckmann]

Hi Dries,

no, I am suggest to experiment with the command RESET_DATA_PROVIDER, cf. the Data Provider specific command 'Assign Query/Query View'. The idea is to assign the query at run time using a button. This will allow to open the the needed queries on user request.

Regards,

Gregor

[Former Member]

Hi Gregor,

What would be acceptable is that my 10 queries are not updated automatically but only when the user would like to. But the "old result" (which could be the initial load for example) should always remain visible. With RESET_DATA_PROVIDER I presume the user will only activate the query he wants to plan for, while the others remain not visible. How do you see this?

D

[Former Member]

Try to restrict the data by defining filters and use variables.

Ravi Thothadri

[Former Member]

Hi Ravi,

It don't think it is due to my filters. When analyzing the trace, getting the data is less then 1/3 of my total time ... Also I only have the problem when working with BEX workbooks ...

Also when clicking on the save button (linked to one data_provider) it takes 30 seconds. this trace only gives 2 seconds of reading data.

-> do you have experience with workbooks of 5 to 10 different sheets and are they performant?

D

null

null

[Former Member]

Hi,

I checked with deactivating the result rows. there is not significant change ... saving still takes 30 seconds for example (for a workbook of 6 layouts).

d

[Former Member]

It was significant for a workbook of mine...

Do you use many macros ? Automatic Excel calculations ?

[Former Member]

We have 200 buttons but no additional VB coding or other excell manipulations.

Also the number of data records is not so much in my workbook. When I use one worksheet it is performant.

D

[Former Member]

Do you have one query per sheet ?

How many aggregation levels ?

[Former Member]

I have one query per sheet,sometimes two. We have no graphics. In total I have three aggregation levels ...

D

[Former Member]

Hi,

Change the query to suppress results, and check your performance again.

[Former Member]

Hi,

what do you mean with supress results, are you talking about result rows?

D

[Former Member]

Yes, I do.

Remove all result rows (Result lines : always suppress), and compare the performance.

[Former Member]

Hi,

I'll try to do this tomorrow when I have access to the system. Did you had the same problem and was it solved by doing this?

D