Scenario : ( affecting the weeks with days spanning two following years) .
The YQMD hierarchy is right but
the YWD hierarchy is wrong. Ex: 20120101 is wrong in the YWD hierarchy as it’s
‘Week Year’ is 2011 ( as the day belongs to a week in 2011) while - using
the same Year attribute in both hierarchies – we set it to 2012 . ( right of
course for the YQMD hierarchy)
You can fix it at either the relational - view or table level - or at the UDM level and in either a generic way or at detailed level. The recommended way : at the relational level and in a generic way, but you can mix the : level ( generic or detailed ) X level ( relational or UDM ) and have 4 combinations to select.
I'll try to exemplify two of the alternatives :
I'll try to exemplify two of the alternatives :
Generic x Relational :
In your view you may write a statement like :
Select ..., <Week Year> = cast(case
when Month_Number =
12 and WeekNo =
1 then Year_Number + 1
when Month_Number =
1 and WeekNo in (52,53) then Year_Number - 1
else Year_Number
end as nvarchar(4)) + '-' + [Week]
from <Time view or table>
Detailed x UDM :
In the Data Source View, select
your Date DataTable, right click on it , select New Named Calculation. Name it
for instance Week Year and create it with an expression like :
CASE
<DateKey>
When
20071231 then 2008
When
20081229 then 2009
...............................................
When
20120101 Then 2011
…………………………………………….
ELSE <Year>
END
Replace <DateKey> and <Year> with the corresponding names of your
columns.
In your Date dimension now, drag
and drop Weak Year from DSV to the dimension attributes pane .
Update the attribute
relationships correspondingly.Only in your Week hierarchy ( YWD), replace on the first level the Year attribute with the Week Year attribute.
Process the Date dimension and
the cube and test the ‘week adjustments’ at the start//end of the years where
you implement it.
