Visualising Analysis Services Trace Information in Reporting Services

More fun with Reporting Services. The other week I was working with a customer who I suspected was experiencing query performance problems as a result of the issue described here:

http://blogs.msdn.com/sqlcat/archive/2007/10/19/ssas-small-big-query-interaction.aspx

Certainly in Profiler I could see some fairly trivial queries which were taking much longer than they should have done, and which when I ran them on a test system ran much faster. And certainly when I ran a 'big query' and a 'small query' simultaneously I saw the 'small query' run slow, and when I got the secret ini file settings from PSS my 'small queries' ran much faster. But there was still some doubt in my mind over the extent to which the customer was suffering this problem - just by looking at my Profiler traces from the Production servers I couldn't tell whether lots of small queries were overlapping with the big queries.

What I then thought was that it would be useful to be able to visualise the information from a Profiler trace in some way. I did some experiments with different chart types and different tools, mainly Excel 2007, but in the end I went with Reporting Services 2005 and the Dundas Gantt chart control. I'm not sure whether the Gantt chart has made it into the current RS2008 CTP but I guess it will soon if it hasn't; in any case the data was coming from a 2005 system so that's what I used.

Read more...