none
MDX subcube: how to use scope function? RRS feed

  • Question

  •  
    Hi to all,
    I have a problem with scope function. I'm trying to assign at a member of a dimension, different values depending on the time hierarchy used.
    My time dimension has 2 hierarchies: Calendar (levels are Year, quarter, month, day) and Week (levels are: Year, week). There's not attribute in common (Year of Calendar and Year of Week are two different hierarchy attributes).  
    My mdx script has some problems with scope function, if I write only this:

    Scope([Measures].AllMembers);   
        
        Scope([Time].[Calendar].Members,[Time].[TimeKey].members);  
        ([Calculations].[YTD]=  "CalendarValue"
        );   
        End Scope;  
     
    End Scope;  
     
     


    All works perfectly and all works also if I I write only this statement:

    Scope([Measures].AllMembers);   
     
        Scope([Time].[Week].Members,[Time].[TimeKey].members);  
        ([Calculations].[YTD]= "WeekValue"
        );   
        End Scope;  
     
    End Scope;  
     
     


    The statement doesn't work no more, or better it works but the cube give me back only null values when I dice it with Calendar Hierarchy, when I write this:

    Scope([Measures].AllMembers);   
     
        Scope([Time].[Calendar].Members,[Time].[TimeKey].members);  
        ([Calculations].[YTD]= "CalendarValue"
        );   
        End Scope;  
     
        Scope([Time].[Week].Members,[Time].[TimeKey].members);  
        ([Calculations].[YTD]=  "WeekValue"
        );   
        End Scope;  
     
     
    End Scope;  




    There's somenthing wrong in my statement? One of you can help me?
    Thanks in advance,
    Francesco
    Wednesday, February 4, 2009 12:50 PM

Answers

  • yes,

    if you overwrite one value 2 times in your mdx-script, the last one counts

    what surprises me is that if you slice by week-hierarchy that all cells are empty except 2008
    because the first scope should overwrite the value since if you not explicity define the value for [Time].[Calendar], the defaultmember is taken what i guss is the all-member?
    further you scope [Time].[Calendar].members -> this set includes the all-member thats why i wrote that you should use descendants instead to exclude the all-member

    you could also include the all-member of [Time].[Calendar]  in the second scope statement so the value is only overwritten, if there is nothing selected in the [Time].[Calendar] hierarchy

    for parallel hierarchies i usually use

    SCOPE(...);
        SCOPE(Descendants([Time].[Hier1].defaultmember, , AFTER),
                    [Time].[Hier2].defaultmember);
            this=...;
        END SCOPE;

        SCOPE([Time].[Hier2].defaultmember,
                    Descendants([Time].[Hier1].defaultmember, , AFTER));
            this=...;
        END SCOPE;
    END SCOPE;

    this way only 1 scope-statement will be hit regardless of what you might select

    greets,
    gerhard
    - www.pmOne.com -
    Wednesday, February 11, 2009 4:57 PM
    Answerer

All replies

  • Hi to all,
    No ideas? No one??
    Francesco
    Monday, February 9, 2009 6:08 PM
  • ok,
    just an idea
    you use [Time].[Calendar].members  which includes the All-Member of Time.Calendar
    then you use [Time].[Week].members which includes the All-Member of Time.Week

    so you overwrite the value 2 times because the second scope is always executed because the All-member is included

    try [Time].[Week].[Week].members instead e.g. Descendants([Time].[Calendar].levels(0).item(0), , AFTER) (everything below the all-member not including the alll-member)

    greets,
    gerhard
    - www.pmOne.com -
    Monday, February 9, 2009 7:42 PM
    Answerer
  • Thanks Gerhard, I have tried you solution but unlukely it doesn't resolve the problem, I explain me better, It seems that the second statement overwrite the values indipendent of what I have write on scope. TO prove this I have restricted the scope of the second statement at one year:
    Scope([Measures].AllMembers);   
     
        Scope([Time].[Calendar].Members,[Time].[TimeKey].members);  
        ([Calculations].[YTD]= "CalendarValue"
        );   
        End Scope;  
     
        Scope([Time].[Week Year].&[2008],[Time].[TimeKey].members);       *[Week Year] is the attribute used to make the year level at week hierarchy
        ([Calculations].[YTD]=  "WeekValue"
        );   
        End Scope;  
     
     
    End Scope;


    Now happens that quen I slice my cube with Week hierarcky I see null everywhere  (and this is right) at exception of 2008 where I see "WeekValue" but when I slice the cube with Calendar hierarchy I see "CalendarValue" everywhere at exception of 2008 where I see "WeekValue" that means that this value has been overwritten by the second statement.
    It seems that the cube is not able to distinguish beetween two parallel hierarchy of a same dimension or better....I'm not able to write a statement that divide the cube by hierarchy of a same dimension.
    Have you any other ideas?
    greets,

    francesco

    Wednesday, February 11, 2009 12:14 AM
  • yes,

    if you overwrite one value 2 times in your mdx-script, the last one counts

    what surprises me is that if you slice by week-hierarchy that all cells are empty except 2008
    because the first scope should overwrite the value since if you not explicity define the value for [Time].[Calendar], the defaultmember is taken what i guss is the all-member?
    further you scope [Time].[Calendar].members -> this set includes the all-member thats why i wrote that you should use descendants instead to exclude the all-member

    you could also include the all-member of [Time].[Calendar]  in the second scope statement so the value is only overwritten, if there is nothing selected in the [Time].[Calendar] hierarchy

    for parallel hierarchies i usually use

    SCOPE(...);
        SCOPE(Descendants([Time].[Hier1].defaultmember, , AFTER),
                    [Time].[Hier2].defaultmember);
            this=...;
        END SCOPE;

        SCOPE([Time].[Hier2].defaultmember,
                    Descendants([Time].[Hier1].defaultmember, , AFTER));
            this=...;
        END SCOPE;
    END SCOPE;

    this way only 1 scope-statement will be hit regardless of what you might select

    greets,
    gerhard
    - www.pmOne.com -
    Wednesday, February 11, 2009 4:57 PM
    Answerer
  • Yes Gerhard you are right your statement works! My new statement now is this:

    Scope(descendants([Time].[Calendar].level(0).item(0),,after),[Time].[Week].level(0).item(0));  
        ([Calculations].[YTD]= "CalendarValue"
        );   
        End Scope;  
     
        Scope(descendants([Time].[Week].level(0).item(0),,after),[Time].[Calendar].level(0).item(0));  
        ([Calculations].[YTD]=  "WeekValue"
        );   
        End Scope;  
     
     

    Tis statement now, seems to give me back exactly what I need but I don't understand why....the first statement says: take the all-member of the second hierarchy and all members of the first but not the all-member. The second statement says: thate the all- member of the first hierarchy and all members of the second but not the all-member.....(I belive...) Why in this case the second statement don't overwrite the first one??the all-member is always present...

    thanks,

    greets, Francesco

    Thursday, February 12, 2009 12:24 AM
  • ok,

    if you do your selection using Hier1 only, Hier2 remains on the All-member/defaultmember whereas Hier1 is on the selected member
    so only the first SCOPE will be hit because in the second scope we check if Hier1 is on the All-member/defaultmember, what is not the case

    for Hier2 its the same just the other way round

    the only problem you may be facing is that if you select the year from Hier1 and the Week from Hier2, none of the 2 scopes will be hit

    greets,
    gerhard
    - www.pmOne.com -
    Thursday, February 12, 2009 8:45 AM
    Answerer
  • Thanks a lot Gerhard! Now I understood,your response are very precious!
    Thanks another time!
    Greets, Francesco
    Saturday, February 14, 2009 3:42 PM
  • I'd up the question again with this addition:

    As far as I understood SCOPE creates a permanent subcube for a cube. Once the request falls in to that space the defined MDX rule applies to it.

    As it was mentioned above by Gerhard using Descendants of All-member without itself should limit the subcube(s) with all underlying items but not the hierarchy root. That would allow to construct a set of subcubes with different slices/conditions to serve different selections.

    But! According to MSDN about subcubes: "If you include any member, you include that member's ascendants and descendants", "If you include every member from a level, you include all members from the hierarchy" and "A subcube will always contain every All-member from the cube".

    Doesn't this mean that statement like SCOPE(Descendants([Time].[Hier1].defaultmember, , AFTER), [Time].[Hier2].defaultmember); returns the whole cube instead of only a subset of [Time].[Hier1] below the All-member (since every single member will implicitly generate the All-member)?

    I mean that it is exacly what I saw when using CREATE SUBCUBE Some AS SELECT Descendants([Time].[Hier1].defaultmember, , AFTER) ON 0 FROM Some; SELECT [Time].[Hier1].Members ON 0 FROM SOME; And probably this happens to SCOPE funtionality. I guess.

    Maybe anybody has something to clarify on this?

    --Thanks in advance, Dmitry 


    • Edited by dpokrovsky Friday, March 16, 2012 9:33 AM
    Thursday, March 15, 2012 10:05 AM
  • the MSDN-article is of course correct, but only applies to CREATE SUBCUBE-statement

    by using CREATE SUBCUBE all dimensions and their attributes are filtered by the members you specified in the subcube
    this is similar to using EXISTS() on all attributes in combination with the specified members

    EXISTS([Dim].[Hier].levels(0), <subcubemember of Dim>) will always return the All-Member and therefore it will be part of the created subcube

    SCOPEs work a bit different
    whether a member falls into a SCOPE can be determined with following EXISTS():

    EXISTS(<scope-subcube>, [Dim].[Hier].currentmember).COUNT = 1 
    so if <scope-subcube> does not contain the All-member, COUNT will return 0 when the current-member is the All-member
    (internally this is handled different, this example is just to demonstrate the difference)

    hth,
    gerhard


    - www.pmOne.com -

    Thursday, April 12, 2012 3:13 PM
    Answerer