PowerPivot for Excel and SharePoint
- 4/14/2011
Scheduling Data Refreshes
PowerPivot for SharePoint provides a data-refresh feature that can automatically retrieve updated data from the external data sources you used to build the workbook originally. Any PowerPivot workbook owner can schedule data refresh for workbooks saved to the PowerPivot Gallery or to any other PowerPivot-enabled SharePoint document library. The following illustration shows the drop-down menu that opens the data-refresh schedule page.
)
PowerPivot Gallery offers another way to get to the data-refresh feature: To schedule a data refresh through the PowerPivot Gallery, SharePoint users who have Contributor permission can click the Calendar icon shown for each workbook in a PowerPivot Gallery.
The following illustration shows the icon that opens the data-refresh schedule definition page.
)
The following illustration shows the initial view of the schedule definition page. To start setting up a data-refresh schedule, click Enable. This makes the page active so that you can fill in the values you want to use.
)
The Manage Data Refresh page has six sections, as described in the following table.
|
Section |
General description |
|
Data Refresh |
Enable or disable a data-refresh schedule. |
|
Schedule Details |
Define the frequency and timing details of a data refresh. |
|
Earliest Start Time |
Specify the earliest start time for a data refresh. |
|
E-mail Notifications |
Specify email addresses of the users to be notified in the event of data-refresh failures. |
|
Credentials |
Provide the required credentials for refreshing data on your behalf. |
|
Data Sources |
Select which data sources should be automatically refreshed. You can also use this section to create custom schedules that vary for each data source or to specify credentials for connecting to the data source. |
Data Refresh
To enable or disable a data-refresh schedule, select or clear the Enable check box on the Manage Data Refresh page. When Enable is selected, you can edit all parts of the data-refresh schedule. When Enable is not selected, the page is read-only and you’re essentially freezing the data, meaning that after you click OK, subsequent data-refresh operations for that workbook cannot occur.
Schedule Details
In the Schedule Details section, you can specify the frequency and timing details of the data refresh by choosing one of the following options:
Daily
Weekly
Monthly
Once
Using the Daily option, you can schedule a data refresh to occur every n day(s), every weekday, or on specific days of the week.
If you select the Also Refresh As Soon As Possible check box, data gets refreshed as soon as the server can process it. This refresh occurs in addition to the periodic data-refresh schedule and is available for periodic schedules only (that is, daily, weekly, and monthly schedules). Select this check box when you want to verify that the data refresh runs properly. For example, you might not know whether data credentials are configured correctly. This option provides a way to test the data refresh before its scheduled execution time. In short, checking the Also Refresh As Soon As Possible option refreshes the workbook as soon as possible once; subsequently, the workbook refreshes following your periodic schedule specification.
)
The Weekly option is for scheduling data refresh on a weekly basis, such as every n week(s) or on specific days of the week.
)
You can use the Monthly option data-refresh schedule to run either on a specific day of the month or on the first, second, third, or last specific day of the week every n month(s).
)
The Once option schedules a one-time data-refresh operation that runs as soon as the server can process the request. After the data refresh completes, the system disables this schedule. Notice that the Also Refresh As Soon As Possible check box is not available for this option.
Earliest Start Time
In the Earliest Start Time section, you specify details about when you prefer data refresh to occur. You can enter a specific time before which data refresh should not start, or you can choose to refresh data after business hours. This page does not determine the time at which the data refresh actually starts; instead, the schedule is queued and processed based on available resources. For example, if the server is busy with on-demand queries (which take precedence over data refresh jobs), the server waits to refresh your data until those queries have been processed. You can also choose to run a data-refresh operation after business hours. The administrator of the PowerPivot Service Application for your organization determines the definition of “business hours.”
E-mail Notifications
In this section of the page, you can specify email addresses for individuals or groups who should be notified when a data refresh fails. You can receive notifications of successful data-refresh operations through the regular SharePoint alerting system for email notification. (The basis of the alert would be a new file added to the target document library.)
Credentials
PowerPivot for SharePoint uses the SharePoint Secure Store Service to store any credentials used in data refresh. In the Credentials section of the schedule page, the schedule owner can specify the Windows credentials that are used to refresh data on his or her behalf. Any data source that uses trusted or integrated security is refreshed using these credentials. For the data refresh to succeed, the selected credentials should have access to the data sources for this workbook. You can choose from one of the following options:
Use an account preconfigured by the administrator (the service application’s unattended data-refresh account).
Use a specific Windows user name and password.
Use a predefined Secure Store Service target application ID that stores the Windows credentials you want to use.
Both the PowerPivot unattended data-refresh account and the predefined Secure Store Service target application ID must be set up by a SharePoint administrator in Central Administration. Because these credentials are shared among all users, this option is typically used where additional credentials are required for data access. A good example is when all the data sources use SQL Server authentication (that is, the actual user names and passwords are on each data source). In this case, the unattended execution account can be a low permission service account. Due to the way data refresh uses Windows accounts, it is normally not a good idea to have the unattended execution account be someone’s primary user account, because anyone can impersonate that user if he or she accesses data by using a trusted connection.
)
A schedule owner can also choose to type the Windows user credentials to be used on the data refresh. These credentials are securely stored in SharePoint’s Secure Store Service.
)
The third option lets a schedule owner specify credentials previously saved in a Secure Store Service Target Application. To use this option, you must enter the Target Application ID used to look up the credentials in the Secure Store Service. The Target Application ID specified must be a group entry, and both the interactive user and the PowerPivot System service account must have read access.
)
Data Sources
A workbook can have many data sources that have different characteristics. As shown in the following illustration, you can choose to create a data-refresh schedule using different options for each data source.
)
The schedule definition page provides options for choosing the data sources to be refreshed and when to refresh them. It also provides fields for specifying database credentials or other non-Windows credentials used on the database connection. You must select at least one data source to save the schedule. The data source’s credentials are not used for impersonation but are instead included on the connection string as UserName and Password. These credentials override those used on the connection string for the original data import.
As shown in the following illustration, different settings are available for each data source. You can specify a custom schedule data source, or you can use the general schedule specified for the workbook.
)