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?
 

Soiled Towels at WPC 2011

I went out to the Microsoft Worldwide Partner Conference (WPC) in L.A.in July. 

There is always plenty to observe in the seemingly mundane stuff that goes on just getting to the event.   My travel arrangements for these kinds of thing rarely go smoothly and this one was no exception, though in this case the problems weren’t of my own making.  





Dear Customer,

We regret to inform you that flight BA2901 from Manchester to Gatwick (London) on 09 Jul 2011 has been cancelled.

Please accept our apologies for any inconvenience this may cause.

We have sent you this information as quickly as possible by automated email and hope it reaches you in time to allow alternative arrangements to be made.

Please do not respond to this email as we will be unable to reply.
I’m sure that BA had excellent reasons for cancelling that flight and that the email was sent as soon as they could identify who was affected by the cancellation.  However, it arrived at about 8pm on Friday evening.  I had a taxi booked to take me to Manchester airport at 4:40am on the Saturday morning.  It was lucky that I’m sad enough to be sat checking my email on a Friday evening. 

Here’s how it was interpreted by my brain:

Oi,

There aren’t enough people on your flight so we’ve cancelled it.

This is going to really mess up your travel plans as you fly in 9 hours and probably won’t even pick this email up in time to do anything about it.

Don’t bother replying.

The interesting thing here was that alongside the general annoyance of having to sort out a transport mess, the data-geek in me couldn’t help but also get annoyed at the opportunity missed to use information to make the best out of a bad situation.

BA had my name as part of the booking.  They had my address as part of the billing process.  I’m brand loyal so I’m in the BA Executive club which is also tagged with the booking which gives them everything down to my inside leg measurement.

One of the fundamental objectives of Business Intelligence is to have access to all the information you need to make the right decision at the right time.  Some businesses would give their right arms to have the amount of information about their customers that BA holds about me. 

(Side note - in reality, all this useful data is probably spread across multiple inaccessible legacy IT systems, none of which were built to communicate with the outside world, but since BA seem to have integrated their systems sufficiently well to enable them to send me regular invitations to apply for a personalised BA credit card, I’m thinking that digging out my name when cancelling a flight isn’t asking too much).

Basically BA had enough information dotted around their IT landscape to send me this:

Dear Ron,

We regret to inform you that flight BA2901 from Manchester to Gatwick (London) on 09 Jul 2011 has been cancelled due to [insert reason here].

Please accept our apologies for any inconvenience this may cause.

We have sent you this information as quickly as possible and hope it reaches you in time to allow alternative arrangements to be made.  We will also be sending you a text to the mobile number we have on file for your Executive Club account.

The next available BA flight to Gatwick from Manchester is BA xxyy scheduled to depart at xx:xx.  We have reserved you a seat on this flight. 

Services from other airports in your area are as follows:

Birmingham:  BA xxyy departs 0n:nn. 

There are no services from Leeds or Liverpool.

Please do not respond to this email as we will be unable to reply, however as a member of our Executive Club you can call us on 0xxxyyyzzzz and we will endeavour to assist you with alternative travel arrangements from the options listed above.

If none of the suggested alternatives work for you we will refund your ticket price in full.

There’s a Dynamics CRM opportunity for someone right there.

For those of you concerned with whether or not I made it to the conference, my rapidly rearranged schedule meant the journey out to LA was the simple matter of an 0430 taxi to Manchester Piccadilly train station, a train to London Euston, the Underground from Euston to Victoria, a train from Victoria to Gatwick Airport, a flight from London Gatwick to Las Vegas and a connection from Las Vegas to L.A.  

Highlight of the event:  the food.  Breakfast on the first day was a cheeseburger.  Actually as many cheeseburgers as you could eat but I’m watching my figure so I only had the one.   And a donut, three cups of coffee (with cream), a can of Coke (just in case) and a slice of cheesecake.  There’s nothing like a healthy start to the day. 

Here’s a photo of the cheesecake, featuring real grated cheese. Mmmmmmm. 

Oh, and I met Johnny Depp, saw Bon Jovi, but never did see anyone taking advantage of the towel bin.

The Return of the Galloping Data Architect

This is a new site for me after a lengthy sabbatical.  

My previous posts can be found here:  http://consultingblogs.emc.com/mickhorne/