When we use Excel to interact with an SSAS tabular model or a Power BI dataset via Analyze in Excel, Excel generates MDX queries which are then sent to the SSAS engine.
So whenever a user is complaining about a pivot table connected to an SSAS or PBI dataset being too slow or showing wrong figures we may need to retrieve the MDX queries generated by Excel to investigate the issue.

There are several ways to retrieve the MDX queries generated by Excel such as using profiler, Xevents or even installing an Excel add-in.

However, all these options may not be possible in your company if you have a strict policy regarding the tools that can be installed or if you don’t have sufficient permission on the SSAS server.

So if none of the above options is available to you, you can still use this Visual Basic script to get the MDX queries created by Excel.

Retrieve the MDX query

1. Open the Excel tab with the pivot table
2. From the Excel tab press “Alt+F11 ” to get into the Visual Basic Editor
3. Copy/Paste the below VBA script and specify the destination path of the output TXT file
4. Press run

Viscual Basisc Scrit to run

Sub CheckMDX()
     
Dim pvtTable As PivotTable
Dim fso As Object
Dim Fileout As Object
Set pvtTable = ActiveSheet.PivotTables(1)
Set fso = CreateObject("Scripting.FileSystemObject")
Set Fileout = fso.CreateTextFile("C:\Temp\MDXOutput.txt", True, True)
Fileout.Write pvtTable.MDX
Fileout.Close

End Sub

Output of the MDX query generated by Excel