PerformancePoint (PPS) is typically used against multidimensional data sources (i.e. Analysis Services), but it does have the ability, albeit limited, to work against tabular data sources such as SQL Server tables, SharePoint Lists, Excel Services, Import From Excel Workbooks, and even PowerPivot (with its xVelocity engine).

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 multidimensional (i.e. 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 (SSAS).  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.

To use PowerPivot as a data source, in PPS create a Multi-Dimensional Analysis Services data source and use a connection sting pointing to an Excel file in SharePoint that has a PowerPivot model.  So this allows you to have PowerPivot connect to various sources such as a SharePoint list or SQL Server tables that in turn can be used as a multi-dimensional analysis services data source in PerformancePoint.

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.  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.

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, 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 analytic grid reports, a multidimensional data source is required.

More info:

PerformancePoint 2010 Data Sources

You Can Use SQL Server “Denali” PowerPivot Models as PerformancePoint 2010 Data Sources

Using PowerPivot with PerformancePoint Services (PPS) 2010

Using PerformancePoint Services (PPS) with PowerPivot SQL Server 2012 RC0

Building a PerformancePoint Dashboard…without a cube?