The LandPhil be honest, be honorable, be kind, be compassionate, and work hard.

June 6, 2011

SharePoint: PowerPivot gotcha

Filed under: PowerPivot,SharePoint — phil @ 9:42 am

PowerPivot is an interesting beast. If you don’t know what it is, I’ll give you a very brief (and probably incorrect overview): PowerPivot is an Excel plugin, provided free from Microsoft, that allows you to import data from external sources into your very own localized cube for analysis. It’s very, very fast AND very, very small (bordering on the impossible as far as compression goes.) However, it makes your Excel workbooks pretty big (still much smaller than the amount of data you can have, but big enough that sending it to someone via email is pretty much out of the question.) Enter PowerPivot for SharePoint. What this does is create a dedicated analysis services engine on your application layer and when you upload a workbook with powerpivot data, that data is separated out into the analysis services engine. Thus allowing anyone viewing to see the fruits of your labor without needing to install Excel and PowerPivot on their local desktop. There’s a catch, of course, there always is.

Installing PowerPivot on your SharePoint farm is no easy feat. Well, Microsoft claims its an easy feat, but only sort of, and only if you install PowerPivot and your farm at the same time. Most people don’t do that. In fact, no one should do that. In fact, if you’re thinking of doing that just stop now and redesign your farm. But that’s a different discussion. You want to install PowerPivot. And you want to install it on your existing farm. Good news: There’s lots of documentation. (How to: Install PowerPivot for SharePoint on an Existing SharePoint Server)

Here’s the gotcha. You’re going to read through all of that documentation. And you’re going to install the analysis services engine off of the SQL CD. And you’re going to configure a PowerPivot service application in SharePoint. And you’re going to create an unattended refresh account in the Secure Store.

And you will have a 50/50 chance that this will all work just fine. Here’s the rundown: If you installed your farm with default settings, and ran the configuration wizard with default settings, and installed SQL with default settings, it might work. If you, like most everyone else, used service accounts instead of the “local service” and the “network service”, then there is a fairly good chance you did all of the above and missed a critical step.

Let me first explain how the services work together. First off, Excel Calculation Services is what displays your workbook in the browser. In order for Excel Calculation Services to use the slicers against your PowerPivot data, it needs to be able to communicate with the analysis services cube on the application server. This is the unattended refresh account inside the Excel Calculation Services service application settings. In order for the analysis services engine to refresh the cube data from the original external source, it needs a separate account. This is the unattended refresh account inside the PowerPivot service application settings. Now, you’ve configured those accounts if you read the information from Microsoft above. But you get the dreaded error: “The data connection uses Windows Authentication and user credentials could not be delegated. The following connections failed to refresh: (name of connection in workbook for powerpivot cube).”

Here’s what you need to know: The account that is running “Claims to Windows Token Service” needs two more permissions. One is it needs to be a local administrator on the machine that has the PowerPivot analysis engine installed, and the second is that that account also needs the permission “Act as part of the operating system” that can be found in the Local Security Policy -> Local Policies -> User Rights Assignment. These are changes that need to be made to the server, not to SharePoint or SQL.

As it turns out, you can find that information here: (It’s under Community Content, if you, like me, read the article and were still frustrated.)

January 11, 2011

SharePoint: PowerPivot Installation How-To

Filed under: PowerPivot,SharePoint — phil @ 12:33 pm

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

Powered by WordPress