Calculate date and time key in fact table using existing date time field

I have date time field in a fact table in the format MM/开发者_运维问答DD/YY HH:MM:SS (e.g 2/24/2009 11:18:47 AM) and I have seperate date and time dimension tables. What I would like ask is that how I can create date key and time key in the fact table using the date time field so that I can join the date and time dimension.

There are alot of reference for creating seperate date and time dimension and their benefit but I could not find how to create date and time keys in the fact table using existing date time field.

I have also heard that having date time field in fact table has certain benefit. If so, what would you recommend, should I have all three (date key, time key and date time field) in the fact table. Date key and time key are must to have for me and I am concerned about fact table size if I have date time field also in the fact table.

Thank you all for any help you can give.

What you need to do (if I understand correctly) is to create two fields in your Fact table:kTime, kDate.

We would always suggest using the primary keys for DimTime and DimDate as having meaning (this being a special case normally Dim tables' promary keys dont have any meaning). So e.g. in DimDate, we would have kDate as primary key with values formatted as YYYYMMDD so that you can order by kDate and it puts them in date order. Then have DimTime table having kTime primary key in the form HHMM or HHMMSS (depending on the resolution you need.

It is best to keep the actual date time field on the fact table as well, as it allows SQL to use its inbuilt date/time functions to do subsetting, but if you extend your Dim tables with useful extra columns : DimDate (add DayOfWeek, IsHoliday, DayNumber, MonthNumber, YearNumber, etc) and DimTime (HourNumber, MinuteNumber, IsWorkingTime), then you can perform very interesting queries very simply.

So to answer your question, "how to create date and time keys in the fact table using existing date time field?" ... as you are loading the data into the fact table, use the inbuilt date/time functions to create separate Date fields and Time fields.

It depends very much on how many rows you expect in your Fact table wether this approach will produce a lot of data, but it is the easiest to work with from a data warehouse point of view.

best of luck!