Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

OR Queries in MDX

with 13 comments

I’m always happy to hear from readers of this blog (you can find my contact details at http://www.crossjoin.co.uk/contact.html) and happy to take requests for subjects to blog about. For example I got an email from Joseph Boschert the other week asking if I could post something about OR queries in MDX and since this is a subject that comes up on a fairly regular basis and can be quite confusing I agreed.

The simplest kind of OR query is where you want to display or slice by two members on the same hierarchy. For example in Adventure Works if I wanted to see Internet Sales where the year was 2003 or 2004 then I could write a query something like this:

SELECT [Measures].[Internet Sales Amount] ON 0,
{[Date].[Calendar Year].&[2003], [Date].[Calendar Year].&[2004]}
ON 1
FROM [Adventure Works]

and if I wanted to see an aggregated value I could either put the set containing 2003 and 2004 in the WHERE clause, as follows:

SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE({[Date].[Calendar Year].&[2003], [Date].[Calendar Year].&[2004]})

or in a subselect:

SELECT [Measures].[Internet Sales Amount] ON 0
FROM (SELECT
{[Date].[Calendar Year].&[2003], [Date].[Calendar Year].&[2004]} ON 0
FROM [Adventure Works]
)

Both queries should return the same result, although of course the WHERE clause and subselects are not equivalent (see Mosha’s posting here for an explanation of how they differ). For the sake of simplicity I’ll stick to using WHERE clauses for the rest of this post.

Things become a little more complex when you want to do an OR over two different hierarchies on the same dimension because the default behaviour here in MDX is to AND. For example, what if I was interested in seeing the value of Sales made in 2003 or on a Friday? The following query gives me the value of Sales made in 2003 and on a Friday:

SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE([Date].[Calendar Year].&[2003],[Date].[Day Name].&[6])

How do I get the OR? Well, the answer is this:

SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE(
{([Date].[Calendar Year].&[2003],[Date].[Day Name].[All Periods])
,
([Date].[Calendar Year].[All Periods],[Date].[Day Name].&[6])})

…but let’s take some time to understand why this works. The set in the WHERE clause contains two tuples, the first representing all Day Names in 2003 and the second representing Fridays in all Calendar Years. This is clearly what we want to pass into the OR, but isn’t there a danger of double counting here? Actually no because the WHERE clause is pretty clever. If you were to write the following query you’d see a different, incorrect, higher value:

WITH MEMBER MEASURES.DOUBLECOUNT AS
([Date].[Calendar Year].&[2003],[Date].[Day Name].[All Periods],[Measures].[Internet Sales Amount])
+
([Date].[Calendar Year].[All Periods],[Date].[Day Name].&[6],[Measures].[Internet Sales Amount])
SELECT MEASURES.DOUBLECOUNT ON 0
FROM [Adventure Works]

…because by summing the tuples together we’re adding the value of Sales on Fridays in 2003 twice, ie double-counting it. Luckily the WHERE clause doesn’t do a simple sum though, it looks at the area described in the set and doesn’t count any overlapping cells twice. You can visualise this area by running this query:

SELECT {} ON 0,
UNION(
CROSSJOIN({[Date].[Calendar Year].&[2003]},[Date].[Day Name].[Day Name].MEMBERS)
,
CROSSJOIN([Date].[Calendar Year].[Calendar Year].MEMBERS,{[Date].[Day Name].&[6]})
)
ON 1
FROM [Adventure Works]

It’s basically a set of tuples that contains all days in 2003 and Fridays in all other years. Incidentally, if we needed to create a calculated member that didn’t double-count we’d need to sum up this same set as follows:

WITH MEMBER MEASURES.NOTDOUBLECOUNTED AS
AGGREGATE(
UNION(
CROSSJOIN({[Date].[Calendar Year].&[2003]},[Date].[Day Name].[Day Name].MEMBERS)
,
CROSSJOIN([Date].[Calendar Year].[Calendar Year].MEMBERS,{[Date].[Day Name].&[6]})
)
, [Measures].[Internet Sales Amount])
SELECT {MEASURES.NOTDOUBLECOUNTED} ON 0
FROM [Adventure Works]

You could also use a query something like this:

WITH MEMBER MEASURES.NOTDOUBLECOUNTED AS
([Date].[Calendar Year].&[2003],[Date].[Day Name].[All Periods],[Measures].[Internet Sales Amount])
+
([Date].[Calendar Year].[All Periods],[Date].[Day Name].&[6],[Measures].[Internet Sales Amount])
-
([Date].[Calendar Year].&[2003], [Date].[Day Name].&[6],[Measures].[Internet Sales Amount])
SELECT MEASURES.NOTDOUBLECOUNTED ON 0
FROM [Adventure Works]

…which explicitly subtracts the tuple containing the double-counted value and may be slightly faster, but makes the potentially dangerous assumption that everything is additive.

The last scenario we need to mention is ORing across hierarchies from dimensions, for example if I wanted to see Sales where the transaction was either in 2003 or to a Customer in the US. Again, thankfully, the WHERE clause ‘just works’ (the example of two hierarchies on the same dimension doesn’t do what it does because of auto-exist) so that the following query doesn’t double-count:

SELECT [Measures].[Internet Sales Amount] ON 0
FROM [Adventure Works]
WHERE(
{([Date].[Calendar Year].&[2003],[Customer].[Country].[All Customers])
,
([Date].[Calendar Year].[All Periods],[Customer].[Country].&[United States])})

Before I finish, I should also mention that the Analysis Services Stored Procedure Project has a very useful sproc that makes it very easy to generate the kind of asymmetric sets of tuples we’ve been dealing with here, written by Darren Gosbell:
http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=AsymmetricSet&referringTitle=Home

Written by Chris Webb

April 4, 2007 at 1:34 pm

Posted in MDX

13 Responses

Subscribe to comments with RSS.

  1. Thanks for the information – very helpful.  What was catching me off guard initially was that if you do ORing across hierarchies or dimensions, all analyzed dimensions/hierarchies need to have the All level included in the where clause.  As a result, here is the query for my cube:
     
    SELECT [Measures].[Claims] ON 0FROM [MO Medical Claims]WHERE({([Diagnosis].[DIAGNOSIS].&[1st deg burn chin],[Secondary Diagnosis].[DIAGNOSIS].[All],[Third Diagnosis].[Diagnosis].[All],[Fourth Diagnosis].[Diagnosis].[All],[Fifth Diagnosis].[Diagnosis].[All]),([Diagnosis].[DIAGNOSIS].[All],[Secondary Diagnosis].[DIAGNOSIS].&[1st deg burn chin],[Third Diagnosis].[Diagnosis].[All],[Fourth Diagnosis].[Diagnosis].[All],[Fifth Diagnosis].[Diagnosis].[All]),([Diagnosis].[DIAGNOSIS].[All],[Secondary Diagnosis].[DIAGNOSIS].[All],[Third Diagnosis].[Diagnosis].&[1st deg burn chin],[Fourth Diagnosis].[Diagnosis].[All],[Fifth Diagnosis].[Diagnosis].[All]),([Diagnosis].[DIAGNOSIS].[All],[Secondary Diagnosis].[DIAGNOSIS].[All],[Third Diagnosis].[Diagnosis].[All],[Fourth Diagnosis].[Diagnosis].&[1st deg burn chin],[Fifth Diagnosis].[Diagnosis].[All]),([Diagnosis].[DIAGNOSIS].[All],[Secondary Diagnosis].[DIAGNOSIS].[All],[Third Diagnosis].[Diagnosis].[All],[Fourth Diagnosis].[Diagnosis].[All],[Fifth Diagnosis].[Diagnosis].&[1st deg burn chin])})

    Unknown

    April 4, 2007 at 7:15 pm

  2. Yes, it does end up being a bit long-winded, which is exactly why Darren\’s sproc is so useful.

    Chris

    April 4, 2007 at 9:57 pm

  3. This is a good read.
    I am able to get the correct value I am looking for when I actually write the MDX statement. However, when I try to implement this as a Member in the actual cube I get an Error with the “The function expects a string or numeric expression for argument. A tuple set expression was used.”
    I have looked at the multi-select postings and I can get that solution to work again writing the MDX.
    I am looking to generate a Distinct Count value with OR conditions as a Calculated Member in the cube.

    Thanks,
    Robert

    Robert

    July 21, 2011 at 7:21 pm

    • Hi Robert,

      Can you post the actual code you’re using for your calculated member?

      Chris

      Chris Webb

      July 22, 2011 at 8:06 am

  4. Chris,

    The idea is to get a count based on 3 seperate conditions.
    The first condition lets say is Clothing all promotions
    The second condition is Bikes and Touring 3000 Promotion
    The third condition is Components and Sport Helmet Promotion.
    Below is the MDX and TSQL..
    The MDX Measures.[Customer Count] matches the TSQL Distinct Count, however, the Measures.test generates the expecting numeric or string expression. I understand why it is happening. My question is can this be re-written as a Member to generate the correct result?

    The Measures.test returns the expecting numeric or string expression.
    with member Measures.test as
    ({([Product].[Category].&[3], [Promotion].[Promotion].[All Promotions])
    ,
    ([Product].[Category].&[1] –Bikes
    ,[Promotion].[Promotion].&[13]–touring 3000 bike promotion
    )
    ,
    ([Product].[Category].&[2] –components}
    ,[Promotion].[Promotion].&[8]) –sport helmet
    })
    select
    { Measures.test,
    [Measures].[Customer Count]
    }on 0
    from [Adventure Works]
    where
    ({([Product].[Category].&[3], [Promotion].[Promotion].[All Promotions])
    ,
    ([Product].[Category].&[1] –Bikes
    ,[Promotion].[Promotion].&[13]–touring 3000 bike promotion
    )
    ,
    ([Product].[Category].&[2] –components}
    ,[Promotion].[Promotion].&[8]) –sport helmet
    })

    Here is the TSQL Code
    select COUNT(distinct CustomerKey)
    from dbo.FactInternetSales fs
    inner join (select dprod.ProductKey, dpc.EnglishProductCategoryName
    from dbo.DimProduct dprod
    inner join dbo.DimProductSubcategory dpsc
    on dprod.ProductSubcategoryKey = dpsc.ProductSubcategoryKey
    inner join dbo.DimProductCategory dpc
    on dpsc.ProductCategoryKey = dpc.ProductCategoryKey) ctlDimProd
    on fs.ProductKey = ctlDimProd.ProductKey
    inner join dbo.DimPromotion pdrom
    on fs.PromotionKey = pdrom.PromotionKey
    where ctlDimProd.EnglishProductCategoryName = ‘Clothing’
    or (ctlDimProd.EnglishProductCategoryName = ‘Bikes’ and pdrom.EnglishPromotionName = ‘Touring-3000 Promotion’)
    or (ctlDimProd.EnglishProductCategoryName = ‘Components’ and pdrom.EnglishPromotionName = ‘Sport Helmet Discount-2002′)

    As always thanks for the help!!

    Robert

    Robert

    July 22, 2011 at 6:49 pm

    • Hi Robert,

      The problem with your calculation is that you’re trying to return a set of tuples – and you can’t see a set of tuples, you need to aggregate or sum the set together to get a single numeric value to see in the cell. Try something like:

      with member Measures.test as
      aggregate({([Product].[Category].&[3], [Promotion].[Promotion].[All Promotions])
      ,
      ([Product].[Category].&[1] –Bikes
      ,[Promotion].[Promotion].&[13]–touring 3000 bike promotion
      )
      ,
      ([Product].[Category].&[2] –components}
      ,[Promotion].[Promotion].&[8]) –sport helmet
      }, Measures].[Customer Count])

      Chris Webb

      July 23, 2011 at 10:03 pm

  5. Chris,

    That was/is exactly the problem and I really appreciate the feedback. I have successfully created my calculated member in the cube to return the correct results.

    Thanks,
    robert

    Robert

    July 26, 2011 at 6:01 pm

  6. Chris – I tried your OR method and it worked great!

    SELECT [Measures].[Fact Member Purchases Count] on 0
    FROM [MbrPurchase4]
    WHERE (
    {[Dim Date].[Month Of Year].&[March 2012],[Dim Date].[Month Of Year].[All]
    ,[Dim Date].[Month Of Year].&[All],[Dim Date].[Month Of Year].[March 2012]
    });

    I’m new to SSAS but experienced with tsql. One of the most powerful things I do with OR in tsql is turn on and off filtering with parameters. Here is a quick example:

    Declare @Active as bit

    –Set @Active = 1; // will return only active
    –Set @Active = 0; // will return on inactive
    Set @Active = Null; // will return all (active & inactive)

    SELECT [DepartmentName],[Active]
    FROM [PriceGuide].[dbo].[DepartmentList]
    where(( @Active is null) or ([Active] = @Active))

    Question: Can this be done with MDX?

    Brian Sivel

    March 27, 2013 at 6:34 pm

    • Hi Brian,

      Kind of. MDX does have parameters but you can’t set values for them in the query itself, you can only pass values to these parameters from .NET or whatever you’re using to run your query. However you could declare some calculated measures to do this or named sets to do something similar. For example,

      WITH SET ACTIVESET AS IIF(1=1, {[Dim Date].[Month Of Year].&[March 2012]}, {})

      Chris Webb

      March 28, 2013 at 6:47 am

  7. Hi Chris,

    How can we pass a null value to a parameter ??

    Basically what I want to achieve is a searchable parameter list i.e two parameters(dependent ) will be there in which 2nd parameter will display values in the drop down containing keywords typed in 1st parameter(like functionality in tsql) and by default the 1st parameter will be empty and 2nd parameter will have all members in it ..

    I’ve achieved first requirement (like functionality one) can you please guide me with second one ..?

    my mdx looks something like this so far is

    WITH MEMBER [Measures].[ParameterCaption] AS [Customer].[Customer].[Customer].CURRENTMEMBER.MEMBER_CAPTION
    MEMBER [Measures].[ParameterValue] AS [Customer].[Customer].[Customer].CURRENTMEMBER.UNIQUENAME
    MEMBER [Measures].[ParameterLevel] AS [Customer].[Customer].[Customer].CURRENTMEMBER.LEVEL.ORDINAL
    SET FILTERED AS
    FILTER ([Customer].[Customer].[Customer].ALLMEMBERS,
    instr([Customer].[Customer].[Customer].CURRENTMEMBER.MEMBER_CAPTION,@Part)>0 )
    SET FILTERED1 AS
    FILTER ([Customer].[Customer].[Customer].ALLMEMBERS,
    instr([Customer].[Customer].[Customer].CURRENTMEMBER.MEMBER_CAPTION,NULL)>0 )

    SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS,
    iif(@Part=null,FILTERED1,Filtered) ON ROWS
    FROM [PeninsulaDataWarehouse]

    here @Part is the 1st parameter and this mdx script belongs to 2nd parameter dropdown.

    Thanks in advance !!!

    Newbie37

    December 31, 2013 at 6:13 am

  8. Hi I am trying to achive OR & AND clause in where clause. I am able to achieve half of them, but the last two lines am not able to achieve, am getting an error(below). Can you check the query and let me know what is wrong in that? also is this approach is right one? This is an urgent, Can any one help me.

    Error : The function expects a string or numeric expression for the argument. A tuple set expression was used.

    SELECT NON EMPTY {
    [Measures].[Sum of TEST_RESULT_VALUE],
    [Measures].[Sum of PHYSICOCHEMICAL_RESULT_VALUE],[Measures].[Sum of BLEND_WEIGHTAGE] }
    ON COLUMNS, NON EMPTY { ( [DIM_BLEND].[BLEND_NUMBER].[BLEND_NUMBER].ALLMEMBERS
    * [DIM_TESTTYPE].[TESTTYPE_CODE].[TESTTYPE_CODE].ALLMEMBERS
    * [DIM_PHYSICOCHEMICAL].[PHYSICOCHEMICAL_DESC].[PHYSICOCHEMICAL_DESC].ALLMEMBERS
    * [DIM_ADDITIVE].[ADDITIVE_NAME].[ADDITIVE_NAME].ALLMEMBERS
    //* filter([DIM_ADDITIVE].[ADDITIVE_NAME].[ADDITIVE_NAME].ALLMEMBERS,[Sum of BLEND_WEIGHTAGE]>=1.)–,[Sum of BLEND_WEIGHTAGE]<=1.5))
    * [DIM_RESULT].[RESULT_NAME].[RESULT_NAME].ALLMEMBERS
    –* [DIM_RESULT].[RESULT_CODE].[RESULT_CODE].ALLMEMBERS
    * [DIM_TESTKEY].[PERFORMANCE_TESTKEY].[PERFORMANCE_TESTKEY].ALLMEMBERS ) }
    DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM (
    SELECT (
    {
    [DIM_TESTKEY].[PERFORMANCE_TESTKEY].&[0000040520],
    [DIM_TESTKEY].[PERFORMANCE_TESTKEY].&[0000180065],
    [DIM_TESTKEY].[PERFORMANCE_TESTKEY].&[0000182667],
    [DIM_TESTKEY].[PERFORMANCE_TESTKEY].&[0000246677],
    [DIM_TESTKEY].[PERFORMANCE_TESTKEY].&[0000247183],
    [DIM_TESTKEY].[PERFORMANCE_TESTKEY].&[0000182472],
    [DIM_TESTKEY].[PERFORMANCE_TESTKEY].&[0000119216]
    } )
    ON COLUMNS FROM [PTREPORTS_Tabular_Cube])
    WHERE {
    ([DIM_RESULT].[RESULT_CODE].&[FC1_TTTT],[DIM_PHYSICOCHEMICAL].[PHYSICOCHEMICAL_CODE].[All],[DIM_ADDITIVE].[ADDITIVE_ID].[All]),
    ([DIM_RESULT].[RESULT_CODE].&[FC2_TTTT],[DIM_PHYSICOCHEMICAL].[PHYSICOCHEMICAL_CODE].[All],[DIM_ADDITIVE].[ADDITIVE_ID].[All]),

    ([DIM_RESULT].[RESULT_CODE].[All],[DIM_PHYSICOCHEMICAL].[PHYSICOCHEMICAL_CODE].&[1],[DIM_ADDITIVE].[ADDITIVE_ID].[All]),
    ([DIM_RESULT].[RESULT_CODE].[All],[DIM_PHYSICOCHEMICAL].[PHYSICOCHEMICAL_CODE].&[2],[DIM_ADDITIVE].[ADDITIVE_ID].[All]),
    ([DIM_RESULT].[RESULT_CODE].[All],[DIM_PHYSICOCHEMICAL].[PHYSICOCHEMICAL_CODE].&[3],[DIM_ADDITIVE].[ADDITIVE_ID].[All]),
    ([DIM_RESULT].[RESULT_CODE].[All],[DIM_PHYSICOCHEMICAL].[PHYSICOCHEMICAL_CODE].&[4],[DIM_ADDITIVE].[ADDITIVE_ID].[All]),

    //([DIM_RESULT].[RESULT_CODE].[All],[DIM_PHYSICOCHEMICAL].[PHYSICOCHEMICAL_CODE].[All],[DIM_ADDITIVE].[ADDITIVE_ID].&[6065]),
    //([DIM_RESULT].[RESULT_CODE].[All],[DIM_PHYSICOCHEMICAL].[PHYSICOCHEMICAL_CODE].[All],[DIM_ADDITIVE].[ADDITIVE_ID].&[5536])
    ([DIM_RESULT].[RESULT_CODE].[All],[DIM_PHYSICOCHEMICAL].[PHYSICOCHEMICAL_CODE].[All],filter([DIM_ADDITIVE].[ADDITIVE_ID].&[6065],([FACT_BLEND_TEST_RESULTS].[BLEND_WEIGHTAGE].&[7.E-1] : [FACT_BLEND_TEST_RESULTS].[BLEND_WEIGHTAGE].&[2.]))),
    ([DIM_RESULT].[RESULT_CODE].[All],[DIM_PHYSICOCHEMICAL].[PHYSICOCHEMICAL_CODE].[All],filter([DIM_ADDITIVE].[ADDITIVE_ID].&[5536],([FACT_BLEND_TEST_RESULTS].[BLEND_WEIGHTAGE].&[7.E-1] : [FACT_BLEND_TEST_RESULTS].[BLEND_WEIGHTAGE].&[2.])))
    }

    Joby

    April 17, 2014 at 2:08 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,072 other followers

%d bloggers like this: