locked
ParallelPeriod with unbalanced intermediate hierarchy level error, 53 week year issue RRS feed

  • Question

  • Hello:

    We are trying to add a 53rd week to our 2011 year calendar. We produces cube with 2 years of data. User want to compare week 53 of current year with week 52 of prior year. Therefore, week 1 of current year would not have year-ago comparison. All our change measures (year over year) rely on the ParallePeriod function. We use both 13 4-week periods (quadweeks) and 12 4-4-5 periods (we also call quadweeks), depending on the client.

    Our current time dimension has this structure:

    Week is grain and key attribute

    The user hierarchy, TimeHier, is Week > Quadweek, with that attribute relationship defined.

    Adding a 5th week to the last quadweek for the current year results in

    member measures.yagotime as

    ParallelPeriod(time.timehier.quadweek, 13).uniquename 

    not working as we want but the behavior is understandable, since it has no corresponding year-ago value.

    My thought was to add a 'ghost' or dummy week to the beginning of the prior year, in the first quadweek, with no associated fact data. I would also add the Year level to TimeHier.

    TimeHier becomes Week > Quadweek > Year, with the attribute relationship defined.

    This results in the same number of periods per year at each level, 53 weeks, 13 quadweeks, and 1 year.

    However, when I use

    member measures.yagotime as

    ParallelPeriod(time.timehier.year, 1).uniquename

    with this design, I still don't get the correct year-ago value for week 53 of the current year - it returns null instead of week 53 of prior year.

    My conclusion is that, despite the BOL definition of ParallePeriod function (finding value with same relative position at specified level), the fact that the intermediate level in the hierarchy (quadweek) is unbalanced gives unwanted results.

    Why does this happen?

    I have looked at the other threads on ParallelPeriod and 53 week years without finding an answer.

    Thanks,

    David


    • Edited by David Clem Wednesday, November 30, 2011 8:40 PM
    Wednesday, November 30, 2011 6:59 PM

Answers

  • with this design, I still don't get the correct year-ago value for week 53 of the current year - it returns null instead of week 53 of prior year.

    My conclusion is that, despite the BOL definition of ParallePeriod function (finding value with same relative position at specified level), the fact that the intermediate level in the hierarchy (quadweek) is unbalanced gives unwanted results.

     

    ParallelPeriod works by by navigating to a relative point in the hierarchy. So week 53 is actually seen as the 5th child of the 13th quadweek. This is why adding a dummy week to the first quadweek has no effect.

    So I think one solution would be to use a CASE statement to check the current level and then use lag to go back the desired number of members.

    The expression would look something like the following (I have not tested this so it may have some errors, but you can get the general idea):

     

    CASE WHEN [Time].timehier.Currentmember.level is [Time].[timehier].[week] THEN  [Time].timehier.Currentmember.lag(53).uniquename

    WHEN [Time].timehier.Currentmember.level is [Time].[timehier].[quadweek] THEN [Time].timehier.Currentmember.lag(13).uniquename

    WHEN [Time].timehier.Currentmember.level is [Time].[timehier].[year] THEN [Time].timehier.Currentmember.lag(1).uniquename

    END


    http://darren.gosbell.com - please mark correct answers
    • Marked as answer by David Clem Thursday, December 1, 2011 4:58 PM
    Thursday, December 1, 2011 6:05 AM

All replies

  • with this design, I still don't get the correct year-ago value for week 53 of the current year - it returns null instead of week 53 of prior year.

    My conclusion is that, despite the BOL definition of ParallePeriod function (finding value with same relative position at specified level), the fact that the intermediate level in the hierarchy (quadweek) is unbalanced gives unwanted results.

     

    ParallelPeriod works by by navigating to a relative point in the hierarchy. So week 53 is actually seen as the 5th child of the 13th quadweek. This is why adding a dummy week to the first quadweek has no effect.

    So I think one solution would be to use a CASE statement to check the current level and then use lag to go back the desired number of members.

    The expression would look something like the following (I have not tested this so it may have some errors, but you can get the general idea):

     

    CASE WHEN [Time].timehier.Currentmember.level is [Time].[timehier].[week] THEN  [Time].timehier.Currentmember.lag(53).uniquename

    WHEN [Time].timehier.Currentmember.level is [Time].[timehier].[quadweek] THEN [Time].timehier.Currentmember.lag(13).uniquename

    WHEN [Time].timehier.Currentmember.level is [Time].[timehier].[year] THEN [Time].timehier.Currentmember.lag(1).uniquename

    END


    http://darren.gosbell.com - please mark correct answers
    • Marked as answer by David Clem Thursday, December 1, 2011 4:58 PM
    Thursday, December 1, 2011 6:05 AM
  • Thanks, Darren.

    I figured as much - just trying to avoid rewriting the dozens of change measures we have.

    Thursday, December 1, 2011 2:16 PM