Using PowerPivot for SharePoint 2013
- 5/15/2013
- A brief history
- When do I use PowerPivot for SharePoint?
- Publishing to SharePoint
- Scheduling data refreshes
- Workbooks as a data source
Scheduling data refreshes
PowerPivot for SharePoint provides a data-refresh feature that can automatically retrieve updated information 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. To manage data refresh, perform the following procedure:
In your document library, select the workbook for which you want access refresh settings and then click the More Options button (the ellipsis icon).
A dialog box opens showing the workbook’s current authentication settings
In the lower-right corner of the dialog box that opens, click the More Options button (again, the ellipsis icon), as depicted in Figure 7-4.
Figure 7-4 Accessing the dialog box for a workbook.
On the menu that appears, click Manage PowerPivot Data Refresh to schedule a data refresh, as shown in Figure 7-5.
Figure 7-5 Click Manage PowerPivot Data Refresh on the menu.
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 (see Figure 7-6). Note that if the user does not have sufficient privileges on the workbook, the Calendar icon is not available on the page.
Figure 7-6 Accessing PowerPivot Data Refresh from a PowerPivot Gallery by using the Calendar icon.
Regardless of how you get to the Manage Data Refresh page, the details on it are the same. Figure 7-7 shows the initial view of the page.
Figure 7-7 Enabling Data Refresh in the Manage Data Refresh page.
Select the Enable check box to make the page active so that you can fill in the values that you want to use.
)
)
)
)
The Manage Data Refresh page is organized into six sections. Table 7-1 presents an overview of each section, and detailed descriptions are given in the subsections that follow.
Table 7-1 Manage Data Refresh page sections
|
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 the e-mail address of the users to be notified in the event of data refresh failures. |
|
Credentials |
Provide the credentials that will be used to refresh data on your behalf. |
|
Data Sources |
Select which data sources should be automatically refreshed. You also use this section to create custom schedules that vary for each data source, or specify different authentication methods for each 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. If this check box is selected, you can edit all parts of the data refresh schedule. If the check box is cleared, the page is read-only, and after you click OK, subsequent data refresh operations are disabled for that workbook.
Schedule Details
In the Schedule Details section, you can specify the frequency and timing details of the data refresh. There are four options from which to choose:
- Daily
- Weekly
- Monthly
- Once
With the Daily option (see Figure 7-8), you can schedule data refresh to occur every n day(s), every weekday, or on specific days of the week.
)
Figure 7-8 Daily schedule details options.
If you select the Also Refresh As Soon As Possible check box, data is refreshed as soon as the server can process it. This refresh occurs in addition to the periodic data refresh schedule. This option is available for periodic schedules only (that is, daily, weekly, and monthly schedules). Select this check box if you want to verify that the data refresh will run 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 one time, and then it is refreshed following your periodic schedule specification.
The Weekly option (see Figure 7-9) is for scheduling data refresh on a weekly basis such as every n week(s) or on specific days of the week.
)
Figure 7-9 The Weekly schedule details options.
The Monthly option (see Figure 7-10) schedules data refresh to run on a specific day of the month or on the first, second, third or last specific day of the week every n month(s).
)
Figure 7-10 The Monthly schedule details options.
The Once option (see Figure 7-11) is for scheduling a one-time data refresh operation that runs as soon as the server can process the request. After the data refresh is complete, the system disables this schedule. Notice that the Also Refresh As Soon As Possible check box is not available when this option is selected.
){kind=link}
Figure 7-11 The Once schedule details option.
Earliest Start Time
In the Earliest Start Time section, you specify details regarding when you prefer data refresh to occur (Figure 7-12). You can enter a specific time before which data refresh should not commence, 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.”
){kind=link}
Figure 7-12 The Earliest Start Time section on the Manage Data Refresh page.
E-mail Notifications
In this section of the Manage Data Refresh page, you can specify email addresses for individuals or groups who should be notified when a data refresh fails (Figure 7-13). 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.)
){kind=link}
Figure 7-13 The E-mail Notifications section on the Manage Data Refresh page.
Credentials
PowerPivot for SharePoint uses the SharePoint Secure Store Service (SSS) 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 behalf. Any data source that uses trusted or integrated security is refreshed by using these credentials. For the data refresh to succeed, the selected credentials must have access to the data sources for the workbook. You can choose from one of the following options (see also Figure 7-14):
- Use the data refresh account configured by the administrator (this is the service application’s unattended data refresh account)
- Use a specific Windows user name and password
- Use a predefined SSS target application ID that stores the Windows credentials that you want to use
)
Figure 7-14 The Credentials options on the Manage Data Refresh page—specifying an account configured by the administrator.
Both the PowerPivot data refresh account and the predefined SSS target application ID must be set up by a SharePoint administrator in Central Administration. Because these credentials are shared among all users, for instance, this option is typically used where additional credentials would be actually used for data access.
A schedule owner can also choose to type the Windows user credentials to be used on the data refresh, as illustrated in Figure 7-15. These credentials are securely stored in the SharePoint SSS.
)
Figure 7-15 The Credentials options on the Manage Data Refresh page—specifying Windows user credentials.
With the third option (see Figure 7-16), a schedule owner can specify credentials previously saved in a SSS Target Application ID. To use this option, you must enter the Target Application ID used to look up the credentials in the SSS. 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.
)
Figure 7-16 The Credentials options on the Manage Data Refresh page—specifying a Target Application ID.
Data Sources
A workbook can have many data sources, each with different characteristics. Figure 7-17 shows that you can choose to create a data refresh schedule by using different settings for each data source, or even disable the data refresh for it (by clearing its corresponding check box). You can have, for instance, one data source scheduled to refresh daily and a second source scheduled monthly.
)
Figure 7-17 The Data Sources section on the Manage Data Refresh page.
The schedule definition page provides options for choosing the data sources to be refreshed, when to refresh them, and which security options to use for each one. 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.
Figure 7-18 shows that different settings are available for each data source. You can specify a custom schedule data source or use the general schedule specified for the workbook.
)
Figure 7-18 The Data Sources section, showing schedule and credential details for a data source.