Applies to: SQL Server 2014 | SQL Server 2012 | SQL Server 2008 R2
This article contains troubleshooting tips and techniques for working through data refresh issues with PowerPivot for 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.
The following sample PowerShell command will look for ULS events from the last 48 hours from the category "data refresh"
Get-SPLogEvent -starttime(get-date).addhours(-48) | Where-Object {$_.category -eq "data refresh" -and $_.level -eq "high"} | select timestamp, area, category, eventid, level, correlation, message
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:
Workaround: Review the following possible causes or actions you can perform:
The Excel Services Application is not configured to use an Analysis Services server 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.
Issue: PowerPivot Data Refresh error with interactive Report Sheets (Power View)
Issue: Power Pivot data refresh fails due to unsupported features in the workbook
Error: Data Refresh jobs fail with a message similar to " The data refresh job failed because it has been inactive for more than 3600 seconds"
See The data refresh job failed because it has been inactive for more than 3600 seconds
Error: “The data connection uses Windows authentication and user credentials could not be delegated”.
For resolution, see this post from Lee Graber on the PowerPivot Twins web site. In addition, here is the link to the official error message help topic; it’s not as detailed as Lee’s post, but that can sometimes be a good thing.
"OLE DB or ODBC error: Login failed for user '<username>'; 28000; Cannot open database "<databasename>" requested by the login. The login failed.; 42000. A connection could not be made to the data source with the DataSourceID of '<datasourceidentifier>', name of "<datasourcename>". An error occurred while processing the "<tablename>" table. The current operation was cancelled because another operation in the transaction failed."
This error appears in the data refresh history page when the server could not connect to external data sources on remote servers. Either the account used to run data refresh, or the data source credentials specified in the schedule does not have read permissions on the data source. To resolve this issue, grant the account read permissions and retry data refresh.
This error indicates that the server is already processing the maximum number of data refresh jobs and it won’t attempt to process anymore until one or more jobs are finished. To determine what the maximum number of data refresh jobs are for your system (and whether you can increase that value), see http://technet.microsoft.com/en-gb/library/ee637268(SQL.110).aspx.
The data refresh job is trying to use a target application that provides credentials for a data source connection string. This might be the target application you specified in the data sources section of the schedule page. Or, if you did not specify credentials in the data source page, it is the target application used to run the data refresh job, which is also used to access external data if the connection string includes Integrated Security=SSPI. For more information about how Secure Store Service target applications are used in PowerPivot data refresh, see Enable and Configure Data Refresh
See this KB article for a workaround to this error.
Error: "Credentials were not found for the current user within the target application <appname>. Please set the credentials for the current user".
See this WIKI page for a workaround: http://social.technet.microsoft.com/wiki/contents/articles/3979.aspx
This error occurs when another SharePoint user who is not the schedule owner (i.e., the person who created the schedule) checks out and revises, or uploads, the workbook. A data refresh schedule that was previously created for the workbook is now disabled. This behavior is by design. It ensures that credentials originally entered by the schedule owner are not used to run or refresh a workbook that he or she did not personally upload. If you get this error, and you have Contribute permissions on the workbook, you can setup a new schedule. Otherwise, you will need to contact the schedule owner and ask him or her to recreate the schedule.
Errors in the high-level relational engine. The following exception occurred while the managed IDbConnection interface was being used:The network path was not found. ; The network path was not found. The network path was not found. A connection could not be made to the data source with the DataSourceID of '82f49f39-45aa-4d61-a15c-90ebbc5656d',Name of 'DataFeed testingreport'. An error occurred while processing the 'Testing Report' table. The operation has been canceled.
How to solve this?
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 username 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.