none
How to avoid unused dimensions from a calculation? RRS feed

  • Question

  • Hi

    I started learning of SSAS 2008 (have experinece with 2000) and stuck with misunderstanding of the approaches how to work with multiple hierarchies within one dimension.

    Assume we have:

    1) dimensions: Time(Day>Month>Year>All), Products(Product>Type>All; Product>Season; Model>Family), Stores(Store>Network>All; Store>Location>All)

    Note, that Product's Model>Family hierarchy doesn't have All-member!

    2) measures: Budget(by Time(Month) and Stores(Store)), Sales (by Time(Day), Product(Product), Store(Store))

    So we want to calculate plan-fact, i.e.:

    Budget(at Time(Month>Year>All), Stores(Store>Network>All; Store>Location>All)) - Sales(at Time(Month>Year>All), Stores(Store>Network>All; Store>Location>All))

    In all other dimensions' combinations the difference should be NULL (because we can't observe the budget for a day or any product).

    So, is there any way to avoid the unused dimensions (which may be set to any of it's member at the time) with all there hierarchies from the calculation?

    --Tanks in advance.


    • Edited by dpokrovsky Friday, March 16, 2012 10:53 AM addition
    Friday, March 16, 2012 10:47 AM

All replies

  • Hello Dmitry, if I understood your problem correctly, you need to split your Products dimension into two - one for your (Product>Type>All; Product>Season), hierarchies and new one for (Model>Family) since they have different granularity.

    There is the way to do it with the single dimension, please take look on this article:

    http://ms-olap.blogspot.com/2010/01/different-granularity-in-single.html

    Best regards, Vlad.

    Tuesday, March 20, 2012 4:55 PM
    Moderator
  • Thank you for the reply. Actually I was looking for a any possibility to do some kind of check like Products.CurrentHierachy.Level = "All"without  specifying a hierarchy itself and if it’s not at top – return NULL. I supposed to use it in SCOPE definition to ensure that no value of Budget would be returned if there are any Products’ members in the set of the query but "All".

    But it seems that there is no such way – I have to enumerate all validating dimensions and their hierarchies explicitly. Moreover this approach wouldn’t work with SCOPE functionality, I guess.


    Tuesday, March 27, 2012 6:53 AM
  • Hi Dmitry

    Have you tried the ROOT() function. Not sure it will work, but may be worth trying:

    CREATE MEMBER CurrentCube.[Measures].[Budget] AS NULL;

    SCOPE(ROOT([Products]), [Measures].[Budget]);

        THIS = ([Measures].[Actual Budget]);

    END SCOPE;

    Not exactly sure it is what yuo want

    Tuesday, March 27, 2012 9:24 AM
  • Thank you for the reply. Actually I was looking for a any possibility to do some kind of check like Products.CurrentHierachy.Level = "All"without  specifying a hierarchy itself and if it’s not at top – return NULL. I supposed to use it in SCOPE definition to ensure that no value of Budget would be returned if there are any Products’ members in the set of the query but "All"...


      Dmitry, you can try to create calculation like this one:

    Case

            When[Product].[Product Categories].CurrentMember.Level.Ordinal = 0

            Then <your calculation here>
            Else        NULL  End

    HTH, Vlad.

    Tuesday, March 27, 2012 2:33 PM
    Moderator