[SOLVED] PowerPivot for SharePoint 2010: An error occurred connecting to this data source.

Mar 7, 2012 · 4 min read  ·
Share on:

I'm currently working on a project where I need to quickly make pretty graphs and charts for data in small but important SharePoint lists. After looking through a number of books and consulting with my SharePoint buddies, PowerPivot for SharePoint 2010 seems to be the best tool for the job.

Getting it to work hasn't been easy, I believe because there's so many components that come into play. You've got the PowerPivot add-on for Excel 2010, the PowerPivot service for SharePoint, Excel Services for SharePoint, SQL Server Analysis Services and PerformancePoint Dashboards. Here's how it all started...

  • I was assigned to make pretty charts for a site

  • I installed the Excel + the PowerPivot add-on

  • Exported the SharePoint list

  • Opened the exported list, enabled Data Connections, and made a PowerPivot chart thing

  • Checked to make sure PerformancePoint & Excel Services features were enabled

  • Created a BI site

  • Saved the Excel file to SharePoint

  • Launched Dashboard Designer using the BI page

  • From there, I attempted to access the cube in a PerformancePoint dashboard, per Microsoft's instructions

  • Run Dashboard Designer

  • Create

  • Data Source

  • Analysis Services

  • Use the following connection:  https://sharepoint/Documents/localpivot.xlsx

I left the authentication as default and received the following errors:

  • You do not have permissions to see this data or the server is unavailable. Additional details have been logged for your administrator. Contact the administrator for more details.
  • An error occurred connecting to this data source. Please check the data source for any unsaved changes and click on Test Data Source button to confirm connection to the data source.

Supposedly, installing ASADOMD10 from the SQL Server 2008 R2 Feature Pack would fix the issue, but it didn't. This farm is running on my local workstation, so I check Event Viewer and see: The Unattended Service Account "AD\yo.momma" does not have access to the server specified by the data source connection string. But.. but.. I'm the administrator of this whole thing. I 0wn this farm, what gives? So I check the IIS log:

2012-03-06 18:52:33 127.0.0.1 POST /_vti_bin/client.svc/ProcessQuery - 80 - 127.0.0.1 - 401 2 5 0 2012-03-06 18:52:33 127.0.0.1 POST /_vti_bin/client.svc/ProcessQuery - 80 - 127.0.0.1 - 401 1 2148074254 0 2012-03-06 18:52:33 127.0.0.1 POST /_vti_bin/client.svc/ntlm/ProcessQuery - 80 AD\yo.momma 127.0.0.1 - 200 0 0 343 2012-03-06 18:52:33 127.0.0.1 POST /_vti_bin/PPS/PPSAuthoringService.asmx - 80 - 127.0.0.1 Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+Protocol+2.0.50727.5448) 401 1 2148074254 0 2012-03-06 18:52:33 127.0.0.1 POST /_vti_bin/PPS/PPSAuthoringService.asmx - 80 AD\yo.momma 127.0.0.1 Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+Protocol+2.0.50727.5448) 200 0 0 15 2012-03-06 18:52:33 127.0.0.1 POST /_vti_bin/PPS/PPSAuthoringService.asmx - 80 - 127.0.0.1 Mozilla/4.0+(compatible;+MSIE+6.0;+MS+Web+Services+Client+Protocol+2.0.50727.5448) 401 2 5 0 2012-03-06 18:52:41 172.16.0.125 POST /_vti_bin/PowerPivot/redirector.svc/ DataSource=%2FDocuments%2Flocalpivot.xlsx 80 - 172.16.0.125 ADOMD.NET 401 2 5 0 2012-03-06 18:52:41 172.16.0.125 POST /_vti_bin/PowerPivot/redirector.svc/ DataSource=%2FDocuments%2Flocalpivot.xlsx 80 - 172.16.0.125 ADOMD.NET 401 1 2148074254 0
You can see the myriad 403 Forbidden -- maybe it's some messed up NTLM permissions? So I check /_vti_bin/PowerPivot/ and, voila! The files don't exist.

That's not a Forbidden, that's really a 404 Not Found. I never even installed PowerPivot for SharePoint in the first place. You can enable references to it around SharePoint if I recall, but the service is part of the SQL Server install. Well, damn.

I decided to start entirely from scratch by removing my workstation from the farm, and uninstalling the two versions of SQL Server, Express and Denali CTP, on my workstation. Then I setup a new farm using SQL Server's Installation interface (I didn't even realize I could do that, bad admin!) I won't lie..the install was so clean -- it created the local Central Admin instance as well as a new Site Collection with all the PowerPivot features already enabled. Delicious.

If the above was not your issue, you may need to:

  • Make sure SQL Server PowerPivot is setup as a Service Application: Central Admin -> Manage Service Applications
  • Make sure it's associated with the web app: Centeral Admin -> Application Management -> Configure Service Application Associations -> Application Proxies
  • Make sure the PowerPivot Service has started: Central Admin -> Manage services on server

Also, check your Event Viewer logs, that's been helpful for me.