PowerPivot ? things to love, things to hate

[James Beresford]

I’ve recently been involved in a couple of cases for a client that revolved heavily around PowerPivot, and its prompted me to highlight some of the things which make me simultaneously love and hate it (though in balance its clearly a thing to love).

I hate PowerPivot because…

So let’s start with the negative. It’s a v1 Product, which means of course it will get better. But because it’s v1 and Microsoft clearly felt a compelling need to give Qlikview something to worry about (and TM1 to some extent) so it’s been shoved into the limelight in a starring role its not entirely ready for.

So, my gripes are:

• No drillthrough. You can slice and dice superfast, sure…* but unlike SSAS (which has flawed drillthrough), you can’t drillthrough at all in a Pivot Table pointing at a PowerPivot workbook
• No API. So there’s no way to programatically build a* PowerPivot workbook, or interact with the PowerPivot data model from Excel (even for a simple data refresh)
• No Data Mining Support. There are some half hearted workarounds documented here but you simply cannot point the Excel Data Mining Add-In at a PowerPivot data model. If you want to do that, you need to lean on Predixion Software (fortunately Jamie McLennan, part of the original MS Data Mining team is heavily involved so it’s a good bet this is a good option, but the cloud aspect may deter some). To add insult to injury, there is no Data Mining Add-In for 64-bit Excel, and the Data Mining and Power Pivot Add-ins often conflict.

Another common gripe is about a lack of Hierarchy support (this doesn’t really bother me but drives SSAS guys up the wall). This is think is due to be resolved by BISM anyway in Denali. So, at the end of round 1, it’s taken a few punches, but it’s the new kid and it’s far from out for the count…

I love PowerPivot because…

Microsoft have been pushing “BI For the Masses” as their vision since SQL2005. With SharePoint 2010 and Office 2010 I think they are finally getting their vision translated as reality. To start with, I wasn’t entirely sure what the benefit of loading PowerPivot workbooks as data sources into SharePoint actually was, but I had my lightbulb moment last week. If you have a Data Warehouse, or a set of data sources that as a business user you don’t really have any control over, you can be a bit stuck. Building cubes can be a bit of a challenge and you may not be able to get the access / skills / project approval needed to build such a data model. But if you can do it in PowerPivot… then you can model your data to your need, upload to SharePoint – and suddenly you have your data mart for your department. The amount of power this gives to the users who are stuck in the “Import to Access, do some dodgy manipulation, then surface in Excel” rut is phenomenal. I’m not going to get started on what can be done with the REST API with Excel and SharePoint.

Of course another easy thing to love about it is that it all lives in the Excel environment that so many BI users know and love, so user adoption is rarely a challenge.

I think however, there’s one thing that simply cannot be ignored - it’s mindbogglingly fast. In a POC I did recently with my colleague Boyan Penev (to be fair, he did all the work) we were tossing around an 80 million row fact table with some hairy relationships in it and it still responded in subsecond times. Because of this, it’s going to be very, very hard to ignore as the underlying technologies (SharePoint and Office 2010) gain traction.



Get More from the original blog...