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

Aggregates size

Hi Experts,

Please clarify my doubts.

1. How/where to find the Aggregates size and speed etc?

2. Aggregates increase only query performance? or load performance also please explain?

3. On which basis we create aggregates?

Thanks in Advance

Ram

Add a comment
10|10000 characters needed characters exceeded

Related questions

7 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Dec 14, 2011 at 06:06 AM

    hi,

    AGGR THUMB RULE IS <= 1/10th SIZE OF THE CUBE..

    Percentage of DB time > 30%

    We recommend that you create aggregates, especially in the following cases:

    · The execution and navigation of query data leads to delays with a group of queries.

    · You want to speed up the execution and navigation of a specific query.

    · You often use attributes in queries.

    · You want to speed up reporting with characteristic hierarchies by aggregating specific hierarchy levels.

    aggr reduces load performance means increases load time . because after data load is comleted corresponding request need to be roll up if there are so many unnecessary aggregates on cube performance will be poor.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 14, 2011 at 05:48 AM

    Hi,

    1. How/where to find the Aggregates size and speed etc?

    Size of Aggregates

    2. Aggregates increase only query performance? or load performance also please explain?

    It will increse the Query performance only

    3. On which basis we create aggregates?

    When 10-20% of the size of the fact table; for example if your fact table contains 1 million rows, the aggregate should have less than 200,000 rows

    @bwsatya

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 14, 2011 at 05:51 AM

    hi

    1.

    AGGR THUMB RULE IS <= 1/10th SIZE OF THE CUBE..

    Aggregate thumb rule

    2.

    Aggregates are subsets (with aggregated data) of fact table data and are stored in an infocube like structure.

    Different Aggregation Levels:

    Different type of aggregation can be used

    *: Group according to characteristic or attribute value

    H: Group according to nodes of a hierarchy level

    F: Filter according to fixed value

    When do we go for Aggregates :

    Ratio records selected (DBSEL) / records transferred (DBRANS) > 10

    Records selected > 10,000

    Database time over 30 percent of total runtime

    Aggregates improve both load and query performance. if there are so many unnecessary aggregates on a cube load performance will be poor.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 14, 2011 at 05:56 AM

    This message was moderated.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 14, 2011 at 05:57 AM

    Hi,

    2. Aggregates increase only query performance? or load performance also please explain?

    Aggregates will increase query performance and they will increase data loading time because after normal data load to target we will have to do roll up to add data in aggregates.

    3. On which basis we create aggregates?

    Generally aggregates will be proposed by system itself and on top of that you can build aggregates as per the reporting needs.

    Regards,

    Durgesh.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 14, 2011 at 06:03 AM

    Hi,

    How/where to find the Aggregates size and speed etc?

    You can refer the table RSDDAGGRDIR.

    Aggregates increase only query performance? or load performance also please explain?

    Aggregates will increase the query performance and decrease the load performance as additional time will be taken to roll up data into aggregates

    On which basis we create aggregates?

    When a query is frequently used for reporting and we have huge amount of data in the Infocube on which the query is built then in that case we can go for the creation of aggregates on that infocube as this will increase the query performance

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 14, 2011 at 07:19 AM

    Just to add to the already provided answers:

    - you may also build a rather large aggregate that is not used to speed up queries but the build up of the other aggregates.

    - to find out which aggregate to build, you can use transaction RSRT, DEBUG+EXECUTE -> show aggregates found.

    By that you can figure out exactly how a perfect aggregate for this query would look like.

    regards,

    Lars

    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.