January 19, 2012 at 11:47 am
Hello,
I'm currently working on a Data Warehousing project for my company, and have been experimenting with SSIS this week. I'm attempting to structure our data warehouse in a dimensional format, and am struggling with adding a standard time or date dimension to my DB.
Can anyone offer any suggestions for creating a time dimension using SSIS?
January 19, 2012 at 1:55 pm
The first thing I would suggest is to separate time from date and make them 2 dimensions.
Time is a relatively easy dimension to create. Determine your graunlarity (Seconds, Minutes) etc. then create a record for each member of the granularity in 24 hours (86400 rows for seconds, 1440 for minutes). It is probably a good idea to have both 12 hour and 24 hour time. I also like to have time bands to make determining day parts easier. Such as 5 minute bands, 10 minute bands, 15 minute bands, 30 minute bands and 60 minute bands.
A basic date dimension is pretty simple, dates, and all sorts of human readable, and machine sortable versions. For example you might want to have January and 201201 in separate columns to provide you with some query and layout flexibility. Same thing for quarters Q1 & 2012Q1, etc. If your fiscal year is not the same as the calendar year, you should have a complete set of both calendar and fiscal columns. Things can get more complicated when you start to add holidays that don't always fall on the same date every year, and they get even more complicated when you are global and want to keep track of GMT and offsets. There are some good scripts for them online, I suggest you search a bit to find one you like, and then modify it to your specific needs.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply