Is SQL Server 2005’s analysis services a good tool to use for reporting and KPI reporting in particular? In short – yes, but it does require a fair amount of skills to achieve this. I have found that you really need to understand how the cubes work properly before attempting to add in the more exotic KPI measurements.

Where the cubes really come into their own (as opposed to stored procedures running of relational tables) is in the MDX scripted calculations, in particular I have found the ‘periods to date’ functions extremely useful. Previously we would have had to precalculate these measures, or in some cases do it on the run, through a SP with mountains of T-SQL code. Now we simply have the short MDX expression on the cube – even better is that it allows any period calculation (week to date, month to date, quarter to date etc.) to be implemented instantaneously, and even better vs the same period last year.

The KPIs added to the cube are just one step further (another tab in the Visual Studio Designer). So now implementing and reporting on these measures is so much quicker and much more accurate than it ever was before. The big challenge is always (and should always be), exactly what does the business need to measure?

Latest from this author