Excel 2016 PowerPivot MDX query designer error

  • Article

Problem:

When you open Excel 2016, go to the Power Pivot tab, click the Mange button to bring up Power Pivot window

In Power Pivot, click Get External Data, choose From Other Sources

Choose Microsoft Analysis Services. Click Next

Fill in the SSAS server name, then click Next,

 

Click on the Design button, you may get the following error

Cannot load the query designer. If you contact Microsoft support about this error, provide the following message: Could not load type 'Microsoft.DataWarehouse.Design.IErrorReportingService' from assembly 'Microsoft.DataWarehouse.Interfaces, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'..

 

Root cause:

This problem happens when you install SQL Server 2012 on the machine where you install Excel 2016. Excel 2016 first tries to find the DLL in GAC. If SQL Server 2012 is installed, Excel 2016 finds it in C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.DataWarehouse.Interfaces\v4.0_11.0.0.0__89845dcd8080cc91\Microsoft.DataWarehouse.Interfaces.dll

The problem is, the GAC version of this assembly is not compatible with Excel 2016. Excel 2016 comes with its own version of the DLL at C:\Program Files\Microsoft Office\root\Office16\ADDINS\PowerPivot Excel Add-in\Microsoft.DataWarehouse.Interfaces.DLL

Excel 2013 has not such issue, because the GAC version of the DLL from SQL Server 2012 is compatible.

Workaround:

The workaround is to force install/register the newer version of the Microsoft.DataWarehouse.Interfaces.dll to the GAC. This way both SQL Server 2012 and Office 16 will not have problems running Query Designer.

Run gacutil.exe /if Microsoft.DataWarehouse.Interfaces.dll from the directory where the Office 16 dll is located, that is usually

C:\Program Files\Microsoft Office\root\Office16\ADDINS\PowerPivot Excel Add-in