Create a PowerPivot Workbook for SharePoint
If you plan to publish a PowerPivot workbook to SharePoint, be sure to consider how it might be used in a collaborative environment. The workbook might become the basis for Reporting Services reports created by your colleagues, repurposed in other PowerPivot workbooks, or linked to from different sites, possibly in different farms. Knowing in advance that your published PowerPivot workbook might be used in new and different ways can help you make decisions about how you add, modify and present the data that you create in a PowerPivot workbook.
This topic contains requirements, design tips, and data sharing and security issues to consider when designing workbooks that are intended for publication to SharePoint.
Preparation
Create Refreshable Data Connections
Security Precautions for Publishing PowerPivot Data
Preparation
If you are familiar with Excel, you already have the foundational skills for creating and using PowerPivot data. The best way to build on those skills is to learn about the expression language and the Excel data visualization features that allow you to present PowerPivot data in the most effective way.
Visualizing data Although the PowerPivot workspace is tabular and has no data presentation features of its own, you can create sophisticated presentations reports by using data visualization objects in Excel such as PivotTables, PivotCharts, filters, and Slicers. If you are not familiar with the use of PivotTables or PivotCharts, the new capabilities in PowerPivot will give you a reason to learn. For more information about working with PivotTables, see Overview of Reports, Charts, and PivotTables.
Adding and updating formulas In PowerPivot, formulas are more than a way to calculate numbers; formulas created by using the Data Analysis Expressions (DAX) language let you build relational queries, look up related values, and develop custom calculations over time. DAX is an extension of the formula language in Excel, but is far more powerful. For more information about DAX, including samples, see Data Analysis Expressions (DAX) Language.
Sharing your work Saving a PowerPivot workbook to SharePoint 2010 gives you a way to share your work with others in your organization. When you publish your workbook to SharePoint, you can take advantage of the presentation features in PowerPivot Gallery. For more information about PowerPivot Gallery, see Use PowerPivot Workbooks on SharePoint.
Create Refreshable Data Connections
Data that you load into a workbook is static by default, but once published, you can arrange for automatic refresh of the data. Data refresh that runs on SharePoint uses the connection information in your workbook. While you can replace user name and passwords in a data refresh schedule, you cannot change the connection information in a workbook that is published to a server. If you want to change a data source connection in a workbook that you saved to SharePoint, you must download it to your workstation to the make that change in PowerPivot for Excel. For this reason, before you save a file to SharePoint, check the data source connection information to verify that the connection will work once data refresh is initiated from the server. Imported data that is kept on file shares or corporate servers that are generally available to a large group of users are most likely to have connection information that is specified in a way that will resolve equally well on SharePoint. In contrast, data that you import from a local file on your computer might not work if the server tries to use the same connection string to refresh data.
The credentials and connection strings that are used in data refresh are stored inside the PowerPivot workbook, one connection for each data source. This includes the file location or file name that you specify during data import, which is stored verbatim in the workbook and can be viewed by other users of the workbook. The following is a summary of how you can change different parts of the connection string:
Data source. Connection strings can be edited after you create the workbook, but the change must be made in PowerPivot for Excel, not on SharePoint. This change requires that the data in the workbook be refreshed and all formulas recalculated. Depending on the complexity of your workbook, this might take a long time. For more information, see Different Ways to Update Data in PowerPivot.
User name and password. You can override the credentials that are stored inside the workbook by specifying different ones when you set up data refresh on SharePoint, but doing so requires extra steps by your SharePoint administrator.
Best practices for setting up connection information
Although PowerPivot data is static by default after you import it into the PowerPivot window, you can enable regular updates from connected data sources by ensuring that the connections you define are reusable after the workbook is published to SharePoint.
For best results, follow these recommendations:
Use connected data sources wherever possible. Do not use data sources that are on private computers that might be shut off, or on servers that have restricted access, unless all the potential users also have access.
Specify data or file locations in a format that is accessible over a network connection. An HTTP URL or a shared folder (such as \\AWSRV-01\public\MarketData) are examples of locations that can be accessed from either a client workstation or a SharePoint server.
Securing PowerPivot Workbooks on SharePoint
Before you publish a PowerPivot workbook to SharePoint, review the data so that you fully understand what you are publishing. Whenever you publish a workbook, the columns and tables in the workbook will be available to all users, even if you create a different presentation for reports.
In other words, all data that you add to the PowerPivot window is accessible even if it is not visible. For example, filters that you use to exclude information in a PowerPivot table or PivotTable do not apply when you use that same PowerPivot data to create a Reporting Services report or to import data into a second workbook.
Remember that SharePoint permissions are set on documents, and not on information within a document. If you have a workbook that contains sensitive data, be sure that you publish the workbook to a library that is only accessible to authorized users.
For more information, see Secure a PowerPivot Workbook on SharePoint.