Excel Services & PowerPivot for SharePoint

Troubleshooting Excel Services and PowerPivot for SharePoint rendering and refreshing in the browser.

PowerPivot for SharePoint - Browser Refresh Fails (Data Refresh not supported in Office Web Apps)

PowerPivot for SharePoint - Browser Refresh Fails (Data Refresh not supported in Office Web Apps)

  • Comments 9
  • Likes

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: 

            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 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

Comments
  • 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.

    Thanks.

  • 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

  • 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

  • Hi Nico,
    I have the same issue with sharepoint online.
    Do you have find a solution ?

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment