issues in regards to build the hierarchy RRS feed

  • Question

  • Hi There,


    I've already posted the hierarchy issues before, but I still didn't get any good idea yet.

    I'm facing with fiscal calendar hierarchy now, and here is the current situation.

    Most of the fact (measures) are cacluated based on 52 weeks and in 2007 and 2001 (fiscal), there are 53 weeks.

    We compare the data with current year and prior year and  parallelperiod function is needed for the prior year calcuation. But this fucntion only works when the hierarchy is well defined.


    I heard some suggestions from the other experts that I need to create 2 fiscal calendar hierarchies which are (year -quearter -month -day hierarchy) and (year- week hierarchy). The former works fine since this hierarchy does not affect 52 weeks caculcations.  But with year -week hierarchy , I can' roll up the year level since 2007 year has 53 weeks and it gives an error. So, some people suggest to do ETL process which has a starting date and ending date for each year in the date dimension and then to define the hierarchy based on the created date dimension. But I'm not clear what it means and how to do a ETL process for that hierarchy.


    If anybody has any idea or similar experience with ETL process for this kind of hierarchy, please give me some comments.


    Thanks in advance.



    Tuesday, September 4, 2007 8:44 PM