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

Analyze MDX with MDX Studio

Always adhering to the MDX best practices could be a difficult quest to follow. While there is plenty of information out there, it is scattered across book, whitepapers, blogs, BOL, forum posts etc. And this information could be difficult to digest, and sometimes not clear how to apply in the specific scenarios. On top of that, not all the information out there is reliable. Some of it is outdated, and what used to help in one version of AS, could be bad practice in newer version. All this makes practitioner’s life very difficult. But it doesn’t have to be this way. For long time, I wanted to automate many of the checks for MDX best practices, and today I am happy to announce that this functionality is now part of MDX Studio. Starting with version 0.4.0, MDX Studio features the “Analyze” button. It goes through the MDX, analyzes it, and flags issues. To illustrate this better, let’s consider the following example:

WITH 
 MEMBER
  Measures.WorkingDays AS
    Count(
      Filter(
        Descendants(
          [Date].[Calendar].CurrentMember
          ,[Date].[Calendar].[Date]
          ,SELF)
       ,  [Date].[Day of Week].CurrentMember.Name <> "1"
      )
    )
 MEMBER
  Measures.SalesPerWorkingDay AS 
  '
    IIF(
     [Measures].[WorkingDays] IS NULL OR [Measures].[WorkingDays] = 0
     ,NULL
     ,[Measures].[Internet Sales Amount]/[Measures].[WorkingDays])
   '
SELECT [Measures].[SalesPerWorkingDay]  ON 0
, [Date].[Calendar].[Month].MEMBERS ON 1
FROM [Adventure Works]

In this example we are looking at sales per working day, where working day is defined as anything but Sunday. Let’s run Analyzer over this query and see what it will find out:

Line Column Message Link
7 30 MDX function 'CurrentMember' may raise an error or produce non-desired result when user applies multiselect More Info
10 33 MDX function 'CurrentMember' may raise an error or produce non-desired result when user applies multiselect More Info
10 47 Use IS operator to compare objects instead of comparing them by name More Info
4 6 Consider rewriting Count(Filter(set, cond)) construct as Sum(set, summator) with summator IIF(cond, 1, NULL) More Info
3 7 If you want to avoid division by zero - use 'value = 0' check, if you want to check whether cell is empty - use IsEmpty function, if you want to check that MDX member or tuple exists - use 'obj IS NULL' check More Info
2 6 Don't use single quotes for definitions of calculation Measures.SalesPerWorkingDay More Info

 

 

 

 

 

 

 

 

 

It is easier to navigate through the messages in the desktop edition of MDX Studio, where clicking on the message highlights the relevant portion of the MDX. Also there messages can be sorted, hidden (on right-click context menu) or copy/pasted to Excel. Messages are followed by the link to more information how the particular rule can be applied.

The warning above are relatively simple, but MDX Studio Analyzer is capable of doing more sophisticated checks. Example of such check is detection of common subexpressions. For the following query

WITH 
 MEMBER
  Measures.SalesPerWorkingDay AS 
  '
    IIF(
     Count(
      Filter(
        Descendants(
          [Date].[Calendar].CurrentMember
          ,[Date].[Calendar].[Date]
          ,SELF)
       ,  [Date].[Day of Week].CurrentMember.Name <> "1"
      )
    ) = 0
     ,NULL
     ,[Measures].[Internet Sales Amount]
      /
       Count(
         Filter(
           Descendants(
             [Date].[Calendar].CurrentMember
             ,[Date].[Calendar].[Date]
             ,SELF)
          ,  [Date].[Day of Week].CurrentMember.Name <> "1"
         )
       )
    )
   '
SELECT [Measures].[SalesPerWorkingDay]  ON 0
, [Date].[Calendar].[Month].MEMBERS ON 1
FROM [Adventure Works]

MDX Studio Analyzer will report that the exactly same Count(Filter(…)) expression was used twice:

Line Column Message
15 9 Same expression was used before at Line 3 Column 7. Consider eliminating common subexpressions for better performance and to take advantage of cache

 

The difficult choice that I faced was – for which version of Analysis Services to tailor the messages – AS2005 or AS2008. While some of the advices would stay the same, many others are different, and in some cases are contradictory. For example, in AS2005, CASE operator was never optimized, so at certain scenarios it was better to use nested IIFs, but in AS2008, CASE is as optimized as IIF, so it is preferable to nested IIFs. Another example is NON_EMPTY_BEHAVIOR property, which often times is essential to get good performance in AS2005, but usually is not needed in AS2008, and is better to be removed. With AS2005 everybody recommends replacing IIFs with SCOPE whenever possible, but it is not needed with AS2008 etc. At the end I decided to go with AS2008 only – it just feels as a better investment for the future. AS2008 should be released almost any day now, and I expect much faster migration to it from AS2005, then what we saw with migration from AS2000 to AS2005. The reason is simple – migration to AS2008 from AS2005 is very smooth – all the fundamental concepts stay the same, both client and management object models stay code compatible etc.

One last thing to mention: Best practices are exactly what they are – best practices. It means, that most of the time using them you will be better off. However, they are absolute rules to be followed always. Sometimes there are exceptions from the rules. AS is a complex, feature-rich product, and MDX engine is probably the most sophisticated piece in it. Sometimes features interact in such a way, that some best practices might result in worse results. For example, one of the advices that MDX Studio will give is when it sees Filter(CrossJoin(…)), it will recommend using CrossJoin(Filter(…)) instead. It is common sense to reduce the size of the set before crossjoin’ing. But not only this is not always possible, but also there are rare cases where it would result in worse performance. So while you are encouraged to follow the best practices, always use them as a guide, and measure before and after applying the rules to make sure things do improve.

MDX Studio 0.4.0 is released with desktop and online versions simultaneously.

Desktop version can be downloaded from: http://cid-74f04d1ea28ece4e.skydrive.live.com/browse.aspx/MDXStudio/v0.4.0

Online version can be accessed at: http://mdx.mosha.com

As usual – please post your comments, suggestions, bug reports etc at MDX Studio forum.

Published Sunday, July 13, 2008 10:29 PM by mosha
Anonymous comments are disabled
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement