THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

Attribute Aggregation Usage Priority

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.

Published Thursday, May 17, 2007 1:17 AM by Marco Russo (SQLBI)


No Comments
New Comments to this post are disabled

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.

This Blog



Privacy Statement