PerformancePoint 2010 Data Sources

Working with Multi-Dimensional and Tabular PerformancePoint Data Sources

As the foundation for key performance indicators (KPIs), data sources are the most important part of business intelligence (BI) solutions. It is not an exaggeration to say that a BI solution is only as good as its underlying data. PPS supports two primary types of data sources: multidimensional and tabular.

In this post, you will learn about the differences between these two types of data sources. You will also learn how these data sources can be applied appropriately in various scenarios.

Data sources are an extremely important piece of any BI solution. A scorecard or dashboard can only be as good as the data it is presenting, so it is important to consider the quality, accessibility, and format of the data before embarking on a BI solution.

Data sources serve as the foundation for KPIs and analytic charts and grids. In addition, data sources can be used for dashboard filters. Specifically, tabular data sources can be used for the Custom Table filter and the Analysis Services data source can be used for the Multidimensional Expressions (MDX) query, member selection, named set, time intelligence, and time intelligence connection formula filters.

The following two sections discuss the two broad categories of data sources that PPS supports: multidimensional data sources and tabular data sources.

Multidimensional Data Sources

Multidimensional data sources are all variants of SQL Server Analysis Services. They feature dimensions, fact data, and support for the MDX language, as opposed to columns and values that are used in tabular data sources.

Multidimensional data sources are really the primary use case for PPS, and many features such as analytic charts and grids and the decomposition tree in PPS require multidimensional data to operate. If you do not have any data in multidimensional format, consider porting some of it to a multidimensional format to take full advantage of the features of PPS.

• Analysis Services (2005, 2008, and 2008 R2)
• SQL Server 2008 R2 PowerPivot

Tabular Data Sources

Tabular data sources come in a wide variety of formats. Tabular data sources all feature columns and rows and conceptually are similar to a spreadsheet. It is also possible to create custom tabular data sources using the PPS SDK.

Tabular data sources have limited functionality. You can represent them as KPIs on scorecards or have them appear as data values within filters to interact with various nonanalytic report types. Generally, this is the extent of their functionality. Listed here are the tabular data sources supported by PPS:

• Excel Services (Excel Services 2007 or 2010)
• Import from Excel workbook (Excel 2007 or 2010)
• SharePoint list (SharePoint 2007 or 2010)
• SQL Server table (SQL Server 2005, 2008 or 2008 R2)
• Custom data source

Conceptually, tabular data is turned into "microcubes" within Dashboard Designer. Each tabular data source can define dimension and fact data types in the data source definition editor.

Dimension values are populated through members that are currently available in the data column. For instance, if a dimension column can contain Yes or No values. If the data only contains No values, it will not be possible to select a Yes value when adding a dimension filter to a KPI until the data contains at least one Yes value. In addition, dimensions created from tabular data are also always flat. Therefore, it is not possible to create a parent/child relationship and hierarchies between dimension values.

Fact data types are determined by the contents of the list. If all the data values are numbers, the data type is considered a number and can be aggregated as numbers. If just one value is text, the entire list will be considered as text fact data.

The Data Source template allows you to select the appropriate data source for your KPIs. For KPIs, you can use all types of PPS data sources. For analytic chart and grid reports, a multidimensional data source is required.

• Microsoft

• PerformancePoint

• PerformancePoint 2010

• PPS

• PPS 2010

• SharePoint