My thoughts on why PowerPivot and where to place it

By | April 28, 2010

I was pointed to a excellent discussion on the linkedin Microsoft Business Intelligence group  forum post yesterday, go check it out the discussion is excellent the questions are probably on a lot of your minds now.

So I decided to put in my thoughts on PowerPivot on that forum, it became quiet a story 🙂 so i decided to put on my blog as well:

Although PowerPivot is a very powerful tool it is NOT meant as a complete BI solution, this is a point i try to make on every presentations/sessions i do on PowerPivot.

What Microsoft has done with the release of PowerPivot is starting to bridge the gap between IT and business by providing a managed self service BI to solve the ad hoc questions that are in any organization. Your BI/IT department just cannot solve all information questions in a organization, the ROI to solve all questions just isn’t positive to fix using traditional BI using ETL, cubes and reports. Think of a question someone in you organization has where he wants to combine values from two cubes to get information. He might take a look at it once or twice but it isn’t needed in his daily work process, making the ROI very hard to make. Currently this would be fixed by someone in your organization who is pretty good with excel, the information product start to live a life of its own, out of control of IT/BI.

With PowerPivot end(or Power) users get the chance to solve these kinds of ad hoc questions themselves on their own desktop. Pulling information into PowerPivot, combining tables using natural keys and creating powerfull analytics using DAX and Excel visualisation options. Remember your IT / BI department still has to supply them with the information they need to support their daily work. When this information will be shared to other users in your organization by deploying it to sharepoint the users can view this information in HTML (no need for Excel 2010 of PowerPivot add-in) and the IT department gets the possibility to monitor PowerPivot workbook usage with the IT management dashboard. IT/BI can identify the information question that start to become mission critical and take action to take it out of the self service into the traditional BI.

So the idea is to keep using SSAS, SSRS and Perf. Point for your traditional BI (or BI from another company) supporting your primary process. Let end users (or a few excel power user) get information that isn’t supported by your IT/BI department using PowerPivot. I agree the single version of the truth isn’t maintained using PowerPivot, but you can make sure the end users have access to trusted base data sources (like cubes, reports,sharepoint lists) and you can see what they do and what information they use instead of closing your eyes to users getting information in an unmanaged environment.

Again this is to my understanding of the take of Microsoft on PowerPivot, it is an addition to the current BI stack, not a replacement. Of course it being a Microsoft product there are all kinds of hacks available and more and more will show up when it will be used more widely.

The reason for Microsoft to integrate PowerPivot into SharePoint makes sense to me, the collaboration and managed part is very important. Because of the cost I indeed will see a big threshold for PowerPivot for SharePoint being implemented in SME. But also i think it only will be a matter of time that we get PowerPivot for BPOS. BPOS being the cloud sharepoint for SME.

For the future, I think Microsoft is very aware of it missing a business wide meta-data layer, PowerPivot 1.0 is just a start, in the future we probably will see PowerPivot, SSAS, SSRS and Perf. Point and new tools being more merged together. Also the release of Master data services in SQL Server 2008 R2 is a start on defining a business wide layer, we probably will see a integration between MSD and SSAS dimensions.

I also saw a few other questions here that are not related to the original post, i would be happy to answer other question on PowerPivot at my question page:
Also you can take a look at the PowerPivot FAQ at

I hope this will help you getting an understanding of PowerPivot and how to place it in the MS BI stack.