Planning for PerformancePoint data sources (PerformancePoint Services)
Published: August 12, 2010
In PerformancePoint Services you must create a connection to the data source or sources you want to use in your dashboard. All data used in PerformancePoint Services is external data, living in data repositories outside of PerformancePoint. After you establish a data connection, you will be able to use the data in the various PerformancePoint feature areas.
PerformancePoint supports tabular data sources including SharePoint Lists, Excel Services, SQL Server tables and Excel workbooks; and multi-dimensional (Analysis Services) data sources
PerformancePoint Services does not support Office 2010 “Click-to-Run” (C2R) applications as data sources. You must install the client.
Tabular Data Sources
A user can create a data connection to SharePoint Lists, Excel Services, SQL Server tables or Excel workbooks. For these kinds of data sources, you can view a sample of the data from the Dashboard Designer tool and set specific properties for the data depending how you want the data to be interpreted within PerformancePoint. For example, you can indicate which datasets should be treated as a dimension. You can specify if a dataset is to be treated as a dimension or a fact; or if you do not want the data to be included, you can select Ignore. If you decide to set the value as a fact, you can indicate how those numbers should be aggregated in PerformancePoint Services. You can also use datasets that have time values within PerformancePoint Services and use the PerformancePoint Services time intelligence features to set time parameters and create dashboard filters.
Tabular data sources can only be used for PerformancePoint scorecards and filters.
Data contained in a SharePoint List on a SharePoint Site can be used in PerformancePoint Services by creating a SharePoint List data source in Dashboard Designer. Data from SharePoint Lists can only be read. Modification to SharePoint List data must be done from SharePoint.
Users may connect to any kind of SharePoint List.
Data in Excel files published to Excel Services on a SharePoint Site can be used in PerformancePoint Services by creating an Excel Services data source. Supported published data can only be read in PerformancePoint Services. Published parameter values can be modified from the Dashboard Designer. If you use an Excel Services parameter in calculating a KPI, it is easy to make additional changes. PerformancePoint Services supports the following Excel Services components: Named Ranges, Tables and Parameters
You cannot connect to an Excel Services as a data source when the site or library contain the workbook you are trying to connect to is set for Anonymous Access.
SQL Server tables
You can create a data source connection to a SQL Server database and use the data within PerformancePoint Services. SQL tables and SQL views are supported data sources within PerformancePoint Services.
The data from SQL Server database is read-only. PerformancePoint Services does not support any modification to SQL Server database.
After creating and saving a SQL data source connection in Dashboard Designer, the data source is listed in the workspace browser with a pencil icon still viewable, as if the data source was not saved and still is being edited. This is a known issue.
You may use the content of an actual Excel file as a data source in PerformancePoint Services by creating an Excel Workbook data source connection and select only the data that is to be used. The original Excel file will be independent from the PerformancePoint copy. PerformancePoint Services 2010 supports Excel 2007 and Excel 2010 workbooks as data sources.
Multidimensional Data Sources
Use data residing in a SQL Server Analysis Services cube in PerformancePoint Services by creating a data connection to the source. PerformancePoint Services lets you map the desired time dimension and the required level of detail for its hierarchies to the internal PerformancePoint Services Time Intelligence.
Unlike tabular data sources, users cannot browse an Analysis Services cube within PerformancePoint’s Dashboard Designer.
PowerPivot for Excel
In PerformancePoint Services you can use a PowerPivot model as a data source to build your PerformancePoint Services dashboards. To use PowerPivot as a data source within a PerformancePoint Services dashboard, you must have PerformancePoint Services activated on a SharePoint Server 2010 farm and have PowerPivot for SharePoint installed. Once a PowerPivot model has been created by using the PowerPivot add-in for Excel, this Excel file must be uploaded or published to a SharePoint site that has PowerPivot services enabled. Create the data source connection in Dashboard Designer using the Analysis Services data source template.
ConceptsCreate a PowerPivot data connection (PerformancePoint Services)
Configure data sources to work with Time Intelligence by using Dashboard Designer
Configure Analysis Services data source time settings by using Dashboard Designer
Configure tabular data source time settings by using Dashboard Designer