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

aggregates usage

Posted on Jul 31, 2006 at 01:41 PM 12 Views

gurus

i have to evaluate all my aggregates in production and get rid of few of them.

questions what i have is

when was the last time any query actually accessed my aggregate?

what are the queries which actually use this aggregate?

we do have statistics cubes active. i used the standard Aggregate usage query but i am not convienced with the data. it actually shows only one query has used my aggregate

i am trying to get information from RSDDSTAT and RSDDSTATAGGR tables but i am able to understand which of the available fields will answer my question

can anyone help me out please. would be great if you can be more specific

thanks

BW Business Explorer (SAP BEx)
Add comment
10|10000 characters needed characters exceeded

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jul 31, 2006 at 02:04 PM

    Hi Surya,

    Did you refer this document: <b>SAP BW Query Performance Tuning with Aggregates</b>.

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

    Regards

    Hari

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 31, 2006 at 07:02 PM

    Are your queries on multiproviders? I believe some of what gets captured for multiproviders has changed over versions and SPs, but not all aggregate usage gets logged in RSDDSTAT. At one point MultiProvider queries that ran in parallel did not log the aggregate, and before that, a MP couldn't use an aggregate. I'm not 100% certain what scenarios get logged, but if you think about it, a MP query could be hitting the multiple aggregates - both aggregates from the different underlying cubes and in some cases, different aggregates on the same cube for different Restricted KF. Since BW is only logging to RSDDSTAT a single entry for that query navigation, it can't possibly reflect the use of multiple aggregates. I believe I saw a while back that indicated that in this case, no aggregate would be included on the record written to RSDDSTAT.

    An easy way to confirm this would be to look at the usage counts and last used date/time in the Maintenance for Aggregate window. That will tell you how many times an aggregate has actually been used and the last time it was used. These should give you a confirmation about which ones are being used the most. This, unfortunately, is a total for the aggregate and is not at the query level.

    You can compare the usage counts shown in the Maintenance of Aggregate screen (count is from the aggregate's creation/last change) with what you get from RSDDSTAT for the same timeframe. I'll bet that for Multiproviders RSDDSTAT is too low.

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 31, 2006 at 09:05 PM

    thanks for your suggestions

    Pizzaman that was a helpful information.

    our change run jobs run every week end and i was under the impression that after every change run only that week usage count will be displayed at aggregate maintanance window.

    please correct me if i am wrong

    thanks

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member
      Aug 01, 2006 at 12:03 PM

      That would be true if the degree of change was large enough (believe the default is 10%) that the BW decided it was better to refill the aggregate rather than update it. In that case, I believe the Last Change Date would be updated.