An error occurred while working on a data model (Interactive PowerPivot data refresh error)

  • Artykuł

 An error occurred while working on a data model - Interactive PowerPivot data refresh error using authenticated user’s account

We recently came across a tricky issue with a manual (interactive) PowerPivot data refresh in an environment where Kerberos is configured. We are running SharePoint 2013 with a SQL Server 2014 backend. Having looked at many different articles and blogs, I decided to compile all information I found online in one short blog. You will get LOTS of hits online if you search this topic (and I've listed the most relevant blogs/articles) at the bottom in "References" section, but only ONE blog provided the exact resolution we were looking for.

Error on a manual refresh of an Excel workbook:

"An error occurred while working on the Data Model in the workbook. Please try again". That's the error being thrown when you try to do an interactive data refresh in an Excel Services Web App (not OWA, Excel Services Web App, OWA doesn't support PowerPivot data refresh, see "References")

Error on Refresh

This is a very generic error that is thrown when you open the workbook in the browser and go to Data tab-->Refresh All Connections:

Untitled picture

This blog will help resolve your issue if:

  1. You have Kerberos setup in your environment.
  2. You created the workbook using Excel 2013 (requirement for a manual data refresh!)
  3. You're using the identity of the currently logged-on user as credentials to connect to the data source. Check Excel Services Authentication Settings defined for the workbook to make sure this is true.
  4. You are seeing this error in the Event log of the Application server:  Source: "MSOLAP$POWERPIVOT" -  Message:"OLE DB or ODBC error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.; 28000."
  5. You are viewing the workbook in an Excel Web App (not OWA) -

https://mysharepoint2013/_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

Solution:

If you answered "Yes" to all of the above, follow this link to get your resolution to the issue:

https://blogs.technet.microsoft.com/excel_services__powerpivot_for_sharepoint_support_blog/2013/02/01/powerpivot-2013-for-sharepoint-external-data-refresh-failed-using-interactive-data-refresh-via-use-the-authenticated-users-account/

Go through all of the configuration steps described in the article. After checking off every item, we discovered that our issue was the account running "SQL Server Analysis Services (PowerPivot)" did not have constrained delegation setup for Kerberos. That step is described in #7 in the article. We ended up changing the account running "SQL Server Analysis Services (PowerPivot)" to a different account that had constrained delegation setup. Sure enough, the data refresh was successful after this change. If you need help creating SPN and setting constrained delegation for the account running "SQL Server Analysis Services (PowerPivot), follow #7 in the blog.

References: https://www.sandeepnakarmi.com/2015/03/external-data-refresh-failed-error-for-powerpivotexcel-services/ https://blogs.technet.microsoft.com/excel_services__powerpivot_for_sharepoint_support_blog/2013/01/31/powerpivot-for-sharepoint-browser-refresh-fails-data-refresh-not-supported-in-office-web-apps/ https://social.technet.microsoft.com/Forums/office/en-US/823e9b4b-32c4-426b-a9f6-40743793f16b/manual-data-refresh-in-powerpivot-does-not-work-in-sharepoint-2013?forum=sharepointgeneral https://social.technet.microsoft.com/wiki/contents/articles/3870.powerpivot-troubleshooting-data-refresh.aspx https://msdn.microsoft.com/en-us/library/jj879294.aspx - Power Pivot Data Refresh with SharePoint 2013

https://technet.microsoft.com/en-us/library/hh369968(v=office.14).aspx - Excel Services data refresh flowchart

https://blogs.technet.microsoft.com/excel_services__powerpivot_for_sharepoint_support_blog/