locked
MDX calculation slice not working as expected RRS feed

  • Question

  • Hi all

    I have the following situation:

    Dimensions:

    -Employee
    -Engagement
    -Date

    Besides its key attribute the engagement dimension has an attribute called [Type]. This attribute defines if an engagement (project) is chargeable or not => the possible values are C (chargeable) N (non-chargeable)

    I have one fact table containing the hours employee have been staffed on the engagements

    I want a calculated member (in my case measure) the gives back the number of hours on chargeable projects.

    The calculation I defined in my mdx script is the following:

    ([Measures].[Hours], [Engagement].[Type].[C])

    If I run a query similar to the following:

    select
    {
    [Measures].[HoursChargeable]} on axis(0),
    {
    [Employee].[Employee].[Employee].Members} on axis(1)
    from [MyCube]

    the results are as expected

    If I work a query like

    {
    [Measures].[HoursChargeable]} on axis(0),
    {
    [Engagement].[Engagement].[Engagement].Members} on axis(1)
    from [MyCube]

    I receive back for all members on the rows the sum of all the chargeable engagement.

    What is wrong in my calculation. Since my calculation tuple is partial, I expected SSAS to complete it with [engagement].[engagement].CurrentMember which would give back the correct result.

    What would be the correct expression which would work with any dimension. Thanks a lot for your help

    Regards
    Stefan




    Wednesday, January 13, 2010 7:13 PM

Answers

  • Hi,

    yes, you're right, there's nothing wrong with your cube. Only the calculation has to be modified. Like this:


    iif(
        [Engagement].[Type].CurrentMember Is [Engagement].[Type].[All],
        ( [Measures].[Amount], [Engagement].[Type].&[C] ),
          [Measures].[Amount]
        )


    Your idea is to provide C-type value when there's no mentioning of Engagement dimension. In other words, when the Type attribute is on its root member. And therefore, the calculation should be as you designed, but only for that case.

    Another way to do it is to scope All member of Type hierarchy and to provide your tuple as the value of choice in that case.

    Gettin' rusty :-)


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    • Marked as answer by Stefan Z. _ Friday, January 15, 2010 6:58 AM
    Thursday, January 14, 2010 12:24 PM
    Answerer

All replies

  • Hi Stef,

    seems like you have a default member on Engagement dimension which is resetting current member of your key attribute. For example, maybe you have a third attribute not mentioned here, i.e. Status, which you set on "Finished only" member (one of a few possible). That would explain this behavior.

    If that is true, simply provide its root member inside that tuple in order not to be able to shift current member of key attribute to its root member.

    Or have you perhaps put Type's default member to "C"? In that case switch the member "C" in the tuple for .CurrentMember. However, my advice is to avoid default member or be extra careful when making tuples and expressions.

    Hope it helps,

    Regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    Thursday, January 14, 2010 1:29 AM
    Answerer
  • Hi Tomislav

    thanks for your answer. There are no default members defined on my Engagement dimension. I created a testing cube with two dimensions and one fact table with very few members. In this cube no default members are defined. The effect is exactly the same. The conducts me to think that it is the standard behavior. Nevertheless it is not intuitive.

    Regards

    Thursday, January 14, 2010 7:39 AM
  • OK then,

    which version of SSAS are you using?

    I've tested these two queries on 2008 and they behave as expected. The first one returns result as in your first example. The second one returns good result when left as is or when uncommenting current member row. But it behaves as in your second example (false, returning all the same values) when we uncomment the other line, the one with default member. Other comments in queries are for testing purposes (to be able to compare transposed results).


    with member X AS
    ([Measures].[Reseller Order Count], [Date].[Day of Week].&[7])
    select
    {X} -- * [Date].[Calendar Year].AllMembers 
    on axis(0),
    {[Sales Territory].[Sales Territory Country].AllMembers} on axis(1)
    from [Adventure Works]
    



    with member X AS
    ([Measures].[Reseller Order Count], [Date].[Day of Week].&[7]
    -- ,[Date].[Calendar].CurrentMember
    -- ,[Date].[Calendar].DefaultMember
     )
    select
    {X} -- * [Sales Territory].[Sales Territory Country].AllMembers
    on axis(0),
    {[Date].[Calendar Year].AllMembers} on axis(1)
    from [Adventure Works]
    



    Anything else about your dimensions or cube? Are you sure you haven't made the default member in MDX script?

    Regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    Thursday, January 14, 2010 8:05 AM
    Answerer
  • Hi Tomislav

    can I send you my small example? in that solution there are no specialities (=> no default members) at all and it behaves the same.

    PS: I am using SSAS 2008

    Regards
    Stefan
    Thursday, January 14, 2010 8:27 AM
  • Sure, anytime. My address is in contact info.

    Regards,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    Thursday, January 14, 2010 9:24 AM
    Answerer
  • I did not find any contact info under your profile...

    Thursday, January 14, 2010 9:47 AM
  • It's here. Shouldn't be hard to figure it out from description.


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    Thursday, January 14, 2010 10:01 AM
    Answerer
  • Hi,

    yes, you're right, there's nothing wrong with your cube. Only the calculation has to be modified. Like this:


    iif(
        [Engagement].[Type].CurrentMember Is [Engagement].[Type].[All],
        ( [Measures].[Amount], [Engagement].[Type].&[C] ),
          [Measures].[Amount]
        )


    Your idea is to provide C-type value when there's no mentioning of Engagement dimension. In other words, when the Type attribute is on its root member. And therefore, the calculation should be as you designed, but only for that case.

    Another way to do it is to scope All member of Type hierarchy and to provide your tuple as the value of choice in that case.

    Gettin' rusty :-)


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    • Marked as answer by Stefan Z. _ Friday, January 15, 2010 6:58 AM
    Thursday, January 14, 2010 12:24 PM
    Answerer
  • Thanks for your answer. Your expression is working but not in case [Engagement].[Type]&[N] is chosen. I had to change it slightly to
    iif
    (
    [Engagement].[Type].CurrentMember Is [Engagement].[Type].[All] Or [Engagement].[Type].CurrentMember Is [Engagement].[Type].&[N]
    ,
    ([Measures].[Amount], [Engagement].[Type].&[C] )
    ,
    [Measures].[Amount]
    )

    Thanks a lot.

    Friday, January 15, 2010 6:58 AM
  • Ehm, I didn't know you were expecting that value for N too. Which confuses me. Since you have just two members on that dimension, C and N, when do you expect False case of that iif() to occur? I mean, Type can be either All, N or C. The first two are covered with True part, while the last must go to False part. But C combined with Amount in the False part will again give you (Amount, C) combination, as in True case. Which comes down to a hypotesis that this attribute is completely redundant and that what's missing is a slice on your fact table that will pass only C-type events.

    Or am I missing something here?

    Best,


    Tomislav Piasevoli
    Business Intelligence Specialist
    www.softpro.hr
    Friday, January 15, 2010 11:24 AM
    Answerer