SQL Server Advent Calendar 5 – Analysis Services Performance

Day 5 of my virtual advent calendar, about stuff I like in SQL Server 2008..

Over 80% of the budget on improvements to Analysis Services in SQL Server 2008 went on improving performance. The answer was called block space computation and to understand this you should be aware that Cubes are often quite sparse so for example every customer doesn’t buy every product everyday (nice if they did!).  However in SQL server 2005 calculated members were applied to the whole cube wasting a lot of time and effort in the process.  More sophisticated users worked around this by using not empty predicates in queries with limited success.

Block space computation fixes this by filtering the incoming query before calculated members are applied (the meat grinder in the diagram below) to it to remove any cells which have a default value in them. 

Thus calculations are only applied where they are needed and this just works behind the scenes without any user intervention. 

So I would recommend testing your performance using an evaluation copy of SQL Server 2008 by simply backing up / restoring to the new server.

Technorati Tags: sql server 2008,analysis services,block space computation