PowerPivot for SharePoint 2016 Scheduled Data Refresh Error: “The operation has timed out”


UPDATE: 4/25/17: PowerShell to increase timeout included in workaround section.

Scenario:

You create a PowerPivot workbook with one or more data sources and upload it to SharePoint 2016. You then schedule the workbook to refresh and the refresh fails with the following error: “The operation has timed out“.

Cause:

This issue is caused by a change to the design of the product in SharePoint 2016 that limits a single PowerPivot data source to a refresh duration of 100 seconds (or 1:40). Note that this can also be caused by slow or underperforming data sources if you think your data should be refreshing in under 100 seconds. If you are pulling a small amount or data from a complex data source that takes a long time to query, you may consider alternative workarounds as increasing the timeout may not be the best option.

Symptoms:

  1. If you expand the refresh history, you will notice that the data source of which refresh failed on ran for exactly 1:40 (100 seconds).
    100sfail
  2. You may notice that some of your data sources actually succeed, or that the refresh succeeds intermittently if all of the data sources refresh in less than 100 seconds.
  3. In the SharePoint ULS logs, you will see the actual error reported, but it will not seem to correlate to anything meaningful as far as a cause (because it doesn’t….)
    00/00/2017 14:49:18.44 w3wp.exe (0x00000) 0x59D4 PowerPivot Service Data Refresh 99 High EXCEPTION: System.Net.WebException: The operation has timed out     at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request)     <<truncated stack>>
  4. You will likely see a correlating event in the Application Event logs (EventID: 5214).
  5. If you have multiple workbooks pulling from the same source, and one workbook is slowing the data source down causing the other queries to wait or slow (past the 100s), you may see multiple workbooks fail that do not normally fail.

Potential Workarounds:

  1. Optimize your queries to run faster.
  2. Query less data.
  3. Add hardware to your data source to process queries faster.
  4. Use PowerShell to increase the default timeout setting.
    • From a SharePoint 2016 Administrator enabled PowerShell prompt:
      $farm = Get-SPFarm
      #The timeout value is in milliseconds, so be very careful to not set it too low!!!
      $farm.Properties.Add("WopiProxyRequestTimeout", <new timeout value>);
      $farm.Update();
      #to double check the setting
      $farm.properties
    • To set this to a different value in the future:
      $farm = Get-SPFarm
      $farm.Properties.WopiProxyRequestTimeout = <new timeout value>
      $farm.update()

      Disclaimer: Increase this timeout value at your own risk! We realize that the new default timeout is very low, but you should also be aware of the amount of data that you are pulling vs the time it should take to pull that data. Setting this timeout too high and allowing users to pull large amounts of data can cause performance issues for PowerPivot, SharePoint and Office Online Server. You will also be limited by the default timeouts for SharePoint, SQL and your external data sources. This timeout value may not always be the answer. There are more workarounds and optimization options depending on the type of data you are pulling.

NOTE: This blog applies SPECIFICALLY to the issue listed above and ONLY to PowerPivot for SharePoint 2016 Scheduled Data Refresh. This may not be the only context that you see this error and you could also run into it in other versions of the product (but it will not be for the same reason!!).

Comments (4)

  1. Hi Rick,

    We have same issue and applied above work around, but some how this is not working for us, basically my case also same, such as below 3MB file schedule data refresh working fine, but large files showing error as “The operation has timed out“.

    Can you please let me know if there is any other settings which need to be adjust? I have also posted a question but nobody responded so far. Hope you can help us on this.

    https://social.technet.microsoft.com/Forums/en-US/44441b33-4844-4ed6-9751-c08390e962e7/sharepoint-2016-powerpivot-schedule-data-refresh-error-the-operation-has-timed-out-for-the-file?forum=SP2016

    1. Hello Chandu,
      This timeout message (if it is the exact same issue as described in the blog) is not relevant to the size of the workbook, it is generally related to how long it takes a single data source from within the workbook to refresh. The PowerShell provided is a template to set the timeout to the desired value. Note that the value is in milliseconds, so if you set it too low for those specific workbooks to refresh, they may not function despite your custom setting.

      1. Thank you! Rick.

        I have updated with 180000 milliseconds (1800 seconds) and start working for below 30 MB files, but when I schedule for above 40 MB files I am seeing error as “Call to Excel Services returned an error.” and ULS log error as “ExcelServicesException: We noticed that you haven’t been interacting with this workbook, so we paused your session.”

        Can you please help me, if any settings/properties need to change in SharePoint 2016 farm as well as OOS (office online server) to resolve this issue.

        1. Hello Chandu,
          “Call to Excel Services Returned an Error” is probably the most generic error we see and “ExcelServicesException: We noticed that you haven’t been interacting with this workbook, so we paused your session.” unlikely to be the underlying issue. A couple of quick recommendations would be to ensure that you are reviewing the ULS logs from Office Online Server that correlate to your data refresh along with your ULS logs from SharePoint. You should see the real issue there (and it may not always have a correlationID attached to it). I would also post your question on the OOS TechNet forum (link) or open a support ticket for further assistance. I know from much experience that those errors can be tricky :).

Skip to main content