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. 

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,

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