Aggregates - Query specific performance optimization

[Former Member]

Hi.

I am trying to build aggregates on two cubes (Actual and Plan) to improve the performance on two or three specific queries that are build on a MultiProvider base don these two cubes. In this respect, a few questions comes to mind:

Does anyone have some good documents or anything that could help me out as to what to consider when setting up these aggregates?

Is there anything specific I should look for in the query that I want to optimize performance for?

Would it be a good idea to build an aggregate that uses all the characteristics / navigational attributes for this one specific query?

Also, is it a bigger challenge to make aggregates “pay off” since the query is built on a Multiprovider and it is not possible to build aggregates on a MultiProvider?

Any input greatly appreciated! Thanks.

[edwin_harpino · Accepted solution]

hi Jon,

check if helps doc 'query performance tuning with aggregate'

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/cbd2d390-0201-0010-8eab-a8a...

try to search oss note on 'query performance multiprovider'

query performance

oss note

557870 'FAQ BW Query Performance'

and 567746 'Composite note BW 3.x performance Query and Web'.

Prakash's weblog

/people/prakash.darji/blog/2006/01/27/query-creation-checklist

/people/prakash.darji/blog/2006/01/26/query-optimization

BW Performance Tuning Knowledge Center - SAP Developer Network (SDN)

[original link is broken]

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/3f66ba90-0201-0010-ac8d-b61...

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/cccad390-0201-0010-5093-fd9...

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/ce7fb368-0601-0010-64ba-fad...

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/c8c4d794-0501-0010-a693-918...

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/064fed90-0201-0010-13ae-b16...

hope this helps.

[former_member378040]

Jon,

You can use transaction RSRT to execute the report for which you are thinking of creating an aggregate in debugging mode and select all the check boxes under aggregate once the "Debug Option" screen appears. The system will propose you the Aggregate that will be suitable for this report. Based on this you can go and create the aggregate and run this report again to see if there is any imrovment in the performance.

The "Maintainance of Aggregte" screen shows the quality of aggregate based on the compression it carried out(with a ratio) and the number of hits.

Hope it helps.

If it does please award points appropriately.

Amit

[Former Member]

Hi Jon,

run the queries in rsrt and select debug. Check the flag for the aggregates and the system will tell you what aggregates it is using or expecting for the queries. You can use this information to create your aggregates. As it is not possible to build aggregates on a multiprovider, the system will tell you the aggregates to be built on both basis providers.

regards

Siggi