Exporting MDX queries from SQL Server profiler

I was working on a project to load test and benchmark SQL Server Analysis Services(SSAS) database (blog post to follow soon  a brief about which is here). As part of the exercise, I needed to capture MDX queries running on the given SSAS Cube.  SQL Server profiler makes this very simple. Here are the steps.

1. Start SQL Server profiler and connect to the SSAS server. If you want to you can configure the trace properties and filter for one database. Start the trace.

2. Open Excel and connect to SSAS cube.

3. Build your pivot table (or drag and drop stuff around to get a range of queries.)

4. Go back to profiler and Stop the trace.

5. Click File–>Export–>Extract SQL Server Analysis Services Event–>Extract MDX Events.

6. Save the file as MDX.

A slight issues is that this will store all the MDX queries in one mdx file. If you need to have each query in a separate file, I would suggest using notepad++ and creating a small macro do this. Works like a charm!!

Another approach to get the queries is to use OLAP Pivot Table Extension where you build the pivot table and copy the MDX from the extension. The steps are better explained on codeplex.

Advertisements

One comment

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