Which would you use, and why? A separate time dimension or putting a timestamp in a fact table? Or perhaps both?
I am building a data warehouse, and need to represent the time of day that events occur at, down to the one second granularity. I want to roll data up; plotting a graph of number of events per hour of the day, for example.
Kimball's "The Data Warehouse Toolkit", has a design for a time of day dimension. A more recent blog post recommends not doing that, and using a timestamp in the fact table instead:
If I go with timestamps in the fact table, will it still be easy/fast to roll up by the hour?
Any other trade-offs to consider in making this choice?