Weeks and Months roll-up in Time dimension

We need to change our Time dimension roll-up ('Total Year' 'Quarter' 'Month') as some users want to see the week level as well. What is the best way to do that? I still want to use the DTS and both need to be Time dimension. Thanks for your reply.


Assuming you have data down to the daily level, another option would be to create an alternate hierarchy in your time dimension. You would use your level zero members (individual days) that are currently rolling up into months and share them. Your Time dimension you would then have two children; "By Months" and "By Weeks".Under months you would have Jan, Feb, Mar, etc. Under Weeks you would have Week 1, Week 2, ... Week 52. The level zero members are the same for both. What I like about this method is it does not add any overhead since you are using shared members, and you don't run into a situation where your total "by months" varies from your total "by weeks". Since your base level data is the same you can have confidence that your data will tie out. Of course the end user needs to decide when they run their report wether they want to run by months or by weeks. My users were more than happy to do this when I explained there was no practical way to have months and weeks in the same hierarchy since weeks will overlap months. I have an outline set this way and I am using DTS effectively. If you want, send me an email and I will send you an OTL file with the Time dimension as I explained.Best Regards;

Related Topics