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

Inspecting calculation dependencies with MDX Studio

MDX Studio is a universal tool for developing, debugging and analyzing MDX. With 0.4.6 release, there is a new feature in MDX Studio which allows to inspect and analyze dependencies between different calculations. Dependencies are automatically calculated whenever Parse Tree is built, and can be accessed in the dedicated tab. The picture below shows some dependencies from the MDX Script of Adventure Works sample cube (click on image for larger picture).

Of course, such a picture could be overwhelming, therefore there is a helper navigational grid on the left side. For every object participating in the dependency graph, it shows number of objects it depends on (either directly or indirectly) and number of objects depending on it (either directly or indirectly). Clicking on object name shows all the dependencies of that object, whereas clicking on number shows only outgoing or incoming dependencies accordingly. For example, we now can quickly find out which object most of other objects depend on. In Adventure Works example, these are measures Reseller Sales Amount and Sales Amount with 6 dependencies each. Internet Sales Amount follows closely with 5 dependencies.

To some degree, Adventure Works is not very interesting example to work with. Every object there either depends on others, or others depend on it, but never both. I.e. one of the numbers in either Out or In columns is always zero. This doesn’t make very complicated graphs, just one level of indirection. But MDX Studio is capable of following arbitrary number of dependencies computing transitive closure on either outgoing or incoming edges.

Here is more interesting example:

CREATE [Measures].[Internet Gross Profit] =
     [Measures].[Internet Sales Amount] 
    - 
     [Measures].[Internet Total Product Cost];
 
CREATE [Measures].[Internet Gross Profit Margin] =
     [Measures].[Internet Gross Profit]
    /
     [Measures].[Internet Sales Amount];

CREATE [Measures].[YTD Gross Profit] =
    Sum(YTD([Date].[Calendar]), [Measures].[Internet Gross Profit]);

CREATE [Measures].[Previous Period YTD Gross Profit] =
    ([Date].[Calendar].PrevMember, [Measures].[YTD Gross Profit]);

CREATE [Measures].[Previos Period Gross Profit] =
    ([Date].[Calendar].PrevMember, [Measures].[Internet Gross Profit]);

And here is how the full graph of dependencies for Internet Gross Profit will look like (it has 4 dependant and 2 depending objects)

The earliest build of MDX Studio which supports this functionality can be downloaded from here:

http://cid-74f04d1ea28ece4e.skydrive.live.com/browse.aspx/MDXStudio/v0.4.6 (but always check for later release!).

Special thanks to Greg Galloway, who suggested the idea of dependency viewer (on Friday, and I liked it so much, I implemented it over the weekend).

As usual, please report all problems and make suggestions in MDX Studio Forum.

Published Sunday, September 07, 2008 5:52 PM by mosha
Filed under:
Anonymous comments are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement