No of Aggregations Vs % of Performance gain

Hi,

For performance optimizing my cubes, I used the aggregation design wizard to create aggregations. The only thing that I am not able to understand is:

The number of aggregations and the percentage of performance gain (as shown in the design wizard) keep varying, when i change the AggregationUsage properties of the attributes of the dimensions in the cube.

Here is a set of rules for setting the AggregationUsage property, that I had read from a blog on the net:

1. Attributes that are not displayed = none
2. Attributes that are not used in any hierarchy = none
3. Extensively used attributes = Full
4. All other attributes = Unrestricted

I first set the Month attribute, Key attribute of the date dim as Full. (not sure if this is correct)

now,

if I set the AggregationUsage = default I get more number of aggregations (20-35), but the % performance gain is less (22%).

If I set the according to the above rules I get more performance gain (70%) but less aggregations (1-3)

My question is, which is the best one, and why

also please give some tips to set AggregationUsage property.

Regards




Answer this question

No of Aggregations Vs % of Performance gain

  • Lee John

    HI,

    Thanks for the help.

    I had made changes according to what was given in the blog.

    my doubt is, is it better to have more number of aggregations with less % performance gain

    or less number of aggregations with more % performance gain.

    I am getting 80% performance gain, when the number of aggregations are very less like 2-4 aggregations

    or upon tinkering with the properties, I get

    25% perf gain with about 34 aggregations.

    Now which is actually going to improve my performance of ssas

    Regards



  • donkaiser

    Dear Vijay,

    Are you read the blog from this blog:
    http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!907.entry

    Because at that blog you can find that for commonly queried attributes, it is better to set Unrestricted on the AggregationUsage property. For rarely queried attributes, you can set None. That blog is about Designing Effective Aggregations in AS2005, maybe you can find useful information from that blog.

    Best Regards,

  • No of Aggregations Vs % of Performance gain