Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Using PowerPivot with PerformancePoint Services (PPS) 2010

Posted by denglishbi on January 3, 2011

[tweetmeme source=”denglishbi” only_single=”false”]

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.

image image
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
image image
Select the ContosoRetailDW SQL Server database Choose the option to select from a list of database tables
image imageimage
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)
image image
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
image image
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
imageimage image
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
image image
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)
image image
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
image image
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
image image
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)
image image
Create and setup the Dashboard Connect the filters to the Dashboard items
image image
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:




23 Responses to “Using PowerPivot with PerformancePoint Services (PPS) 2010”

  1. Savita Verma said

    I tried to install the Contoso Retail database. I could successfuly install database but when i try to restore the restore ContosoRetail.abf backup file; i get error saying – ‘Feature and compatibility configuration’ error..’Database storage location’ feature is not included in 64 bit standard edition’ ..any help??

    • denglishbi said

      In order to use the Analysis Service database you are going to need either a Developer, Enterprise, or Data Center edition of SQL Server. This database leverages Enterprise features like Perspectives that are not available in Standard edition. Based on this solution and example using PowerPivot with SharePoint you would need one of these versions of the software since the PowerPivot setup with Analysis Services is an Enterprise feature.

  2. Ali said

    I am installing an instance of SharePoint 2010 with Powerpivot using an existing farm option. I have SQL Server 2008 R2 on Windows Server 2008 on my VM. I am able to install the addon using my SQL Server R2 setup. I have also started the following Services in sharePoint successfully:
    Claims to Windows Token Service
    Excel Calculation Services
    Secure Store Service
    SQL Server Analysis Services
    SQL Server PowerPivot System Service
    I am having issues with being able to manage the PowerPivot service application. I am able to create the PowerPivot service application and assign it a new application pool, set up a application account, set up the data base etc. I also checked the button for “Add the proxy for this PowerPivot service application to the default proxy group. ” I only have one application for PowerPivot service application. However once I create the application and click ‘Manage’ in the ribbon to go ahead and access the PowerPivot dashboard feature I get a web page not found (HTTP 404 error). I have tried to delete the application and recreate it in several different modes and I have also rebooted my VM. I have all the appropriate services running and am able to view the PowerPivot Gallery and Data Feed Library in the “Site Actions” -> “More Options”. I can create a PowerPivot library and load workbook to it aswell. I just can’t refresh data since I haven’t set up my Secure Store Service Application ID with the appropriate credentials. I am also able to connect to the powerpivot install in Analysis services. The only step I haven’t configured is setting up the unattended account and link it to my service application due to the HTTP 404 error. I also don’t see the PowerPivot dashboard option under the General Application Settings in SharePoint Central Adm. I hope I am providing enough details.

    • denglishbi said

      Verify that you have followed all of the steps prior to configuring the PowerPivot service. Did you deploy the PowerPivotWebApp.wsp? Also, are you using Domain accounts when you configure the application? Is it the same account as the farm admin or different one? Review the documentation and forum links here – http://msdn.microsoft.com/en-us/library/ee210616(SQL.105).aspx#installAS and http://powerpivotgeek.com/docs/PowerPivot_Existing_Farm_Installation.pdf and http://social.msdn.microsoft.com/Forums/en/sqlkjpowerpointforsharepoint/thread/fb391478-f900-401d-9d88-3ceffb05ca1a.

      • Ali said

        I have deployed the Powerpivotwebapp.wsp and below are the settings:
        Name: powerpivotwebapp.wsp
        Type: Core Solution
        Contains Web Application Resource: Yes
        Contains Global Assembly: No
        Contains Code Access Security Policy: No
        Deployment Server Type: Front-end Web server
        Deployment Status: Deployed

        What are the conditions for setting up the service application other than it being a domain account? I set myself up as the Security account for the application pool. I have rights to access the database server and access and create permission. However I am not a SharePoint farm admin. I also want to point out that under Site Actions -> View All Site Content I have a PowerPivot Management document library created and I understand this is what the dashboard uses. But I am unable to view any data in the files. I see excel files here and ODC files for connecting to the database to obtain the usage statistics. However I can go in the database and query the database which I created for the service application pool.
        Lastly the Analysis Services service is something I didn’t set up so I don’t know what the account for it is. How can I check this account for the Analysis Services is same as one I need to set up for the Secure Store Service?

      • denglishbi said

        To view the credentials for SSAS you can leverage the SQL Server Configuration Manager on the server. Not sure where you are at now with PowerPivot and the issues you are facing. I would suggest verifying the documation information, looking for suggestions in the forums, and possibly posting in the forums to resolve your current issues – http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpointforsharepoint/threads

      • Ali said

        Thanks for the links Dan. It was actually the Claims 2 Windows Token service which was set to a domain rather than a local system account which was causing some of my problems.

  3. Andrew Sadler said

    Hi Dan,

    Have you found anyway to set/control the truncations of dimension member names in PPS analytic charts?



  4. […] Using PowerPivot with PerformancePoint Services (PPS) 2010 […]

  5. Excellent info. Thanks.

  6. […] Detailed walkthrough : Dan English’s BI Blog […]

  7. Steve Sofian said

    Hi I have been trying to connect to the PowerPivot, but am not able to select the cube. The error thrown was The Unattended Service Account does not have access to the server specified in the connection string. Have been setting permissions all over the place but still the same error. Any idea? I am using SQL 2012 RC0

    • denglishbi said

      I have also been trying to connect to PowerPivot with both the CTP3 and RC0 releases and have not been able to either. I receive the message “An error occurred connecting to this data source. Please check the data source for any unsaved changes and click on Test Data Source button to confirm connection to the data source.” I can successfully connect and use a Tabular instance of SSAS, so not sure what is going on. I have not been able to make a selection for the Cube drop-down list when configuring the data source for a PowerPivot file. Were you able to get that part to work? If so, what does your connection string look like?

      • Steve Sofian said

        Yeah exactly the same. I have not been able to select from the Cube drop down list. Encountered the same error as yours. Anywy I have logged in as a feedback to Metro. Hopefully will be able to get a reply 🙂 Will update here when i do.

        Btw, thanks for a great blog on BI 🙂

  8. pmdci said

    Awesome tutorial.

    IF anyone is in doubt on how to calculate the Pct Measures (both GrossMarginPct and the GrossMarginPctQuota), it is just the value of GrossMargin / SalesAmt (and GrossMarginQuota / SalesAmtQuota)

  9. Lee Polikoff said

    I saw that in the analytic charts and grids in PerformancePoint above you have the cells formatted with $. I have tried to do this to no avail.The best I could do was use the DAX FORMAT function in a measure which allowed me to display the values in a grid, but I wasn’t able to chart the data. I am running PowerPivot for SQL Server 2008 Ver 10.51.2500.0 and am unable to upgrade to the new version due to current company restrictions. What do I need to do to do this?


  10. […] Using PowerPivot with PerformancePoint Services (PPS) 2010 […]

  11. ansoneesan said

    I’ve created several Analytical Grids in Performance Point Services, and none of them are formatted with a “tree” for those rows that should. For example, Your firt example is a grid showing sales by product category, and the category has a “+” that can be clicked and then have the products under that category appear.

    I have a similar situation, but instead of product category, the dimension is job number. But instead of the job numbers being able to be expanded and ten seeing the underlying components of the job, there is a separate row for every job and every job number. For example:


    1000 3
    1000 4
    1000 5

    In the grid display, there should be a row for job 1000 with a “+” next to it, then once you expand it, the three component numbers appear.

    My grids look nothing like that. How do I get them to format prperly?

    • denglishbi said

      Create a user defined hierarchy and reference that, you will then get the desired results in the grids and even scorecards.

      • ansoneesan said

        Thanks Dan!

        I appreciate the input. So if I ever need to have that functionality in an analytic chart or scorecard, I need to have an actual hierarchy defined? So, unlike the cube browser in BIDS, the PPS grids don’t “infer” the hierarchies?

      • denglishbi said

        correct, they behave a bit different than other tools like say Excel.

        cube browser in bids (ssdt) and ssms gone now in 2012. get ssrs mdx designer (flattened results). Excel is the way to go, particularly in 2013.

Leave a Reply to Ali Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: