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.
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:
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.
Ola Ekdahl has worked with PerformancePoint since its early alpha stages and was a technical reviewer for Microsoft Office PerformancePoint Server 2007. He has extensive experience developing business intelligence solutions for financial services and entertainment.
Ola Ekdahl is a Microsoft Technical Trainer and a Senior Instructor with IT Mentors. His mission is to help organizations understand Microsoft technology and use it to their advantage. Ola has a well-rounded background in Microsoft programming and competitive technologies including C#, ASP.NET, VB.NET, .NET Security, SQL. He has expertise in business intelligence development including SQL Analysis and Reporting Services, PerformancePoint Server, SharePoint Portal Technologies, Java, and J2EE.
Ola’s first-hand experience includes application development as well as infrastructure migration projects in the financial services, entertainment and music industries. He holds MCSD.NET and MCSE certifications and is a Microsoft Certified Trainer. Check out Ola’s blog at http://blogs.itmentors.com/ola Follow Ola on Twitter: www.twitter.com/olaekdahl
The "Rough Cut" version of Ola's latest book, Microsoft Office PerformancePoint Services 2010 Unleashed, has been selected as Microsoft Subnet's February, 2010, book giveaway.