SSAS-Info.com

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

More information and download is here.

Created by Chris Webb.