Protiviti / SharePoint Blog

SharePoint Blog

July 30
PowerPivot for SharePoint 2013 A Technical Guide – Part 1 of 4

​Introduction
PowerPivot is a business intelligence and data analysis tool built as an extension to Excel and SharePoint’s Excel Services. As the business community continues to expand its use of business intelligence tools, including PowerPivot for Excel, we expect usage of PowerPivot for SharePoint to grow in usage as well.
This post is the first in a four part technical series on PowerPivot for SharePoint 2013. And will focus on:

  • Prerequisites required for running PowerPivot on SharePoint 2013
  • An overview of the technical components of a SharePoint 2013 on-premises PowerPivot installation
  • Accounts and permissions required for PowerPivot on SharePoint 2013.

Subsequent posts will focus on installation, configuration, and troubleshooting.

 
Prerequisites
PowerPivot for SharePoint 2013 minimally requires:
  • SQL Server 2012 SP1 – SQL Server Enterprise or Business Intelligence edition.
  • Excel Services – SharePoint 2013 Server Enterprise Edition.
PowerPivot is generally installed after your SharePoint 2013 farm is up and running and Excel Services has been enabled and configured. If you want to use the data refresh capabilities of PowerPivot, you will also need to configure the Secure Store Service Application.

Component Overview
In a typical PowerPivot for SharePoint 2013 installation there are two SQL instances (they do not have to be on the same SQL Server).
  • There is at least one database instance that holds the PowerPivot and Secure Store Service Application databases
  • There is also one Analysis Services instance for PowerPivot, which 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:

  • The Secure Store Service Application is a claims-aware authorization service that stores and retrieves credentials. PowerPivot uses the secure store service application to store credentials used to refresh data in the PowerPivot Excel workbooks.
  • The Excel Services Service Application is a browser based version of Excel for SharePoint. When Excel Services and PowerPivot is enabled on a SharePoint 2013 farm, users can view and interact with PowerPivot workbooks.
  • The PowerPivot Service Application is primarily responsible for managing and running scheduled data refresh and calculating and storing usage statistics.
Note that, in addition to the basic functionality of a SharePoint 2013 farm, 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
The diagram below illustrates these components and their relationships:
 

 
Accounts and Permissions
Following is a brief overview of the accounts and permissions required for PowerPivot on SharePoint 2013. This overview is intended to provide a basic understanding of what accounts are required for PowerPivot to run correctly on a SharePoint 2013 farm.
 
SQL Server 2012 Analysis Services (POWERPIVOT)
Sample Account Name: SPAnalysisServices

Account Type: Domain User

Required Permissions:

  • Run as account for the "SQL Server 2012 Analysis Services (POWERPIVOT)" windows service on the SQL Server application server.
  • Member of local security group: SQLServerMSASUser$<ServerName>$PowerPivot
  • Member of local security group: WSS_WPG
Notes:

  • Windows service that runs on a SQL Server 2012 application server. In smaller SharePoint installations, this is usually the same server that the SharePoint database engine is installed.
  • Built in machine accounts are not allowed
  • Must be the same for all Analysis Services server instances used by a single farm
PowerPivot Service Application
Sample Account Name: SPPowerPivot

Account Type: Domain User

Required Permissions:

  • Run as account for the PowerPivot Service Application
  • Analysis Services system administrator
  • Connect, read and write to PowerPivot Service Application Database
Notes:

  • Shared web service that runs under an application pool identity in a SharePoint farm
  • Built in machine accounts are not allowed
  • On smaller farms or farms that don't need the account separation, this is typically the same domain account that is used to run other Service Application on SharePoint 2013.
PowerPivot Unattended Data Refresh Account
Sample Account Name: SPDataRefresh

Account Type: Domain User

Required Permissions:

  • Must be assigned Contribute permissions on any PowerPivot workbook for which it is used
  • Read permissions on any external data sources needed for a data refresh operation
Notes:

  • By default, the PowerPivot Configuration Tool configures this as the farm account
  • This can be left as the farm account if account separation is not needed and permission requirements outlined above are met. Larger farms or farms with external data sources may want a separate account for auditing and traceability.
  • If you don't use the farm account, additional permission configuration may be required on the Central Administration site and the PowerPivot Service Application Database in order for the PowerPivot reports to work in Central Administration.
Excel Services Service Application
Sample Account Name: SPExcelServices

Account Type: Domain User

Required Permissions

  • Analysis Services PowerPivot Instance system administrator
Notes

  • Excel services should be configured in advance of the PowerPivot installation.
  • On smaller farms or farms that don't need the account separation, this is typically the same domain account that is used to run other Service Applications on SharePoint 2013.
Conclusion
This post summarized:

  • Prerequisites required for running PowerPivot on SharePoint 2013
  • An Overview of the technical components of a SharePoint 2013 on-premises PowerPivot installation
  • Accounts and permissions required for PowerPivot on SharePoint 2013.
In the next post, we will walk through how to install PowerPivot for SharePoint 2013.
 

Quick Launch


© Protiviti 2019. All rights reserved.   |   Privacy Policy