Chris Webb's BI Blog

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

Scoped Assignments and Multiselect

with 12 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

12 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

  6. I am running into the same situation and I am in a bind.

    I tried to use an IF to see if there are more than one member like this:
    SCOPE(MEASURES.TESTCALC, [Date].[Calendar Year].[Calendar Year].MEMBERS);
    THIS = 2;

    If (count(existing [Date].[Calendar Year].[Calendar Year].MEMBERS)>1)
    Then
    THIS = 5;
    End if;

    END SCOPE;

    The IF works only if there is one member, but NOT if there are more than one members in the WHERE clause of the query!

    Each leaf has a different value for a particular measure – that works. But when more than one leaf is selected, I want it to default to specific number (the end user controls what leaves are selected). This is for one measure only. All the other measures aggregate normally.
    Any help is appreciated!

    BJ

    July 9, 2014 at 8:39 pm

    • Hi BJ, you are getting confused between IF and IIF() here (which is understandable). IF is a way of defining a scoped assignment, which means that it is evaluated when the MDX Script is executed (usually after processing) and not at query time. What’s more, you are scoping at the year level, which means that there will only ever be one year existing. If you want to detect whether more than one year is selected in a query then I suggest that you use Mosha’s method for detecting multiselects rather than trying to use a scoped assignment: http://sqlblog.com/blogs/mosha/archive/2007/08/25/mdx-in-katmai-dynamic-named-sets.aspx

      Chris Webb

      July 10, 2014 at 9:49 pm

      • Crhis, thanks for the explanation for the “IF”. It makes sense.
        Also thanks for the link to Mosha’s blog – it works. Someone else had posted a link to that blog that did not work.

        Here is my true problem:
        I have a dimension, and for each of the nodes and leaves, Measures.Benchmark gives the correct data.
        However, when multiple leaves are selected, I need the Measures.Benchmark for the parent node, and not an aggregated measure of the selected leaves.
        This measure is used in several calculations including KPIs, so it makes sense to set the value in a SCOPE statement.

        thanks

        BJ

        BJ

        July 10, 2014 at 10:07 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,301 other followers

%d bloggers like this: