I and Alberto have posted a suggestion on Microsoft Connect to improve aggregation wizard behavior in a future version of SQL Server. The idea is to give an aggregation usage priority to dimension attributes for each cube or measure group. If you find the idea interesting, please vote it. The following is the description of the suggestion.
It would be useful to have the ability to define a “priority level” for the dimension attributes that have “unrestricted” aggregation usage. The priority level should inform the aggregation wizard about which attributes will most likely improve calculations but has a lower effect than “full” usage.
A typical scenario would be that of a balance sheet having multiple parent child hierarchies on the account dimension, several LastNonEmpty calculations and other important dimensions (say “Organization”). In this situation we would like to have a Time-Account aggregation, a Time-Organization aggregation but we find it useless to have Time-Account-Organization aggregations.
The wizard should try to create an aggregation on each of these attributes but should not try to create their Cartesian product as it currently does with “FULL” aggregation usage.
The user interface for attribute aggregation priority definition should not be tied to the attribute properties but it should have its own panel (per cube/measure group) displaying attributes grouped and sorted by aggregation priority. A drag-and-drop feature would be welcome.
Even if the cost of aggregations seems to be high (both on process time and disk usage), the final result is terrific for this kind of scenarios. Actually we can do that by hand, but it has a very high cost of development and maintenance.