Manage a Data Refresh Schedule

Important: Click PowerPivot Data Refresh to view the latest information about this subject on the Microsoft web site.

You can schedule data refresh to get automatic updates to PowerPivot data inside an Excel workbook that you published to a SharePoint site.

What do you want to do?

Learn about requirements for data refresh

Data Refresh Overview

Configure the schedule

Verify data refresh

Learn about requirements for data refresh

  1. You must have Contribute level of permission or greater on the workbook to create a data refresh schedule.

  2. 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.

  3. The workbook must be checked in at the time the refresh operation is finished. 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.

Top of Page

Data Refresh Overview

A PowerPivot data source in an Excel workbook can originate from multiple external data sources. These data sources might be local files, other Excel workbooks, or external databases or data files that you access from remote servers or network file shares. For PowerPivot data sources that contain imported data from connected or external data sources, you can configure data refresh to schedule an automatic import of updated data from those original sources. Only one data refresh schedule can be created for each PowerPivot workbook. Typically, the person who authored the workbook defines the schedule.

As the workbook owner, you define the schedule, specify the credentials used to connect to external data sources, and choose which data sources to include in the refresh operation. Optionally, you can specify individual schedules and credentials for each data source that contributes data to the PowerPivot data source. Precision scheduling for individual data sources enables you to match the refresh schedule to fluctuations in the external data sources. For example, if an external data source contains transactional data that is generated throughout the day, you can create an individual data refresh schedule for that data source to get its updated information nightly.

Top of Page

Configure the schedule

  1. In the library that contains the workbook, select the workbook and then click the down arrow to display a list of commands.

  2. Click Manage 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.

  3. In the schedule definition page, in the section Enable Scheduled Data Refresh, select the Enabled checkbox.

  4. In Credentials, enter a name and password for a SharePoint user account that is also a valid Windows user account. Enter the account information in DOMAIN\user format.

    Alternatively, you can specify the unattended data refresh account. This account must be defined in the system before you can use it. The account you specify will be used to open the workbook for data refresh processing. The account must have Contribute permissions on the workbook. It must be a Windows domain user account.

  5. If the Windows user account is also a valid e-mail account, you can select the checkbox to get email notifications in the event of a processing error.

  6. In Schedule Details, specify the type of schedule and provide additional detail to create a more granular schedule.

  7. 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 exact hour and minutes at which time you would like the data refresh request to be added to a process queue. You specify minutes in 15-minute intervals.

      Earliest start time defines when a request is added to the process queue. Actual processing occurs when the server has adequate resources to begin data processing. Actual processing time will be recorded in data refresh history after processing is complete.

  8. In Data Sources, select the All data sources checkbox if you want data refresh to re-query all of the original data sources. If you select this option, any external data source that provides data to the PowerPivot data source is automatically included in the refresh, even if the list of data sources changes over time as you add or remove data sources that are used in the PowerPivot workspace.

    Clear the All data sources checkbox if you want to manually select which data sources to include. If you later modify the workbook by adding a new data source, be sure to update the data source list in the schedule. Otherwise, newer data sources will not be included in the refresh operation.

    The list of data sources that you can choose from is retrieved from the PowerPivot data source when you open the Manage Data Refresh page for the workbook.

    Be sure to select only those data sources that meet the following criteria:

    • The data source must be available at the time that data refresh occurs and available at the stated location. If the original data source is on a local disk drive of the user who authored the workbook, you must either exclude that data source from the data refresh operation, or find a way to publish that data source to a location that is accessible through a network connection. If you move a data source to a network location, be sure to open the workbook in the PowerPivot client application and repeat the data retrieval steps. This is necessary to re-establish the connection information that is stored in the PowerPivot data source.

    • The data source must be accessed using the credentials that are embedded in the PowerPivot data source or that are specified in the schedule. Embedded credentials are created in the PowerPivot data source when you connect to the external data source from the PowerPivot client application. If you want to override these credentials, you can specify a different user account in the schedule as long as the server is able to store them.

    • Data refresh must succeed for all of the data sources that you specify. Otherwise, the refreshed data is discarded, leaving you with the last saved version of the workbook. Exclude any data sources that you are not sure about.

    • Data refresh must not invalidate other data in your workbook. When you refresh a subset of your data, it is important that you understand whether the workbook is still valid once newer data is aggregated with static data that is not from the same time period. As a workbook author, it is up to you to know your data dependencies and ensure that data refresh is appropriate for the workbook itself.

      An external data source is accessed through an embedded connection string, URL, or UNC path that you specified when you imported the original data into the workbook using the PowerPivot client application. Original connection information that is stored in the PowerPivot data source is reused for subsequent data refresh operations. There is no separate connection information that is created and managed for data refresh purposes; only existing connection information is used.

  9. Optionally, you can define individual schedules for specific data sources. This is useful if you have original data sources that are themselves updated on a schedule. For example, if a PowerPivot data source uses data from a data mart that is refreshed every Monday at 02:00 hours, you can define an inline schedule for the data mart to retrieve its refreshed data every Monday at 04:00 hours.

  10. Click OK to save your schedule.

Top of Page

Verify data refresh

You can view the current and past record of data refresh operations in the Data Refresh History page for the workbook. This page only appears if data refresh has been scheduled for a workbook. If there is no data refresh schedule, the schedule definition page appears instead.

You must have Contribute permissions or greater to view data refresh history.

  1. On a SharePoint site, open the library that contains an Excel workbook using embedded PowerPivot data.

    There is no visual indicator that identifies which workbooks in a SharePoint library contain PowerPivot data. You must know in advance which workbooks contain refreshable PowerPivot data.

  2. Select the document, and then click the down arrow that appears to the right.

  3. Select Manage Data Refresh.

The history page appears, showing a complete record for all refresh activity for PowerPivot data in the current Excel workbook, including the status of the most recent data refresh operation.

In some cases, you might see actual processing times that differ from the time you specified. This will occur if there is a heavy processing load on the server. Under heavy load, the PowerPivot service instance will wait until enough system resources are free before it begins a data refresh.

The workbook must be checked in when the refresh operation is finished. The workbook will be saved with the refreshed data at that time. If the file is checked out, data refresh is skipped until the next scheduled time.

If you see a status message that is unexpected (for example, a refresh operation failed or was cancelled), you can investigate the problem by checking permissions and server availability.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!