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 


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