Datetime or DateID and TimeID in transactional fact table

[Scott]

My transactional fact table is tracking status change. To enable me to do point in time analysis, I need to store the source row updated date. I know normally all date/times in facts will be separate fields as id's but in the scenario to find the status at a point in time, for querying is it better to store this as a datetime?

[ron.dunn]

I think it depends on whether you need to know how things change, for example, at 11am across all days.

If it is possible to analyse by both date AND time, at the same time, then separate the dimensions.

[Scott]

The fact grain is when the status changes so it will be purely used to see what the status was at that point in time, so i'm thinking I need to keep as a datetime and if analysis is required on the separate elements I will add dateid and timeid dimensions.

[Sponsored content]