Friday, March 04, 2005

Analysis Services Problems Part II

In my last posting I talked about the trouble that you can get yourself into trying to run PTS in poor network environment. We discussed how to get XMLA as far as the desktop however did not discuss how we can get client tools to consume this data with the minimum of hassle.

This was one of the biggest challenges that had to be overcome, I thought about writing an OLEDB provider for XMLA which the client software could use and be fooled into thinking it was running from PTS… As luck would have it, at the same time I was introduced to a company called Digital Aspects (http://www.digitalaspects.com), who had built their first version of something similar and we were keen to test it.

I could clearly see that the potential for a great tool was there, although a little rough round the edges, when used alongside compressed XMLA documents, we could see that significant performance gains would be possible if we could get the provider to cater for the many different types of queries that our users run.

Its fair to say that we embarked on a fairly long journey at this point in partnership with Digital Aspects to really come up with something that worked for us. After probably six months of versions shipping backwards and forwards, I like to think that the provider as it stands now caters for all the weird and wonderful queries that our users like to chuck at it. Of course getting the provider to perform was not the whole story, a lot of changes had to also be made to our client application Intelligent Apps to work effectively with the new provider, however the difference is staggering!

So let’s talk about performance and the improvements that we can quantify.

Connection to our main cube used to take in order of around 10-20 minutes in some locations even with all the different PTS connection properties set on the client, with the new provider around 10-20seconds is pretty representative now.

In terms of queries themselves, we are seeing on average over 500% performance increase, however in some cases reports which we could not run at all or would have taken all day are now taking 5 minutes. All in all a success story from the user perspective.

I hope this was an insight into a way of tackling performance in a large diverse org, next time I will talk about the Analysis Services locking model.