Home > MDX Challenges > MDX #9 – Capture MDX Queries with SQL Server Profiler

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.

clip_image001

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

 clip_image002

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.

clip_image003

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.

clip_image004

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.

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: