Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server 2008 BI, Perception & Reality


Following all the acquisition mania in the BI marketplace last year, it’s interesting to see that the Microsoft approach of growing your own BI platform seems to be paying off according to this article by Gartner in Information Week.

It has taken a while though – I well remember my first foray into olap services in SQL Server 7.0, we were using a market leading olap tool to build a cube from a fact table with 75 million rows in for an electricity company. It was taking 26 hours to build and then failed and we used the same hardware and SQL Server 7.0 olap services to build the same cube and it successfully completed processing after three hours, and because it was an open solution we were able to use the same front-end for the users.

That was in 1999 and since then there has been loads of work on the Microsoft BI platform so by last year I worked on a project that could load 15 millions rows of new fact data a night into a cube with two years of history.

In the latest version there aren’t the big changes that we saw moving from 2000 to 2005 as the hard work was done then.  However as I have posted before the key things are analysis services 2008 are:

  • Help and advice to get the optimal design, namely highlighting this using blue squigglies under the offending items.
  • Better wizards and visualisations such as the  attribute designer and aggregation designer to help you be more productive
  • Improvements to the processing for sparse data sets, i.e. where the majority of the set is a default value (not necessarily null) using what is termed block space computation 
  • Dynamic sets, i.e. your top ten customers will be your top ten customers every time the set is evaluated.  This is better than SQL Server 2005 where the rule to derive the set is only evaluated when the set is created.

The key white paper on these new SQL Server 2008 analysis services features is here.