
Retrieve the MDX queries generated by Excel
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

- Open the Excel tab with the pivot table
- From the Excel tab press “
Alt+F11
” to get into the Visual Basic Editor - Copy/Paste the below VBA script and specify the destination path of the output TXT file
- 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

4 thoughts on “Retrieve the MDX queries generated by Excel”
Thank You!
Hi Ben,
Thanks for this great little script. I have just run it, but had to resolve 2 issues before it would run, and Excel’s guidance to resolve these was somewhat limited. The first was that my company laptop does not have a C:\Temp folder. And then the second issue was that I needed to enable the Scripting library in Tools/References, and on my setup that is called Microsoft Script Control 1.0.
hi Pete,
Thanks for your comment and sharing with us!