Create a SQL Server table data connection (PerformancePoint Services)


Applies to: SharePoint Server 2010 Enterprise

Topic Last Modified: 2016-12-08

A SQL Server table or view can be used as a data source by PerformancePoint Services. Only tables and views can be used as SQL Server data sources. SQL database data cannot be modified from the Dashboard Designer.

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 2010 farm. If external data sources do not reside within the same domain, authentication to the external data sources will fail. For more information, see Planning considerations for services that access external data sources in Services architecture planning (SharePoint Server 2010).

In this article:

In PerformancePoint Services the security settings for data sources are stored in each data source. For SQL Server tables, you have the choice of authenticating to the server by using the Unattended Service Account or by using Per-User Identity. Per-User Identity requires Kerberos protocol.

It is recommended that you select your authentication method before you specify your server connection.
To Create a SQL Server table data source connection
  1. Launch 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 SQL Server table. Click OK.

  4. In the left navigation pane (workspace browser), type in 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 Refresh Interval, specify the duration (in minutes) before data is refreshed from the source.

  7. In the Connection Settings section, select the method by which to connect to the data source.

    To use a standard connection:

    1. Select Use standard connection

    2. Type the full path for the server to which you want to connect. This populates the options in the database list.

    3. In the Database box, select a database name.

    To use a specific connection string:

    1. Select Use the following connection.

    2. Type a connection string with the full path of the server and database to which you want to connect.

    3. In the Table drop-down list, select the specific table that you want to use.

  8. Click Test Data Source to confirm that the connection is configured correctly.

  9. In the Workspace Browser, right-click the data source, and then click Save.

    If the SQL Server table that you select for the data connection contains a datetime column, you might receive an error message that indicates the data source has not been saved. This can occur even though the data source has indeed been saved. This is a known issue.

To view a demonstration of how to create a SQL Server table data connection in PerformancePoint Services, watch the following video.

Video: Create a SQL Server table data connection in PerformancePoint Services

Play button