Share via

PowerPivot Troubleshooting: Data Refresh

  • Article

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.

Where to get information about data refresh failures

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

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. 

What to check for first

  • Check the PowerPivot Data Refresh timer job in Central Administration to ensure it is running. It should be configured to run every minute.
  • If data refresh is failing across the board, check the following accounts: PowerPivot service application (the service will be stopped in SharePoint), the Analysis Services instance (the service will be stopped). If data refresh is failing for just some workbooks, check the account used to run data refresh (unattended data refresh account, user account, other stored account), and then check the service account of the remote database servers that provide the data.
  • Verify the account used to run the data refresh job has Contribute permission on the workbook. This account is specified in the Credentials section of the data refresh scheduling page. It will be one of the following: unattended data refresh account, an account of the person who set up the schedule, an account associated with a target application in Secure Store Service.
  • Verify the account used to run the data refresh job has read permissions on the data source.
  • Verify the account is the same domain or a trusted domain, and that neither the account nor its password is expired.
  • Verify the account password has not expired.

Data Refresh Errors with SQL Server PowerPivot for SharePoint 2013

Issue: Cannot Locate a server to load a workbook.

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.” For example, you navigate to the document library containing the workbooks. You open the context menu of a workbook and click Manage PowerPivot Data Refresh.

 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.

  • 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

 

Data Refresh Error with Previous versions of PowerPivot

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.

Error: "OLE DB or ODBC error: Login failed for user '<username>'; 28000; Cannot open database

Full error message:

"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.

 

Error: “The service is running ‘MAXCONCURRENTJOBS’ and will not look for any more at this point.”

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.

 

“Target application not found.”

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

 

"The provided Secure Store target application is either incorrectly configured or does not exist".

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

 

"The data refresh schedule is disabled because another user has updated the PowerPivot workbook"

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?

 

Problems using the Unattended data refresh account

You configured the PowerPivot unattended data refresh account, but now data refresh is failing. Check for these possible conditions:

  • Choosing the wrong credentials option in the schedule data refresh page. Credentials for data refresh are specified when you set up the data refresh schedule. To use the unattended data refresh account, you must choose the first option in the schedule page. You cannot choose the third option (entering a target application ID of the PowerPivot unattended data refresh account). The third option has different permission and target application ID account requirements. If you try to use the third option for the unattended data refresh account, you will get "The provided Secure Store target application is either incorrectly configured or does not exist".
  • Insufficient permissions on remote computers or data sources. The “PowerPivot unattended data refresh account” is actually a Secure Store Service target application that stores a Windows domain user account and password. The Windows domain user account that is stored in the target application must have read permissions on the data, and it must have Contribute permissions on the workbook in SharePoint. The account and password must be valid (same domain, not expired, etc.).

 

Call to Excel Services returned an error

If on the PowerPivot Data Refresh History page you see an error message similar to "Call to Excel Services returned an error", verify the location of the data source, for example, an Excel workbook is registered as a trusted location with Excel Services. For more information, see the following:

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.

Performance problems

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.