As part of the SharePoint 2013 reporting services, I was trying to render SSAS cube data in SharePoint BI (Business Intelligence) site library. But, then click on “Refresh All Connections” to fetch the latest data from SSAS cube I was experiencing below issue:
powerpivot data refresh error
Diagnosis this issue:
To know the issue in Excel Services we need to enable “Diagnostic Logging” which will log the connection/authentication as a part log file.
Steps to enable Diagnostic Logging:
1. Log on to WFE with FARM account
2. Launch Central administration
3. Navigate to Diagnostic Logging
Central Administration -> Monitoring -> Configure Diagnostic Logging
powerpivot data refresh error access is denied
4. Select “Excel Services Application” as part of Diagnostics Logging page and click “OK” at the end of the page.
sharepoint 2013 powerpivot data refresh error
5. By enabling this SharePoint will log the actual error message in log file as part of 15 Hives, the default path for log files is:
%CommonProgramFiles%\Microsoft Shared\Web Server Extensions\15\LOGS\
6. Soon you enable diagnostics logging from client/user machine “refresh all connections” in excel (as shown in picture-1)
Note: This will give the same error but SharePoint will log the detailed error in log file
7. Navigate to log folder and open the latest log file and search for “Excel Services Application External Data” when I do it I found the below error message.
05/14/2018 18:07:55.84 w3wp.exe (0x37BC) 0x552C Excel Services Application External Data ai2vl Information The workbook ‘http://SharePoint13:9535/PowerPivot Gallery/SaleOrders.xlsx’ attempted to access external data using the unsupported provider ‘Provider=MSOLAP.7;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=SaleOrders;Data Source=SharePoint13\tab;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update Isolation Level=2’. [Session: 1.V23.108d20qHa37P9g/cpouEEz514.5.en-US5.en-US36.94632022-8f93-4af2-9a9a-fd10c6df64721.A1.N User: 0#.w|sharepoint13\spadmin] f291659e-4995-e0b2-9f14-af19942f9e12
Error message clearly says that “attempted to access external data using the unsupported provider ‘Provider=MSOLAP.7”
As the error message shows that excel services using a unsupported provider we need to ensure that, MSOLAP.7 provider is part of trusted data providers.
Follow the below steps to add the new trusted data provider:
1. Log on to WFE with FARM admin account
2. Open “Excel service application” as part of “Manage Service application”.
Central Administration -> Application Management -> Service Applications -> Manage Service Applications
powerpivot pivot table not refreshing
manage powerpivot data refresh sharepoint 2013 error
3. Select “Trusted Data Provider” to add the new provider
sharepoint 2016 powerpivot data refresh error
4. Validate the list of trusted providers for “MSOLAP.7” and then click on “Add Trusted Data Provider”
manage powerpivot data refresh sharepoint 2013 error Tutorial
5. Enter the provider details as shown below and click on “OK” button
sharepoint 2016 powerpivot data refresh error Tutorial
6. Once the provider is added go back to user/client machine it will work seamlessly.
Hope this helps happy BI implementation in SharePoint 2013.
Download SharePoint Online Tutorial PDF FREE!
Get update on Webinars, video tutorials, training courses etc.
I am Krishna.Vandanapu a SharePoint architect working in IT from last 13+ years, I worked in SharePoint 2007, 2010, 2013, 2016 and Office 365. I have extensive hands on experience in customizing SharePoint sites from end to end. Expertise in SharePoint migration tools like Sharegate, Doc Ave and Metalogix. Migrated SharePoint sites from SharePoint 2007 to 2010 and 2010 to 2013 several times seamlessly. Implementing CSOM with Microsoft best practices. Spent quality time in configuring SharePoint application services like User Profile, Search, Managed Meta data services etc. Now exploring SharePoint Framework and SharePoint 2019
Please log in again.
The login page will open in a new tab. After logging in you can close it and return to this page.
Sign up to EnjoySharePoint.com newsletter
Download SharePoint Online FREE Ebook
Bonus you will get:
-> FREE SharePoint Training Updates
-> Get Update on SharePoint FREE webinar -> FREE Video Tutorial update -> Download latest FREE PDF tutorials (90 pages) and many more
Your FREESharePoint Online Ebook is on its way to your Email Inbox. **************** You will get notification on FREE SharePoint online training, SharePoint webinars, Video Tutorial & also latest FREE PDF tutorials