When you create a data connection to an Analysis Services data source in Microsoft Excel, the Microsoft Analysis Services OLE DB Provider for Microsoft SQL Server (MSOLAP) is used for the data connection. Each version of Analysis Services has its own MSOLAP provider version. Here is a list of versions:
MSOLAP.4 – SQL Server 2008
MSOLAP.5 – SQL Server 2012
MSOLAP.6 – SQL Server 2014
Read the following article for more information about using the correct version of MSOLAP:
Excel will use the version of the MSOLAP provider installed on the client machine. In the following example, Excel has configured MSOLAP.5 as the data provider in the data connection string:
You may have multiple versions of the MSOLAP provider installed on the client machine. How does Excel know which MSOLAP version to use? It looks for the version that is configured in the registry. So even though you have installed MSOLAP.6 on your machine in order to connect to Analysis Services 2014, Excel may configure the connection to use MSOLAP.5 in the connection string instead. This will cause issues as you cannot use MSOLAP versions that are earlier than the data source version.
You can fix this by changing the MSOLAP version in the registry keys below, which define what Excel uses to connect to Analysis Services. The location of the registry depends on whether Microsoft Office is an MSI or Click-to-Run (C2R) installation, and whether it is 32-bit or 64-bit.
Office 32-bit MSI
Office 64-bit MSI
Office 32-bit C2R
Office 64-bit C2R
Here is an example of Office 32-bit C2R configured with MSOLAP.5:
To find out whether your installation is MSI or C2R, in Excel go to File > Account. If you see an Office Updates section, the installation is C2R:
If there is no Office Updates section, then it is an MSI installation:
To find out if Excel is 32-bit or 64-bit, click on About Excel in the same Accounts screen and it will show you in the dialog box at the top: