my first look at the aggregation wizard


  • Hi.  I'm taking my first look at the aggregation wizard and wondering before I go further...I'm focused on a month attribute in my Calendar Date Dim.  It happens to also be involved in a user hierarchy.  It appears in the wizard that it is aggregating according to defaults right now.   Does ssas automaticallly aggregate across user hierarchies?...which would make an experiment of this type a waste of time?   I'm trying to be careful here.

    We run srd 2008.

    Friday, June 15, 2012 8:02 PM

All replies

  • Hi db042188,

    Attributes only exposed in attribute hierarchies are not automatically considered for aggregation by the Aggregation Design Wizard. It is possible to flag an attribute as an aggregation candidate by using the Aggregation Usage property. However, before you modify the Aggregation Usage property, you should consider whether you can take advantage of user hierarchies.

    From a performance perspective, natural hierarchies behave very differently than unnatural hierarchies. In natural hierarchies, the hierarchy tree is materialized on disk in hierarchy stores. In addition, all attributes participating in natural hierarchies are automatically considered to be aggregation candidates. Unnatural hierarchies are not materialized on disk, and the attributes participating in unnatural hierarchies are not automatically considered as aggregation candidates.

    For more information about it, please refer to:
    SQL Server 2008 White Paper: Analysis Services Performance Guide: 


    Monday, June 18, 2012 7:12 AM
  • Thx Eileen.  I originally set up the attribute relationships in this case as more or less day->month->quarter->semester->year.  I believe that makes them part of a user hierarchy.  Before I read the paper, can you tell me if that is synonymous with a natural hierarchy?  And if so, is the month attribute then just a candidate for some sort of aggregation or for sure being aggregated in some fashion?   If its only a candidate, can/should I force the issue in the wizard by clicking the column that appears to instruct ssas to aggregate?  
    Monday, June 18, 2012 2:02 PM
  • Hi.  I made my first pass thru the performance guide.

    I came away with a few questions:

    1) my estimated #rows on my 280 million row measure group is 0 in the metadata.   Can that explain terrible performance in pivots that involve this measure?  Can I reset this number without redeploying/reprocesing where I see that capability in ssms, and see the benefits right away?

    2) I dont see a setting called max threads at the server level...where is that?

    3) I dont see a setting called MaxParallel either...where is that?

    4) my date which is in an attrib relationship with month which in turn is in a relationship etc etc all the way up to year has a flexible realtionship while month to quarter etc are all rigid.  Might that flexible setting be killing me?

    5) beside pretty bad performance on my 280 million row measure, we also see that within or across other dims ,  involving our acct number dim in pivots often drags down performance.  This dim is huge (555K) and I'm sure also costly in some calc'd measures that depend on it.  Any thoughts there? 

    • Edited by db042188 Friday, June 29, 2012 5:24 PM must have hit "submi" before I was done
    Friday, June 29, 2012 5:11 PM
  • Hi. 

    Also I dont see an estim row count in ssms for my dims.   I think the white paper said there would be one but I may need to reread.  If yes, why cant i find it?   I dont see it in VS either.

    Also, as I contemplate doing a full aggregation on my most popular date's month attribute on my largest measure group of 280 million rows, knowing I have 30-40 other dims over this measure also which I assume means the permutations would be substantial, is there a way to predict the downside of such a move?   I believe ssas already knows this is a date.   The aggreg usage is Default currently. 

    • Edited by db042188 Friday, June 29, 2012 7:27 PM more info
    Friday, June 29, 2012 5:51 PM
  • i dont notice any improvement in performance after adding a second and third dimension to my pivot after first pivoting on the now fully aggregated month attribute with an aggregation design performance setting of 37%.   This also includes a rigid relationship that was previously set to flexible between date and month in the shared time dimension.

    Also, I dont see the expected 17gig bloat in db size I expected.  Maybe the full process I just ran ignored my aggreg design?

    the initial pivot on just the month across my 280 million row measure group did seem faster though.

    Thursday, July 19, 2012 8:21 PM