Skip to Content
author's profile photo Former Member
Former Member

Issue with query performance

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Mar 27, 2009 at 08:02 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Mar 27, 2009 at 08:05 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Mar 27, 2009 at 08:08 AM

    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,

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Mar 30, 2009 at 12:08 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.