MDX Script Performance Analyser
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:
- Connects to the cube using ADOMD.Net and AMO
- Read's the cube's MDX Script and splits it up into its constituent statements, storing them in an array
- Executes a Clear Cache command to ensure that all queries are run on a cold cache
- Executes a Clear Calculations command so that for the current session the cube appears as though its MDX Script contains no commands
- For each statement in the array of statements from the MDX Script, it then:
- 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
- Runs the query you entered in the textbox
- Stores how long the query took to run, plus other interesting metrics
- 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
Generally speaking, whenever you see a steep (and sustained) increase in the amount of time taken to run your query then you know that the accompanying MDX Script statement has had that effect and might be worth optimising.
For instance, look at the following screenshot and notice that the left graph spikes and sustains the increase:
Then you can move to the next page(s) of the report and find the MDX of the specific statement(s) in the calc script which are responsible for the slow performance. Download a sample report
to see how this works.