Generally fact tables have more than one time-stamp (generally
date). If it has only one time-stamp, take a hard re-look at it.
Time dimension is one of the core elements to all information requirements.
Most of the analysis is linked to some time element (Monthly, quarterly, year-on-year,
end of month, end of day..). Therefore most of the fact tables will have a time
related keys. For example in a fact table carrying invoice level sales data, you will have Invoice date, Purchase order data, delivery date, payment received data, cheque deposit data, cheque clearing date, funds transfer date etc..
In case of single or multiple time related fields in a fact table,
do the following:
- Make each time field as a separate time dimension, linked to its own
time dimension table.
- Use surrogate key instead of actual time stamp as the foreign key in the
fact and linked time dimension table. This is needed because:
- Sometimes, the dates in the source system are manually fed and are
expected dates, and they get changed with actual date, when the even
happens. For example expected delivery date replaced by actual delivery
date, once the delivery happens.
- Sometime, due to a date coming from different system, there could
be difference in the time and date stamps (though by few seconds)
|
