A Simple Cure for Constipation

By coincidence I was in New York during the recent earthquake. 

As the earthquake hit I was sat leaning on my desk with a cup of extra strong coffee.  I actually thought I’d been overcome by a sudden and catastrophic medical condition as I started rocking, Arthur Fowler style.  The over-riding emotion seconds before death…?  Mild embarrassment.  Not very rock & roll. 

I was there to work on some performance tuning for SSAS.  What bigger and better challenge is there than an affront on your technical manhood?   “This report runs too slowly” came the cry from the users, and they were right – 38 minutes no less which would be a poor time for it to run on a ZX Spectrum.  38 minutes isn’t slow, it’s glacial.  38 minutes?  THIRTY EIGHT minutes for ONE query to run?


In these situations, it pays to approach the problem in a structured manner.  It’s Excel against SSAS so the first thing to do is capture the MDX.  As expected, it’s messy.   Running the MDX directly against the cube confirms that there’s definitely a blockage somewhere.   Cube constipation. 

Some rights reserved by Alexander Ekman

Watching the perfmon stats don’t identify anything obvious and running the query through a profiler trace tells me that it’s mainly a formula engine issue.  There’s a big calculation script in the cube so commenting that out and trying again can be revealing – and it is.  We’re down to a couple of seconds.  From here it’s a process of stepping through the script trying to identify the rogue elements, and there are several that cause performance to fall off a cliff because block computation isn’t being encouraged.

MDX has never been my strong point.  I just don’t spend enough time working with it to completely master it.  Fortunately at Thorogood we have a few for who it is and who do.  Paddy in our Bangalore office is something of a guru and handing it over to him overnight results in a marked improvement.  18 minutes.   Over the next three days we tune, test, tune, test, tune, test, tune, test.  Paddy works his magic and the time comes down to 9 minutes.    In the meantime, the pressure from the client has been building.  Microsoft support has been called in, panic is raging through the building and the entire architecture is being called into question.  Negotiations begin with the users about whether 9 minutes is acceptable and then somebody notices that 3% of the six billion cells returned by the 9 minute query don’t match with those returned by the 38 minute query.  There’s a rogue scope statement somewhere.  Back to square one.

It’s good to get a wake-up call occasionally. 

Now, you might read that and understand everything that’s mentioned.  You might read it as two paragraphs of meaningless technobabble.  If you’ve any sense, you didn’t read it at all and you didn’t need to because the point is not the process, or the steps taken or the incremental performance improvements, or the skill of the people involved or the technology.  The point is the fact that this was immediately seized upon as a technical challenge.

The solution came as something of a shock.  One of the users noticed that they’d run the wrong report.  When they tried out the correct one, the query times were fine.  My feelings?  More mild embarrassment for not looking there first, which as I now know is roughly equivalent to death.

The moral of the story is that if you’ve got a troublesome blockage, changing your diet can sometimes be more effective than testing out a range of suppositories.


As a final thought, I know Excel can generate terrible MDX (expertly described here by Richard Lees) and I know that it does that to maintain backwards SSAS compatibility or maybe it does that because the Office team didn’t bother to catch the bus across to the Analysis Services building at Redmond often enough when they were writing the MDX generator, but surely it’s time for someone at MS to add a little “Do not generate bollocks MDX” checkbox into my data connection settings in Excel?