THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

Announcing MDX Studio CTP1 (v0.1 Alpha)

Redmond, WA September 18, 2007 - Mosha Pasumansky today announced the immediate availability of MDX Studio Community Technology Preview (CTP) 1, version 0.1 Alpha.

MDX Studio is tool which helps users of Microsoft Analysis Services to analyze complex MDX expressions, monitor performance characteristics of MDX queries and get insight into how MDX interacts with other UDM features such as attribute relationships. MDX Studio provides unique visualization representation of MDX expression and allows the user to interact with different stages of MDX execution. MDX Studio is aimed to be compatible with all the versions of Microsoft OLAP servers - OLAP Services 7.0, Analysis Services 2000, SQL Server Analysis Services 2005 and SQL Server Analysis Services 2008 (codename Katmai).

The licensing terms were not disclosed, however Mosha has explicitly noted, that everybody who was registered for the 'Deep Dive to MDX' preconference presentation at PASS 2007 summit will have lifetime eligibility for free copy for all future versions of MDX Studio.

The MDX Studio CTP1 v0.1 Alpha can be downloaded from http://cid-74f04d1ea28ece4e.skydrive.live.com/browse.aspx/MDXStudio/v0.1 (if this SkyDrive thing actually works). Mosha Pasumansky welcomes all the feedback, but especially critique and bug reports. The current 0.1 Alpha version is pretty much work in progress, with many features not yet implemented (for example query execution is synchronous and Cancel doesn't work, drag and drop is almost non-existing etc).

The brief explanation of how this thing works follows:

MDX Studio is very similar to SSMS, but with deeper focus on MDX. It includes connection string builder which allows user to specify different connection string properties categorized in groups - Security, MDX Semantics, Performance Hints etc.

Below is a screenshot of query execution in MDX Studio. Note, that on the left hand side, some of the useful Perfmon counters are automatically computed as deltas for just this query. Counters can be configured through Tools -> Options -> Perfmon menu.

 

Perhaps most important feature of MDX Studio is behind the "Parse" button. In SSMS, it simply validates that the MDX query is syntactically correct. MDX Studio doesn't support yet parsing of MDX SELECT queries or other MDX commands, but it supports parsing of MDX expressions. Below is sample screenshot of the parse tree for the expression of simple calculated member which computes Reseller Sales Amount for all products containing string "Front" in their names.

But what is more interesting, the user can now interact with this tree and see how each subexpression evaluates. Let's position the cursor on CurrentMember node under Descendants. We see in the Results pane, that this current member evaluates to Components member. Note the 'Current Context' window on the right hand side, where user can change the coordinates of the current context, thus affecting results of the MDX expression evaluation.

Next, let's move cursor on Descendants, and see what it evaluates to. The Results pane now displays the set which is result of applying Descendants to the Components member.

Let's make things even more interesting. We are running Filter over this Descendants set which checks boolean expression. Let's see what it evaluates to. MDX Studio understands that the boolean condition needs to be evaluated in the context of iteration over Descendants set, therefore the result looks like below:

Let's dig in deeper. Why some of the comparisons are true and other false. Now we can look inside boolean expression, and position cursor over VBA!Instr function. Again, MDX Studio is intelligent enough to show how Instr evaluates over the set inside Filter:

We could go on and on with this. Once we computed Filter, we now can see how Sum was calculated:

Hopefully this gives an idea what MDX Expression debugger is capable of doing.

Lastly, there is a handy "Coordinates" button, which allows the user to see effect of applying strong hierarchies. Here is a screenshot which shows how August 2003 decodes other attributes:

Of course, it is possible to use any MDX expression here as well. So if we will add FirstChild, we will get to the August 1st 2003:

There are plenty of plans for new features (like support for MDX Scripts) and finishing existing features, but at this point I believe there is enough functionality implemented to start sharing it with the community and gathering the feedback. Please let me know what do you think, what do you like and again, especially what you don't like so I can fix it. 

Published Tuesday, September 18, 2007 11:39 PM by mosha
Filed under:
Anonymous comments are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement