ISOWeeks in SSAS Revisited
Posted by thomasivarssonmalmo on November 24, 2012
I wrote a blog post a few years ago about IsoWeeks in SSAS and now it is time to do an update. Since SQL Server 2008 and later we have a TSQL Datepart() argument that will remove the need to build TSQL User Defined Functions to add this information into a date dimension table. You will see this in the code samples but have a look at the datepart() TSQL function i Books On Line for more information.
ISOweeks seems to be the fashion here in the Scandinavian countries. The main idea is that a year can contain 52 or 53 weeks depending on if the new year starts on Thursday or later. All ISOWeeks have seven days and the main problem for SSAS or BISM Multidimensional is that with ISO weeks they cross over calendar years and this means a many to many relation between ths IsoWeek and the calendar year if you try to add a calendar year as a hierarchy level above IsoWeek. The question is if you should add a year level above the IsoWeek or not. If you decide to add a year level that must be a business defined IsoYear level and not a calendar year. In this blog post we assume that such a level is needed and the business rule is that week 52 and 53 should always belong to the previous year and week 1 to the new year. As an outcome of this descision is that ISOYears and calendar years will not have sum up in the same way. Totals over years will be the same but not the yearly distributions.
Enough said and now it is time to transfer this IsoWeek requirement into code and a working solution. My code is a theoretical example and has no real world background except for the date dimension. It is simplified to check that the numbers are summed correctly and includes one order for each day that is part of the date dimension.
I will start with the code for a date dimension below. I am using a CTE(TSQL Common Table Expression) with a start date(2009-01-01) and an end date to the last date of the current year ( where Year(DateValue) < = Year(GetDate()) ) . You can also see the datepart-function with the ISOWeek argument. I am doing a select Into TSQL statement to create a new table from the CTE. The last column(1 as IsoYearWeek) was added to create an integer column that we will use in a second step. The column created with the TSQL case statement is the business rule mentioned earlier to create the IsoYear classification. I thanks my colleague Eva Eriksson for her kind help.
In a second step I use this TSQL statement to update the IsoYearIsoWeek column:
Set IsoYearIsoWek = IsoYear * 100 + IsoWeekInt
The result will look like this in Management Studio.
Finally it is time to create the dummy fact table with one order for each date in the date dimension. This is also done with a CTE part of SQL Server since the 2005 version.
It is time to build the date dimension and the cube in the SQL Server 2012 Data Tools previously named BIDS. I assume that you now how to do this except for the settings I have added all attributes to the date dimension. In the wizard you should set the type properties for the different date attribute members that will be part in MDX Time calculations. Some of the attribute types are kept as regular because they are not important for this example.
I have created these hierarchie below in the date dimension.
I have also created the attribute relations for the hierarchies like this.
Finally it is time to add the fact table. In the cube structure tab it look like this.
And in the dimension usage tab it look like this.
Process the cube and start Excel to have a look at the cube. I am using Excel 2013. I have created two pivot tables from the same connection. The pivot table to the left is the IsoWeek hierarchy and the one two the right is the calendar hierarchy. We have the same totals but the distributions differs. If you expand the IsoWeek hierarchy for IsoYear 2012 all weeks should have 7 as the sum of orderQty, like you can see in the second picture.
I a future post I will cover a many to many requirement in a date dimension with weeks.