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."
"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
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!
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
Thank you for sharing!! Problem is solved!
Can you refresh external data (from SQL Server) in a workbook (without data model) when using OWA for viewing and editing? The workbook is configured to use a specific target application ID set up in SSS.
Great article, do you know if we can change these settings for SharePoint online (Office365)?I would like to use the "Excel Web Access" web part. However, this is producing the exact error mentioned in this article. It is using the Office Web Apps Server view mode.Any clarification would be greatly appreciated.
I am using Odata data feed connection and creating a pivot report but getting this error. We cannot locate a server to load the workbook Data Model. Please suggest what is wrong...or where i need to look to correct this...Thanks
Thanks. I've been troubleshooting this for the past few days and it's nice to finally have it working.
Hi Eric, Could you tell us how did you get this working
I have the same issue with sharepoint online.
Do you have find a solution ?
Thank you a lot, problem sovled
Wei: You can refresh certain workbooks when using OWA. Please read the following articles for details to see what is supported:
Nico: To use Excel Web Access in SharePoint Online (Office365), you need PowerBI:
thanks problem solved