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 ArchitectureThe 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
See http://technet.microsoft.com/en-us/library/ee210692.aspx for TechNet documentation
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.
PowerPivot Related Service ApplicationsThe 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 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 SolutionsThe 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.