adding a time dimension problems

Question

adding a time dimension problems

mixalissen

SSC-Addicted

Points: 493
More actions
September 18, 2010 at 6:36 am

#141938

I am trying to add a time dimension to my project, using the examples in pro sql server 2008 analysis services. There the author add a time dimension using the dimensions wizard.
the thing is that he never mentions linking the dimension to the fact table. In the fact table the columns for dates are integers, 20001212 but in the time dimension table, the pk is datetime. what is the procedure one should follow to link the two?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

naveendas Mr or Mrs. 500 Points: 590 More actions · Answer 1

The 'link' between the fact and the dimension should be an integer, so the time dimension in your case should have a integer key in the format YYYYMMDD

RonKyle SSC-Dedicated Points: 31558 More actions · Answer 2

I agree that the date should be an integer, but I disagree that it must be a human readable one. Human readable ones have the advantage of being readable, of course, but I rarely need to read the dates, and if I do I just make the join. Using integers, which means some date is "Day 1" allows for very easy math for the inevitable calculations that follow. When an order closes, the column that gets filled for the number of days it was opened is simply x-y. The human readable dates can't be manipulated that easily.

RonKyle SSC-Dedicated Points: 31558 More actions · Answer 3

BTW, rather than call the dimesion Time, call it Date or even Calendar. One day you may need to add Time, and you'll be glad you did.

mixalissen SSC-Addicted Points: 493 More actions · Answer 4

I use the dimension wizard to generate the new dimension. I cannot see how I can specify that the date there can be of integer format. Can you elaborate on that?

Furthermore, what will the problem be if the date in the fact table and the date in the dimension in of datetime format?

Can you provide a link, or explain the steps of the procedure in order to generate a schema with a time dimension?

RonKyle SSC-Dedicated Points: 31558 More actions · Answer 5

I would refer you to Ralph Kimball's work on Date and Time dimensions, such as in The Data Warehouse Toolkit 2nd Ed. There have also been some recent articles at this site on the topic. You'll need to dig a little for those.

As far as the date, I admit it would be interesting to try that. Still, as I use a smallint for my dates, that's two bytes per field. A smalldatetime is twice that. I know SQL 2008 has a date only data type, which is 3, but better than 4. You want an OLAP record to be as tight as possible, and if the record has a lot of dates (I have more than ten in one of mine) the size difference matters.

amit_gupta01 SSChasing Mays Points: 605 More actions · Answer 6

Spare me if i have not understood your problem correctly...

In Data Source view , If you add new named calculation in time/date dimension and make it key column then you can join new column with Fact table.

Named Calculation Name = DateKeyInt

Named Calculation expression = CONVERT(INT,CONVERT(VARCHAR(8), <DateColumn>, 112))

Thanks,
Amit G
http://www.msbiconcepts.com