Share via


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
    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
    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.