Chris Webb's BI Blog

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

Scoped Assignments and Multiselect

with 9 comments

Something interesting to note regarding how scoped assignments behave with multiselect…

On the Adventure Works cube, add the following code to the MDX Script:

CREATE MEMBER CURRENTCUBE.MEASURES.TESTCALC AS 1;
 
SCOPE(MEASURES.TESTCALC, [Date].[Calendar Year].[Calendar Year].MEMBERS);
    THIS = 2;
END SCOPE;
 
SCOPE(MEASURES.TESTCALC, [Date].[Calendar Year].[All Periods]);
    THIS = 3;
END SCOPE;

Then run the following query:

 

SELECT [Measures].[TESTCALC] ON 0

FROM [Adventure Works]

WHERE([Date].[Calendar Year].&[2001])

 

It returns the value 2 as you would expect. Now run the following query where there is a set in the Where clause, giving a multiselect on 2001 and 2002:

SELECT [Measures].[TESTCALC] ON 0

FROM [Adventure Works]

WHERE({[Date].[Calendar Year].&[2001],[Date].[Calendar Year].&[2002]})

It returns the value 1 – which, strangely, is the value of the original TESTCALC calculated measure before any of the scoped assignments were applied, even though it would seem that the two scoped assignments between them should cover the cells returned by this query.

Now delete the code you added to the MDX Script and add the following:

 

CREATE MEMBER CURRENTCUBE.MEASURES.TESTCALC AS 1;

 
SCOPE(MEASURES.TESTCALC, [Date].[Calendar Year].MEMBERS);

    THIS = 4;

END SCOPE;

When you try to run the two queries above you get the value 4 returned in both cases. When I first saw this I thought the results returned by two scenarios were inconsistent and that there was something buggy going on in the first one, but Jeffrey told me there is a specific rule in the engine that dictates this behaviour, so it’s how it’s intended to work – as a result, it’s something that needs to be understood and allowed for in any scoped assignments you write.

Written by Chris Webb

October 27, 2011 at 10:43 pm

Posted in MDX

9 Responses

Subscribe to comments with RSS.

  1. I’ve just tested it and of course
    SELECT [Measures].[TESTCALC] ON 0,
    [Date].[Calendar Year].Members on 1
    FROM [Adventure Works]

    returns the expected result (3 for [All Periods] and 2 for individual years). I would really like to know how the “specific rule” is defined and why this can be “intended behaviour”

    Andrej Kuklin

    October 28, 2011 at 11:03 am

  2. I too would like to understand why its the intended behaviour.
    -Saurav

    Saurav Mishra

    October 28, 2011 at 12:13 pm

  3. Chris,

    I recreated your experiment and I see similar results but with a couple of exceptions noted below. I ‘d like to know the rules that are in effect here if anyone can offer an explanation. We are struggling with a couple of our scoped measures and this might help explain the results we are seeing.
    Thanks,
    Paul

    CREATE MEMBER CURRENTCUBE.[Measures].[TESTCALC] AS 1;

    SCOPE([Measures].[TESTCALC], [Time].[Fisca].[Year].Members);
    THIS = 2;
    END SCOPE;

    SCOPE([Measures].[TESTCALC], [Time].[Fiscal].[All]);
    THIS = 3;
    END SCOPE;

    SELECT
    {[Measures].[TESTCALC]} ON COLUMNS,
    {[Time].[Fiscal].[All]
    ,[Time].[Fiscal].[Year].[FY2010]
    ,[Time].[Fiscal].[Period].[FY2010-APR]} ON ROWS
    FROM [Test Cube]

    As expected, results in:
    All 3
    FY2010 2
    FY2010-APR 1

    Each of these queries also create results with values from the scope statements:

    SELECT {[Measures].[TESTCALC]} ON COLUMNS FROM [Test Cube] WHERE {[Time].[Fiscal].[All]}
    Result: 3

    SELECT {[Measures].[TESTCALC]} ON COLUMNS FROM [Test Cube] WHERE {[Time].[Fiscal].[Year].[FY2010]}
    Result: 2

    SELECT {[Measures].[TESTCALC]} ON COLUMNS FROM [Test Cube] WHERE {[Time].[Fiscal].[Period].[FY2010-APR]}
    Result: 1

    Using a multi-member WHERE clause causes the unscoped value to appears:

    SELECT {[Measures].[TESTCALC]} ON COLUMNS FROM [Test Cube]
    WHERE {[Time].Fiscal].[Year].[FY2010], [Time].[Fiscal].[Year].[FY2011]}
    Result: 1

    Using Excel 2007, I get a different answer. Looking at Excel’s MDX, I see a subquery. This query returns the “all” scope value of 3.
    SELECT FROM
    (SELECT
    ({[Time].[Fiscal].[Year].&[2010],[Time].[Fiscal].[Year].&[2011]}) ON COLUMNS
    FROM [Test Cube]) WHERE ([Measures].[TESTCALC])
    Result: 3

    Paul Stephens

    October 28, 2011 at 4:54 pm

    • With a subselect I would expect to get the All Member value back – a subselect doesn’t alter the currentmember, only a where clause does. Also, are you using a user hierarchy (as opposed to an attribute hierarchy) for your tests?

      Chris Webb

      October 28, 2011 at 9:54 pm

  4. Hi Chris,

    This is in reference to the post http://social.technet.microsoft.com/Forums/en-US/sqlanalysisservices/thread/e0a4975f-8f19-40d0-90ff-4fcca1d301c1

    Thanks for your help and I was able to solve the issue. Here is the modified code

    CREATE MEMBER CURRENTCUBE.[Measures].[SalesAmount]
    AS
    null,
    FORMAT_STRING = “0″,
    VISIBLE = 1 ;

    /* When the channel is at all Level and when a region is selected show region data */
    SCOPE([In Market Channel].[InMarketChannelAttr].[All Channels],[In Market Region].[InMarketRegionAttr].members);
    [Measures].[SalesAmount]=[Measures].[Re Sales Amount];
    END SCOPE;

    /* When the channel is at all Level and when all region show channel data */
    SCOPE([In Market Channel].[InMarketChannelAttr].[All Channels],[In Market Region].[InMarketRegionAttr].[All Regions]);
    [Measures].[SalesAmount]=[Measures].[Ch Sales Amount];
    END SCOPE;

    CREATE MEMBER CURRENTCUBE.[Measures].[TotalAmount]
    AS
    [Measures].[SalesAmount],
    FORMAT_STRING = “0″,
    VISIBLE = 1 ;

    However I do find one scenario , If I change the Scope statements i.e. If I add the second scope statement at the first place and when I select multiple regions, I am encountering the same issue. However If I first write the scope of region members and then add a scope for all region member then it works fine.
    Please let me know why the behaviour. I have no clue why this is happening.

    Thanks once again !

    ram

    April 28, 2012 at 6:59 am

    • It’s hard to say – I’d probably need to spend about an hour looking at your cube and attribute relationships doing some tests before I could be sure why that was happening.

      Chris Webb

      April 28, 2012 at 8:40 pm

  5. Hi Chris,
    I watched your video “fun with scope assignment on SQLbits.com. It is very helpful, I have better understand the scope statement than before.
    But I have hard time to understand the problem below.
    The first scope statement works just fine; in the second statement I change to use data range to replace the calendar year, it return empty. I cannot figure out why. Can you please help?

    scope([Date].[Calendar].[Calendar Year].&[2011]);

    scope([Measures].[Most Frequent Price1]);
    This=topcount(
    [Price].[Price].[Price].MEMBERS,
    1,
    [Measures].[Invoice Transaction Count]
    ).Item(0).ITEM(0).MemberValue;

    end scope;
    end scope;

    scope([Date].[Calendar].[Date].&[20110101]:[Date].[Calendar].[Date].&[20111231]);

    scope([Measures].[Most Frequent Price1]);
    This=topcount(
    [Price].[Price].[Price].MEMBERS,
    1,
    [Measures].[Invoice Transaction Count]
    ).Item(0).ITEM(0).MemberValue;

    end scope
    end scope

    thanks
    Hua

    Hua Yang

    July 2, 2012 at 8:53 pm

    • Is [Date].[Calendar] a user hierarchy? Can you try scoping on the attribute hierarchy used to build that level instead?

      Chris Webb

      July 2, 2012 at 11:26 pm

      • Chris,

        I reviewed the video, found my problem.

        thank you for your reply and your video!

        hyang

        July 9, 2012 at 1:45 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 2,868 other followers

%d bloggers like this: