Why PowerPivot for SharePoint?

From time to time I hear questions and comments from folks questioning why they should purchase the server-side component for PowerPivot. After all, the client software is free (with a Office 2010 Professional license). Why spend the money to invest in the server pieces . . . And because it requires a SQL Server Enterprise Edition license, it can get costly.

Having spent 3+ years of my life developing this product, naturally I have a fair amount of passion around this topic. The following is my own personal reasons; my own personal goals for the software and why I believe PowerPivot is such a pivotal (pun intended) component in a company’s BI strategy. This is not marketing and I apologize up front for it. So here goes:

What is the value-add for PowerPivot for SharePoint?

1 — High fidelity rendering of workbooks without having Excel 2010 installed on every desktop. Using Excel Services you get the same interactivity (via slicers) that you get on the desktop. This is part of the producer-consumer model of PowerPivot. The producers are thick-client Excel desktop users. They are the domain experts. They are the power users. They develop the workbooks. Then the consumer part of the model is the publishing of those workbooks to SharePoint for mass consumption by the ‘consumers’. This gives companies true “BI for the masses” in a managed self-service environment.

2 — Automatic daily, or on some periodic basis, update of the data in the workbook. Users come in tomorrow and they see all new data. And the end-users set it up and managed them all by themselves. Refresh is scheduled by-data source, so you can have your transactional data be updated daily and your product reference data month, or similar.

3 — Everything that SharePoint brings to the table can also be used with PowerPivot for SharePoint (since we are just a workbook in SharePoint). Things like check-in/out, collaboration, versioning, approval workflows, email alerts when documents are updated or new documents posted, retention policies, etc. — Everything SharePoint. I cannot not express too much how important this is. We pick up a huge amount of functionality and a platform for customization just be being part of SharePoint. Some database and BI shops don’t yet the point of SharePoint, but to end-users it is a GREAT system. Many times they can solve their own problems just by using it as a platform.

4 — The gallery feature (with its Silverlight control that visualizes the workbook and reports) is ONLY available under PowerPivot for SharePoint. A Gallery provides a professional, high-value ‘center’ for PowerPivot workbooks. If designed properly, many times, just viewing the thumbnail is enough to tell consumers something of value, e.g. all of the boxes are green, even without viewing the report. The producers of the PowerPivot workbooks just love this feature because it gives them the platform to highlight all of the their work. In countless customer visits and engagements, I’ve seen companies where Excel experts spend literally hours and days working on their workbooks. Excel workbooks are the application delivery platform of choice – and a Gallery makes all of their hard work, look GREAT.

And lastly, but MOST IMPORTANT,

5 — Our entire usage facility is driven as part of PowerPivot for SharePoint, meaning that using the backend server allows us to track who is using what workbook, when, how often, with what data sources. We peek inside embedded PowerPivot databases and pull out the actual data connections and that is included in our usage data. We tell you how much resources are being consumed on the box and by PowerPivot services (for capacity planning). We give you a query histogram where the IT backend folks can monitor how long queries are taking. We know which workbooks are being used the most, and by whom (we track all connections). If you have just the client then you are missing all of this value-add from the server. The whole point of the PowerPivot infrastructure is to provide IT a ‘view’ into how the users are using the system. You cannot get that from a file share and using just the client.

PowerPivot is more than a slick end-user reporting tool. Overall the product is designed around a vision of empowerment for BI end-users. A vision that uses managed, self-service components (mostly on the server) to enable end-user to accomplish for themselves what heretofore has only been an IT-developed world. The client add in focuses on a rich powerful BI calculation engine; the server components focus on the self-service “glue.” More than any other Microsoft product, the combination of the two is greater than the sum of their parts.

(Thank you for your patience in reading all of this, as you can see I have a fair amount of passion. I truly believe that self-service is the next great thing for BI and how the vast amount of users will interact with BI)