Tool to profile MDX query processing and MDX Performance Analysis

All my career till date, most of the time I have been working on assignments where my primary role have remained of improvising performance, whether it may be application performance, query performance, database performance or system performance. A good debugging tool acts as a backbone while working on performance tuning and optimization. Industry class tools are already available for application in Visual Studio and for databases like SQL Profiler. But I have not seen a tool of the likes of SQL Profiler for MDX, at least in SSAS parlance.

I found an interesting tool on Codeplex that serves this purpose.

MDX Script Performance Analyser Overview (reference: Codeplex)

The MDX Script Performance Analyser allows the user to identify how much each calculated member, named set and scoped assignment in an Analysis Services cube's MDX script contributes to the overall performance of a given MDX query.

To use the tool, all you need to do is connect to the database and cube you'll be querying and then enter an MDX query in the main textbox and hit the 'Run Query' button. When you do this, the tool does the following:

1. Connects to the cube using ADOMD.Net and AMO
2. Read's the cube's MDX Script and splits it up into its constituent statements, storing them in an array
3. Executes a Clear Cache command to ensure that all queries are run on a cold cache
4. Executes a Clear Calculations command so that for the current session the cube appears as though its MDX Script contains no commands
5. For each statement in the array of statements from the MDX Script, it then:
   1. Executes the first statement in the MDX Script within the session, so that the cube now acts as though its MDX Script contains only this statement and all previously executed statements
   2. Runs the query you entered in the textbox
   3. Stores how long the query took to run, plus other interesting metrics
6. Once the query has run on the equivalent of the entire MDX Script in the cube, a report is generated which contains graphs and charts illustrating the data captured earlier

Download MDX Script Performance Analyser