MDX Script Performance Analyser

• 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

As a result you get to see how much each calculation has contributed to the overall time taken by the query; when you see a big jump upwards in the query time graph (that isn’t followed by a big fall subsequently – which could happen with scoped assignments) then at that point in the MDX Script there’s an expensive calculation.

As you’ll see if you download the source code it is in a very basic state at the moment, but it works most of the time for me and has come in very handy on a number of occasions. There are a lot of changes and improvements that I’d like to make (such as recording the total number of cells returned by the query, the total number of non-empty cells and the number of Query Subcube events at each step) and if anybody out there has other suggestions or would like to sign up as a developer I’d be only too happy to hear from you.

I also need to thank Greg Galloway for making some invaluable contributions to the code and for fixing at least one critical bug for me. This is probably also a good point to mention another project on Codeplex that Greg and Darren Gosbell have been working on over the last few months, BIDS Helper:
http://www.codeplex.com/bidshelper

… which contains some really useful extra functionality for people working with AS in BI Development Studio.