PowerPivot – things to love, things to hate

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.

One thought on “PowerPivot – things to love, things to hate

  1. You are very fast with these posts 😉 Here is a quick list of mine:


    Ease of Use


    Many-to-many rels
    Dimension attribute ordering (not customisable)
    No ability to swap efficiently on disk – especially when loading more data from the rdbms – instead it blows up

    All in all, without ppvt we would have been unable to tick any boxes…

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>