SharePoint 2013 – Configure PowerPivot for SharePoint

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).

image

So we select “Perform a new installation of SQL Server 2012.

Add the PowerPivot feature.

image

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.

image

Here we see the new instance name and configuration.

image

Here I have used a domain account for this service.

image

I have added the service account and farm admin accounts to the admins role for this instance.

image

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.  Also add the SPUnattended account, this has been added after the screen shot above.  Then grant the unattended account datareader access on the PowerPivot database once the application has been created.

image

Reference
Technet article for PowerPivot

http://technet.microsoft.com/en-us/library/jj820150.aspx

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.

image

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.

image

Press ok on this screen and the tool will validate the environment.

Enter the account information and hit Validate.

image

Accept the notice and click Yes

image

Wait for this to complete.

image

You should now see the PowerPivot application available in the configurable applications in Central Admin, Application Management, Manage Service Applications.

image

Checking Installation

Here we see screen shots of the deployed applications, SQL databases and application service.

Central Admin, system Settings, Farm Solutions

image

SQL Database with default GUID Naming

image

Default Application Service, Central Admin, Application Management, Manage Service Applications

image

 

Personally I would delete this application by clicking the top element and clicking Delete.  Accept notification and check the box.

image

This gets rid of the database also.

Then create the PowerPivot App again form Central Admin , Application Management, Manage Service Applications, New..

image

Specify a name and select the BI App Pool

image

Change the database name

image

Leave rest as Default and press OK.

image

All Done.

We now have an appropriately named PowerPivot service and database name.

image

now from your BI site you can add the PowerPivot App.

image

i have created as Excel type.

image

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.

Advertisements

About Mitesh Chauhan
Mitesh Chauhan, Azure Cloud Solutions Architect. This is my blog where I share articles and thoughts on IT Infrastructure and architecture. The topics I am most passionate about are Implementation and architecture of rock solid Cloud Infrastructure based around SQL Server and Windows Server mainly using Microsoft Azure. MCTS - Azure Architecture MCTS - Azure Implementation MCSE Server Infrastructure (Windows Server 2012) , MCITP SQL Server 2008, Togaf Certified, Prince 2 Practitioner.

One Response to SharePoint 2013 – Configure PowerPivot for SharePoint

  1. Pingback: SP 2013 – Configure PowerPivot for SharePoint - IT Consulting Solutions Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: