Using PowerPivot for SharePoint

PowerPivot applications are created using Excel 2010 with the PowerPivot add-in, as

covered in the preceding chapter. After a PowerPivot application is created, it should be

uploaded to the SharePoint PowerPivot Gallery to be shared across the organization.

PowerPivot Gallery

When documents are loaded into a typical SharePoint library, they are presented in a

simple list. This works fine, but wouldn’t it be great if the documents were able to be

previewed in a library visually, like how photos are presented with thumbnail previews?

Well, that is precisely what the PowerPivot Gallery provides for PowerPivot applications.

Although this might seem like simple eye-candy, this has significant benefits for usability

and user adoption. It’s much easier for users to remember an application visually than

simply by the filename. Also, the ability to visually review the applications in the gallery

makes them more engaging, which can lead to increased user adoption.

SILVERLIGHT AND THE GALLERY

The PowerPivot Gallery uses Silverlight for the various presentations. As a result,

Silverlight needs to be installed to use the gallery. In addition, Silverlight is a 32-bitonly

application, so the 32-bit version of Internet Explorer is required, as well.

PowerPivot Gallery

The PowerPivot Gallery offers three different presentations: Gallery view, Carousel view,

and Theater view. These are selectable from the Library ribbon, as shown in. Gallery view: The Gallery view is the most basic of the views and lists a preview of

the file along with a smaller thumbnail view of each sheet in the file. As you hover

over each thumbnail, the primary view changes to show that sheet. Clicking the

primary view or the thumbnail launches the PowerPivot viewer and loads the file

full screen for interactivity.

When viewing the workbooks in the gallery, you will notice two faint icons in the upperright

corner for each workbook. The icon on the right, which looks like a calendar, is the

Manage Data Refresh function. Clicking this icon launches a page where a data refresh

schedule can be created. Currently, the highest frequency that can be set for data refresh is

daily. This can be useful for applications where the data is loaded nightly, because the

PowerPivot applications could then be set to refresh each morning.

When a PowerPivot workbook gets refreshed, PowerPivot needs to take a new “snapshot”

of each sheet for visual presentation in the gallery. The PowerPivotGallery component

initiates this snapshot process automatically, but if a user attempts to look at the gallery

before the snapshot has been updated, PowerPivot places an hourglass icon on the

outdated views as a visual indicator that these views are not yet current. 

Publishing PowerPivot workbooks to SharePoint can be done by either the Upload function

from within the SharePoint PowerPivot Gallery, the Save As option from within

Excel, or the Save and Send option for publishing from within Excel. Although all of these

methods produce the same result (that is, getting the file into SharePoint), the mechanisms

and options used are different. The Upload option is initiated from within the SharePoint PowerPivot Gallery. The Upload function is a synchronous transfer and is the fastest option but also results.The Save As option is initiated from within Excel by selecting Save As and browsing

to the PowerPivot Gallery in SharePoint as the destination. This option uses an asynchronous

transfer, which enables the bandwidth demand to be more balanced, but

takes more time than the Upload option.

. The Save and Send option is similar to the Save As option but enables the user to specify

publishing options, including selecting which sheets of the workbook to expose in

the browser and setting parameter values that may be used in the worksheet.

Although there is no way to force a particular PowerPivot publishing method, each organization

should consider whether there is a preferred method and recommend this to

their users.

Controlling Data Exposure and Spreadmarts

Although the uploading of PowerPivot workbooks is beneficial to an organization, allowing

these workbooks to be downloaded can be very detrimental. Because these PowerPivot

applications contain significant data and may also contain business rules, allowing them

to be downloaded can be risky. In addition, downloading these applications usually results

in multiple copies of various versions of the application, which is a very unmanageable

FIGURE 10.15 Publish initiated from Excel.

182 CHAPTER 10 PowerPivot for SharePoint

scenario. This scenario has been thought of as a datamart of spreadsheets, or a spreadmart.

This can be controlled by preventing users from downloading these applications.

To prevent users from downloading the workbooks into Excel, Excel Services provides a

SharePoint group named “Viewers.” Users placed into the “Viewers” group can still fully

interact with the workbook, including changing the slicer values, but they are prevented

from downloading the Excel file.

Monitoring PowerPivot

One of the biggest advantages of a tool like PowerPivot is the ability to give end users the

freedom to build the analytic applications that they find useful for managing the business.

However, one of the biggest concerns for SharePoint administrators is being able to

manage the effect that these applications will have on the rest of the SharePoint environment

and to plan for future resources.

To meet this need, PowerPivot for SharePoint includes some very useful tools for monitoring

which files are being used and how they are performing. The SharePoint Usage and

Health Data Collection service is used to collect this information, and the PowerPivot

Management Dashboard is used to monitor this information.

Enabling Usage and Health Data Collection

The Usage and Health Data Collection service is new to SharePoint 2010. This service

collects and logs SharePoint health indicators and usage metrics for analysis and reporting

purposes. During the multiserver farm install process, there was a statement about

making sure the Usage and Health Data Collection service was enabled. To do this, follow

these steps:

1. Go to Central Administration and navigate to Monitoring, Configure Usage and

Health Data Collection.

2. Confirm that the Enable Usage Data Collection box is checked, and then check the

boxes for PowerPivot data collection, namely the following:

• PowerPivot Connections
• PowerPivot Load Data Usage
• PowerPivot Unload Data Usage
• PowerPivot Query Usage

3. Confirm that the Enable Health Data Collection box is checked.

Using the PowerPivot Management Dashboard

To launch the dashboard, go to Central Administration and navigate to General

Application Settings, PowerPivot Management Dashboard.

The dashboard is divided into the following sections:

. Infrastructure - Server Health: Provides graphical trend charts for the following

statistics:

• Query Response Times
• Average Instance CPU
• Average Instance Memory
• Activity
• Performance

. Actions: Links to PowerPivot-related settings within SharePoint.

. Workbook Activity: This is a chart of the number of queries by the number of

users over time, but it is more than just a static chart. It enables you to “play” the

chart and watch how the usage occurs over the time span presented. This is useful

for tracking peak activity and determining when additional resources might be

necessary.

. Data Refresh: The automatic data refresh capability of PowerPivot is one of its most

useful features. As such, it’s extremely important to know whether those refreshes

succeed or fail, and if they fail, why. The Data Refresh section contains a list of

FIGURE 10.16 PowerPivot management dashboard.

184 CHAPTER 10 PowerPivot for SharePoint

recent activity, which can be drilled down to see all history for a particular data file.

The Data Refresh section also contains a list of recent failures for troubleshooting.

. Reports: The Server Health and Workbook Activity sections are actually displays

from a PowerPivot application, which is connected to an Analysis Services cube

named ITOps Sandbox. The Excel files that provide that information are listed in the

Reports section as Server Health.xlsx and Workbook Activity.xlsx. Custom reports

can also be created against this cube.

Summary

While PowerPivot enables end users to create powerful analytic applications, the

PowerPivot features of SharePoint enable IT to keep the applications accessible, current,

and organized.

In this chapter, we explored how PowerPivot for SharePoint is installed, how the

PowerPivot Gallery can be used to access and refresh these applications, and how the

PowerPivot Management Dashboard can be used to keep things running smoothly.

Best Practices

The following are best practices while working with PowerPivot for SharePoint:

. Don’t skimp on memory: Because PowerPivot for SharePoint loads the workbooks

into the server’s memory, the application server hosting PowerPivot services should

have plenty of memory. The recommended amount of memory for an enterprise

PowerPivot server is 32GB to 64GB. This will vary from organization to organization,

but the determining factor is the number and size of concurrent workbooks that will

be used.

. Review and set file size limits: Because PowerPivot applications include the actual

data, the files can be larger than what is commonly stored in SharePoint. By default,

SharePoint limits the size of uploaded files to 50MB and Excel Services limits the size

of a workbook to 10MB. These limits should be reviewed and adjusted as necessary

to meet the requirements for the environment.

. In a multiserver SharePoint farm, turn off Excel Calculation Services on the

WFEs: If the instance of ECS on the WFE tries to connect to the PowerPivot application

service, it will fail because the Analysis Services OLE DB driver is not installed

on the WFE.

. Turn off the Excel Web Access warning: Because PowerPivot applications require

Excel Services to query external data, users will frequently be prompted with the

SharePoint Excel Web Access external data warning. In the Excel Services Application

settings, the Warn on Refresh check box should be cleared.