If you’re trying to explore one of the cool new integrations in SharePoint 2010 BI stack, PowerPivot, you might run into the problem where your PowerPivot workbooks will return the following error, when you try clicking on the slicer in your excel sheet in attempt to filter the data:
Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connections failed to refresh Data
Now, the first you might think – that the issue is related to the data refresh, and there is plenty on the internet related to the data refresh. The error is actually related to the PowerPivot not being able to access the data. The refresh capability is the ability to refresh workbooks automatically and has nothing to do with this error, in fact it will mislead you trying to troubleshoot the wrong issue.
If your SharePoint is running in a multiserver environment where your database is sitting somewhere else and your web front end sitting on another machine you might think that authentication has failed.
You may even think you need to install Kerberos, however …
The PowerPivot system is claims aware, and uses the Claims To Windows Token Service to recreate the client’s Windows identity using the client’s claims token in order to connect with the Analysis Service engine that runs on the application server.
Also Microsoft tells us:
When a PowerPivot workbook is uploaded in SharePoint Server, it already contains the PowerPivot data that the workbook uses. When the user opens the PowerPivot workbook in Excel Web Access and interacts with the slicers, the PowerPivot System Service loads the data in the workbook directly into its Analysis Services engine. No access is made to the data connection embedded in the workbook.
However, if your PowerPivot workbook contains a data connection to a SQL Server instance that is linked to another SQL Server instance on a separate machine, you’ll need to configure Kerberos authentication with identity delegation for data refresh your data.
Check out how to configure Kerberos in this scenario using the doc here.
If you fall under the scenario where you don’t have distributed data stored on multiple servers and used in your PowerPivot workbook, then ensure the following:
You have installed SQL Server Analysis Services (PowerPivot) on your web front end machine.
When you navigate to Central Admin -> Services on this server, in the list of services on your web front end machine you see SQL Server Analysis Services (PowerPivot) is a started state.
If you don’t see it at all on the web front end server, you must install SQL Server Analysis Services (PowerPivot).
If you see it in the stopped state, try starting it again and refresh the page; if the service stops shortly after, the account which runs the service might have an expired password.
A simple way to verify that out of the box configuration works, navigate to Central Admin -> Manage Service Applications -> PowerPivot Service Application and ensure that PowerPivot Service Application dashboard shows its own system reports.
If you don’t see those, it’s a very high chance you won’t get your custom reports working.
