Analysis Services 2000 to 2008

  • Article

Analysis Services 2000 was  my life for several years and like a good friend you tried to appreciate the good things and try and work around what is not so good.  I enjoyed how seductive and easy it was to build a cube, I hated the fact that you could leave the designer open go home and the cube wouldn’t build because of that, but you told yourself it was your fault not the product teams. 

Then out comes SQL Server 2005 and it jars. The ease of use seems to have gone, where are my virtual cubes and dimensions and what on earth is all this new stuff – the measure groups, the attribute relationships to name a few?

I was lucky enough to get trained up by Reed Jacobsen and he was unlucky enough to have broken quite a few bones and was strapped up while giving the course.  Anyway I got the idea behind the new Analysis Services, I understood why the data source views were important (to abstract the data from the source), and I saw how Microsoft had embraced Ralph Kimball’s bus architecture when I saw the dimension usage grid in the Business Intelligence Design Studio.  A good way to describe both versions is that in Analysis Services 2000 everything is hierarchy based while in 2005/8 it’s attribute based.

This complete overhaul of the Analysis Services came at a price and that is backward compatibility. Moving from Analysis Services 2000 to 2005 generally involves redeveloping the cube in all but the simplest cases.  However I don’t really see this as a big deal as rebuilding a cube is only a small part of a BI project. If most of the design stays the same it can be fairly simple mechanical exercise,  The biggest part being testing and confirming the numbers are OK in the new version.

The next version of Analysis Services in SQL Server 2008 is just around the corner, and it’s time for more change, but not as much as last time. I am reminded on the transition of OLAP Services in SQL Server 7 to Analysis Services 2000 in that the changes are subtle but significant. The 20005 cubes will migrate to 2008 without having to be reprocessed, but there changes and I have listed a few of my favourites here:

  • The new graphical attribute designer lets you see your design more clearly
  • Best practice warnings underline anything which may affect the performance of your cube, in addition to red underlining showing any errors
  • The block computation can radically speed up queries across sparse data
  • Dynamic grouping lets you create a list of top ten selling products that reflects the latest data.
  • Backup performance is now linear i.e. a cube that doubles in size will take twice the time to back up rather than three or four times longer as is currently the case.

So you might then reasonably consider moving straight to SQL Server 2008, from 2000 as there will be no additional work involved and no additional gotcha’s. 

Technorati Tags: Analysis Servicesl,olap services