Get the ISO Week correct in the Adventure Works cube
Posted by thomasivarssonmalmo on September 19, 2008
A few months ago I wrote about how you can calculate the ISO week by creating a user defined function with TSQL in your database. You will need to read that blog post first and use the code for the UDF that you can find here. What I did not show was how to create a hierarchy in the date dimension in a SSAS 2005 cube.
If you simply add the calendar year level above the ISO week level(Date, ISO week, Calendar Year) you will see a problem if you expand the dates between the last week of previous year and the first week of the following year.
The reason for this is the problem I have written about earlier: Many to many relations in natural hierarchies. An ISO week can belong to more than one Calendar Year and some dates will disappear.
How can we solve this problem? I have created a business rule that simply says that ISO week 52 and 53 always belongs to the previous year and week 1 always belongs to the new year. To test it on the Adventure Works cube you can open the Adventure Works DW database in management studio and run the following TSQL script. You must have created the UDF for ISO week before you run it.
Alter Table DimTime
Add IsoWeekTest int,
IsoYear int
Add IsoWeekTest int,
IsoYear int
Go
Set datefirst 1
Update DimTime
Set IsoWeekTest = dbo.IsoWeek(FullDateAlternateKey),
IsoYear =
Case When dbo.IsoWeek(FullDateAlternateKey) = 1 and DatePart(dy,FullDateAlternateKey) <= 10 Then Year(FullDateAlternateKey)
When dbo.IsoWeek(FullDateAlternateKey) = 1 and DatePart(dy,FullDateAlternateKey) > 362 Then Year(FullDateAlternateKey) + 1
When dbo.IsoWeek(FullDateAlternateKey) = 52 and DatePart(dy,FullDateAlternateKey) >= 355 Then Year(FullDateAlternateKey)
When dbo.IsoWeek(FullDateAlternateKey) = 52 and DatePart(dy,FullDateAlternateKey) Between 1 and 2 Then Year(FullDateAlternateKey)-1
When dbo.IsoWeek(FullDateAlternateKey) = 53 and DatePart(dy,FullDateAlternateKey) Between 362 and 366 Then Year(FullDateAlternateKey)
When dbo.IsoWeek(FullDateAlterNateKey) = 53 and DatePart(dy,FullDateAlterNateKey) Between 1 and 3 Then Year(FullDateAlterNateKey)- 1
Set IsoWeekTest = dbo.IsoWeek(FullDateAlternateKey),
IsoYear =
Case When dbo.IsoWeek(FullDateAlternateKey) = 1 and DatePart(dy,FullDateAlternateKey) <= 10 Then Year(FullDateAlternateKey)
When dbo.IsoWeek(FullDateAlternateKey) = 1 and DatePart(dy,FullDateAlternateKey) > 362 Then Year(FullDateAlternateKey) + 1
When dbo.IsoWeek(FullDateAlternateKey) = 52 and DatePart(dy,FullDateAlternateKey) >= 355 Then Year(FullDateAlternateKey)
When dbo.IsoWeek(FullDateAlternateKey) = 52 and DatePart(dy,FullDateAlternateKey) Between 1 and 2 Then Year(FullDateAlternateKey)-1
When dbo.IsoWeek(FullDateAlternateKey) = 53 and DatePart(dy,FullDateAlternateKey) Between 362 and 366 Then Year(FullDateAlternateKey)
When dbo.IsoWeek(FullDateAlterNateKey) = 53 and DatePart(dy,FullDateAlterNateKey) Between 1 and 3 Then Year(FullDateAlterNateKey)- 1
Else Year(FullDateAlternateKey)
End
The set datefirst command is needed to set monday as the first date in the week. The SQL Server default is sunday. The case code is using the fact that the datepart function will always return generally usable results with the day of year argument combined with the ISO week number.
If you identify a combination that will not work and place the ISO week under uncorrect years please leave a comment. I have checked dates from 2003 to 2011 but can have missed something. The Adventure Works time dimension do not have that many members.
Open the Adventure Works cube project in BIDS and start with a refresh command in the data source view so that the new columnns will appear in the time dimension table. Now we can start with building the new hierarchy in the date dimension.
I have created the hierarchy with Date, ISO week and ISO year as the levels. Since the ISO Week number is not unique over years you must add the ISO Year as a collection key to the Iso Week attribute. See the picture below. I use the name IsoWeekTest in the script but have changed the name in the hiearchy.
Do not forget to add the Iso Week as the name column or you will get an error message when you try to process the dimension. The ISO Week attribute’s orderby property must also be changed to key.
In the hierachy you shall add the Iso Year attribute as an attribute relation on the Iso Week level. This means that you will have to remove the ISO Year attribute as an attribute dimension to the dimension key.
Process the dimension and browse it when it is finshed. Expand the hierarchy as in the following picture.
The business rule seems to work fine and all dates shows up. Now you can process the cube and have a look at the hierarchy in a tool. Here is how it looks in ProClarity.
The ISO year and week hierarchy is finally up and running in the Adventure Works cube project.
Thomas.I Microsoft BI & Analytics 
Unknown said
We are switching from a Co. fiscal week to ISO and when reviewing our specs I discovered the weeks we defined based on ISO are different than those calculated by function you provided. For example ISO converter on this site http://www.personal.ecu.edu/mccartyr/isowdcal.html matches standard our company is proposing. It shows 12/29 is week 1 of 2009 and your function returns it as week 53 of 2008. The standard we are using is the ISO 8601 calendar. I was hoping to find a calculation for the 8601 standard and was excited when I saw this article – any idea why the iso function is different than ISO 8601 standard?
Thank you, Brad Chapman
Thomas said
Hi Brad. Thanks for the comment. The ISO UDF is from the book "Inside Microsoft SQL Server 2005 : TSQL programming" page 22-23. Before you use the function you must set (DateFirst 1), in the connection and then the week will be correct according to ISO 8601. Then 2008-12-29 will have IsoWeek 1, like you describe.
Luca said
Is this solution valid for SSAS 2008 too? Is there a simpler (more direct) solution for the new version?
Thank you, Luca
thomasivarssonmalmo said
Hi,
Not that I have heard of.
Sam Kane said
Here are this and some other articles on SSAS Date Dimension
http://ssas-wiki.com/w/Articles#Date_Dimension