During MS BI Conference 2008 I had a presentation called “Got MDX problems ? MDX Studio to the rescue !”. Given how the theme of self-service BI has been in the center of the conference, I decided to apply some self-service BI to analyze the performance of the presentation. The conference website provided all the evaluation data through Excel Services running in Sharepoint, but as a true self-service power user, I immediately exported all the data to Excel to do all the analysis on my machine. Fortunately, even with 3000 participants, 120 sessions and 8 questions in each evaluation form – the total number of rows was well within today’s Excel abilities to handle, so I didn’t need to install Gemini to deal with millions of rows yet. The overall score was 3.94 (out of 5), barely above median at 3.87. By this overall score my session ranked at mediocre 50 out of 120. Slicing data by individual question, and comparing it to the rest of the sessions, I got some insights. The good news were that I did really well in what I considered the most important question in the evaluation: “How applicable/relevant was the content of this session to the requirements of your job”. Here, my session ranked 10th with score of 4.45. It scored even better in the “How knowledgeable was the speaker on the subject” – rank 9 with score of 4.68. But what pulled the total numbers down was “How effective was the speaker in presenting the information”. Here I ranked 86th with score of 3.64, below both median and average.

So the conclusion is that the content was very valuable and useful, and I had all the knowledge, but clearly had a problem with presenting this content and knowledge effectively. Now I needed to drill down into that area – so I went over all the comments trying to correlate them with this low score. This revealed 3 areas that appeared repeatedly through the comments:

1. Some people were not able to make it to the session because the room was too small, and and filled to capacity well before the session started. Few of these people filled the evaluation forms giving low satisfaction scores. So while I feel it is a little unfair to score the session that one didn’t attend, but on the other hand I totally understand their frustration, and they didn’t have any other mechanism to give feedback other than filling the evaluation forms.

2. By far the biggest complaint was that the computer screen in the room was too small, and it was difficult or impossible to see what was on it. Pretty much everybody gave this feedback – even people who gave me high scores. And same sentiment was repeated again and again in the comments section of my blog as well.

3. Lastly, I did not speak very clearly, so few people struggled not only to see what was on the screen, but also to understand what I was saying.

OK, now I understood the root cause of the problem. But this isn’t enough, with all the talk about “Actionable BI”, the real value will come from taking the corrective action. But how can I correct the three problems outlined above ? I cannot go back in time and change the room and the screen, of course, but what I decided to do was to record screencast of some of the less trivial operations in MDX Studio and publish it. The presentation had 4 parts for it, representing MDX development cycle:

Development –> Debugging –> Profiling –> Optimizing

I choose to do the Debugging portion, and instead of speaking – put notes on the screen (point 3 addressed!). The resulting recording took 7 minutes, showed 2 most interesting examples of debugging from the session, multiple debug watches, coordinate overwrites etc.

The only problem is that 7 minutes took almost 1 GBs in raw AVI file – and I don’t know place where I could host it. Uploading both to MSN video soapbox and to youtube produced such a low quality that it was impossible to see. So I compressed into WMV format, which is somewhat blurry, but still can be followed through, and uploaded to skydrive. Perhaps someone may have a better idea how to publish higher quality screencast on the web.

Link: MDX Studio Debug Screencast (29.2 MBs)

The queries used in the screencast:

1.

WITH 
  MEMBER Measures.[Total Number Of Products] AS 
    Count(
      Descendants(
        [Product].[Product Categories].CurrentMember
       ,[Product].[Product Categories].[Product])) 
  MEMBER Measures.[Number Of Sold Products Increasing] AS 
    Count(
      Filter(
        NonEmpty(
          Descendants(
             [Product].[Product Categories].CurrentMember
            ,[Product].[Product Categories].[Product])
         ,[Measures].[Internet Sales Amount])
       ,
            ([Date].[Calendar].CurrentMember ,[Measures].[Internet Sales Amount])
          - ([Date].[Calendar].PrevMember    ,[Measures].[Internet Sales Amount])
        > 0
      )
    ) 
SELECT
  { [Measures].[Total Number Of Products] ,[Measures].[Number Of Sold Products Increasing]} ON 0
 ,[Date].[Calendar].[Month].MEMBERS ON 1
FROM [Adventure Works];

2.

WITH MEMBER [Measures].YOY AS 
  [Measures].[Sales Amount] - ([Measures].[Sales Amount], [Date].[Calendar Year].PrevMember)
SELECT [Measures].YOY ON COLUMNS
, [Date].[Calendar Quarter].[Calendar Quarter].MEMBERS ON ROWS
FROM [Adventure Works]