Applies to: SQL Server 2008 R2 and SQL Server 2012
This article consists of troubleshooting tips and techniques for working through PowerPivot data refresh problems in SharePoint.
PowerPivot data refresh history page of the workbook. In a document library, click Manage Data refresh to open the data refresh history page. See View PowerPivot Data Refresh history.
In Central Administration, look in PowerPivot Management dashboard (admins only). See PowerPivot Management Dashboard.
In Central Administration, look in the health rules and reporting page. Health rules alert you to problems with server configuration or availability.
ULS logs (admins only). See View ULS Log Files. ULS logs will show access denied errors if the PowerPivot System Service cannot connect to the remote server that provides the data. If you are using a ULS Viewer, filter on PowerPivot service in the Area column, Data Refresh in the Category column, and High in the Level column. If you are using Notepad, copy the Correlation ID from the SharePoint error message and use it as a search term in the ULS log.
Secure Store Service audit log. Logging is enabled by default. You should be able to use the audit log to gather information about the success or failure of requests to use a particular target application. For more information, see Configure Secure Store Service (SharePoint 2010).
SQL Server Profiler, connected to the Analysis Services instance that is part of a PowerPivot for SharePoint installation, and then run data refresh. If the failure occurs during data processing (i.e., the external data source no longer contains a table or no longer uses the same table names), you will see the error in the trace.
When you attempt to manage the refresh schedule of a workbook, you see an error message similar to the following: “We cannot locate a server to load the workbook Data Model.”
The Excel Services Application is not configured to use a Analysis Services sever in SharePoint mode. Review the Data Model Settings of the Excel Services application in SharePoint Central Administration.
If your SharePoint environment is configured to use Kerberos, you need to modify the C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\PowerPivot\Web.config file. Change all instances of <transport clientCredentialType="Ntlm"/> to <transport clientCredentialType="Windows"/>.
Verify that the service account used for your Excel Services Application is an Analysis Services administrator.
Review the ULS log file, after you configure the Excel Service Application for verbose logging, in the Diagnostic logging section of SharePoint Central Administration.
Error: “The data connection uses Windows authentication and user credentials could not be delegated”.
You configured the PowerPivot unattended data refresh account, but now data refresh is failing. Check for these possible conditions:
In a data refresh operation, the Analysis Services instance (not the PowerPivot System Service) requests the data from external data sources. Connections are made by the Analysis Services instance on behalf of a user. Analysis Services impersonates the current user who ran the data refresh job (either the PowerPivot unattended account or a Windows user), or it passes credentials on the connection string (but only if the data refresh schedule includes credential information for connecting to external data sources). The user name and account that is passed to the external database server must have a database login on that server.
All data sources that are specified in the schedule must succeed, or the data refresh job will fail. For example, suppose your PowerPivot workbook retrieves data from 3 different data sources but one of them is offline, in this case data refresh will fail, even though the other 2 sources could be accessed successfully.
If data refresh jobs are timing out before they complete, there are some things you can do to increase the capacity of data refresh operations. You can install an additional PowerPivot for SharePoint server in the farm. Alternatively, if your application server has sufficient RAM and multiple CPUs, you can increase the number of data refresh jobs that will run in parallel on the server. The default is calculated based on RAM. The maximum value that you can raise it to is determined by the number of CPUs (i.e., if you have 4 CPUs, you can increase the maximum concurrent data refresh jobs to 4). For more information, see http://technet.microsoft.com/en-gb/library/ee637268(SQL.110).aspx.
Craig Guyer - MSFT edited Revision 26. Comment: adding more structure for H3
Richard Mueller edited Revision 24. Comment: Clean up <a name> tags in HTML
Craig Guyer - MSFT edited Revision 20. Comment: removed the old manual toc
Craig Guyer - MSFT edited Revision 19. Comment: adding [toc]
Craig Guyer - MSFT edited Revision 18. Comment: added section for sharepoint 2013
Ed Price - MSFT edited Revision 8. Comment: Great article! Added tags
Heidi Steen - SQLUE edited Revision 4. Comment: more formatting bugs
Heidi Steen - SQLUE edited Revision 2. Comment: formatting bugs
Heidi Steen - SQLUE edited Original. Comment: fixed formatting problems
I am recieving some error message for the same which is mentioned at social.msdn.microsoft.com/.../24679248-3b08-4b73-95c9-a3da0b12d7d5
Please help me...
I am not getting alert for the error "The data refresh job failed to update the PowerPivot workbook because the file was in use by user"
santosh_sethi@hotmail.com
Carsten Siemens edited Revision 28. Comment: Fixed typo and added tag: has TOC, has comment
Craig Guyer - MSFT edited Revision 29. Comment: deleted broken link to blog
Craig Guyer - MSFT edited Revision 30. Comment: adding link to new topic and updating some of the formatting