Vidas: My next blog entry contains updated code !
It is quite often that I have to process all the dimensions in a database. I like to use scripts for that. Here I have step by step guide on how to create an SSIS package that processes all dimensions in one database.
With SQL Server SP2, Microsoft added an Aggregation Manager utility to its samples. It is not installed by default, and has to be downloaded separately from http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en, file SqlServerSamples.msi
This utility allows you to see existing aggregates, and allows you to easily add aggregates manually or from a Query log. Do not miss this one!
With SQL Server 2005 Service Pack 2 Microsoft introduced storage format vardecimal. As I work a lot with big fact tables, I decided to test how this new format affects storage. If you want to get estimated impact on using vardecimal storage format, you can run stored procedure sp_estimated_rowsize_reduction_for_vardecimal. This SP expects table name as parameter and returns row count, average row length for fixed format and average row length for vardecimal format.
I prefer to use usage statistics to generate SSAS 2005 aggregates, but during the initial phases of a project they are not available. So I usually build a set of random aggregates, and after enough usage statistics are accumulated, I rebuild aggregates based on usage. Microsoft provides a nice wizard to generate aggregates on measure groups and partitions. But because I have to do this task way to many times, I decided to build a simple SSIS package that does it for me.