MDX #9 – Capture MDX Queries with SQL Server Profiler

Very often, we are asked by our business users to explain certain behaviors of PivotTable in Excel. Our first instinct is to capture the MDX queries fired from Excel. Once I have  the actual MDX query, I can analyze the query and the result, and will be able to provide our business users with a more in-depth explanation.

Capture MDX Queries with SQL Server Profiler

There are many very helpful blogs that explain simple steps to do this. As a SQL developer, using SQL Server Profiler can be a natural choice.

Use SQL Server Profiler to capture MDX queries fired from Excel

1. Open SQL Server Profiler from Microsoft SQL Server –> Performance Tools –> SQL Server Profiler.

2. Create a new trace with Analysis Services as Server Type. Enter your Analysis Services server name. Click Connect to make connection to the Analysis Services server.

3. No need to use the Standard template. Choose the Blank template.

 

4. Only select Query End as the Event to capture. Click the Run button at the page bottom. Now the trace is running and ready to capture any queries sent to the Analysis Services server.

5. Fire up the MDX queries from Excel by Refresh the PivotTable. You should see results similar to the following in the SQL Server Profiler. The MDX queries fired from Excel should be captured by the Profiler and displayed at the bottom of the trace window.

Two other ways to capture MDX queries fired from Excel

This blog will be incomplete without mentioning the following two common ways:

1. Use the OLAP PivotTable Extensions. This is an Open Source CodePlex project, and you can download it from http://olappivottableextend.codeplex.com/. Once you installed the Excel add-in, you can right-click a cell in a PivotTable and will notice a menu item “OLAP PivotTable Extensions…”.

The MDX tab will display the MDX query. The option to format the query is also very nice.

You might not want to recommend this If your company doesn’t allow third-party components.

2. Another option is to write some VBA code. Check out Marco Russo’s blog Display the MDX query of an Excel 2007 PivotTable at http://sqlblog.com/blogs/marco_russo/archive/2007/01/18/display-the-mdx-query-of-an-excel-2007-pivottable.aspx.

SQL Server Profiler can be used to capture MDX queries fired from any OLAP application

SQL Server Profiler can capture not only MDX queries fired from any OLAP application, but also XMLA commands sent from applications such as BIDS.