Chris Webb's BI Blog

Chris's blog has some good tools for performance testing Analysis Services. 

I do a lot of performance tuning as part of my consultancy work, and quite often when I start looking at a customer's cube I find that for any given query that needs to be tuned there are several (sometimes hundreds) of calculations which affect the cells in the query and which could be the cause of performance problems. To help me work out which calculations are the ones that need to be looked at I put together a tool - the MDX Script Performance Analyser - which I've just got round to putting up on Codeplex so it can be shared:

http://www.codeplex.com/mdxscriptperf

Basically what it does is this:

• First of all, you connect to the cube that your query runs against
• Then you enter your query in the text box at the top of the screen and hit 'Run Query'
• This then starts the following process:
  • The tool reads the cube's MDX Script and splits it up into its constituent statements, storing them in an array
  • It executes a Clear Cache command to ensure that all queries are run on a cold cache
  • It 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:
    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
  • 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

Source: Chris Webb's BI Blog