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 Failed
An 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:
- SharePoint view mode - Excel services is used to view workbooks in the browser. For example, you will see a URL similar to the following: http://sharepoint/_layouts/15/xlviewer.aspx?id=http%3a%2f%2fsharepoint%2fPP%2fLCA%2520BI%2520-%2520Financial%2520Report%2520Usage.xlsx&Range='Financial+Report+Usage+Tracking'!A1&DefaultItemOpen=1
We can see that the xlviewer.aspx is invoked to view the workbook.
- Office Web Apps Server view mode: Excel Web Apps is used to view workbooks in the browser. For example, you will see a URL similar to the following: http://sharepoint/_layouts/15/WopiFrame.aspx?sourcedoc=%2FPP%2FLCA%20BI%20%2D%20Financial%20Report%20Usage%2Exlsx&action=view
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 2013
http://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.
- Excel Web Access Web Part
- Refresh OData connections
- View and interact with Power View reports
- View and interact with PowerPivot data models
- Refresh PowerPivot data models
- Refresh data by using the Excel Services unattended service account
- Refresh data by using Effective User Name connections
- Kerberos delegation
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-SPWOPISuppressionSetting
http://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 Apps
http://technet.microsoft.com/en-us/library/ff431687.aspx
Overview of Office Web Apps and how they work on-premises with harePoint 2013
http://technet.microsoft.com/en-us/library/ff431685.aspx
How to Restore : Remove-SPWOPISuppressionSetting -Extension "XLSX" -Action "view"
'■ SharePoint' 카테고리의 다른 글
| Document Lock (0) | 2018.05.10 |
|---|---|
| sharepoint 2013 Management Shell delete list item (0) | 2017.05.26 |
| 사용자의 Manager 자동으로 가져오기 (0) | 2017.01.27 |
| Workflow Manager 1.0 offline install (0) | 2017.01.14 |
| Manually Install Microsoft App Fabric Prerequisite for SharePoint 2013 (0) | 2016.11.23 |