locked
Question on Scope RRS feed

  • Question

  • Hi,


    I am having a product dimension with multiple attribute and user defined hierarchies(total of 24).

    Now I need to scope a particulat member in a attribute hierarchy and I use a SCOPE similar to the following example:



    SCOPE([Product].[Product Group].[MemberName],[Measures].[Mymeasure]);

    THIS=100;

    END SCOPE;

    This scope works when I have [Product Group] ONLY on rows but stops working when I nest one more product attribute(say [Product].[Flavour Type] on rows. To resolve this, I had to use the following scope:


    SCOPE([Product].[Product Group].[MemberName],[Product].[Flavour Type].members,[Measures].[Mymeasure]);

    THIS=100;

    END SCOPE;

    The above scope works as expected, but does this mean that I should be including all my 25 product attributes in the scope? Can you please let me know if there is any better way to do it.

    I am using SSAS 2008.

    Thanks!








    Friday, January 15, 2010 5:23 PM
    Answerer

Answers

  • It is relatively easy once you figure it out.



    A scope defines a subcube. It's not a dot, plane or cube, it's a multidimensional space. Meaning, there is a member of every hierarchy in the cube inside that scope's definition. A multidimensional tuple is always present, in every part of the code we make, in every little definition. That's the way OLAP works although we tend to forget that.

    A luxury we have in form of not having to define such a huge tuple is a double-edged sword. The good thing is that during the coding process we can lean on current members and therefore keep our expression shorter. The bad thing is that we easily overlook what the current context really is because of such short expressions and we expect more than we can get. Don't get me wrong, we need them to be short and it's a good thing they can be such. It's just that we must not make them too short. Luckily, this isn't such a case, we don't have to bring all 24 hierarchies inside the scope. Here we have something else going on.



    Current members come either from default members or from overwrites. Defaults come from dimension definitions or from MDX script and are relatively easy to comprehend. Moreover, most of the time they are not explicitely defined, which means they are implicitely defined by SSAS as root members.

    Overwrites on the other hand are trickier. There are explicit overwrites, the one we specify in the code and there are implicit overwrites, the one that role like dominos initiated by an explicit overwrite. The problem is - we're blind. We know they exist, but we just can't see them. It's like quantum mechanics. And although occasionally we've been given some of the rules (which btw sometimes change from version to version), it's a misterious territory full of exceptions and booby traps never properly marked on our road to success. We could learn how overwrites behave and therefore predict what will happen, but we'd never know when we'll hit the next uncharted mine. Anyway, life goes on and so shall we with this interpretation here.



    Some overwrites trigger other things, some don't. Otherwise, we'd have no dominos to play with. Therefore, there is a stop mechanism for them. For example, implicit overwrite will never trigger another implicit overwrite. That's an example of a stop, and a logical one too. Only explicit overwrites trigger implicite ones. And only explicit overwrites trigger scopes. So it's not a case of attribute overwriting either. What is it then?



    In scope's definition it says we have selected an area that consists of members and a measure. Implicitely, there are default members of all other hierarchies, with a great probability of them being root members of those hierarchies. When we have only the first hierarchy in query, the one that scope has been defined on, we can see that scope in action. But, it is so only because all other hierarchies were either on their defaults (or implicitely adjusted to members on rows in this query, row by row). And as we said, implicite overwrites don't trigger the scope.

    Once we've explicitely requested members from another hierarchy, a tuple of two members failed to trigger the scope because scope is defined to fire only when the second hierarchy is on default member. Here it wasn't the case, hence it didn't work. The same would happen for any other hierarchy of the cube. The scope is designed to work only for non-root members of the first hierarchy and default members of all other hierarchies. That's how it should be read and interpreted.



    So, what's the solution, what can we do?

    Like in math, we can start from the opposite. First, by defining 100 as the value for that measure. Then, by scoping subcubes where we need another value. Root() function might help in that case. It looks like the root of all hierarchies should be something else.



    I took the liberty of making a detailed explaination in order to help others on this often misunderstood topic.

    Best,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    • Marked as answer by Raymond-Lee Thursday, January 28, 2010 4:10 AM
    Saturday, January 16, 2010 1:04 AM
    Answerer
  • Hi All,

    Thanks for your replies, I managed to get a solution for this! I had to add the key attribute to the scope and all my product attributes start working.


    SCOPE([Product].[Product Group].[MemberName],
    [Product].[KeyAttribute].[KeyAttribute].members,
    [Measures].[Mymeasure]);
    THIS=100;
    END SCOPE;
    Monday, January 18, 2010 5:52 AM
    Answerer

All replies

  • Hi,

    Maybe you are hit by the many times strange rules of attribute relationship overwriting? See http://www.sqlserveranalysisservices.com/OLAPPapers/AttributeRelationships.htm for an explanation.

    Frank
    Friday, January 15, 2010 10:45 PM
  • It is relatively easy once you figure it out.



    A scope defines a subcube. It's not a dot, plane or cube, it's a multidimensional space. Meaning, there is a member of every hierarchy in the cube inside that scope's definition. A multidimensional tuple is always present, in every part of the code we make, in every little definition. That's the way OLAP works although we tend to forget that.

    A luxury we have in form of not having to define such a huge tuple is a double-edged sword. The good thing is that during the coding process we can lean on current members and therefore keep our expression shorter. The bad thing is that we easily overlook what the current context really is because of such short expressions and we expect more than we can get. Don't get me wrong, we need them to be short and it's a good thing they can be such. It's just that we must not make them too short. Luckily, this isn't such a case, we don't have to bring all 24 hierarchies inside the scope. Here we have something else going on.



    Current members come either from default members or from overwrites. Defaults come from dimension definitions or from MDX script and are relatively easy to comprehend. Moreover, most of the time they are not explicitely defined, which means they are implicitely defined by SSAS as root members.

    Overwrites on the other hand are trickier. There are explicit overwrites, the one we specify in the code and there are implicit overwrites, the one that role like dominos initiated by an explicit overwrite. The problem is - we're blind. We know they exist, but we just can't see them. It's like quantum mechanics. And although occasionally we've been given some of the rules (which btw sometimes change from version to version), it's a misterious territory full of exceptions and booby traps never properly marked on our road to success. We could learn how overwrites behave and therefore predict what will happen, but we'd never know when we'll hit the next uncharted mine. Anyway, life goes on and so shall we with this interpretation here.



    Some overwrites trigger other things, some don't. Otherwise, we'd have no dominos to play with. Therefore, there is a stop mechanism for them. For example, implicit overwrite will never trigger another implicit overwrite. That's an example of a stop, and a logical one too. Only explicit overwrites trigger implicite ones. And only explicit overwrites trigger scopes. So it's not a case of attribute overwriting either. What is it then?



    In scope's definition it says we have selected an area that consists of members and a measure. Implicitely, there are default members of all other hierarchies, with a great probability of them being root members of those hierarchies. When we have only the first hierarchy in query, the one that scope has been defined on, we can see that scope in action. But, it is so only because all other hierarchies were either on their defaults (or implicitely adjusted to members on rows in this query, row by row). And as we said, implicite overwrites don't trigger the scope.

    Once we've explicitely requested members from another hierarchy, a tuple of two members failed to trigger the scope because scope is defined to fire only when the second hierarchy is on default member. Here it wasn't the case, hence it didn't work. The same would happen for any other hierarchy of the cube. The scope is designed to work only for non-root members of the first hierarchy and default members of all other hierarchies. That's how it should be read and interpreted.



    So, what's the solution, what can we do?

    Like in math, we can start from the opposite. First, by defining 100 as the value for that measure. Then, by scoping subcubes where we need another value. Root() function might help in that case. It looks like the root of all hierarchies should be something else.



    I took the liberty of making a detailed explaination in order to help others on this often misunderstood topic.

    Best,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    • Marked as answer by Raymond-Lee Thursday, January 28, 2010 4:10 AM
    Saturday, January 16, 2010 1:04 AM
    Answerer
  • many thanks Tomislav for a detailed explanation.

    I still have few issues around this,


    "In scope's definition it says we have selected an area that consists of members and a measure. Implicitely, there are default members of all other hierarchies, with a great probability of them being root members of those hierarchies. When we have only the first hierarchy in query, the one that scope has been defined on, we can see that scope in action. But, it is so only because all other hierarchies were either on their defaults (or implicitely adjusted to members on rows in this query, row by row). And as we said, implicite overwrites don't trigger the scope."

    I believe this is not true for dimensions which does not have any hierarchies specified in the SCOPE

    Example:

    SCOPE([Product].[Product Group].[MemberName],[Measures].[Mymeasure]);
    THIS=100;
    END SCOPE;

    The above scope works well when I have a non [Product] dimension nested on rows(i.e.) I have Time and [Product Group] nested on rows and the SCOPE still gets executed. The issue comes only when I have another [Product] attribute nested on rows along with [Product Group].

    Hence when you say "there are default members of all other hierarchies", you talk about only the hierarchies within the same dimension?


    Also my requirement is little more complicated, sorry I should have specified it in the first post

    I have the following requirement

    If My [Product Group]="Type 1" then [MyMeasure]=[someMeasure]*100
       My [Product Group]="Type 2" then [MyMeasure]=[someMeasure]*200
       My [Product Group]="Type 3" then [MyMeasure]=[someMeasure]*300
    else null

    i am not sure how Root() would help me in this case. Do let me know your thoughts

    Thanks!

    Saturday, January 16, 2010 3:13 AM
    Answerer
  • Hi All,

    Thanks for your replies, I managed to get a solution for this! I had to add the key attribute to the scope and all my product attributes start working.


    SCOPE([Product].[Product Group].[MemberName],
    [Product].[KeyAttribute].[KeyAttribute].members,
    [Measures].[Mymeasure]);
    THIS=100;
    END SCOPE;
    Monday, January 18, 2010 5:52 AM
    Answerer
  • Hi Prakash,

    I'm glad you managed to solve your problem.

    Yes, it seems I got carried away in the end and made a mistake. Scope is relevant only for dimension's hierarchies. Thanks for correcting me.

    Best regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    Monday, January 18, 2010 5:54 PM
    Answerer