Save to SharePoint (Power Pivot)

You can save Excel workbooks that have Power Pivot data models to a SharePoint site that has been extended to support data model access. After the workbook is published, SharePoint permissions and document properties determine how others can view and use your workbook.

Before You Start

Publishing workbooks to SharePoint requires you to have sufficient permissions, software, and knowledge of how to prepare your workbook for publication.

Server Software

To view the workbooks in a browser, the SharePoint server you are using must have Power Pivot for SharePoint and Excel Services. Both services are required for processing and rendering workbooks on SharePoint.

You can sometimes identify whether you have the correct software by reviewing the libraries and context menus on your site, but you might need to ask your SharePoint administrator whether the required server software is installed. If your site includes Power Pivot Gallery, Power Pivot for SharePoint is installed. Similarly, if you can open an Excel workbook in a browser window without having to download the file to your Excel desktop application, Excel Services is running on your SharePoint server.

If you plan to schedule automatic data refresh for your workbook, be sure to check with your server administrator to ensure the data providers you used to import data are also available on the server.

Permissions

When you save a document to SharePoint, SharePoint permissions determine who can view, edit, or delete your work. The following table describes which SharePoint permissions grant specific rights to the workbooks you publish:

Permission	Rights
View	Anyone with View permission can open the workbook by going to the URL address for the file. In addition, View permission will also allow someone to use your workbook as a read-only external data source for other Excel workbooks or reports.
Contribute	Contribute permission is more powerful because it conveys permission to add, edit, or delete documents in a SharePoint library.
Full control	Full control includes all of the permissions associated with Contribute, plus the ability to create or manage the list or library, and undo a check-out.

Access to any SharePoint site or library is granted through permissions a Site administrator or site owner specifies for groups or individual users.

Most people have either View or Contribute permissions. You can tell which permissions you have by looking at the Site Actions menu on your SharePoint site. If the only item on the menu is View All Site Content, you have View permissions and you cannot publish files to this SharePoint site. If you see a longer list of items, or if you see Library Tools in the ribbon, you have Contribute permissions.

Prepare Your Workbook

Before you publish an Excel workbook that contains Power Pivot data, you should follow this checklist to prepare your document:

1. Read about how to create a workbook that uses connection information that continues to work after the file is published.

2. Understand the security considerations for publishing Power Pivot data in SharePoint.

3. You can improve the visual presentation of your workbook on SharePoint with these suggestions:

   • Give your Excel worksheets descriptive names. Worksheet names become page labels when the document is published to Power Pivot Gallery.

   • Turn off unused visual elements. In the Excel window, in the Show area of the View ribbon, turn off Gridlines, Formula Bar, and Headings.

   • Hide Excel worksheets that are empty or that are not relevant to the report. In the Excel window, right-click a sheet tab and select Hide Sheet.

   • Select cell A1 prior to saving and publishing the workbook. When a published file is opened in a browser window, focus is placed on the last active cell in the workbook. By placing the cursor in cell A1, you ensure that the top left corner of the worksheet is positioned at the top left corner of the browser window.

Check Authentication and Refresh Settings

For the best results, use the Excel default settings for authentication and data refresh.

Verify Windows authentication

By default, Excel specifies Windows Authentication so the identity of the person viewing the workbook is used when accessing data model data. Keeping the default setting allows Power Pivot server software to capture accurate information about who is using Power Pivot workbooks, allowing you to better collaborate with the colleagues who are using your data. If you specify None or a Secure Store Service (SSS) ID, workbook usage data will be recorded for the Excel Services unattended user account (or for the SSS ID) rather than an actual person in your organization.

1. In Excel, on the Data ribbon, click Existing Connections.

2. In the Existing Connections dialog box, click a connection in the workbook > Open

3. Click Properties > Definition > Authentication Settings.

4. Verify Use the authenticated user's account is selected.

Specify refresh control settings

Refresh control settings can affect how you preview a Power Pivot workbook in the Power Pivot Gallery library. For example, if your workbook uses the refresh on open property, and Excel Services is configured to issue a warning for data refresh operations, you will see a thumbnail image of Excel's data refresh warning instead of a document preview image.

1. In Excel, on the Data ribbon, click Connections > Properties.

2. In Usage > Refresh Control, select or clear the Refresh data when opening the file checkbox based on the following recommendations; that vary depending on how the data model data is accessed:

Data access	Refresh control recommendation
Embedded in the workbook (most common scenario)	Clear the checkbox Refresh data when opening the file. Because all of the data is embedded, refreshing it each time you open the workbook is unnecessary.
Stored as an external data source (for example, if you setup an external data connection to a workbook rather than import its data)	Select the Refresh data checkbox when opening the file. Because the data is in a different location, you want refresh to occur to pick up any recent changes to the data. To allow preview image generation, ask your SharePoint administrator to customize the Warn on refresh setting in Excel Services. For more information, see Create a trusted location for Power Pivot sites on TechNet.
Server-side data refresh operations (applies to workbooks that you save to SharePoint and schedule for Power Pivot data refresh).	Select the checkbox Refresh data when opening the file. Because data refresh is scheduled for this workbook, Power Pivot will automatically enable the Refresh data when opening file property on this workbook to ensure that the PivotTable cache is replaced with newer data. If you clear the checkbox, it will be selected automatically when you save the workbook back to SharePoint.

Choose a Location for Your File

Documents that you share on a site are typically kept in a document library. Shared Documents is the most common document library, but a SharePoint site that has Power Pivot for SharePoint will often have Power Pivot Gallery which offers preview, custom library views, and easy access to data refresh configuration schedules for the workbook.

You must choose a SharePoint server that has Power Pivot for SharePoint. If you publish to a server that does not have the Power Pivot server software, the data will not load when you open the document from a library.

You can choose Shared Documents, Power Pivot Gallery, or any other document library to store your workbook. To use these locations, you must know the Web URL address of the library. On a default SharePoint site, Web URL addresses for these libraries are as follows:

• http://<server name>/Shared%20Documents

• http://<server name>/PowerPivot%20Gallery

• To find the Web URL address of any library, right-click a document in the library and choose Properties. The Address (URL) shows the SharePoint path to the document. Be sure to use your mouse to select the entire URL. Sometimes long URLs might be partially hidden from view.

• Most likely, you will need to edit the URL before you can use it. Specifically, if the URL includes viewer and source properties, you should remove those properties to get a usable location that consists of server, site, and library names. For example, given the following URL, you can derive the actual URL path by keeping the portion in bold and deleting the rest: http://Contoso/Sales/_layouts/xlviewer.aspx?id=/PowerPivot%20Gallery/ContosoSales.xlsx&Source=http%3A%2F%2FContoso%2FPowerPivot%2520Gallery%2FForms%2FAllItems%2Easpx&DefaultItemOpen=1. Based on the URL property, the actual path you should use for publishing purposes would be 'http://Contoso/Sales/PowerPivot%20Gallery/ContosoSales.xlsx' if you are publishing the ContosoSales.xlsx file, or just 'http://Contoso/Sales/PowerPivot%20Gallery' if you want just the site and library.

• When choosing a location, pick a library that is available to the people with whom you want to share the workbook. You might need to request permissions on the library or the document to ensure workbook availability.

Save Your File to SharePoint

1. In Excel, click File > Save As > SharePoint or Sites > Browse, then select a location.

2. In the Save As dialog box, in File name, type a name for the file if you are using an existing location. Or, if you chose Browse for a location, enter all or part of a SharePoint site URL. If you enter a portion of the URL address, such as http://<server name>, click Save to open a connection to the server you specified and then browse the site for the library that you want to use. For examples of a SharePoint URL, see Choose a Location for Your File in this topic.

3. Click Save to publish the workbook to the library.

In a browser window, verify that the document appears in the library. Newly published documents will appear in the list. Library settings determine where the document appears (for example, sorted in ascending order by date, or alphabetically by name). You might need to refresh the browser window to view the most recent additions.

Upload a Power Pivot Workbook from SharePoint

Another approach for adding a Power Pivot workbook is to upload it from a SharePoint site. Use this approach if you want to start from SharePoint.

1. In a SharePoint site, open a document library.

2. In Library Tools, click Documents > Upload Document, > Upload Document .

3. Browse to the location of the saved .xlsx file, select it, then click OK.

The file is uploaded to the SharePoint site and appears in the folder. You may need to click the refresh button in your browser before you can see it. It can now be opened in SharePoint. You can tell people where the file is by giving them the URL address for the file.