Why Choose PowerPivot?

If you are on the market for self-service, or in-memory BI tools you have some options. You have to consider functionality, cost and the future. If you are a SQL Server and/or SharePoint and/or Microsoft Office user, PowerPivot should be a top-of-the-list contender. I will discuss a few points to show why.


First and foremost, it is cheap. To utilise PowerPivot you need Excel and possibly SharePoint. If you need to empower your users with the capability to expand Excel and do heavy analytics on a workstation PC, PowerPivot is essentially free for Excel. If you want to let them collaborate and share their work, then SharePoint comes to the mix. If you do have SharePoint Enterprise in your organisation, then PowerPivot is, again, free. A free self-service BI platform – not a bad option, is it? Surely the cheapest.


Functionality-wise, the outstanding “feature” is the integration with Excel. How many other self-service BI tools out there allow you a seamless integration with Excel? When Excel users become PowerPivot users, they have all the capabilities of Excel, plus PowerPivot. They can pick Top/Bottom 10, flick to percentage representation of values, use the charting functionality of Excel the way they are used to; they can also utilise the rest of the Excel functionality they love. With a little bit of DAX knowledge they can build new calculations on top of massive data sets. Writing [Quantity]*[Price] gives us [Sales Amount]. Simple, isn’t it? Furthermore, if you prepare a nice, well-referenced datamart for them you do not need to worry about the lack of knowledge of SQL – all modelling gets done in Excel in a very familiar for users environment – spreadsheets with rows and columns. Data can be previewed, filtered and ordered; new columns can be added with Excel-like syntax – a paradise for moderately Excel-savvy users.

Once ready with the model, if users want to share their work they can simply publish to SharePoint. From there other users can either browse the workbooks (if they have a browser – right…), or if they are interested in more on-the-fly analysis they can connect to the workbooks through Excel and slice/dice the data just the way they do with SSAS cubes. No need for client installations and no need for powerful workstations. In fact, to connect to published models they only need Excel 2003 and Windows XP. The minimum hardware requirements for those are, well…minimum.

As for IT Services departments – they still can manage the situation. They can monitor, advise and service – precisely their purpose. While a standalone, isolated and incompatible server could be a problem, the sort of manageability and visibility BISM and PowerPivot offer will, no doubt, appeal to ITS.

The Future

Let’s zap to the future. Microsoft has made a strong commitment for a multitude of future enhancements. The models will be available in SQL Server Analysis Services and DAX will get massively enhanced. There will be numerous enhancements on the modelling side, querying side, engine side, etc, etc – all in all – BISM and PowerPivot have a great future. In fact, from what I can sense, BISM and DAX will become more and more powerful and if you commit to spending your money on another product I can guarantee that you will be thinking back and regret this step, especially if you like Excel and SQL Server. Just think back of where SSAS was in 2000 and where it is now. Well, by what it looks like at this moment, we’ll have a similar situation after Denali and ahead of it. Better than ever will be the integration between the components in the Microsoft BI stack, too. With Crescent and SSRS reading BISM models easier, the pegs will fit together even more seamlessly.

Am I advertising Microsoft BI? Yes. Am I objective – maybe no, but allow me to be excited about it. From the poll on the top right of this blog, where I asked how you feel about the recent BISM announcements, I noticed that many people are either “Angry”, or “Excited” about the new developments around SSAS. I wonder what will the reactions be when Denali ships and more people get hand-on experience with BISM. Will there be as many “Angry” people out there? I doubt it. Will everyone get ecstatic – well, maybe not, but I believe that a lot of users will get more Excited/Happy about it.

6 thoughts on “Why Choose PowerPivot?”

  1. ” When Excel users become PowerPivot users, they have all the capabilities of Excel, plus PowerPivot. ”
    With all due respect, I prefer to differ in this point. Some examples –
    a) You lose undo feature as calling a macro or addin like powerpivot tends to erase the Undo stack
    b) Additional effort to sort time fields like months (considering that time intelligence is a feature in powerpivot)
    c) You can modify pivot tables with vba but the same cant be done with Powerpivot tables
    There are more minor irritants. But considering all the upsides, I completely agree with you, and am really excited about this. I was really happy to see myself able to play around with a million rows in powerpivot with the same ease of a thousand rows in excel


  2. Jason,

    You get DAX instead of VBA 😉

    With “all the capabilities of Excel” I meant things like pivot table analytics, charting, etc. – features which users are very familiar with and like. It is very easy and learning-curve-free to do these native Excel operations, which doesn’t hold true for many competitors.


  3. Hehe, true Boyan, I was just putting myself in the shoes of an user who is already accustomed to excel and has learnt VBA… Now he will have to learn DAX also! But personally, I feel it is worth the trouble and agree that the learning curve would be smooth… 🙂


  4. Boyan,

    Nice post. I think there is one more piece of the cost puzzle though. As I understand it, if you are using SharePoint, you need an Enterprise SQL license for the SharePoint server. Even if you already had SQL Enterprose on another server already. It’s not necessarily a deal breaker, but it is a significant cost. Unless I misunderstood the licensing requirements.



    1. Hi Drew,

      Yes, you need SQL Server Enterprise license to use SharePoint. That is why I started the post with a few “if”s. If you do have SharePoint already running and you are using Excel then PowerPivot should be the number one candidate for your self-service/in-memory BI needs.


Comments are closed.

%d bloggers like this: