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=220.127.116.11, Culture=neutral, PublicKeyToken=89845dcd8080cc91’..
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_18.104.22.168__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.
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