Skip to Content
avatar image
Former Member

Aggregate thumb rule

Hi Guys

Wat is an Aggregate Thumb Rule?

Thanx

Kumar

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

4 Answers

  • avatar image
    Former Member
    May 10, 2006 at 05:51 PM

    Go for an aggregate if the size of the aggregate is <= 1/10th the size of cube.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 11, 2006 at 01:36 AM

    Kumar,

    As a rule of thumb, an aggregate is reasonable and may be created, if:

    Aggregation ratio > 10Aggregation ratio > 10, I.e. 10 times more records are read than are displayed, AND

    Percentage of DB time > 30% Percentage of DB time > 30%, I.e. the time spent on database is a substantial part of the whole query runtime

    -Doodle

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 11, 2006 at 05:08 AM

    Hi Kumar,

    check this see if it helps -

    /people/vikash.agrawal/blog/2006/04/17/query-performance-150-is-aggregates-the-way-out-for-me

    regards

    Vikash

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 11, 2006 at 04:53 PM

    As with all "rules", you need to consider the context. SAP suggests aggregates where you have summarization (select/trans) of 10 or more or DB access > 30%.

    Does that mean I sit down and create aggregates for every Infocube/qeury that exceeds these threshoplds? Of course not. Does it mean I never consider an aggregate where the the results are lower than these thresholds? Of course not, as well.

    If I have a query that runs just a few times a month and has a summarization ratio of 50 to 1, and an aggregate would reduce run time by 90%, say from 30 seconds to 3 seconds - I probably wouldn't bother with aggregates at all.

    If I have a query that reads a large portion of a large InfoCube and has a summarization ratio of only 8 to 1, on average runs in 5 seconds, and runs perhaps 2,000 times a day, and accounts for use of 60% of my BW's processing resources - I would certainly try aggregates. Even if I could gain only a modest 20% reduction in run time (from 5 to 4 seconds) and system resource, because of the frequency of use and total resource consumption, that 20% reduction transalates to large overall system improvement.

    OLAP Cache might also factor into the equation. If you can cache the results once in the morning and never have to hit the DB the rest of the day, do I need an aggregate?

    Add comment
    10|10000 characters needed characters exceeded