SharePoint Excel Data Refresh Common Issues

blank

SharePoint Excel Data Refresh – Common Issues

Version: 1.0

SharePoint Excel Services requires different bits and pieces to work correctly in order for it to deliver your BI Reports correctly. There are times when you have to review and update the configurations in order to make it work.

I’ll cover some of my experiences related to issues and fixes in working with the SharePoint Excel Services in this publication.

Common SharePoint Excel Services Issues

Following are the few common issues that can happen with SharePoint Excel Services and the resolution of those issues.

a. Unable to refresh data for a data connection in the workbook. … The following connections failed to refresh

a. Unable to refresh data for a data connection in the workbook. … The following connections failed to refresh

I created a nice report in Microsoft Excel using the SSAS (SQL Server Analysis Services) as the data source. I was able to refresh the data connection within MS Excel file to pull the latest data or apply filters to get the data I wanted. No issue so far and every thing looks great.

MS Excel Report with SSAS Data Connection - Data Refresh Option
MS Excel Report with SSAS Data Connection – Data Refresh Working

I uploaded my report to a Business Intelligence (BI) Center site on SharePoint. I launched the report using Excel Web Services and I was able to see the data. I got the following error when I tried to do the Data connection refresh in an attempt to pull the latest data from SSAS data source.

Error:
Unable to refresh data for a data connection in the workbook. Try again or contact your system administrator. The following connections failed to refresh:
SSASserverName – InstanceName SSAS database ProjectDW TimeReport

SharePoint Excel Service Excel Report with SSAS Data Connection - Data Refresh Error
SharePoint Excel Service Excel Report with SSAS Data Connection – Data Refresh Error

More precisely, here is the what the screen looked like. Also I couldn’t apply the filters and my nice report turned out to be ‘acting not so nicely’.

SharePoint Excel Service Excel Report with SSAS Data Connection - Data Refresh Error Fullpage
SharePoint Excel Service Excel Report with SSAS Data Connection – Data Refresh Error Fullpage

Issue Cause

I started the SharePoint Log Viewer on the server and then launched my Excel report again in Excel Web services to reproduce the error. Paused the live monitoring of the ULS logs and started reviewing the log information. I found the line item in the log pertaining to data refresh issue.

Area: Excel Services Application
Category: External Data
Event ID: 2052
Level: Information

Highlighting the log entry revealed the message detail about it.

The workbook ‘http://sharepoint.domain.com/sites/dept1/team2/deptsolutions/BI/Excel Analysis Reports/SPExcelBIReport_ByFM.xlsx’ attempted to access external data using the unsupported provider ‘Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=SSAS Project Control;Data Source=SSASServerNameInstanceName;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error’. [Session: 1.V21.3V9WQUVIjSGKsFX87MBjr90.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.289039c6-fe3c-4074-ac1f-48520e7f45a41.N User: 0#.w|domainfmasood]

SharePoint LogViewer - Excel Data Refresh error with SSAS Cube - Unsupported Data Provider Used
SharePoint LogViewer – Excel Data Refresh error with SSAS Cube – Unsupported Data Provider Used

Though, above information is sufficient clue to go about fixing the issue, I’m putting few more log entries that were logged for the same session.

Few ULS Log Entries:

1. 
ExternalSource.GetExternalKeyAndStateAndMarkUsed: Populating the session’s credentials for external data. Index=0, Credentials=[8126098, domainfmasood]

2. 
ConnectionRequest.ConnectionRequest: New connection request. SessionId=1.V21.3V9WQUVIjSGKsFX87MBjr90.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.289039c6-fe3c-4074-ac1f-48520e7f45a41.N, WorkbookVersion=ConnectionInfo.WorkbookVersion: Uri=http://sharepoint.domain.com/sites/dept1/team2/deptsolutions/BI/Excel Analysis Reports/SPExcelBIReport_ByFM.xlsx, Version=Thursday, 23 October 2014 19:19:17

3.
The workbook ‘http://sharepoint.domain.com/sites/dept1/team2/deptsolutions/BI/Excel Analysis Reports/SPExcelBIReport_ByFM.xlsx’ attempted to access external data using the unsupported provider ‘Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=SSAS Project Control;Data Source=SSASServerNameInstanceName;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error’. [Session: 1.V21.3V9WQUVIjSGKsFX87MBjr90.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.289039c6-fe3c-4074-ac1f-48520e7f45a41.N User: 0#.w|domainfmasood]

4. 
Refresh failed for ‘SSASServerName_InstanceName SSAS Project Control ProjectDW ProjectTimeReports’ in the workbook ‘http://sharepoint.domain.com/sites/dept1/team2/deptsolutions/BI/Excel Analysis Reports/SPExcelBIReport_ByFM.xlsx’. [Session: 1.V21.3V9WQUVIjSGKsFX87MBjr90.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.289039c6-fe3c-4074-ac1f-48520e7f45a41.N User: 0#.w|domainfmasood]

5.
ExternalSource.ValidateConnection: Unable to get a connection: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException: Exception of type ‘Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionException’ was thrown.     at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.ValidateConnectionInfo(ConnectionRequest connectionRequest, ConnectionInfo connectionInfo)     at Microsoft.Office.Excel.Server.CalculationServer.ConnectionManager.GetConnection(ConnectionRequest connectionRequest, ExtendedConnectionInfo extendedConnInfo, Credentials credentials, Int64 privateConnectionId, Boolean auditConnection)     at Microsoft.Office.Excel.Server.CalculationServer.ExternalSource.TryGetValidatedConnection(Request request, Credentials credentials, Extended…

6.
…ConnectionInfo extendedConnectionInfo, Boolean shouldReportFailure, Boolean auditConnection, Connection& connectionOut). sessionId=1.V21.3V9WQUVIjSGKsFX87MBjr90.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.289039c6-fe3c-4074-ac1f-48520e7f45a41.N, externalSource=SSASServerName_InstanceName SSAS Project Control ProjectDW ProjectTimeReports

7.
ConnectionInfoManager.InitConnInfoFromOdcFile: ODC File Uri is local: C:UsersfmasoodDocumentsMy Data SourcesSSASServerName_InstanceName SSAS Project Control ProjectDW ProjectTimeReports.odc

8.
ExternalSource.ExecuteOperation: We exhausted all available connection information. Exception: Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInfoException: Exception of type ‘Microsoft.Office.Excel.Server.CalculationServer.Interop.ConnectionInfoException’ was thrown.     at Microsoft.Office.Excel.Server.CalculationServer.ConnectionInfoManager.GetConnectionInfo(Request request, String externalSourceName, Int32 externalSourceIndex, Boolean& shouldReportFailure)     at Microsoft.Office.Excel.Server.CalculationServer.ExternalSource.ExecuteOperation(Request request, ExternalSourceStateInfo externalSourceStateInfo, ExternalSourceStateInfo prevExternalSourceStateInfo, Int32 index, ConnectionInfoManager connectionInfoManager, ExternalDataScenario scenario, DataOperation dataOpe…

9.
…ration, Boolean verifyPreOperationConnection), Data Connection Name: SSASServerName_InstanceName SSAS Project Control ProjectDW ProjectTimeReports, SessionId: 1.V21.3V9WQUVIjSGKsFX87MBjr90.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.289039c6-fe3c-4074-ac1f-48520e7f45a41.N, UserId: 0#.w|domainfmasood

10.
ExcelService.PostProcessRequest: finished request of type RefreshAll

Issue Resolution

I went to SharePoint Central Administration site and reviewed the Trusted Data Providers under Excel Services. The location of the configuration is Central Admin home > Application Management > Manage Service Applications > Click on your Excel Service Application > Trusted Data Providers.

In SharePoint 2010, MSOLAP.5 data provider is not present default. See the screenshot below.

SharePoint Excel Services - Trusted Data Provider - MSOLAP.5 not present by default
SharePoint Excel Services – Trusted Data Provider – MSOLAP.5 not present by default

Click on Add Trusted Data Provider link on top left and configure the MSOLAP.5 data provider.

SharePoint Excel Services - Trusted Data Provider - Configure MSOLAP.5 Data Provider
SharePoint Excel Services – Trusted Data Provider – Configure MSOLAP.5 Data Provider

Once added, you should see the MSOLAP.5 data provider in the list.

SharePoint Excel Services - Trusted Data Provider - MSOLAP.5 Data Provider Added
SharePoint Excel Services – Trusted Data Provider – MSOLAP.5 Data Provider Added

Once I had the MSOLAP.5 added as the Trusted Data Provider, in excel services, refresh all data connection feature started working. At that point I was also able to use filters in my report without any errors.

My SharePoint BI report lived happily ever after.

References:
1. Primer on SharePoint Excel Data Refresh Settings
2. SQL Server Version Numbers
3. Excel Services Data Refresh Flowchart – Review the screenshot titled E and section E in the table at bottom right for information related to this issue.
4. Add MSOLAP.5 as a Trusted Data Provider in Excel Services
5. How to: Install the Analysis Services OLE DB Provider on an Excel Services Computer

Keywords:

  • Fix Excel Data Refresh Issue
  • Fix Excel Data Refresh error
  • Fix Excel Data Refresh connection failure
  • Excel Data Refresh Issue
  • Excel Data Refresh connection failure
  • Data refresh issue due to MSOLAP.5
  • Missing Data Provider MSOLAP.5
  • Adding a new Trusted Data Provider
  • Trusted Data Provider in Excel Services
  • Trusted Data Provider in SharePoint Excel Services
  • Unable to Refresh SSAS Data from Excel
  • Event ID 2052
  • External Data Connection Failure
  • External Data Refresh Failure
  • SharePoint BI Report Refresh Issue
  • SharePoint BI Report data Refresh Issue
  • How to fix Excel Data Connection issue
  • How to fix Excel Data refresh issue
  • How to fix Excel Data Connection refresh issue
  • Get the real time data in Excel Services

Leave a Reply

Sign up for Softvative Newsletter

Get notified about new articles and deals

Receive the latest technology and leadership news and resources from us

Subscribe To Softvative Newsletter