Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

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.

 

DateDimensionCode

 

In a second step I use this TSQL statement to update the IsoYearIsoWeek column:

Update dbo.TestDateDim
Set IsoYearIsoWek = IsoYear * 100 + IsoWeekInt

The result will look like this in Management Studio.

DateDimensionTable

 

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.

 

FactTable

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.

AttributeTypesDateDim

I have created these hierarchie below in the date dimension.

 

DateHierarchies

I have also created the attribute relations for the hierarchies like this.

 

Attribute relations

Finally it is time to add the fact table. In the cube structure tab it look like this.

 

FactTable2

And in the dimension usage tab it look like this.

Fact Table

 

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.

 

TheTwoHierrachies

 

IsoWeekExcel

I a future post I will cover a  many to many requirement in a date dimension with weeks.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: