locked
Dynamic Week Start Date RRS feed

  • Question

  • Hi all,

    I have a requirement whereby the user is allowed a flexible week start date .... basically planning in blocks of weeks ... but the user can select any day of the week to start. 

    My date dimension is of course pre-populated with a fixed start day, which works for all my other reporting needs.  

    At the moment it looks like I need to get the data at the day level and perform aggregation in my MDX queries - which isn't great as I can't use my KPIs ... I have to aggregate each of the measures used in my KPIs. 

    Is there any other possible way of handling this requirement?

    Thanks!

    Keith. 

    Tuesday, August 25, 2015 11:23 AM

Answers

  • Hi K,

    Let me take a stab at what you want. As I understand it, some users, want to see weeks that start on Monday, some with week starting Tuesday etc. If that's the case try this. It's really quite simple. This solution will simply add a few attributes to your date dimension. It will not make your cube any bigger and will perform very well.

    In the table (or view is my preference) for your date dimension (table/view with one record per calendar day) add in 7 new columns for 

    WeekStartingMonday, WeekStartingTuesday, etc, which will contain the WeekId for the week defined by the start date. In a simple way, this could simply be a number between 1 and 53 denoting the week. They will be subtly different as WeekStartingTuesday will have Tuesday as the first day with the new week etc. You could give these weeks captions as well, but not necessary at this stage.

    In the table/view for your date dimension, add in 7 new columns for

    WeekStartingMondayDayKey, WeekStartingTuesdayDayKey, etc, which will contain the DayOfWeekId for the day as defined for that week start. For example, WeekStartingMondayDayKey will always be 1 for Monday, 2 for Tuesday; WeekStartingTuesdayDayKey will always be 1 for Tuesday, 2 for Wednesday etc.

    In your Date dimension, add the new 14 attributes. For each of the day attributes keep the number 1-7 as the key, but provide the name as the day name. Eg, the key might be 1 and the name might be 'Friday'. Now specify the OrderBy attribute as Key. That way, when the attribute is browsed, it will sequence days by the key, not the name. So, WeekStartingWednesday will have Wednesday (key=1) as the first day, Thursday (key=2) as the second day etc.

    Create 7 hierarchies, one for each Week Start day, with Week as the first level and day as the lower level. You may also want to add in Year at the top level of the hierarchy.

    Now, your users can choose between one of 7 hierarchies for weeks, depending on which day they would like the week to start.

    By the way, I have written a wiki article on this as it might be useful to others. See http://social.technet.microsoft.com/wiki/contents/articles/32032.supporting-multiple-first-day-of-the-week-calendars-in-a-cube.aspx

    Hope that helps,


    Richard


    • Edited by RichardLees Thursday, August 27, 2015 5:56 AM add reference to wiki
    • Marked as answer by Keith_d99 Tuesday, September 1, 2015 3:20 PM
    Thursday, August 27, 2015 12:54 AM

All replies

  • Hi Keith,

    Based on your description, I'm still not clear about your requirement. You want the user select any day of week, then you get the corresponding weeks' aggregation data. Or you want to get the next seven days' data starting from the user's selected date.

    If you're the first scenario, you can get the parent level member(week) to get the corresponding week level data. The expression can be like: Aggregate([Date].[Calendar].currentmember.parent,[Measures].[Measure]).

    If you want to get the next 7 days data, you can use the parallelperiod() function to get the next 7 day and apply range in aggregate() function. The expression can be like:

    Aggregate([Date].[Calendar].currentmember:parallelperiod([Date.[Calendar].[Date],7,[Date].[Calendar].currentmember),[Measures].[Measure])

    Since you will break the existing relationship between fact data and dimension under the selecting grain, you can't direct use that fact data in the KPI. You have to aggregate each measure used in your KPI.

    Regards,


    Simon Hou
    TechNet Community Support


    Wednesday, August 26, 2015 12:45 PM
  • Hi K,

    Let me take a stab at what you want. As I understand it, some users, want to see weeks that start on Monday, some with week starting Tuesday etc. If that's the case try this. It's really quite simple. This solution will simply add a few attributes to your date dimension. It will not make your cube any bigger and will perform very well.

    In the table (or view is my preference) for your date dimension (table/view with one record per calendar day) add in 7 new columns for 

    WeekStartingMonday, WeekStartingTuesday, etc, which will contain the WeekId for the week defined by the start date. In a simple way, this could simply be a number between 1 and 53 denoting the week. They will be subtly different as WeekStartingTuesday will have Tuesday as the first day with the new week etc. You could give these weeks captions as well, but not necessary at this stage.

    In the table/view for your date dimension, add in 7 new columns for

    WeekStartingMondayDayKey, WeekStartingTuesdayDayKey, etc, which will contain the DayOfWeekId for the day as defined for that week start. For example, WeekStartingMondayDayKey will always be 1 for Monday, 2 for Tuesday; WeekStartingTuesdayDayKey will always be 1 for Tuesday, 2 for Wednesday etc.

    In your Date dimension, add the new 14 attributes. For each of the day attributes keep the number 1-7 as the key, but provide the name as the day name. Eg, the key might be 1 and the name might be 'Friday'. Now specify the OrderBy attribute as Key. That way, when the attribute is browsed, it will sequence days by the key, not the name. So, WeekStartingWednesday will have Wednesday (key=1) as the first day, Thursday (key=2) as the second day etc.

    Create 7 hierarchies, one for each Week Start day, with Week as the first level and day as the lower level. You may also want to add in Year at the top level of the hierarchy.

    Now, your users can choose between one of 7 hierarchies for weeks, depending on which day they would like the week to start.

    By the way, I have written a wiki article on this as it might be useful to others. See http://social.technet.microsoft.com/wiki/contents/articles/32032.supporting-multiple-first-day-of-the-week-calendars-in-a-cube.aspx

    Hope that helps,


    Richard


    • Edited by RichardLees Thursday, August 27, 2015 5:56 AM add reference to wiki
    • Marked as answer by Keith_d99 Tuesday, September 1, 2015 3:20 PM
    Thursday, August 27, 2015 12:54 AM
  • Hi Richard,

    Apologies for the delay in replying ... I have only gotten around to trying your solution!

    That seems to work for me! ... Excellent post and relevant wiki article. I am sure many others will find it useful when they stumble across a similar requirement.

    My only problem is that there is a requirement for flexible Month Start Days now!  :)

    Think I will have to aggregate in MDX for that one.  The main downside for me (besides performance) is having to re-write (and maintain) calculations already defined in the Cube. 

    Thank again!

    Keith. 

    Tuesday, September 1, 2015 3:20 PM