Introducing PowerPivot for SharePoint

Business intelligence (BI) is a multistep process that traditionally has been heavily dependent on IT. Microsoft PowerPivot breaks with tradition by giving end users the tools they need to own some or all of the process. IT sets up the infrastructure, and end users implement the steps. As Figure 1 shows, end users utilize two tools: Microsoft PowerPivot for Excel 2010 and Microsoft SQL Server PowerPivot for SharePoint.

PowerPivot for Excel 2010 is a free data analysis tool that lets end users gather, store, model, and analyze data. PowerPivot for SharePoint lets end users share that data. I already discussed PowerPivot for Excel 2010 in “A Walkthrough of PowerPivot for Excel 2010”, so I’ll concentrate on PowerPivot for SharePoint here. After I define PowerPivot for SharePoint and describe its architecture, I’ll discuss several common use cases and provide some administration pointers.

Related: Business Intelligence in SharePoint 2010

Definition

PowerPivot for SharePoint is a service that extends Microsoft SharePoint Server 2010 to support PowerPivot workbooks. Specifically, it’s a named instance of SQL Server Analysis Services (SSAS) 2008 R2 Enterprise Edition running in the new SharePoint Integrated mode. In this mode, SSAS is part of a SharePoint 2010 Enterprise environment, with databases and cubes sourced from Microsoft Excel workbooks that have been published to a SharePoint document library.

Architecture

Administrators need to be familiar with the PowerPivot for SharePoint architecture in order to install, deploy, and manage it. I’m not going to review the installation steps here because SQL Server Books Online (BOL) does a good job of explaining how to install PowerPivot for SharePoint to new and existing SharePoint environments. But I do want to review some of the key components, because the actual deployment involves several layers that can be a bit daunting to understand, especially if you’re new to SharePoint.

Figure 2, which is from the white paper "Microsoft SQL Server PowerPivot Planning and Deployment," identifies key architectural components, including:

• Analysis Services service. This component is the named instance of SQL Server 2008 R2 Enterprise Edition running in SharePoint Integrated mode. The instance is always named PowerPivot.
• PowerPivot System service. This service acts as the communication layer for the Analysis Services service. In Figure 2, you’ll see that this system service is wrapped by another box labeled Service Applications. You can think of a service application as a resource that makes a system service available to the sites in the SharePoint farm. The service application is created and configured automatically when installing PowerPivot for SharePoint using the New Server installation option; otherwise, it can be created manually using the SharePoint Central Administration console.
• PowerPivot Web service. This thin middle-tier connection manager is deployed on the SharePoint web front-end server. It detects external requests for PowerPivot data and forwards them to the service application, which in turn communicates with the PowerPivot System service.
• PowerPivot Gallery. Although it’s called the PowerPivot Gallery, this component is a special-purpose SharePoint document library that contains features (e.g., a Silverlight control) to display and work with PowerPivot workbooks. For example, when a PowerPivot workbook is deployed to the PowerPivot Gallery, a thumbnail image of each worksheet is generated to provide a preview of the workbook.
• PowerPivot Management Dashboard. Exposed through the SharePoint Central Administration console, this web page is used to monitor and administer the overall PowerPivot for SharePoint deployment. (I’ll discuss what the dashboard monitors later.)
• SharePoint timer jobs. Similar to SQL Server Agent jobs, SharePoint timer jobs are used to collect statistics and usage data about PowerPivot. SharePoint timer jobs are scheduled and executed through the SharePoint Timer Service.

Note that in terms of physical layout, it’s certainly possible to install all the components on one dedicated server, such as the HP Business Decision Appliance. This appliance is a single-server implementation that can support 60 to 80 concurrent users, equating to hundreds of real-world end users. (For more information, see "HP Business Decision Appliance.") However, larger enterprise deployments will likely involve multiple dedicated servers. The "Technical diagrams (SharePoint Server 2010)" web page provides sample topologies.

Figure 2 has several arrows denoting communication paths between the various components. To help you better understand the paths, I'll walk you through four common use cases:

1. Saving a workbook to the PowerPivot Gallery
2. Viewing a workbook in a browser
3. Interacting with a workbook in such a way that invokes the PowerPivot for SharePoint components
4. Interacting with a workbook in such a way that turns a PowerPivot workbook into a data source

Use Case 1

Suppose that an end user saves an Excel 2010 workbook to the PowerPivot Gallery. In response, SharePoint saves the entire Excel workbook. Remember, the PowerPivot Gallery is just a special type of SharePoint document library.

In this use case, the various PowerPivot web applications and service applications shown in Figure 2 aren’t used. The Excel workbook is simply stored in a SharePoint content database.

Use Case 2

Suppose that an end user wants to use Internet Explorer (IE) to view a workbook in the PowerPivot Gallery, so the person navigates to the gallery and clicks the workbook. In response, IE first communicates with the PowerPivot Gallery. Excel Web Access and Excel Calculation Services then retrieve the workbook from the content database and render it in the browser, as Figure 3 shows. These two components are part of Excel Services, a SharePoint service application that lets you load, calculate, and display Excel workbooks. (For more information about Excel Services, see the “Excel Services Architecture” web page.)

Once again, this use case doesn’t involve any of the PowerPivot web applications or service applications. By default, when an Excel workbook is rendered through Excel Services, only a static copy of the workbook is displayed. An additional action must be invoked before PowerPivot for SharePoint gets involved, as the next use case demonstrates.

Use Case 3

Suppose that the end user who opened the workbook in Figure 3 refreshes the data connection. In response, Excel Services first re-queries the external data source. The Analysis Services OLE DB Provider then communicates with the PowerPivot System service, which in turn makes a request to the Analysis Services service. The Analysis Services service loads the Excel workbook into memory and returns the query results.

This same communication process occurs when a user changes a slicer. New to Excel 2010, slicers provide buttons that you can click to filter PivotTable data. Referring back to Figure 3, the Estimate, Century, and Decade_Desc boxes are all slicers. The two PivotCharts and the PivotTable in the worksheet are connected to them. For example, if the end user clears the filter applied to the Estimate slicer, the charts and table will be refreshed to display both actual and estimated values.

I want to reiterate a subtle but important point. Refreshing a data source or changing a slicer causes Excel to re-query the PowerPivot data model, but the PowerPivot data model doesn’t re-query any of its data sources. A PowerPivot data model is a point-in-time copy of data from one or more source systems. A PowerPivot data model is updated one of two ways: manually in the Excel 2010 client or through a separate SharePoint scheduling process. (I’ll discuss these methods later.)

Use Case 4

When you use PowerPivot for SharePoint, you can turn a PowerPivot workbook into a data source. In the top left corner in Figure 2, notice the three orange boxes labeled Analysis Services OLE DB Provider, Analysis Management Objects (AMO), and ADOMD.NET. These three technologies are client libraries used by products such as SQL Server Reporting Services (SSRS), PerformancePoint Services, and Excel PivotTables to communicate with SSAS cubes. When a PowerPivot workbook is published to SharePoint, it becomes a URL-addressable data source that looks just like a standard SSAS cube.

For example, suppose that an end user designs a report in Report Builder 3.0. As Figure 4 shows, the data source is SSAS. However, the data source’s connection string points to the PowerPivot workbook stored in SharePoint rather than pointing to a server. In this scenario, the PowerPivot Web service connects directly to the PowerPivot System service. Excel Services isn’t used.

Why would end users want to connect to a PowerPivot workbook as a data source? Even though the Excel 2010 client and Excel Services provide a great interface, there might be situations in which the other technologies are preferable. For example, with SSRS, end users can schedule a report subscription to deliver reports via email on a recurring basis. With PerformancePoint Services, end users can create a dashboard that includes Key Performance Indicators (KPIs) and interactive web-based grids and charts. And with the standard PivotTable interface in Excel 2007 and Excel 2003, end users can connect to a PowerPivot workbook in SharePoint without having to download the entire workbook. For very large workbooks or environments in which not all users have Excel 2010, this option provides a great way of using the PowerPivot data model.

Administration of Data Refreshes

As I mentioned previously, a PowerPivot data model is updated one of two ways: manually in the Excel 2010 client or through a separate SharePoint scheduling process. The data refresh functionality is disabled by default, so an end user or administrator needs to enable it. When a PowerPivot Gallery is displayed in either the Gallery or Carousel view, a small Calendar icon appears to the right of the workbook. Clicking this icon brings up the Manage Data Refresh page shown in Figure 5. To get to this page in the Document Library view, click the down arrow and select the Manage PowerPivot Data Refresh option. In the Manage Data Refresh page, select the Enable check box and create a schedule to refresh one or more of the data sources used by the PowerPivot data model You can also manually refresh the data model by selecting the Also refresh as soon as possible check box.

To clarify, this data refresh capability rebuilds the PowerPivot data model and refreshes the workbook data connection so that the PivotTables and PivotCharts reflect the updated model. From my experience, this is the kind of refresh experience end users expect—not the Excel Services refresh command, which simply re-queries the existing PowerPivot data model.

Administration of the PowerPivot Deployment

Giving end users the ability to easily connect to and consume millions of rows of data is a bit frightening. To manage and monitor the PowerPivot deployment, administrators can use the PowerPivot Management Dashboard, which Figure 6 shows. As I mentioned previously, this dashboard is a built-in component of PowerPivot for SharePoint and is accessible from the SharePoint Central Administration console. To clarify, this is a tool for administrators and not end users.

The PowerPivot Management Dashboard provides performance data for the SSAS instance (e.g., query response time, CPU utilization, memory utilization), general usage statistics for each workbook saved to the SharePoint site, and information about workbook data refreshes. Interestingly, the performance and usage statistics data is collected on a schedule and maintained in a PowerPivot workbook named PowerPivot Management Data.xlsx. In other words, the PowerPivot Management Dashboard uses PowerPivot to monitor PowerPivot.

A New Paradigm

PowerPivot is a new paradigm in BI, which Microsoft calls managed self-service BI. Looking ahead, Microsoft has committed to further PowerPivot investments in the next release of SQL Server, which is code-named Denali. If you’d like to learn about those investments, check out the SQL Server BI Blog "BI for the Masses (including Excel & DBA Pros) Continues…." Even if your organization hasn’t yet deployed PowerPivot, I encourage you to get up to speed on its concepts and architectural components, because they will be the building blocks for a major part of Microsoft’s future BI capabilities.