While I’ve been aware of the PowerPivot Add-in for Excel from Microsoft I haven’t looked at it as a means of delivering Business Intelligence (BI) to end-users of Navision. So last week I decided to have a closer look as part of a SSAS upgrade project as a potential ‘new’ viewer for end-users.

Why would someone use PowerPivot?

Because it can turn ‘raw’ data from more than one table into something that can be used as a basis for creating Pivot Tables and Charts. And it’s the multiple tables or seta of data where standard Pivot Tables fall down.

But to make use of this multi-data set environment the end-user / PowerPivot Designer needs to understand about how data relationships work and how to massage data to work with a Pivot Table. Further enhancements can be undertaken using DAX expressions. Finally, everytime the data is updated it needs to be loaded in its entireity into the PowerPivot environment which may require tens of minutes for each PC that’s running the Pivot Tables and Charts.

And here’s the thing. If you understand all of the above then you could do just as good a job using SSAS with MDX and then connecting in from a PC to generate Pivot Tables and Charts. But this time the data is loaded and stored on a single server providing access to as many end-users as you like without having to move relatively large lasses of data about. And it comes with all the benefits of control, backup and restore that a server has.

Overall, I came away from the experience feeling that PowerPivot could be of use in certain circumstances; but that if anyone managed to become a ‘Power User’ of the product then they’d be better going that extra step and utilise SQL Server Analysis Services (SSAS) to do the job.

Unless of course it’s a size thing. I.e. the normal PowerPivot user works for a company with an enormous IT function whose priorities are such that the chance of getting access to a server to create an SSAS cube is slim to none. But by definition that should probably exclude nearly every Navision implementation there is?