locked
CrossJoin doesn't work with Muliple filters in sub-cube. RRS feed

  • Question

  • I have an .aspx page that generates some MDX.   It works great unless I want to use multiple filters on the data AND multiple "views" (crossjoin) of the data.   This is no doubt best illustrated by example.

    here is the query that does not work.   It does not error, but it returns 0 for a row-count, which is unexpected (ie:  incorrect):

     

    WITH SET MyRows
    AS NONEMPTY(
    NONEMPTYCROSSJOIN ([Advertiser Dimension].[Product].CHILDREN, [Time Dimension].[Date].CHILDREN),
    {[Measures].[Clicks],[Measures].[Leads]}
    )
    MEMBER [Measures].[Count] AS Count(MyRows)
    SELECT 
    { [Measures].[Count] } ON AXIS(0)
    FROM (
    SELECT ({[Time Dimension].[Hour].&[2010-02-02T00:00:00]
    :
    [Time Dimension].[Hour].&[2010-02-25T23:00:00]},{[Affiliate Dimension].[Affiliate].&[1083]}) ON AXIS(0) FROM [Filinet OLAP] 
    );

     

     

     

    Here is a query that does the crossjoin, but only has one filter in the subcube.   This query does work.

                    WITH SET MyRows
                    AS NONEMPTY(
                        NONEMPTYCROSSJOIN ([Advertiser Dimension].[Product].CHILDREN, [Time Dimension].[Date].CHILDREN),
                        {[Measures].[Clicks],[Measures].[Leads]}
                    )
                    MEMBER [Measures].[Count] AS Count(MyRows)
                    SELECT 
                    { [Measures].[Count] } ON AXIS(0)
                    FROM (
    	                SELECT ({[Time Dimension].[Hour].&[2010-02-02T00:00:00]
                    :
                    [Time Dimension].[Hour].&[2010-02-24T23:00:00]}) ON AXIS(0)	FROM [Filinet OLAP] 
                    );
    And, on the other hand, this is a query that does not do a cross-join, but does have multiple filters in the sub-cube.   This query also works, illustrating that, singly, my cross-join technique works, and my filtering technique works.  It's only when combined that they don't work together.  If anyone can explain why, and better yet, offer a solution that works, I'd be much appreciative:

                    WITH SET MyRows
                    AS NONEMPTY(
                        [Time Dimension].[Date].CHILDREN,
                        {[Measures].[Clicks],[Measures].[Leads]}
                    )
                    MEMBER [Measures].[Count] AS Count(MyRows)
                    SELECT 
                    { [Measures].[Count] } ON AXIS(0)
                    FROM (
    	                SELECT ({[Time Dimension].[Hour].&[2010-02-02T00:00:00]
                    :
                    [Time Dimension].[Hour].&[2010-02-24T23:00:00]},{[Affiliate Dimension].[Affiliate].&[1083]}) ON AXIS(0)	FROM [Filinet OLAP] 
                    );
    Thanks!
    Wednesday, February 24, 2010 9:37 PM

Answers

  • Query scoped named sets, like the one in your first query, are resolved once in the context of the WHERE clause, before any sub-selects are evaluated. If you want to calculated in the context of subselects you will need to put the set definition inline in the calculation.

    eg.

    MEMBER [Measures].[Count] AS Count(NONEMPTY(
       [Advertiser Dimension].[Product].CHILDREN
       * [Time Dimension].[Date].CHILDREN),
       * {[Measures].[Clicks],[Measures].[Leads]}
       ))
    SELECT
    { [Measures].[Count] } ON AXIS(0)
    FROM (
    SELECT ({[Time Dimension].[Hour].&[2010-02-02T00:00:00]
    :
    [Time Dimension].[Hour].&[2010-02-25T23:00:00]},{[Affiliate Dimension].[Affiliate].&[1083]}) ON AXIS(0) FROM [Filinet OLAP]
    );

    PS. I have also replaced the NonEmptyCrossjoin() function with the * operator as NonEmptycrossjoin() is a depreciated function and can be replace with normal crossjoins and the NonEmpty() function.
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Marked as answer by Tab Alleman 2 Tuesday, March 2, 2010 7:55 PM
    Thursday, February 25, 2010 3:02 AM

All replies

  • Hmm, on further investigation, I'm finding that my MDX is formed correctly, and the problem may be in the data.   I'll mark this resolved if I figure it out completely.
    Wednesday, February 24, 2010 10:25 PM
  • Query scoped named sets, like the one in your first query, are resolved once in the context of the WHERE clause, before any sub-selects are evaluated. If you want to calculated in the context of subselects you will need to put the set definition inline in the calculation.

    eg.

    MEMBER [Measures].[Count] AS Count(NONEMPTY(
       [Advertiser Dimension].[Product].CHILDREN
       * [Time Dimension].[Date].CHILDREN),
       * {[Measures].[Clicks],[Measures].[Leads]}
       ))
    SELECT
    { [Measures].[Count] } ON AXIS(0)
    FROM (
    SELECT ({[Time Dimension].[Hour].&[2010-02-02T00:00:00]
    :
    [Time Dimension].[Hour].&[2010-02-25T23:00:00]},{[Affiliate Dimension].[Affiliate].&[1083]}) ON AXIS(0) FROM [Filinet OLAP]
    );

    PS. I have also replaced the NonEmptyCrossjoin() function with the * operator as NonEmptycrossjoin() is a depreciated function and can be replace with normal crossjoins and the NonEmpty() function.
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Marked as answer by Tab Alleman 2 Tuesday, March 2, 2010 7:55 PM
    Thursday, February 25, 2010 3:02 AM
  • Ok, I noticed in my data that for the parameters I was using, I would have an empty [Measures].[Clicks] measure.   I changed the parameters in my sub-select such that both the [Clicks] measure and the [Leads] measure would have values, and I saw the expected results.

    So apparently NONEMPTY doesn't work the way I think it does.   Based on my test, it is only returning rows where both [Clicks] AND [Leads] have values.

    What I want is to return rows where EITHER [Clicks] OR [Leads] is not empty.  Is that possible?
    Tuesday, March 2, 2010 7:37 PM
  • Actually, Darren, replacing the deprecated NONEMPTYCROSSJOIN() with * does solve the problem, even with the parameters where one of the measures is empty, so I think I will go with that.

    Thanks!
    • Edited by Tab Alleman 2 Tuesday, March 2, 2010 9:26 PM more specific wording
    Tuesday, March 2, 2010 7:55 PM