locked
Report consuming lot of time RRS feed

  • Question

  • Hi Community,

     

    I've a query for one of the reports in ProClarity.

    When I generate a report without column/row sub-totals, it runs normal. But when I apply aggregation with subtotals, it goes for a toss. In the worst case, the report doesn't respond and is hanged.

     

    Any pointers/solutions as to why this happens?

     

    Regards,

    Bhushan

    Friday, August 22, 2008 9:45 AM

All replies

  • Bhushan,

     

    Sounds like the addition of the subtotal is causing a large query and Professional just becomes unresponsive, is this correct?  It's hard to diagnose your issue without more information.  What do you mean when you say you apply aggregation with subtotals?  Can you play around with the query and see if making it slightly smaller or simpler will isolate the problem?

     

    -Joey

     

    Friday, August 22, 2008 11:29 PM
  • Hi Joey,

     

    Thanks for your reply.

    What you've guessed is correct. The addition of the sub-totals is causing Professional to be unresponsive.

    By aggregation I meant the col/row grand and sub totals. When I do this, the time required for report generation increases. And without aggregation, the query fetches data in a normal speed. Hope I'm clear.

     

    I tried playing with the MDX, like drilling up/down the heirarchies, but it didn't helped me much. The response time still remains almost the same, which is slower. Will amending/optimizing the MDX solve this problem? Or is it because of the amount of data fetched?

     

    Kind Regards,

    Bhushan

    Sunday, August 24, 2008 7:36 AM
  • What's the size of the dataset in the view before you apply totals.  How many hierarchies on rows and how many on columns?  How many members from each hierarchy are selected?

     

    Can you run Profiler on the cube and post the MDX that's being sent for totals?

     

    Monday, August 25, 2008 11:26 PM
  • Hi Bushan, Joey & Ben,

    I experienced the same thing as Bushan: I have a ProClarity page with these settings

    on rows:

    - Attribute1 with 15 members

    - Attribute2 with 40 members

    - Attribute3 with 2 members

    on columns:

    - Attribute4 with 15 members

    - 10 Measures from 4 different measure groups

    on background:

    - Attribute5 with 3 members

    - Attribute6 with 1 member

     

    Without Subtotal and Grand Total it takes 1 second for this ProClarity page to finish executing.

    With Subtotal and Grand Total on the row it takes 10-15 minutes.

     

    I think this is because of WITH MEMBER and AGGREGATE function that ProClarity uses to calculate Subtotal and Grand Total.

     

    Enabling Filter Empty Rows and Filter Empty Columns and Enabling Use Non-Empty CrossJoin do not help much; it still take 10-15 minutes.

     

    For anyone experiencing this problem an possible workaround is to remove all the totals and create another ProClarity page(s) to calculate just the totals.

    Tuesday, December 2, 2008 12:31 PM