OR operation in MDX

Reposted from Jason Thomas blog with the author's permission.

A seemingly harmless question which makes you ignore it thinking you know the answer, but the moment you give it a second look, you start scratching your head. This was my first reaction when I was asked to implement an OR operation in MDX and I really had to think to come up with an answer, quite contrary to the no-brainer I thought this question was. Ever since, this has been in my list of interview questions and this blog would be the second part of the series where I unveil some of my favourite questions.

Problem

Lets face a scenario where you have a relation – Month<—Date & Week<—Date. Now the requirement is such that the Date field should be displayed such that it should either belong to the Month selected or the Week selected.

Answer

Follow the steps below to get the answer

1) The query below will display the Date member captions for all dates

WITH 
  MEMBER [measures].[nam] AS 
    [Date].[Date].CurrentMember.Member_Caption 
SELECT 
  [measures].[nam] ON 0
,[Date].[Date].Children ON 1
FROM [Adventure Works];

2) Suppose we need to display all the Dates such that it either belongs to the month March,2007 or Week 27 CY 2005.

P.S. : Just giving a where clause with a set like the query given below will not work as it will perform an AND operation rather than an OR operation.

WITH 
  MEMBER [measures].[nam] AS 
    [Date].[Date].CurrentMember.Member_Caption 
SELECT 
  [measures].[nam] ON 0
,[Date].[Date].Children ON 1
FROM [Adventure Works]
WHERE 
  (
    {[Date].[Calendar Weeks].[Calendar Week].&[27]&[2005]}
   ,{[Date].[Calendar].[Month].&[2007]&[3]}
  );

3) The correct way is to bring both the conditions inside a set but as of now, they are of different dimensionality and can’t be used together as the members of the same set. Hence we create a tuple of the calendar week and month such that the valid condition of one attribute would be used with the default member of the other dimension (which is usually the All member).

WITH 
  MEMBER [measures].[nam] AS 
    [Date].[Date].CurrentMember.Member_Caption 
SELECT 
  [measures].[nam] ON 0
,[Date].[Date].Children ON 1
FROM [Adventure Works]
WHERE 
  {
    (
      [Date].[Calendar].DefaultMember
     ,[Date].[Calendar Weeks].[Calendar Week].&[27]&[2005]
    )
   ,(
      [Date].[Calendar].[Month].&[2007]&[3]
     ,[Date].[Calendar Weeks].DefaultMember
    )
  };

This query would give the correct output as shown below

This query makes use of the fact that

([Date].[Calendar].DefaultMember,[Date].[Calendar Weeks].[Calendar Week].&[27]&[2005]) = (Date].[Calendar Weeks].[Calendar Week].&[27]&[2005])

We use this technique to make the dimensionality of both the week and month conditions the same. Once this concept is understood, implementing OR conditions in MDX would be a piece of cake. 

Jason has been working with Microsoft BI tools since he joined the IT industry in 2006. Since then he has worked across SSAS, SSRS and SSIS for a large number of clients. He is currently working for Mariner and is based out of Charlotte, NC. His personal blog can be found at http://road-blogs.blogspot.com