SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus
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.
If you would like to receive an email when updates are made to this post, please register here
Subscribe to this post's comments using
About Marco Russo (SQLBI)
Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.