PowerPivot for SharePoint is a collection of middle-tier and backend services that provide PowerPivot data access in a SharePoint 2013 farm. The PowerPivot for SharePoint add-in (spPowerpivot.msi) is an installer package used to install the middle-tier components
Middle-tier: Enhancements to the PowerPivot functionality in SharePoint 2013. Functionality includes using workbooks as a data source, scheduled data refresh, and the PowerPivot Management Dashboard.
PowerPivot for SharePoint 2013 is a Microsoft Windows Installer package (spPowerpivot.msi) that deploys Analysis Services client libraries and copies PowerPivot for SharePoint 2013 installation files to the computer. The installer does not deploy or configure PowerPivot features in SharePoint. The following components install by default:
· PowerPivot for SharePoint 2013. This component includes PowerShell scripts (.ps1 files), SharePoint solution packages (.wsp), and the PowerPivot for SharePoint 2013 configuration tool to deploy PowerPivot in a SharePoint 2013 farm.
· Microsoft OLE DB Provider for Analysis Services (MSOLAP).
· ADOMD.NET data provider.
· SQL Server 2012 Analysis Management Objects.
Note: This topic describes installing the middle-tier PowerPivot solution files and PowerPivot for SharePoint 2013 Configuration tool. After the installation, see the following topic for information on the configuration tool and additional features, Configure PowerPivot and Deploy Solutions (SQL Server 2012 SP1).
Download the spPowerPivot.msi via http://www.microsoft.com/en-us/download/details.aspx?id=35577
Our SP Farm contains 2 App, 2 Web, 2 Search servers and an Office Web Apps farm so the below diagram is used:
PowerPivot for SharePoint 2013 Three Server Deployment
In the following three-server deployment, the SQL Server Database Engine, Analysis Services server running in SharePoint mode, and SharePoint, each run on a separate server. In SQL Server 2012 SP1 the PowerPivot for SharePoint 2013 installer package (spPowerPivot.msi) must be installed on the SharePoint server.
Excel Service Application. The service application is created as part of the SharePoint installation.
PowerPivot Service Application. Default name is Default PowerPivot Service Application.
RUN the spPowerPivot.msi to install data providers, the PowerPivot configuration tool, PowerPivot Gallery, and schedule data refresh.
An Analysis Services Server in SharePoint Mode. Configure the Excel Services Application Data Model Settings to use this server.
The SharePoint content, configuration, and service application databases.
A recommended best practice is to install spPowerPivot.msi on all servers in the SharePoint farm for configuration consistency, including Application servers and web-front end servers. The installer package includes the Analysis Services data providers as well as the PowerPivot for SharePoint 2013 configuration tool. When you installspPowerPivot.msi you can customize the installation by excluding individual components.
Data providers: Several SharePoint and SQL Server technologies use the Analysis Services data providers including Excel Services, PerformancePoint Services, and Power View. Installing spPowerPivot.msi on all SharePoint servers ensures the full set of Analysis Services data providers and PowerPivot connectivity is consistently available across the farm.
You must install the Analysis Services data providers on a SharePoint 2013 server using spPowerPivot.msi. Other installer packages available in the SQL Server 2012 SP1 Feature Pack are not supported because these packages do not include the SharePoint 2013 support files that the data providers require in this environment.
Configuration Tool: The PowerPivot for SharePoint 2013 configuration tool is required on only one of the SharePoint servers. However a recommended best practice in multi-server farms is to install the configuration tool on at least two servers so you have access to the configuration tool if one of the two servers is offline.
- Microsoft SharePoint Server 2013.
- spPowerPivot.msi is 64-bit only, in accordance with the requirements of SharePoint products and technologies.
- SQL Server 2012 Analysis Services (SSAS) server in PowerPivot mode. Excel Services will use the SQL Server Analysis Services instance as a PowerPivot server. Analysis Services can run on the local or a remote computer.
- To install PowerPivot for SharePoint 2013, the current user is required to be an administrator on the computer and a SharePoint Farm Administrators group.
- For more information on PowerPivot for SharePoint requirements and pre-requisites, go to Hardware and Software Requirements (PowerPivot for SharePoint)(http://go.microsoft.com/fwlink/?LinkID=247828).
The spPowerpivot.msi installer package supports both a graphical user interface and a command-line mode. Both methods of installation require that you run the .msi with administrator privileges.
I accept the terms….
All features are selected by default
Make sure you are not using the System Drive
Three of the files copied to the hard drive by spPowerPivot.msi are SharePoint solution files. The scope of one solution file is the farm level while the scope of the other file is the Web application level. The files are the following:
The solution files are copied to the following folder:
C:\Program Files\Microsoft SQL Server\110\Tools\PowerPivotTools\SPAddinConfiguration\Resources
Following the .msi installation, run the PowerPivot for SharePoint 2013 Configuration Tool to configure and deploy the solutions in the SharePoint farm.
When running the configuration tool, be aware that lots of settings will be automatically set for you. If you don’t want that and if you want to configure the below options yourself, DO NOT USE THIS TOOL.
There are a lot of services, service application, managed accounts (application pool accounts), permissions etc involved!! And the tool can also break stuff so be careful!
- You must be a farm administrator.
- You must be a server administrator on the Analysis Services instance SharePoint 2010 only).
- You must be db_owner on the farm’s configuration database.
On a Windows 2012 Server – open the start screen and look for:
Run as Admin
If you want to configure all settings yourself
Create a Managed account and use that account as the Application Pool account for your PowerPivot Service Application (Pool)
Make sure that the Application Pool Account you use for both Excel Services and PowerPivot Service application have enough permissions on your SQL box on all databases. Both accounts should have
SPDataAccess permissions on all your content databases
Make sure that bot the Application Pool accounts have WSS_Content_Application_Pools role membership on your SharePoint Admin and Config database.
Make sure that the Application Pool account which you use for the PowerPivot Service Application has Full Read access on all Web Applications
Make sure that the Secondary logon service is set to Manual and start the service on your SharePoint servers!!