Thursday, April 18, 2013

SharePoint 2013 PowerPivot Component Overview

[This post was completely overhauled on 6/18/2013 to provide more accurate and detailed information]

This post provides a high level overview of the components used in a SharePoint 2013 PowerPivot installation. It includes screenshots to illustrate the components discussed and in some cases I provide links to detailed instructions on how to install and configure the specified component.

Overview of the Component Architecture

The diagram below illustrates the components discussed in this post.

 
In a typical PowerPivot for SharePoint 2013 installation there are two SQL Server 2012 SP1 instances (they do not have to be on the same SQL Server).
  • There is at least one database instance for the PowerPivot and Secure Store Service Applications
  • There is one Analysis Services instance for PowerPivot, which is acts as the computational engine for Excel Services PowerPivot workbooks.
There are three service applications installed on SharePoint 2013 that are used by a full installation of PowerPivot:
  • Secure Store Service Application - claims-aware authorization service that stores and retrieves credentials
  • Excel Services Service Application - Browser based Excel for SharePoint
  • PowerPivot Service Application - Primarily responsible for scheduled date refresh and usage statistics

Note that Analysis Services and Excel Services are the only things required to view PowerPivot workbooks in SharePoint 2013. The other components give you:
  • The PowerPivot Gallery
  • The administrator's dashboard
  • Usage statistics
  • Scheduled data refresh
 

PowerPivot Components in SQL Server 2012 SP1


The screenshot below shows a sample installation of a SQL Server Analysis Services instance used for PowerPivot on SharePoint 2013. Note that the server name ("SPS-SQL" in my example) will vary by installation, but the instance name ("POWERPIVOT") will not. The POWERPIVOT analysis service instance is installed with the SQL Server 2012 Setup Wizard.
 
Detailed instructions on how to setup the POWERPIVOT analysis service instance in SharePoint mode can be found here: http://msdn.microsoft.com/en-us/library/jj219067.aspx

The screenshot below also shows the database  ("SP_PowerPivot" in my example) used by the PowerPivot service application. This database is created by the PowerPivot Configuration Tool.
 
 
Note that there is no Excel Services Database. Excel Services uses cached storage only.

PowerPivot Related Service Applications

The service applications used by PowerPivot can be seen in Central Administration (see screen shot below). The PowerPivot Configuration Tool provided by Microsoft can help you setup and configure all three of these service applications.

Detailed instructions on using the PowerPivot Configuration Tool for SharePoint 2013 can be found here: http://msdn.microsoft.com/en-us/library/hh213153.aspx

 
The PowerPivot Service Application (Mid Tier Service in Health Analyzer Rules) has the following functions:

  • Hosts the refresh scheduling engine, which calls Excel Services APIs for data refresh of Excel 2013 workbooks. For Excel 2010 workbooks, the service directly performs the data model processing but continues to reply on Excel Services for loading the data model and updating the workbook.
  • Provides methods for components such as the user interface pages, to communicate with the system service.
  • Manages requests for external access to workbooks as a data source, received through the PowerPivot Web Service.
  • Scheduled data refresh request management for timer jobs and configuration pages. The service manages requests to read data in and out of the service application database and trigger data refresh with Excel Services.
  • Usage processing and related timer job.
  • 
In the Excel Services, the name of the SQL Server Analysis Services instance used to store and manage PowerPivot workbook data must be identified (see screenshot below).

In the Secure Store Service Application, the PowerPivot Unattended Account for Data Refresh must be configured. The PowerPivot Configuration Tool will create this Unattended Account for you.

PowerPivot SharePoint 2013 Solutions

The farm will have two Globally deployed farm solutions and one farm solution that is deployed to the web application where you will be hosting PowerPivot workbooks (see screenshot below). These solutions are added to the farm by the PowerPivot Configuration Tool.



Your site has to have the PowerPivot Feature enabled to store PowerPivot workbooks, use the data refresh functionality, and display workbooks in the PowerPivot Gallery.


Ultimately, a PowerPivot gallery with a few workbooks in it will look something like this:



Please let me know if this post was helpful to you or if you have a question by writing a comment.

9 comments:

  1. This post provides a advanced level summary of the elements used in a SharePoint 2013 PowerPivot set up. It contains screenshots to demonstrate the elements mentioned.

    ReplyDelete
  2. Excellent post. Thank you doug!

    ReplyDelete
  3. Hi Doug Hemminger

    I'm trying to install PowerPivot for SharePoint 2013 but i have this problem "powerpivot for sharepoint 2013 configuration is missing prerequisites: sharepoint 2013 enterprise edition is required", i have installed in one server SharePoint 2013 Enterprise in anothe Sql Server BI 2012 11.0.3000.0 SP1 Business Intelligence Edition (64-bit).

    Thank you

    ReplyDelete
  4. Thanks so much ... this came in very handy

    ReplyDelete
  5. I have created PowerPivot Gallery with out any problems. However, whenever I try to access the PowerPivot Gallery, I see a windows security prompt trying to connect to the internet. How do I make it go away? Thank you

    ReplyDelete
  6. Hi doug,
    Thanks for this wonderful article.
    I am also trying to setup the BI features for my SharePoint Farm.

    I have 2 questions.

    Question 1 : What if I only install "SQL Server Analysis Services" and not ""spPowerPivot.msi" and run the wizard "Power Pivot for SharePoint 2013 configuration"
    - will i be able to work on power pivot in sharepoint.

    Question 2 : What if I only install "spPowerPivot.msi" and don't install "SQL Server Analysis Services" and run the wizard "Power Pivot for SharePoint 2013 configuration".
    - what features will be enable at this time, and how they will be different from question1 installation.

    ReplyDelete
  7. Hi, check this out http://charts.poweredtemplate.com/powerpoint-diagrams-charts/ppt-business-models-diagrams/0/index.html. It is very big library with different templates. You can find the one you need. All of them are made by professionals and have high quality.

    ReplyDelete