SharePoint: PowerPivot Installation How-To

The first question you have to ask yourself as a new SharePoint administrator in this situation is “What the hell is PowerPivot?” Is it a database? Is it an SQL Server instance? Is it a web application? Is it a service application? The answer to all of those questions is, unfortunately, yes. This makes it sort of complicated.

This all started because we need PowerPivot to chug data for one of the projects I’m involved in. So, of course, it needs to be installed on our farm. Easier said than done.

There was a lot of questions revolving around being able to install PowerPivot on a separate application server in the application layer of the SharePoint architecture. After some poking around, I found some documentation on the subject confirming that it is indeed possible: PowerPivot – Existing Farm Installation (Document)
That’s a handy document to have.

The next step was actually confirming that we could do the installation in an existing farm format. This is where it became truly trying of my patience. The first step is to obtain the SQL Server 2008 R2 media. On the machine you’re going to install PowerPivot, insert the media and launch the SQL installation script. The installation process itself takes forever as the script will run multiple passes of multiple checks to make sure everything is in place to proceed. It is, however, not fool-proof. This means you typically have to wait between 10 and 20 minutes to find out it failed. I’ve been through all of that many times now, so I’ll spare you the headache.

Here are the things that you need to do before running the installation:

1. Make sure the account you are doing the installation with is the original farm administrator account. It will not work with just any account in the farm administrator’s group. (One of the SQL check phases will fail.)
2. If you have already installed SharePoint 2010 (which is most likely the case), you will have also already run the pre-requisites installer. This will screw up the SharePoint/PowerPivot integration assembly. Follow the workaround detailed here: http://support.microsoft.com/kb/2261507
   You will be using Method 2. (I had to use Method 1.)
3. Run the SQL installer.  You will be adding new features.  Choose PowerPivot for SharePoint.  At this point you have to choose between installing in a New Configuration or an Existing Farm.  In order for New Configuration to work, you need to have done the initial SharePoint 2010 installation, but not run the configuration wizard.  If you have a SharePoint_config database already, you are an Existing Farm.
4. Wait.
5. Once it completes (preferably with no errors), you will need to create a PowerPivot service application before configuring PowerPivot on your farm.