The last few days I have spent an exorbitant amount of time (5-6 hours?) getting PowerPivot and Excel Services working on a SharePoint 2010 Farm. I just want to get out there some of the things I had to do to get it all working, and why (at least what I think is why).
First off, most dev setups are using one box for SharePoint, which, in my eyes, masks most every issue you will run into.
Most prod environments are multiple boxes, Web Front Ends, App Servers, etc. This leads to the most pain when setting up these services (PowerPivot, Excel Services) as there are hundreds of different configurations and setups. You need to get it juuuust right.
Anyways, I will explain as best I can.
First, assuming you have PowerPivot running on an app server and Excel Services running to. You upload your PowerPivot workbook to a PowerPivot gallery and you go home happy. But wait, does the data refresh correctly? Can you even open it?
1. If your site is running https, you have to tweak the Excel Services settings.
By default, only http:// shows up here (I think?) so you need to add https:// or you can’t even really get into your PowerPivot.
2. You probably are going to have to up the upload size limit
Some PowerPivot workbooks are big, like 80-100 MB big. Default in SharePoint 2010 for Excel Services is something like 10 MB, and SharePoint default is 50 MB, you need to change for both settings.
3. If you don’t have Kerberos set up, it is tougher.
PowerPivot refreshes data from some system somewhere to its own “cube”, then Excel Services refreshes data from that cube into Excel Web. You have to setup a way for the data to get refreshed into PowerPivot’s cube. No Kerberos? Then you need to use the Secure Store Service and set up a credential, and set that up as the unattended service account for PowerPivot refresh. Then, at least you can get data from somewhere else into PowerPivot’s “cube”
Second step is getting Excel Services to refresh from that cube into Excel web. What I had to end up doing was creating another credential in the Secure Store service for Excel Services Refresh (set up as farm account for now, it has the stroke it needs). And then set that up in Excel Services settings as well for Excel Services unattended refresh account. But also! – you need to change your workbooks before you upload or whatever.
In your workbook, go to your connection, properties, and get to the authentication, and change to “none” instead of “windows authentication”, then your data will refresh from the PowerPivot cube to Excel in SharePoint.
4. Same thing goes for the PowerPivot Management Dashboards
The management dashboards are set to “windows authentication” so they wont work either, you need to change to “none”, in the Management Dashboard site, goto all site content, PowerPivot Management, <some guid> folder, and then 1033 (US English), edit the two workbooks to use “none” and save, and your management dashboard will actually work!
I’m sure there is a ton more I go delve into here, but this is the high level. As Rob (@powerpivotpro) would say – “make sure to click on the slicers!”
Steve Novoselac's Blog 