Monday, August 29, 2005

SQL Server 2005 - Aggregation Confusion ?!

Aggregations work in a completely different way than they did in MSAS 2000. Don't expect them to just happen when you click the design wizard like they did before.

In fact if you design a cube and then click the design wizard and set say 30% aggregations, some confusing things happen.

1) When you process the cube it will take much longer and generate loads of temp files for holding the aggregation work.
2) the space needed to process the cube will grow in line with what you would expect for building aggregations.

For instance my cube increases from needing around 750mb of space to process, to over 2GB.

However when its all finished the cube data directory stays the same size.... NO AGGREGATIONS.

It was Andrew Sadler from Proclarity and Stuart Ozer (MS) who helped me work out what is going on here, sorry for being so dumb :)

in fact its blindingly obvious when you know what to look for.

The clue is in the screen which pops up and shows you the counts for your dimensions and fact table before you enter the design wizard.

You will notice that ONLY the key col of the hierarchy has a count and there are zeros for all the levels of the hierarchy.

In essence it would appear that MSAS does not know how to reach the levels to count them for aggregation, so you have to tell it what to do.

You can solve this by creating a attribute relationship between each level which has a path that leads to the key, confused ?

Look at the screenshot below, this is the geographical dimension from the adventureworksDM, in this screenshot there is no path defined

Therefore when you click on the wizard and look at the rowcounts you get a load of 0's

Now you create the correct attribute relationships like so ...

And check the rowcounts

Now you create the correct attribute relationships like so ...

I hope this helps somebody because i was banging my head for hours with this just never quite getting it to work.