Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James Serra's Blog

James is currently a Senior Business Intelligence Architect/Developer and has over 20 years of IT experience. James started his career as a software developer, then became a DBA 12 years ago, and for the last five years he has been working extensively with Business Intelligence using the SQL Server BI stack (SSAS, SSRS, and SSIS). James has been at times a permanent employee, consultant, contractor, and owner of his own business. All these experiences along with continuous learning has helped James to develop many successful data warehouse and BI projects. James has earned the MCITP Business Developer 2008, MCITP Database Administrator 2008, and MCITP Database Developer 2008, and has a Bachelor of Science degree in Computer Engineering. His blog is at .

Using PerformancePoint against tabular data

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

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

Comments

Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...