We have been seeing Excel Services/PowerPivot data refresh issues using SharePoint 2013 and Office Web Apps (OWA).
Some customers have been running into issues when attempting to refresh data in a PowerPivot workbook on SharePoint 2013 using OWA server, and are seeing errors similar to the following:
"PivotTable Operation Failed An error occurred while working on the Data Model in the workbook."
or
"External Data Refresh FailedAn error occurred while working on the Data Model in the workbook. Please try again.We were unable to refresh one or more data connections in this workbook. The following connections failed to refresh:<Connection>"
First, a little background information on SharePoint 2013 and Office Web Apps:
When SharePoint 2013 is configured to use Office Web Apps (OWA), by default, Excel files stored on SharePoint are viewed in WOPI frames and can be edited via OWA. However, when the SharePoint farm has been configured to use Excel Web Apps, the features available in Excel Services and Power Pivot will depend on how the Excel Web App server has been configured.
Excel Web App runs in one of two modes:
We can see that the xlviewer.aspx is invoked to view the workbook.
We can see that the WOPIFrame.aspx is invoked to view the workbook. We can also see that Web Apps is rendering the workbook at the top of the browser window. (See the screenshots above.)
Please see the following for an in-depth overview of the BI features in Excel Services available by each mode:
Overview of Excel Services in SharePoint Server 2013http://technet.microsoft.com/en-us/library/ee424405.aspx
When OWA Server view mode is used to view workbooks, the following BI features will not be available.
We can use filters and slicers for PowerPivot workbooks if we suppress OWA from handling the .xlsx file type and force SharePoint to use SharePoint view mode. We can do that by running the following command via PowerShell on the SharePoint farm:
New-SPWOPISuppressionSetting -extension xlsx -action view
New-SPWOPISuppressionSettinghttp://technet.microsoft.com/en-us/library/jj219443
Once the suppression setting is applied to the farm, we can now work with slicers and refresh data. We can still also edit the document in the browser with OWA!
Additional Resources:
Deploy Office Web Apps Server: http://technet.microsoft.com/en-us/library/jj219455.aspx
Configure SharePoint 2013 to use Office Web Appshttp://technet.microsoft.com/en-us/library/ff431687.aspx Overview of Office Web Apps and how they work on-premises with harePoint 2013http://technet.microsoft.com/en-us/library/ff431685.aspx
Issue:
==========
Unable to render a workbook in the browser:
Unable to process the request - Wait a few minutes and try performing this operation again"
Cause:
According to the ULS logs, you are exceeding you “Maximum Private Bytes” for some unknown reason. The ULS logs don’t tell me enough. I assume “something” is causing the w3wp.exe process to grow large. Potentially a user is opening a corrupt workbook in Excel Services, and this is causing a memory leak. If you find that something, then you have resolved this issue. If you can't find it, you will want to schedule an application pool recycle after hours (see resolution).
47:24.3 w3wp.exe (0x3938) 0x4838 Excel Services Application Excel Calculation Services eca4 High ExcelService.CheckIfRequestCanBeProcessed: Memory Manager does not have available resources to execute the OpenWorkbook request f01f8b3c-c8be-460d-8313-9ae6f0e5ab19
47:24.3 w3wp.exe (0x3938) 0x4838 Excel Services Application Session 5255 Critical Maximum Private Bytes size exceeded. Unable to complete the operation. [Session: User: 0#.w|wellmark\iae612] f01f8b3c-c8be-460d-8313-9ae6f0e5ab19
47:24.4 w3wp.exe (0x0124) 0x4DD0 Excel Services Application Web Front End abho High ServerSession.GetNextServerHealthBased: There are no healthy servers although the number of available server in the farm is {1}; updated with no candidates available for the health based load balancing. The update duration was {0} millisecs f01f8b3c-c8be-460d-8313-9ae6f0e5ab19
Resolution:
Rebooting the server will remedy this, but that is not realistic.
You can run IISReset /noforce that too should remedy the issue.
To avoid a complete IISReset, you can manually recycle the Application Pool Running Excel Services or you can set the Excel Services Application Pool to recycle once daily, like at 2am (instructions below).
Here are instructions on how to recycle the application pool running Excel Services.
In IIS, you will need to expand “SharePoint Web Services” (make sure you are using “Content View”). You will need to click on each GUID under “SharePoint Web Services” until you see the “ExcelService.asmx”, once you find this (special note, if you have multiple “Excel Services Service Applications”, there will be multiple GUIDs with the ExcelServices.asmx under it, you will need to either delete one of the “Excel Services Service Applications” or set both to Recycle), click on “Advanced Settings…”. Here you will see the “Application Pool” once you find the GUID (in this case 9bf574c7cfc04e7e9a8595ec2e887fb8), you will know which Application Pool to Recycle.
Go to Application Pools > Right Click on 9bf574c7cfc04e7e9a8595ec2e887fb8 > Advanced Settings…
You can set the time you want to Recycle under Recycling > Specific Times