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