Excel 2016 PowerPivot MDX query designer error
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
Comments
- Anonymous
December 10, 2015
Dear HaidongThank you for the post. I added the assembly successfully. But it still does not work rsp. I get exactly the same error message as before:I'm running SQL-Server 2012 Developer Edition and Excel 2016.. I tried the same thing on a desktop computer, where no SQL-Server ist running and there the query designer can be started properly.Der Abfrage-Designer kann nicht geladen werden. Wenn Sie diesen Fehler dem Microsoft-Supportteam melden, geben Sie auch die folgende Fehlermeldung an: Der Typ "Microsoft.DataWarehouse.Design.IErrorReportingService" in der Assembly "Microsoft.DataWarehouse.Interfaces, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" konnte nicht geladen werden..Thank you for any further hints!Greetings Urs- Anonymous
October 01, 2016
Hi,this worked for meLooked at folder Excel is supposed to point to for searching for DLL - C:\Program Files\Microsoft Office\root\Office16\ADDINS\PowerPivot Excel Add-in\Microsoft.DataWarehouse.Interfaces.DLL.Then looked at GAC folder - C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.DataWarehouse.Interfaces\v4.0_11.0.0.0__89845dcd8080cc91\Renamed file to To Microsoft.DataWarehouse.Interfaces.OLD, Then copied Microsoft.Datawarehouse.interface.DLL FROMC:\Program Files\Microsoft Office\root\Office16\ADDINS\PowerPivot Excel Add-in TO C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.DataWarehouse.Interfaces\v4.0_11.0.0.0__89845dcd8080cc91\ and then I was able to use the PowerPivot Design screen for creating his PowerPivot report HTH,Nabil- Anonymous
October 09, 2016
Thanks Nabil, Its Works for me Too.Looked at folder Excel is supposed to point to for searching for DLL – C:\Program Files\Microsoft Office\root\Office16\ADDINS\PowerPivot Excel Add-in\Microsoft.DataWarehouse.Interfaces.DLL.Then looked at GAC folder – C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.DataWarehouse.Interfaces\v4.0_11.0.0.0__89845dcd8080cc91\Renamed file to To Microsoft.DataWarehouse.Interfaces.OLD,Then copied Microsoft.Datawarehouse.interface.DLL FROMC:\Program Files\Microsoft Office\root\Office16\ADDINS\PowerPivot Excel Add-in TO C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.DataWarehouse.Interfaces\v4.0_11.0.0.0__89845dcd8080cc91\ and then I was able to use the PowerPivot Design screen for creating his PowerPivot report - Anonymous
November 07, 2016
Thank you Nabil,Your instructions fixed the issue for me as well.- Anonymous
February 13, 2017
Your copy paste worked Nobil. thanks- Anonymous
February 13, 2017
Nabil (not Nobil) sorry
- Anonymous
- Anonymous
- Anonymous
- Anonymous
- Anonymous
January 15, 2016
The Gacutil.exe tip didn't help me, and I actually had an issue uninstalling SQL Server 2012. If anyone else has run into this, my solution was to simply delete the .dll that SQL Server 2012 used(C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.DataWarehouse.Interfaces\v4.0_11.0.0.0__89845dcd8080cc91\Microsoft.DataWarehouse.Interfaces.dll ) as long as you don't need to use the SQL2012 designer anymore. After that, the PowerView designer began working perfectly. - Anonymous
February 18, 2016
meeting the same problem but your solution doesn't work to me. Do i need to restart machine after running your solutions? - Anonymous
March 21, 2016
Using GACUTIL did not work for me. I renamed Microsoft.DataWarehouse.Interfaces.dll in here: C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.DataWarehouse.Interfaces\v4.0_11.0.0.0__89845dcd8080cc91\To Microsoft.DataWarehouse.Interfaces.OLD, and then both PowerPivot and SSMS can launch the query designer interface.- Anonymous
September 13, 2016
Thank you IJustWant2AskSomething, it worked for me too. Using GACUTIL did not work for me. I renamed Microsoft.DataWarehouse.Interfaces.dll in here: C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.DataWarehouse.Interfaces\v4.0_11.0.0.0__89845dcd8080cc91\To Microsoft.DataWarehouse.Interfaces.OLD, and then both PowerPivot and SSMS can launch the query designer interface. - Anonymous
September 17, 2016
You rock this worked for me too (the renaming) - Anonymous
October 26, 2017
This worked for me! I have VS 2015, SQL Management Studio 2016 and Excel 2016.
- Anonymous
- Anonymous
April 07, 2016
The comment has been removed - Anonymous
April 13, 2016
This was not successful for me, still getting the same error. - Anonymous
April 21, 2016
Thanks for this. I found the .DLL hereC:\Program Files (x86)\Microsoft Office\Office16\ADDINS\PowerPivot Excel Add-inand had to install Visual Studios from here: http://download.microsoft.com/download/4/0/6/4067968E-5530-4A08-B8EC-17D2B3F02C35/vs_ultimateweb.exe - Anonymous
May 19, 2016
Hello, thanks for this article. I am caught in the problem. I performed all the steps. But the gacutil.exe command is caught up by an error.It say - it is not able to find any gacutil.exe in the folder. Could you please suggest. ??Regards, - Anonymous
June 21, 2016
Hello there,I've followed the above steps, confirmed the new version (12) is in the GAC but Excel still won't load the query designer as it's trying to use v11 (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'..) - Anonymous
July 06, 2016
This did not correct the problem for me. In Windows 7 (client PC) and in Windows 10 (my PC), I took the following steps with no resolution.1. Found GacUtil.exe in C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\bin on the client PC and C:\Program Files (x86)\Microsoft SDKs\Windows\v10.0A\bin\NETFX 4.6 Tools on my PC, and copied it over to C:\Program Files\Microsoft Office\root\Office16\ADDINS\PowerPivot Excel Add-in2. Ran a command window "As Administrator"3. Changed my directory to C:\Program Files\Microsoft Office\root\Office16\ADDINS\PowerPivot Excel Add-in after verifying that OFFICE.DLL existed there4. Ran GacUtil.exe from this directory with the script provided above.5. Received a "Assembly Successfully Added to the Cache" message6. Restarted Excel. Error persisted.7. Restarted PC and then opened Excel. Error persisted.