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 (17)

  1. 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-in
    2. 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 there
    4. Ran GacUtil.exe from this directory with the script provided above.
    5. Received a “Assembly Successfully Added to the Cache” message
    6. Restarted Excel. Error persisted.
    7. Restarted PC and then opened Excel. Error persisted.

  2. Adam Everington says:

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

  3. Vikas Gautam says:

    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,

  4. Jose says:

    Thanks for this.

    I found the .DLL here

    C:\Program Files (x86)\Microsoft Office\Office16\ADDINS\PowerPivot Excel Add-in

    and had to install Visual Studios from here:

    http://download.microsoft.com/download/4/0/6/4067968E-5530-4A08-B8EC-17D2B3F02C35/vs_ultimateweb.exe

  5. Jason Frakes says:

    This was not successful for me, still getting the same error.

  6. David Martensen says:

    Thanks for posting this solution. However, I have not had much luck being able to run the GACUTIL.exe from a command line prompt. I have a windows 7 OS and have even added a command line prompt to VS 2010. Still no luck. Any suggestions?

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

    1. Anonymous says:

      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.

    2. Anonymous says:

      You rock this worked for me too (the renaming)

  8. NX says:

    meeting the same problem but your solution doesn’t work to me. Do i need to restart machine after running your solutions?

  9. Mike Chittick says:

    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.

  10. Urs Christen says:

    Dear Haidong

    Thank 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

    1. Nabil says:

      Hi,

      this worked for me

      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

      HTH,

      Nabil

      1. Syed says:

        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

      2. Jon says:

        Thank you Nabil,

        Your instructions fixed the issue for me as well.

        1. sree says:

          Your copy paste worked Nobil. thanks

          1. sree says:

            Nabil (not Nobil) sorry

Skip to main content