Background: I was originally scheduled to participate in a segment of the Best Practices Conference keynote where a group of MCMs would take some time to describe one of their favorite components of SharePoint 2010. Unfortunately, I scheduled a red-eye flight that landed *after* the keynote. This morning's post is a quick brain dump of what I was planning to share with the audience...
PowerPivot is definitely one of most interesting pieces in SharePoint 2010. Don't get me wrong - there are lot of other things that I really like (services architecture, managed metadata, etc.) but PowerPivot really stands heads and shoulders as the best new "power toy".
Before I dive into some bullet points why... let me frame the conversation a bit...
What is PowerPivot?
In the simplest sense, PowerPivot is three things: an engine, an add-in for Excel, and a SharePoint service application.
The engine is built to operate with large sets of disparate data. It's what makes the add-in and service application possible and also makes it possible for other applications to consume PowerPivot workbooks as data sources.
There is a client side component, PowerPivot for Excel add-in, and a server-side service application, PowerPivot for SharePoint. You can use one without the other, but you'll eventually need the client add-in to design and create workbooks than can be used by the PowerPivot service application.
3 things: engine, Excel add-in, SharePoint service application. It's a key point to remember!
What makes PowerPivot so cool?
Simply put - data.
PowerPivot can allow folks to work with huge amounts of data. I've seen Excel workbooks that contain PowerPivot data numbering will into the hundreds of millions of rows... even seen billions of rows... that's right... millions with a B.
PowerPivot is all about data aggregation.
It allows you to pull in data from multiple data sources and then join it altogether as though it were all from single uniform source. If you get into the details of the process, it truly is from a single source - this is where the technology part kicks in.
PowerPivot has a data engine that allows you to import data from stores like SQL relational databases, Analysis Services, other PowerPivot workbooks, text files, and ADOMD.NET just to name a few. The import process packs all this information into a standard Excel workbook. From there, the data is in fact coming from a single uniform source. The data engine is built on Analysis Services and, once you load your workbook data, PowerPivot lets you build pivot tables and charts as well as expand your data mining opportunities via DAX (a new column-based formula language).
PowerPivot is all about flexibility.
The client piece is basically a completely autonomous application that doesn't have anything to do with SharePoint. If you simply want to work with big data, install the add-in and go. The add-in includes the fully featured PowerPivot engine. Your only limitation is limitation of your client machine (x86 vs. x64, processor, memory). You'll need the Excel client to design the workbooks that will be eventually published to SharePoint.
If you need to move up to a more serious, capable framework for calculation and distribution, move those workbooks over to SharePoint. Here you get the opportunity to leverage the scale and power of servers. PowerPivot for SharePoint is new service application that is available in SQL 2008 R2 and designed for the enterprise sku. The nicest thing about PowerPivot for SharePoint is that it leverages your existing Excel Service infrastructure (much like the client version leverages your existing Excel install!) and is can be managed independently using the services architecture.
PowerPivot bridges the gap between the analyst and the data.
How many folks know how to use Excel? (imagine you are at the front of room, you'd probably see every hand go up) What makes Excel so popular? It's powerful. It's easy.
How many folks have ever requested some type of BI report? (fewer hands)
How many folks think there is a fast turnaround time for BI reports? (probably no hands)
Why? Traditional BI has often required large, specialized projects that quite frankly take a lot of time and money to complete.
If you can get the data out to your users, why not let them create their own dynamic reports? If they know how to use Excel, leveraging PowerPivot (in either form - client or server) is a logical next step. As with any well-planned SharePoint deployment, work on finding your trusted PowerPivot authors - not everyone should be allowed to publish PowerPivot workbooks. As with any service, you need to consider the workloads and behaviors to ensure the service works as expected for all users.
PowerPivot bridges the gap like no other Microsoft tool can... It is an engine that uses the familiar Excel experience for ui elements and it is not a completely different developer toolset like other BI tools.
PowerPivot is industrial strength BI that can be delivered directly to your business units.
See you on the next post!
-Maurice