Issue with query performance

[Former Member]

Hi,

I have a multi provider with 2 cubes & 3 ODS & 1 info object.

On top of this MP queries are built. All the queries are in 3.5

It takes more then 30 min to execute each query.

Now we are planning to replace all 3.5 queries in 7.

We will build cube on top of 3 ODS. New MP will have 3 cubes & 1 Info object.

My issue is, the other 2 cubes are too large. We have around 4 million records in each.

And i need only few fields from these cubes.

Is there any way I can have less load on MP?

What would be the best approach to improve query performance?

Thanks,

Gowri

[Former Member]

Without specifics about the data in the cubes and what your results are supposed to look like, I'd have to concur, that aggregates are probably the most likely option to improve performance, but you do have some new options in 7.0 regarding true joins in a mulitprovider, or possibly an infoset.

[Former Member]

Hi Prasanna,

weblog for good query design, just go through it.

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

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

Regards,

[ram_pandey]

What kind of a result set are you looking for? If you want something like an intersection result set (only the common fields from all the 3 cubes to be available in the result set), you can try creating an InfoSet instead of a MultiProvider.

In the InfoSet, you can use your primary cube as the key component and create an inner join on the other two cubes using the common fields from the primary cube. This way you can avoid bringing in unnecessary records from the other two cubes into the result-set.

[Former Member]

Hello Gowri,

I think you should take the help of Aggregates.

You may create aggregates on the 2 large cube, using the characteristics

that you are using in the Query.

Since that cubes are having large amount of data, the use of aggregates will consideraly

reduce the Data manager time i.e. time spent by the query in retriving data from info-provider.

for more details on aggregates please refer the following link:

http://help.sap.com/saphelp_nw04/helpdata/en/7d/eb683cc5e8ca68e10000000a114084/content.htm

Thanx and regards

Priyanka