I have 3 partitions using a year grouping. Current year, previous 4 years, older than 5 years

I have two measure groups, one is a distinct count, so I actually have 6 partitions.

I also use usage based optimization to build my aggregations.

Should each partition have a separate aggregation or should there be one for each measure group?

What is best practice?

Hi John

This is one of those "It depends cases".  If you expect the query patterns for older data to be different than for recent data (Which it often is) then you may want to have different aggregations designs at different levels, for example you may not have aggregations down to the day level for data older then 5 years but would certainly want day level aggregation for the current period. 

This decision is primarily driven by factors such as 

• Processing time (More aggregations mean slower processing)
• Query performance (More aggregations may mean faster queries)
• Size (More aggregations take up more disk space).

Your aggregation strategy is a compromise between these factors.

So if you have a small to medium size cube and a large processing window (Once a day, overnight when no one is using it) then you may lean towards over aggregating, but if you are processing several times a day or even hour you may want to reduce the aggregations to only those that you know you need.