Post reply

Storage Design On SSAS 2005

  • November 12, 2006 at 11:54 pm

    #115263

    Dear All,

    I Have several very large cube. When I design the storage, I used MOLAP, and wanted to use 100% aggregation. But after the processing, the cube still have 0% aggregates. What's wrong?

    FYI, I changed the storage after whole data populated into data warehouse

     

  • November 13, 2006 at 8:02 am

    #671363

    Hey Hendra,

    First point, you should possibly try a smaller aggregation setting, 100% can often mean that performances id degraded (plus it's unlikely that AS will let you do this anyway due to that 30% rule, for more on this do a google on aggregations +"analysis services2005" ).

    When you went through the storage design wizard, and you selected 100%, did the form show what how many aggregations it intended to build?  Prior to this, in the 'count' screen, did you open any of the tree nodes (ie dimensions) and look to see which levels were bold?  The bold ones are the only candidates for aggregations.  If none (or few, or maybe just the key level) were bolded, you'll not get aggregations.

    A few things you can do to ensure you get some aggregations:

    1. (!!MOST IMPORTANT!!) Ensure that your attribute relationships exist and are correct.  There are a few blog posts and documents floating around that are confirming that having these incorrect or even not set up can return incorrect results and definitely degrade performance.
    2. In the cube designer, check the property Aggregation Usage property.  Set it to Unrestricted for any attributes that youd lie to be considered for aggregations.

    Liz Vitt has written a pretty good overview of how to influence the aggregation candidates and the impacts of various property settings here.

    One last point - after designing your aggregations, when going through the Process screen, if all of your partitions are already populated (ie processed), you don't have to do a full process again just to get the Aggs, simply select Process Indexes and this will rebuild the indexes and also the aggregations without having to re-read the data into the partitions.

    Cheers,

     

     

    Steve.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply