Create an Excel Services data connection by using PerformancePoint Services

A data source connection can be made to data within Excel files that have been published to Excel Services on a SharePoint site. Published parameter values can be modified from within PerformancePoint Services. This is useful when a parameter is an input to the value of a published cell. This could be used in a KPI, for example, and enable the dynamic change of that cell value from PerformancePoint Services.

Note: PerformancePoint Services accesses external data sources by using a delegated Windows identity. Consequently, external data sources must reside within the same domain as the SharePoint Server 2013 farm. If external data sources do not reside within the same domain, authentication to the external data sources fails.

Create an Excel Services data source connection

In PerformancePoint Services the security settings for data sources are stored in each data source. For Excel Services workbooks, you have the choice of three authentication options: using the Unattended Service Account, using credentials stored in a Secure Store target application, or using Per-User Identity. Per-User Identity requires the Kerberos protocol.

To Create an Excel Services data source connection

1.       Start Dashboard Designer.

2.       Click the Create tab, and then click Data Source.

3.       In the Category pane of the Select a Data Source Template dialog box, click Tabular List and then click Excel Services. Click OK.

4.       In the left navigation pane (workspace browser), type a name for your data source.

5.       In the center pane, click the Editor tab. In the Data Source Settings section, select the method by which to authenticate to the data source.

6.       In the Refresh Interval box, enter the duration in minutes for the data to refresh.

7.       In the Connection Settings section, type the URL of the SharePoint site.

8.       From the Document Library list, select the SharePoint document library where the workbook is located.

9.       From the Excel Workbook list, select the workbook that you want.

10.   In the Item Name list, select a Named Range or table. Only one Named Range or Table is supported per Excel Services data source.

11.   From the Data Source Settings box, select one of the following Authentication methods:

1.       Unattended Server Account   If you select this method, all users connect by using the Unattended Service Account that was defined for the PerformancePoint shared service when they access the data source.

2.       Use a stored account   Enter the Application ID of a secure store target application. If you select this method, all users connect by using the credentials from the specified Secure Store target application when they access the data source.

3.       Per-user identity   If you select this option, the current user's identity is used to access the data source. Per-User Identity requires the Kerberos protocol.

12.   Click Test Data Source to confirm your connection is configured correctly.