A not uncommon requirement is to support weeks with different start dates. For example, the Finance team might want to see weeks as Tuesday through Monday, while a store manager A might prefer to see weeks from Monday through Sunday, and store manager B might prefer to see weeks as Saturday thru Friday. Do not create new dimensions for this requirement. That is unnecessary and would lead to degraded query performance.
It is quite simple to support multiple week starting dates, without complicating your cube or degrading performance. You should already have a Date dimension, with one member per calendar day. That's all you need. Now, just add attributes to each day denoting the weekno and dayofweek for each of the, up to 7, week start days. Just to be clear, the number of records in your Date table/view will not change, just new columns.
In your existing date dimension table (or view in my projects) add in, up to, 7 new columns for WeekStartingMonday, WeekStartingTuesday, WeekStartingWednesday, etc, which will contain the WeekId for the week defined by the start date. For the first (Monday thru Sunday) week in the year, these days will all have a WeekStartingMonday key of 1. For the first (Tuesday thru Monday) week in the year, these days will all have a WeekStartingTuesday key of 1. To put it another way, each attribute will have a number between 1 and 53 denoting the week number. These 7 attributes 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. Note, if you want to have full weeks at the beginning and end of each year, you will need to add another 7 attributes, which will be the WeekStartingMondayYear, WeekStartingTuesdayYear etc. This attribute will have the year that the day/week is in (ie a number such as 2015, 2016 etc.) Then you can ensure that each year will have exactly 52 or 53 full weeks.
In the table/view for your date dimension, add in another 7 new columns for WeekStartingMondayDay, WeekStartingTuesdayDay, etc, which will have the DayOfWeekId for the day as it behaves in that week start. For example, WeekStartingMondayDay will always be 1 for Monday, 2 for Tuesday; WeekStartingTuesdayDay will always be 1 for Tuesday, 2 for Wednesday etc.
In your Date dimension, add the new 14 (or 21 if you include year) attributes. For each of the day attributes keep the WeekStartingMondayDay number 1-7 as the key, but provide the name as the dayofweek name (Monday, Tuesday etc.) Eg, the key might be 3 and the name might be 'Friday' for a WeekStartingWednesdayDay member. In SSAS specify the OrderBy attribute =Key. That way, when the attribute is browsed, SSAS will sequence days by the key, not the name. So, WeekStartingWednesdayDay will have Wednesday (key=1) as the first day, Thursday (key=2) as the second day etc. Your week attributes (WeekStartingMonday, WeekStartingTuesday etc) will be OK with the number 1-53 as the key and name. Note, ensure that OrderBy attribute =Key for the weeks also, otherwise, SSAS might perform a character sequencing (by caption) of your weeks.
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.
Users can also cross join one of these 7 hierarchies with Dates, then, they will see a heading with the date and the day of week underneath, and broken by weeks.
This solution will perform well in your cube. Since these attributes are simply attributes of existing members, the measure groups will not change in size with their addition. Also, these attributes will perform well as they are physical attributes, not calculations.
No comments:
Post a Comment