Actual / minimum table time value
I am tweaking Fact and Dim tables and trying to find the best way to tweak my timing values. AdventureWorksDW uses a timecode (UID) for each time entry in the DimTime table. I am wondering if there is some reason why I shouldn't just use the time value instead of ie 0106090800 (My granularity is hourly)?
source to share
Smart Keys (in this case, the encoded date and hour number) can lead to problems when you want to change definitions in your dimension. For example, your users might insist on changing from local time to UTC. Now your key is no longer a usable number, it is an old value in a dimension.
Also, with an overflow completeness issue, the date portion of your smart key may not match the actual UTC date or local time change.
To prevent the problem from occurring, you cannot use it for any computation. In this case, it is slightly better than a simple GUID or auto-increment.
Autoincrement keys (or GUIDS) are quick and easy. Most importantly, they are trivially consistent across all dimensions.
Time has a numerical juxtaposition, but it helps to look at this strange coincidence, rather than the basis of good design.
source to share
The primary key should be surrogate, meaningless - however, using YYYYMMDD for the date dimension key is hard to resist, and also makes it easy to partition tables. The trick is that it should still be considered pointless - the fact that it looks like a date should be considered purely random. This key should never be exposed to business users.
source to share