SSAS: Exporting MDX from Profiler part 2

Greg Galloway commented on the previous post I made about exporting captured MDX queries from SQL Profiler, indicating that there was an issue with using the MDX cap 

image

Greg has posted an issue on connect which you can vote on here, but I can't see this behaviour changing in the near future. Maybe in Katmai they could make the extract smart enough to do the parameter replacement as it creates the .mdx file. As far as I am aware the Analysis Services provider in SQL Server Reporting Services (SSRS) 2005 is the only application that can generate these sort of queries at the moment. So if you have SSRS reports that are using parameterized MDX this issue could affect you.

While we are waiting for something to happen with the connect issue, you could have a look at a small sample app that I knocked together. It shows how to connect to a SSAS instance and setup a trace and capture events from that trace. As you can see the UI could not be much simpler, you enter the server you want to monitor and click "start" and it will ask you for a file name and start writing queries out to that file.

In this sample I have set it up to capture QueryBegin events and to also do the text replacement for any queries with parameters. I was hoping to use the QueryEnd event so that I could also report how long it took the query to run with the parameters in question, but unfortunately you can only get the parameters from the QueryBegin event and the duration from the QueryEnd event.

You can download the sample from here and check it out.

Update 7 July 2009: Moved samples to skydrive

Print | posted on Wednesday, July 4, 2007 7:57 AM

Comments on this post

# re: SSAS: Exporting MDX from Profiler part 2

Requesting Gravatar...
I really love this guys. Thanks to the developer!
Left by Bob on Jan 18, 2008 7:58 AM

# re: SSAS: Exporting MDX from Profiler part 2

Requesting Gravatar...
This is awesome, thank you!!

I tried converting to VS2008 and get a compile error that ref's are missing, I added ref's to adomdb.client and server and still get same error. What am i missing please?

Thanks!
Left by ericblair on Aug 10, 2009 11:45 PM

# re: SSAS: Exporting MDX from Profiler part 2

Requesting Gravatar...
Never mind, found the answer here:
http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/c3417232-0a30-413e-8d85-21a43f65e56f/
Left by ericblair on Aug 11, 2009 12:16 AM

# re: SSAS: Exporting MDX from Profiler part 2

Requesting Gravatar...
Awsome utility.
Left by Zaim Raza on Jul 18, 2012 8:21 PM

# re: SSAS: Exporting MDX from Profiler part 2

Requesting Gravatar...
I put the sources on github: https://github.com/Hummer311/MdxQueryCapture/tree/master

Thanks for an invaluable tool!
Left by Patrice Tremblay on Oct 02, 2014 6:30 AM

Your comment:

 (will show your gravatar)