Create a data source

You can use PerformancePoint Dashboard Designer to access different kinds of data stores and to combine data from different resources such as SQL Server 2005 tables, Office Excel 2007 worksheets, and multidimensional data from SQL Server 2005 Analysis Services (SSAS).

KPIs and scorecards can use data from any configured data source. For example, you can create KPIs that use the "slice-and-dice" capabilities of SSAS or that use information in an Excel 2007 worksheet. You can also combine multidimensional and tabular data sources in a single scorecard.

In contrast, analytic charts and grids must use data from SSAS.

 Note   Advanced designers can use the interfaces in the PerformancePoint Monitoring Server SDK to create a custom data source. For more information, see the Monitoring SDK on MSDN.

To access data from a supported data source, you must create a data source element in Dashboard Designer and then configure the element. Dashboard Designer supports three different kinds of data source elements:

  • Multidimensional data sources that access SSAS data.
  • Tabular data sources that access SQL Server 2005 data tables, Excel 2007 worksheets, SharePoint lists, or worksheets in Excel Services.
  • Open Database Connectivity (ODBC) compliant data sources.

 Important   Make sure that you secure your data source connections. Otherwise, the confidentiality or integrity of the data that you access may be at risk. For more information about security best practices, see the PerformancePoint Server 2007 Deployment Guide.

What do you want to do?


Create a multidimensional data source element

  1. Click the Create tab, and then click Data Source.
  2. In the Category pane of the Select a Data Source Template dialog box, click Multidimensional and then click the Analysis Services icon. Click OK.
  3. In the Create New Data Source dialog box, type values for the data source properties.
    The required properties for a multidimensional data source are described in the following table:
Property Description

Name

Type the data source name.

Display Folder

(Optional) Click the Select Display Folder button to specify a new folder location, or click Browse to select an existing folder location.

Permission

Select the Grant Read permission to all authenticated users check box to give users permissions to read the data and settings in the data source. If you do not select the check box, you must configure user permissions when you configure general properties for the data source.
  1. Click Finish. Use the Connection Settings pane that appears to configure a connection to the data source. For more information about how to configure the connection, see configuring a connection for the data source.
  2. To complete the data source configuration and make it available to users in Dashboard Designer, take the following steps:
    1. Enable a server connection    by configuring a connection for the data source.
    2. Map Time dimension values     to the PerformancePoint Monitoring Server application database by configuring time and aggregation settings.
    3. Set general properties    by configuring general properties for a data source such as data source name and user permissions.

Top of Page Top of Page

Create a tabular data source element

The most common data source format is a table.

When you create and configure a tabular data source in Dashboard Designer, you make it possible for Monitoring Server to convert the table data to a multidimensional structure. For example, Dashboard Designer automatically converts the non-numeric columns in the source table into dimensions, and designates the numeric columns as fact data. In addition, it sets the aggregation method of the numeric data to sum.

To create a tabular data source, take the following steps:

  1. Click the Create tab, and then click Data Source.
  2. In the Select a Data Source Template dialog box, click Tabular List.
  3. Select SharePoint Lists, SQL Server Table, Excel Services, or Excel 2007 and then, click OK to continue to the next page.

Show  Show me how to use an Excel 2007 worksheet

 Important   Only worksheets that are created in Excel 2007 can be imported into Monitoring Server.

  1. To import data from an existing workbook, click the Editor tab, and then click Import and browse to the source file that you want to use.
  2. To create a new worksheet in Dashboard Designer, click the Editor or View tab, and then click Edit to open a blank worksheet.
  3. When you are ready to save the data, click Accept changes in the Edit data in Excel dialog box. The data is then imported into Monitoring Server for use as a data source.
  1. In the Name text box, type a name for the data source element.
  2. (Optional) Click the Select Display Folder button, and then enter a new folder location or click Browse to select a location. Select the Grant Read permission to all authenticated users check box to give dashboard users permissions to read the data from the data source. If you do not select the check box, you must configure user permissions when you configure general properties for a data source.
  3. Click Finish. Use the Connection Settings pane that appears to configure a connection to the data source. For more information about how to configure the connection, see configuring a connection for the data source.
  4. To complete the data source configuration and make it available to users in Dashboard Designer, take the following steps:
    1. Create a server connection for a SharePoint List or SQL Server Table data source    by configuring a connection for the data source.
    2. Map Time values to the Monitoring Server application database     by configuring time and aggregation for a data source.
    3. Map column properties to application-database dimensions     by configuring data column properties for a tabular data source.
    4. Set general properties    by configuring general properties for a data source such as data source name and user permissions.

Top of Page Top of Page

Create an ODBC data source element

You can create a data source in Dashboard Designer that uses the Open Database Connectivity (ODBC) interface to access data from an ODBC-compliant database management system (DBMS) such as SQL Server 2000 or SQL Server 2005.

 Important   A connection to an ODBC data source can return only a single value. Typically, dashboard designers use an ODBC data source to acquire single values from an Excel 2007 spreadsheet.

Before you create an ODBC connection, make sure that you consider the following:

  • For best performance results, data source tables should not exceed 20,000 rows.
  • The bit size of the server determines the ODBC connections that you can make. For example, because Office Excel 2007 and Office Access 2007 have 32-bit drivers, you cannot create an ODBC connection to Excel or Access when Monitoring Server is installed on a 64-bit server.

To create an ODBC data source element, take the following steps:

  1. Click the Create tab, and then click Data Source.
  2. In the Select a Data Source Template dialog box, click Standard Queries, and then click ODBC.
  3. Click OK.
  4. Click Finish. Use the Connection Settings pane that appears to configure a connection to the data source. For more information about how to configure the connection, see configuring a connection for the data source.
  5. To complete the data source configuration and make it available to users in Dashboard Designer, take the following steps:
    1. Enable a server connection    by configuring a connection for the data source.
    2. Map Time dimension values     to the Monitoring Server application database by configuring time and aggregation settings.
    3. Set general properties    by configuring general properties for a data source such as data source name and user permissions.

Top of Page Top of Page