Resources For IT Professionals
Post an article
Wikis - Page Details
  • First published by
  • When: 18 Jul 2011 1:00 PM
  • Last revision by (eMicrosoft Partne)
  • When: 30 Jul 2013 7:11 AM
  • Revisions: 34
  • Comments: 14
Options
RSSSubscribe to Article (RSS)
Can You Improve This Article?
Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.
Wiki  >  TechNet Articles  >  Troubleshooting PowerPivot Data Refresh

Troubleshooting PowerPivot Data Refresh

Troubleshooting PowerPivot Data Refresh

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.

Table of Contents

 

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.

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, 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 it’s password are 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 a Analysis Services sever 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 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. 

 

 Issue: PowerPivot Data Refresh error with Interactive Report Sheets (Power View)
 PowerPivot Data Refresh error with Interactive Report Sheets (Power View)

 


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 any more 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 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.

"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 are 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 cancelled. 
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.).

Problems related to data retrieval

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.


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. 


Links to other web pages about PowerPivot data refresh

, , , , , , , , , , [Edit tags]
Leave a Comment
  • Please add 7 and 5 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • 18 Oct 2012 12:27 PM

    Craig Guyer - MSFT edited Revision 26. Comment: adding more structure for H3  

  • 10 Oct 2012 3:10 PM

    Richard Mueller edited Revision 24. Comment: Clean up <a name> tags in HTML

  • 5 Oct 2012 12:38 PM

    Craig Guyer - MSFT edited Revision 20. Comment: removed the old manual toc

  • 5 Oct 2012 12:37 PM

    Craig Guyer - MSFT edited Revision 19. Comment: adding [toc]

  • 5 Oct 2012 12:33 PM

    Craig Guyer - MSFT edited Revision 18. Comment: added section for sharepoint 2013

  • 29 Sep 2011 7:22 PM

    Ed Price - MSFT edited Revision 8. Comment: Great article! Added tags

  • 20 Jul 2011 10:06 AM

    Heidi Steen - SQLUE edited Revision 4. Comment: more formatting bugs

  • 20 Jul 2011 10:00 AM

    Heidi Steen - SQLUE edited Revision 2. Comment: formatting bugs

  • 18 Jul 2011 1:02 PM

    Heidi Steen - SQLUE edited Original. Comment: fixed formatting problems

Page 1 of 1 (9 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Posted by
    on 18 Jul 2011 1:02 PM

    Heidi Steen - SQLUE edited Original. Comment: fixed formatting problems

  • Posted by
    on 20 Jul 2011 10:00 AM

    Heidi Steen - SQLUE edited Revision 2. Comment: formatting bugs

  • Posted by
    on 20 Jul 2011 10:06 AM

    Heidi Steen - SQLUE edited Revision 4. Comment: more formatting bugs

  • Posted by
    on 29 Sep 2011 7:22 PM

    Ed Price - MSFT edited Revision 8. Comment: Great article! Added tags

  • Posted by
    on 30 Jul 2012 11:46 AM

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

  • Posted by
    on 30 Jul 2012 2:08 PM

    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

  • Posted by
    on 5 Oct 2012 12:33 PM

    Craig Guyer - MSFT edited Revision 18. Comment: added section for sharepoint 2013

  • Posted by
    on 5 Oct 2012 12:37 PM

    Craig Guyer - MSFT edited Revision 19. Comment: adding [toc]

  • Posted by
    on 5 Oct 2012 12:38 PM

    Craig Guyer - MSFT edited Revision 20. Comment: removed the old manual toc

  • Posted by
    on 10 Oct 2012 3:10 PM

    Richard Mueller edited Revision 24. Comment: Clean up <a name> tags in HTML

  • Posted by
    on 18 Oct 2012 12:27 PM

    Craig Guyer - MSFT edited Revision 26. Comment: adding more structure for H3  

  • Posted by
    on 15 May 2013 2:19 AM

    Carsten Siemens edited Revision 28. Comment: Fixed typo and added tag: has TOC, has comment

  • Posted by
    on 23 May 2013 1:14 PM

    Craig Guyer - MSFT edited Revision 29. Comment: deleted broken link to blog

  • Posted by
    on 4 Jun 2013 1:17 PM

    Craig Guyer - MSFT edited Revision 30. Comment: adding link to new topic and updating some of the formatting

Page 1 of 1 (14 items)