none
Writeback expression question - Weighted Allocation (SSAS 2008 SP2) RRS feed

  • Question

  • Hi

     

    I'm trying to nail down the behaviour of writeback.

    The Expression needs to change depending on how the value should be allocated.

     

    E.g. If I want to spread a value to the leaf level funds then:

    1000

    *

    [Fund.Currentmember, Measures.Value] / [Fund.All, Measures.Value]

     

    I've noticed that the expression should cater for each dimension that needs to be spread.

    So if it is by fund, and by product then it should have:

     

    [Fund.Currentmember, Product.CurrentMember, Measures.Value]

    /

    [Fund.All, Product.All, Measures.Value]

     

    I've also found that if I need to allocate a value to an actual leaf level value, then the denominator should also carry a current member for that dimension. (I don't want to divide the value of the current member by the all level)

     

    Eg. If I want to capture a value against a particular fund, then the weighted expression would look like this:

     

    [Fund.Currentmember, Product.CurrentMember, Measures.Value]

    /

    [Fund.Currentmember, Product.All, Measures.Value]

     

    What I'm trying to figure out is this:

     

    What if I want to spread a value by a grouping attribute?

    Example, if there was a Fund Group attribute that held multiple Funds, so I shouldn't be dividing by the [All] member.

    Shouldn't I do something like this? :

     

    [Fund.Currentmember, Product.CurrentMember, Measures.Value]

    /

    [FundGroup.BasketA, Product.All, Measures.Value]

     

    I can't get it to work. No it might have something to do with the design of the cube, or the attribute relationships.

     

    But can someone tell me that this is a valid expression, and that my problem maybe lies in the cube itself?

    (There are no ParentChild hierarchies in the cube)

     

    Thanks,

    Jason

     

     

     

     

     

     

     


    MCITP BI Developer 2008 - MCTS SQL Server 2005
    Thursday, April 14, 2011 12:11 PM