Minnu's Blog on Informatica & Data warehouse concepts


Monday, June 16, 2008

How to cater Business Requirement that requires time precise in Sec

In some ways the time dimension seems like the simplest dimension. At least, if you're modeling calendar days, the time dimension is small and predictable. You can make up a 10-year time dimension in an afternoon using a spreadsheet; that's only about 7,300 rows. I wonder that this shouldn't be called as time dimension but as date dimension

But when business demands time precise in Hrs/Min/Sec then all you need is a single timestamp on the transaction fact table record. Still you keep Date Dimension to link up with transaction date

You must do it this way because you usually can't create a time dimension with all the minutes or seconds over a significant period of time. There are more than 500,000 minutes and 31 million seconds in a year.

Hopefully you might not want to create a snow-flake dimension, 1 for date another for time, coz of again a many-many relation that exist between them (am not sure of this, please correct if am wrong)

If your business demands much more than what above solution says then consider below

Even with these powerful techniques, I'm sure there are some tricky time span questions I haven't considered. I collect these tricky puzzles, so email new ones to me at Ralph@ralphkimball.com.