Retrieve the MDX queries generated by Excel

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

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

4 thoughts on “Retrieve the MDX queries generated by Excel

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

Leave a Reply

Your email address will not be published. Required fields are marked *