Storing Timestamp in Time Dimension

Guys,

I have a seperate Time Dimension and a seperate Date Dimension. If I have a Datetime column in the source system (timestamp down to microseconds), I am thinking of breaking the datatime column or value into seperate columns (date and time) in the dimensional model. How do I design for the the microseconds part of the timestamp in the Time Dimension. If I keep Hour Number, Minute Number, Second Number, and Below Second Number columns in the Time dimension, the records in the Time dimension would be really great. Is there any other design to keep microseconds in your time dimension?

Current Design of Time Dimension

Hour Number
Minute Number
Second Number

Please help!

What is it about time that you need a dimension table? Is there a meaning to the business where they have provided attributes to identify particular times of the day? Is a microsecond significant to these designations?

Normally the precise time is stored as a degenerate dimension in the fact. If there is a business need to implement a time dimension table, it is usually at a level of precision necessary to support designations assigned by the business. It rarely goes beyond one minute intervals.

In reading kimball books, he suggests to create a seperate time dimension rather than adding time to the day/date dimension b/c of the number of rows. although there is no business requirement to capture the microsecond part of the timestamp, I didn't want to loose the information in the dimensional model. That's why I was wondering what is the best way of storing the time portion of the datetime attribute.

You are suggesting that we have a date dimension which captures the date part of the datetime attribute and a time degenerate dimension?
For example -
the source datetime value is: 2009/12/30 13:05:59.123456
In the fact table would this look like:

Date FK Other FK Time
101 (key for 2009/12/30) 58 13:05:59.123456

Is my understanding correct?

What Kimball is referring to is IF you implement a time dimension table, you USUALLY implement it as a dimension separate from date. The thing is, it is not common to implement a time dimension because most of the time business simply doesn't have a need to apply interpretations to time. You may see it in industries such as restaurants, retail and electric utilities to segment the day into usable categories.

As far as the degenerate dimension goes you would store it in a timestamp column in the fact table. It would not be a FK to anything. Because in most database system, there is no real difference between storing time only and storing date/time, you usually store date/time in such a column because it is simpler to load and easier to work with if you need to compare the value with other timestamps.