As part of SharePoint 2013 reporting services, I was trying to render SSAS cube data in SharePoint BI (Business Intelligence) site library. But, when click on “Refresh All Connections” to fetch the latest data from SSAS cube I was experiencing below issue:
Diagnosis this issue:
To know the issue in Excel Services we need to enable “Diagnostic Logging” which will log the connection / authentication as 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
4. Select “Excel Services Application” as part of Diagnostics Logging page and click “OK” at the end of the page.
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
3. Select “Trusted Data Provider” to add the new provider
4. Validate the list of trusted providers for “MSOLAP.7” and then click on “Add Trusted Data Provider”
5. Enter the provider details as shown below and click on “OK” button
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.