PowerPivot for SharePoint 2010 – The Business User’s Perspective

This article continues the series on PowerPivot that started with an overview as well as an introduction to PowerPivot for Excel 2010. We’ll now focus on the support PowerPivot provides for collaboration.

Frequently, business users need to share applications they create on their desktops with a workgroup. As it turns out, SharePoint provides a great platform that is critical for realizing the overarching goal of extending business intelligence technology to an organization:

• Great feature set for collaboration: Given that PowerPivot data is stored within an Excel workbook, whatever support SharePoint provides for sharing documents – versioning, workflow, easier security management, the flexibility of creating sites, sub sites or document libraries, customization of portals, etc. – is automatically available to PowerPivot users.
• Increasing pervasiveness: As SharePoint is deployed in more organizations, business users and IT are becoming more familiar with the tool as well as best practices on how to use it. PowerPivot will extend those tools, processes and learnings instead of replacing them.
• Centralized resources: Business users can depend on an infrastructure that’s around, where they can schedule heavy lifting to happen, where they don’t have to worry about backups or management of servers. The separation of duties that SharePoint enables – IT focuses on infrastructure & compliance, while business users focus on the content – is perfect for enabling managed Self Service Business Intelligence.

Let’s take a quick stroll through a few of the features that PowerPivot for SharePoint enables for business users.

PowerPivot Gallery

SharePoint users are very familiar with a flat list of documents such as this:

For our target audience, the library contents are frequently workbooks that users can better associate with visually. In addition, we want to make publishing PowerPivot workbooks inviting, fun, and representative of the care our users put into their spreadsheets. As such, PowerPivot provides several skins that can be applied on a document library. Here’s one where each container is a workbook and each thumbnail is a snapshot of the actual worksheet contained within the workbook:

Here’s another one that is more optimized for using larger real estate:

Both of these views use Silverlight to provide smooth scrolling across snapshots.

Irrespective of the skin, the bitmap shown is a real snapshot captured whenever the document is changed in SharePoint.

Integration with Excel Services

Selecting any of the snapshots launches the appropriate application. Selecting the snapshot of a worksheet, for example, will lead users directly into Excel Services with the right worksheet selected by default:

Remember that the workbook contains PowerPivot data within it. Performing any action that requires access to that snapshot – explicitly forcing a refresh, clicking on a slicer, retrieving a list of possible values to filter on, etc. – will transparently cause PowerPivot for SharePoint to extract this embedded data, load it into SQL Server Analysis Services running on the SharePoint farm, and service the query request from Excel Services. Thereafter all queries for that workbook, whether from the first user or any others, are satisfied using a high performance database engine. PowerPivot for SharePoint continues to manages the lifetime of this database and will automatically unload it if it hasn’t been used for a while.

There are, of course, many more details about this implementation but for this post focused on business users, it is important to note that the only difference end users see is that the first connection to the database may take slightly longer than once the database has been loaded, and of course, that they don’t need to download the complete document on their machine, or even have Excel installed on the machine, to benefit from what someone else in their workgroup produced.

Scheduled Data Refresh

Frequently workbooks contain PowerPivot snapshots that need to be refreshed periodically. Instead of requiring users to manually download the workbook to their desktop, refresh the data set and upload an updated copy of the workbook, PowerPivot for SharePoint allows business users to setup a data refresh schedule per workbook:

This mechanism:

• reduces the need for users to use their local machine resources for performing data refresh
• removes the burden of the mechanics of downloading, refreshing and uploading the file, as well as of remembering to do one more task
• can work well in organizations where there is lower bandwidth between a client desktop and the data sources, but there is higher bandwidth available between SharePoint and database systems, both managed by IT.

If PowerPivot contains data from different data sources, users can specify a schedule for each of those data sources independently as well. This is useful in the case where operations data needs to be refreshed more frequently than reference data, for example. 

Using PowerPivot Workbooks as Data Sources

Some PowerPivot users will spend significant time in importing, cleaning and shaping the data for their use. Others in their workgroup may want to reuse this effort. Today, one option available is to save a copy of the original workbook to retain the data and logic and delete what’s unnecessary. This unfortunately duplicates the data and breaks a link from automatically receiving future updates to the original spreadsheet. If that is too painful, users can choose another approach, of putting everything in one spreadsheet, with different sheets reserved for different users. Both of these options are less than ideal.

The mechanism that PowerPivot for SharePoint uses for extracting embedded data from workbooks and making it available for queries from Excel services is also available for any other application that connects to Analysis Services. i.e. as a user, I can create two different scenarios:

1. PowerPivot in my workbook can import PowerPivot data stored in another workbook. In this approach I get the benefit of a refreshable snapshot without needing to duplicate effort, and/or 
2. My workbook can use PowerPivot data stored in another workbook as a data source and send queries to it directly. This option is not limited to just Excel. In fact, any application that understands how to query a SQL Server Analysis Services database, such as Reporting Services, can query PowerPivot data stored within a workbook. The only change users needs to do is use the SharePoint URL to the workbook that contains PowerPivot data as the server name and PowerPivot for SharePoint takes care of the rest!

Summary

This was a quick overview of the features PowerPivot for SharePoint 2010 introduced in the August CTP. Hopefully you got a taste of how the PowerPivot team looked at this space holistically and the usefulness of our first release. As usual we welcome your comments either on this blog or on the PowerPivot blog itself, a site that contains many deeper articles on this topic.