SharePoint 2013 – Configure PowerPivot for SharePoint
October 30, 2013 1 Comment
To minimise issues and to have greater control before starting the PowerPivot installation and config check the following points :-
- The farm is up and running
- You have Farm Admin account credentials to run the PowerPivot Config Tool with
- Secure Store Service is configured and running
- Excel Services is configured and running
- If you do not configure Excel Services the tool will do this for you with default values. I have configured Excel Services as per article here.
First start by installing the SQL Server (Enterprise or BI Edition) PowerPivot for SharePoint instance on the Application server/s that will run PowerPivot.
Use the SQL Server 2012 media to run a new instance installation of PowerPivot. In our installation here you can see it already has a default instance with the SQL database engine and SSRS in SharePoint mode installed (SSRS is not configured).
So we select “Perform a new installation of SQL Server 2012.”
Add the PowerPivot feature.
I have un-ticked the Add SQL Server DB engine as we already have a DB instance we can use.
The feature selection screen automatically fills in the required boxes.
Here we see the new instance name and configuration.
Here I have used a domain account for this service.
I have added the service account and farm admin accounts to the admins role for this instance.
Spsetup and farm have been added as they are farm admins, the spbisvc account will be used to run the BI web (site) that PowerPivot runs in.
Technet article for PowerPivot
Running the PowerPivot Config tool for SharePoint 2013
The config tool can create the PowerPivot site for you but I have already created my BI site which I want PowerPivot enabled on as I prefer to control how the site is created. If you have not created an Excel Services Service it will also create this with default values.
Ensure Secure Store Service is also configured and running as per previous article on Configuring Core Services.
Once PowerPivot is installed you will need to run the configuration tool. You can do this from the Windows Server start menu. Select the Configuration tool for SharePoint 2013 (not the other one which is for 2010). We see it at the bottom of this screen shot.
Also note you must run this tool for configuration (and removal) of PowerPivot with farm admin permissions. I have used the SPFarmApp account. You can also run PowerShell or configure through Central Admin. We will just run the tool here which will deploy the WSP files to the farm correctly. We can then accept the PowerPivot Service Application name and database name or just delete the service app and recreate it with our preferred names. Not the most elegant but simplest and valid for now.
Press ok on this screen and the tool will validate the environment.
Enter the account information and hit Validate.
Accept the notice and click Yes
Wait for this to complete.
You should now see the PowerPivot application available in the configurable applications in Central Admin, Application Management, Manage Service Applications.
Here we see screen shots of the deployed applications, SQL databases and application service.
Central Admin, system Settings, Farm Solutions
SQL Database with default GUID Naming
Default Application Service, Central Admin, Application Management, Manage Service Applications
Personally I would delete this application by clicking the top element and clicking Delete. Accept notification and check the box.
This gets rid of the database also.
Then create the PowerPivot App again form Central Admin , Application Management, Manage Service Applications, New..
Specify a name and select the BI App Pool
Change the database name
Leave rest as Default and press OK.
We now have an appropriately named PowerPivot service and database name.
now from your BI site you can add the PowerPivot App.
i have created as Excel type.
Remember to add the library location to the trusted Excel Library locations if it is not already in there. this is done through Central Admin, Application Management, Manage Service Applications, Manage Excel Services.
Thanks for reading. to follow SSRS in SharePoint integrated mode.