Improve run time of the query

[Former Member]

hi,

We have one BW report and this query consists of lot of formulae and lot of restrictions(restricted key figures) used and also the query is built on Multiprovider which involves 8 Cubes. The issue here is the report runs for half an hour for any selection criteria you give. I am interested in knowing the different options in which i can make the query run fast. I need your valuable suggestions...

Thanks in advance...

[Former Member · Accepted solution]

Hi,

Multiproviders are always an issue with the performance

You have the following options

1) If your user selections are fixed then you can go for cahce.create cache for the reports based on the fixed selection that user will give and the output of the query will be stored in the system and next time you run the query it will execute in less then 10 seconds.

There are various links available about cache in the SDN have a search on that.

2)Also there is a web link on the main page of the SDN giving about various methodsof performance tuning.

3)I am not sure whether all your queries require data from 8 cubes at all particular time.If it requires then it's better that you try to combine two cubes into one i.e. try to reduce the number of cubes by doing look up's or making customized cubes.

4) Try to create aggregates on the cubes.Aggregates are just like your cube only with your set of data.Do look more for aggregates in this link.

Also check if you have any Calculated key figure in the report which has summation as "before aggregation".If it there then it is bound to be slow and you can't help it because aggregates will not work here.

4)Also the volume of data your report is fetching also makes your report slow try to refine the selection and reduce the nuber of rows coming to the OLAP server.

You can have more of it just go through this SAP link which gives you all the scenarios of performance tuning.

http://help.sap.com/saphelp_erp2005vp/helpdata/en/41/b987eb1443534ba78a793f4beed9d5/frameset.htm

Hope it helps

Thanks

[Former Member · Accepted solution]

Thanks a lot for the quick response. I shall try to look into the options which you have mentioned and see whether my problem is resolved or not.