Automatically Refresh PowerPivot Data in SharePoint
If you have published your PowerPivot workbook to SharePoint, you can schedule automatic refresh of the data in your workbook. This topic describes how to perform automatic refresh of data in a PowerPivot workbook on a SharePoint site.
Note
If you are working with a workbook that is saved locally, not managed on a SharePoint site, you can refresh the data yourself in PowerPivot for Excel. For more information about how to manually refresh the data from within the workbook, see Recalculate Formulas.
Refreshing PowerPivot Data from SharePoint
The following instructions assume that PowerPivot for SharePoint server software is installed and enabled for your SharePoint site. The instructions also assume that the PowerPivot workbook has been published to a SharePoint library, such as Shared Documents or PowerPivot Gallery. For more information about how to do this, see Save to SharePoint.
Additionally, the following prerequisites must be met:
You must have Contribute level of permission or greater on the workbook to create a data refresh schedule.
The account that performs the refresh must be a Windows domain user account.
External data sources that are accessed during data refresh must be available and the credentials you specify in the schedule must have permission to access those data sources.
The workbook must be checked in at the time the refresh operation is finished.
The SharePoint server that you publish to may or may not have the same data providers you used to import the data. SharePoint 2010 is a 64-bit environment. If you used 32-bit Excel to create PowerPivot workbooks, you must work with your server administrator to ensure that the 32-bit version of any data providers used by your workbook are also installed on the server.
Note
The server does not lock the workbook while the data refresh is in progress. If the workbook is checked out at the start of data refresh, the server will get the last known version. If the file is still checked out when the server tries to save the changes, the updated data will be thrown out. If the file is checked in, and it is significantly different from the copy retrieved by the server, the refreshed data will be discarded.
To schedule automatic data refresh
On SharePoint, in the SharePoint library that contains the workbook, select the workbook and then click the down arrow to display a list of commands.
Click Manage PowerPivot Data Refresh. If a data refresh schedule is already defined, you will see the View Data Refresh history page instead. You can click Configure data refresh to open the schedule definition page.
Note
If the command is not available, PowerPivot for SharePoint is either not installed or note enabled for your SharePoint site. For more information about how to activate the feature, see Activate PowerPivot Integration for Site Collections on the Microsoft Web site.
In the schedule definition page, in the Data Refresh section, click the Enable checkbox.
In Schedule Details, specify the type of schedule and how often you want the data to be refreshed.
In Earliest Start Time, specify one of the following:
After business hours specifies an off-hours processing period when database servers are more likely to have current data that was generated throughout the business day. The default value for this processing period is 8:00 p.m., but your SharePoint administrator can change this to a different time.
A specific time, in hours and minutes, that you would like the data refresh request to be added to a process queue. You specify minutes in 15-minute intervals.
In E-mail Notification, type an e-mail address of anyone who should be notified of data refresh failures.
In Credentials, enter account information that PowerPivot for SharePoint will use to refresh. The account will be used to open the workbook for data refresh processing so it must have Contribute permission on the workbook. This account must be defined in the system before you can use it. You can run data refresh using any of the following options:
Click Use the data refresh account configured by the administrator to use a predefined account. This is the unattended data refresh account. If this option is enabled, the account is configured on your server and available for you to use. Note that when you use this option, data refresh history will list this account as the user who ran the data refresh operation.
Click Connect using the following Windows user credentials to specify your own Windows user credentials. Enter the account information in DOMAIN\user format. If this option is not available, it means that a SharePoint administrator disabled it for your site.
Click Connect using the credentials saved in Secure Store Services (SSS) to log on to the data source to run data refresh under a specific Windows user account that is defined in Secure Store Service. To use this option, your SharePoint administrator must have created an SSS ID that stores the account information, and then provided you with the ID that you should use.
In Data Sources, select the All data sources checkbox if you want data refresh to re-query all of the original data sources. All PowerPivot data sources are automatically included in the refresh, even if the list of data sources changes over time.
Clear the All data sources checkbox if you want to manually select which data sources to include. Optionally, you can define individual schedules for specific data sources.
To schedule data refresh for individual data sources, select the data source and click the down arrow to expand your scheduling options, including boxes for entering different credentials for specific data sources. Your workbook already contains all the data source connection information that is required for data refresh. You only need to change it if you want to use different credentials for ongoing data refresh operations.
Note
Some restrictions apply to the data sources that can be automatically refreshed. The data sources must be at a network location that is available to PowerPivot for SharePoint. Be sure to select only those data sources that meet the criteria. For more information, see Different Ways to Update Data in PowerPivot.
Click OK to save your schedule.
See Also