Performance Advisor for… Analysis Services?

It’s been some time since my last post, and several folks have asked “Hey Greg, after such a promising start, what happened?”  The short answer is, “Performance Advisor for Analysis Services.”  Yes, you heard that right.

The long answer is… not long after my last post I was having a chat with John Welch at Mariner, a great Microsoft BI consulting firm also based here in Charlotte, and I was trying explain how Performance Advisor for SQL Server might be used to help troubleshoot Analysis Services performance issues.  Let’s just say that conversation didn’t go quite as well as I’d hoped ;-)  Sure, PA for SQL shows many of the pertinent Windows counters on the dashboard, and the “Other files” element on the disk activity and space tabs will capture SSAS-related IO and space, and Event Manager handles SSIS and SSRS… but it became apparent very quickly that this was not nearly enough.  John asked, “What about temp file spillage… or file cache utilization… or aggregation usage?”

Although PA for SQL may be the best tool out there for optimizing SQL Server performance, clearly an SSAS performance tool it was not.  Needless to say, I left feeling a bit down about that point… but energized by another tidbit I discovered during the meeting: apparently there was no commercial software on the market for Analysis Services performance!  When John said this I really couldn’t believe it.  Up to that point our focus had always been on the SQL database engine and directly related technologies, and I guess I just assumed that, as big of an industry as BI is for Microsoft, that there had to be something out there.  After a lot of looking around, I confirmed that there wasn’t.

Shortly thereafter I went to the dev team and said, “Remember what I said last week about focusing exclusively on the enhanced performance alerting features for PA for SQL that we didn’t get into the initial release… well, forget that for the moment.  An opportunity just fell into our laps, and this type of opportunity doesn’t come along every day!”  Blank stares.  Although I’d like to think that by this point I’ve earned the confidence of the team, let’s just say this sudden change in direction was met with a certain amount of skepticism.

Here we are about 9 months later and within a couple of weeks of the release of PA for SSAS, and I can safely say there are no more skeptics.  When I stop to think about it I’m amazed we were able to get this software to market so quickly.  It took about two years of focused effort to build PA for SQL Server, and if we had started from scratch it would have taken at least as long to build PA for SSAS, probably longer.  The big difference was that we’d already built this fantastic architecture for collecting, managing, and presenting performance data from diverse sources, so a lot of it was pretty much plug-and-play.  The most difficult aspect of the project was definitely the R&D phase, which took several months.  If you’ve worked with both the SQL database engine and Analysis Services even a little, you know they are completely different animals that bear almost no resemblance to each other.  From the way they manage memory and disk access, to the threading models, to usage patterns, they almost look like they were developed by two different companies.  (Probably because originally they were – Microsoft acquired the original OLAP engine from Panorama Software in 1996, around the time of SQL Server 6.5.)

The goal for PA for SSAS was pretty much the same as PA for SQL Server – build a product which presents key, actionable performance metrics in an intuitive, easy-to-consume fashion.  However, due to the vast architectural differences between SQL and SSAS, almost none of the extensive R&D done for SQL Server was reusable.  Determining exactly which SSAS metrics to present and how, so that literally within a few minutes someone who may not even be very familiar with SSAS can understand how it works under the covers, was one of the most challenging undertakings I’ve ever been involved with.  I can remember leaving the office many times during that phase feeling like a tiny ant on the backside of the Analysis Services elephant.

Little by little it all came together though, and I think you’ll be very pleased with the end result.  I can honestly say that it would not have been possible without the extensive work by and encouragement from Carl Rabeler (Microsoft SQLCAT), Chris Webb, and John Welch; Brooke Philpott and our phenomenal technical team; and our great beta testers.  Many, many thanks to all of you!