Using PowerPivot with PerformancePoint Services (PPS) 2010
Posted by denglishbi on January 3, 2011
UPDATE (6/20/2011): Modified table layout of step-by-step walkthrough to be two columns instead of three. Added a downloaded PDF file as well.
UPDATE (2/24/2012): I have a blog for using PowerPivot 2012 with PerformancePoint Servers here - Using PerformancePoint Services (PPS) with PowerPivot SQL Server 2012 RC0.
Analysis Services was one of the primary data sources with the previous version of PerformancePoint to get the most functionality out of the product. Now with the release of the 2010 product we have some new options and one of them is PowerPivot. PowerPivot is also a multi-dimensional data source and in this post I will talk about what this source provides for functionality and what it does not compared to Analysis Services.
First of all you will need to have an environment that has PerformancePoint Services configured. If you are not familiar with how to do that you can take a look at this great posting on the product team’s web site – Deploying PerformancePoint 2010 Soup to Nuts. Once you have that setup then the other thing that you are going to need to do is have PowerPivot setup with SharePoint so that we will be able to utilize the files as data sources. There are plenty of articles and material available in regards to this configuration, here is one of them – How to: Install PowerPivot for SharePoint on a New SharePoint Server.
Once you have your environment setup and ready the next thing will need to do is create your PowerPivot data source that you are going to leverage to create your dashboard with. In this example we are not going to be creating the file and leveraging the PowerPivot PivotCharts and PivotTables, we are simply going to be setting up a file that will be used to provide the data and measures needed for analysis. I will be using it in place of having to implement an Analysis Services solution (except for the fact that PowerPivot is really an Analysis Services database under the hood).
Some of the reasons that we might want to use PowerPivot in a PPS dashboard would be because:
- ability to leverage PPS analytical features like analytical grids & charts and decomposition tree
- quickly integrate disparate data for analysis into a single source
- leverage existing Excel Power User expertise, data, and files
For this example I will be leveraging the Contoso Retail BI Demo data – Microsoft Contoso BI Demo Dataset for Retail Industry. I will also be using the following components to put this example together – Excel 2010 with PowerPivot add-in (this can be downloaded from here – PowerPivot Download), PowerPivot for SharePoint 2010, PerformancePoint Services, and SharePoint 2010 Enterprise.
Warning: This example does not include each step-by-step instruction and expects that you have some basic knowledge of PowerPivot, DAX, and PerformancePoint.
|First step is to launch PowerPivot Window from Excel 2010 to load data||Now from the PowerPivot Window we will select the option to load data from a SQL Server database|
|Select the ContosoRetailDW SQL Server database||Choose the option to select from a list of database tables|
|In this example we will select DimDate, DimGeography, DimProduct, DimProductCategory, DimProductSubcategory, Dim Store, FactSales, and FactSalesQuota||For the two fact tables we will remove a few columns that are not needed for analysis during the load using the Preview & Filter option (remove initial fact key column and last three ETL columns). Also add a filter for FactSalesQuota on ScenarioKey to only include values of 2 (for Budget)|
|Now you can click finish and watch the data load – over 5 millions rows of data!||During the load process the relationships that were defined in the SQL database were retained, so we will not need to setup any additional relationships between the tables|
|We might simplify the snowflaked model and pull the Product Category and Subcategory information into the Product table using DAX and the RELATED function||To simplify the model a bit more we can remove columns and tables that are exposed to the PivotTable in Excel – remove the surrogate key, foreign key, ETL columns, and in the related tables remove all columns|
|Now the creation of the PivotTable data (measures) which will be needed in PerformancePoint to reference – I will comment on this more down below after the example||Measures have been added for PerformancePoint, so now the file can be published to SharePoint to be referenced|
|File –> Save & Send –> Save to SharePoint –> Save As (this will be saved to a Trusted Excel Services location in SharePoint)||In Dashboard Designer a new Data Source will be created – go into the Data Connections library and add a new PerformancePoint Data Source (this will launch Dashboard Designer)|
|Create a Multi-Dimensional Analysis Services data source and use a connection sting pointing to the Excel file in SharePoint – “DATA SOURCE = http://mybi10/Workbooks/ContosoSalesAnalysis.xlsx” and the cube name is “Sandbox”||If you receive this message when setting up the data source to pick the cube you will need to verify the connection string URL and/or verify that the SQL Server Browser service is running|
|Now create a couple of KPIs for Sales and Gross Margin information (you will need to use the Add Lists option in the Workspace portion of the Ribbon to add the PerformancePoint Content list). Make sure to change the Calculation option from Default to Data||Setup a Scorecard and add a dimension column (ProductCategory) to analyze|
|Create an Analytical Chart (you need to save the data source if you haven’t done so yet to create the analytical reports). To apply formatting to the measures switch to Grid report type and change formatting to $ and %. Also filter empty axis and series||Add another Analytical Chart. Apply appropriate formatting and filter to axis and series|
|Add an Analytical Grid. Adjust Settings so that the column and row headings are not truncated in the View options in the Ribbon||Create a couple of Filters, one for the FiscalYear and another for RegionCountry (an MDX Query was used along with the Tree view setup for each filter)|
|Create and setup the Dashboard||Connect the filters to the Dashboard items|
|Save all of the content and then deploy the Dashboard to SharePoint||View and test the dashboard page|
|View the Decomposition Tree (requires Silverlight)|
A couple of items that I wanted to discuss briefly about in regards to this example was the reasoning for setting up measures in the PowerPivot PivotTable and the other was for setting up the KPIs Calculation as Data Value. External tools require that you setup measures in the PowerPivot file in our to reference. Whether you did this in PerformancePoint or Reporting Services, you would need to have measures defined to be able to reference to utilize for analysis. PowerPivot doesn’t have the ability to setup formatting either to pass through, so you also have to perform this in the tools referencing it currently. You can reference items placed in the PivotTables in the Values section, but you can’t guarantee these will always exist, so I recommend setting up measures for every metric that you want to evaluate and utilize with external tools.
The other items that I wanted to mention was the KPI calculation setting. This was changed to Data value so that we would be able to leverage the Decomposition tree option in the right-click context menu. If this was left as Default value this would have been disabled. You currently cannot use the Show Details option since drillthrough is not an option in the current PowerPivot release, but you can use the Decomposition tree for analysis.
For the most part PowerPivot provides very similar functionality when compared to Analysis Services. A few items that you don’t get will be Show Details (Drillthrough), Named Set references, measure formatting (along with formatting configurations – colors), and user-defined hierarchies.
For a good reference point for this type of a setup take a look at this white paper – PerformancePoint Services and PowerPivot for Excel (white paper).
This white paper covers the basics of integration between PerformancePoint Services in Microsoft SharePoint Server 2010 and Microsoft SQL Server 2008 R2 PowerPivot for Microsoft Excel 2010. It describes the basics of utilizing a PowerPivot model in PerformancePoint Services and expands on issues and scope of using these applications together.
Well that was a really brief and fast example of using PowerPivot with PerformancePoint, but you can see how quickly you can build out a fairly advanced dashboard that provides insight and analytical functionality to the end-users.
If you liked this posting you might also be interested in my other two that are similar for Excel Services and Reporting Services:
- Using Excel Services Reports with PerformancePoint Server (PPS)
- Using Reporting Services Report (SSRS) with PerformancePoint Services (PPS)