Thomas.I Microsoft BI & Analytics

A lot of posts in the SSAS2005 newsgroup have questions regarding how to construct a time dimension.

In SSAS2005 you can generate a server time dimension but having control over this is much better and it is not that hard.

Let’s start with creating a list of dates.  Jamie Thomson (http://blogs.conchango.com/jamiethomson/rss.aspx) has a TSQL solution with a new feature in SQL Server 2005 called common table expressions or CTE: s

Start management studio and the database engine. Start a new query and copy this statement:

with mycte as

(

 select cast(‘2003-01-01’ as datetime) DateValue

 union all

 select DateValue + 1

 from    mycte   

 where   DateValue + 1 < = GetDate() + 1000

  )

 select DateValue

from    mycte

OPTION (MAXRECURSION 0)

If you prefer you can switch the GETDATE () function to a fixed date. You can either copy the result set to excel or add an Into-Statement directly after (Select DateValue + 1 Into MyTimeDim) to create a table directly.

With this code you will have the leaf level ready in the time dimension. Notice also that the time part is 00:00:00:000 and that you will have to secure that your fact records use the same time in order to avoid strange results.

The next question is how I could change that date and take away the time part and only leave the date for presentation in a dimension? In TSQL you have the CONVERT() function for that. Normally you use it to change data types but it also have an important third argument where you can format to a specific date format.

Try this TSQL code in management studio:

Select Convert (Char(10),GetDate(), 112)

You will see todays date in ISO-style(20071002) the way we present a date in Sweden.  Change the last argument to a code that suits you. You can find more information about this in Books on Line if you search on CONVERT.

In the dimension editor you will use the full date column as key and the ISO Date as the name column.

To build the natural hierarchies or levels above the date you can use the TSQL DATEPART function.

Be aware of that DATEPART returns an integer and is only unique on the year level (2006, 2007) but not on the quarter (1, 2, 3, 4) and month levels (1-12). You will have to build collections for the key columns of each level in the time hierarchy in the dimension editor (BIDS). For quarter you will use year (2007) and quarter (1) as the collection key. The month level will need the Year (2007) and the month numbers to be unique.

When this is finished you should check that the type property (dimension editor) is properly set for Year, Quarter and Month in the user hierarchy. If not your MDX time calculations will not work.

In the picture below you can actually see that the dates will appear correctly in Excel 2007 and in the right order with this mix of different name and key column.  I have the ISO-date format as name column but the original date column, with the time part, as the key column.