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!!