Excel
Services on Sharepoint 2013 - Data Connection failed to refresh
Error: We were unable
to refresh one or more
data connections in this workbook.
The following connections failed to
refresh:
Problem:
PivotTable
created in Excel 2013 with External Data Connection to SSAS 2012.
The
report works fine and also the refresh. Below is a screenshot of the Connection
Properties and notice the Connection string.
The same
file, when uploaded on Sharepoint 2013, gives the error above on Refresh.
Reason: Connection String > Provider=MSOLAP.6
Normally,
MSOLAP.5 is the Data Provider used in Excel 2013 for SSAS 2012.
Good to know:
"Analysis
Services OLE DB Provider is the native provider for Analysis Services database
connections. MSOLAP is used indirectly by both ADOMD.NET and AMO, delegating
connection requests to the data provider.
ADOMD.NET
is a managed data provider used for querying Analysis Services data. Excel uses
ADOMD.NET when connecting to a specific Analysis Services cube. The connection
string you see in Excel is for an ADOMD.NET connection.
AMO
is a managed data provider used for server administration and data definition.
For example, SQL Server Management Studio uses AMO to connect to Analysis
Services." (https://msdn.microsoft.com/en-us/library/dn141152.aspx)
On one of
my servers I have installed Excel 2013 and both SQL Server 2012 and SQL Server
2014. From this server I create a connection in Excel to SSAS 2012 located on a
different server.
At C:\Program Files\Microsoft Analysis
Services\AS OLEDB I can find both msolap110.dll (for SQL 2012) and msolap120.dll (for SQL 2014) files.
At C:\Program Files (x86)\Microsoft.NET\ADOMD.NET I
have 3 ADOM.NET .dll files: 100, 110, 120.
So when I
try to create a connection to SSAS in Excel 2013, it chooses by default the
latest MSOLAP provider: MSOLAP.6.
But in
Sharepoint 2013, the MSOLAP.6 data provider is not registered as a Trusted Data
Provider.
Instead,
MSOLAP.5 data provider can be used.
More
about OLE DB providers:
Solution:
To
solve this issue permanently, MSOLAP.6 data provider should be registered in Central
Administration Web site > Excel Services Application Trusted Data Providers.
Workaround:
If you have no access to the Central Administration
Web site, you can still solve this issue using one of the following ways, but
not as a permanent solution.
- Modify the Connection string in the Excel file:
If on the server both msolap110.dll and msolap120.dll exist, the connection string can be modified
and saved:
- Uncheck "Always use connection file" box
- Set Provider=MSOLAP.5
- Ok to save
- Upload the file on Sharepoint and Refresh All Connections. You should have no error.
- Modify the Connection file used by the Excel file:
If you uploaded the file and the connection file on
Sharepoint, you can still download a copy of the connection file and save it
locally to modify it:
- Open with Notepad
- Look for connection string and set Provider=MSOLAP.5
</xml><xml id=msodc><odc:OfficeDataConnection
xmlns:odc="urn:schemas-microsoft-com:office:odc"
xmlns="http://www.w3.org/TR/REC-html40">
<odc:Connection
odc:Type="OLEDB">
<odc:ConnectionString>Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Data
Source=ServerName;Initial Catalog=DatabaseName</odc:ConnectionString>
<odc:CommandType>Cube</odc:CommandType>
<odc:CommandText>CubeName</odc:CommandText>
</odc:Connection>
</odc:OfficeDataConnection>
</xml>
- Save the file and uploaded again on Sharepoint to replace the initial one.
- Open the Excel file in browser and Refresh All Connections. You should have no error.
