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: http://msdn.microsoft.com/en-us/library/ff487975.aspx (It’s under Community Content, if you, like me, read the article and were still frustrated.)