Database Reference
In-Depth Information
■
once again, we recommend keeping your design simple! Most developers find that their reports do not need
details about hours or minutes. if you do not regularly need this kind of information, just create a simple date dimen-
sion table and use a datetime key to connect it to the fact table. if a rare occasion comes up where you do need
more than basic information, you can generate the required results with lookup tables and programming.
Tip
Using Foreign Key Constraints
In most dimensions, you want to use an integer value to connect a dimension table to a fact table. This is a
practice recommended for almost all occasions, and Kimball defends this practice strongly. However, even
Kimball agrees that for the time dimension, using a datetime key instead of an integer key is more practical for
tracking periods of hours, minutes, and seconds. Still, there are issues you must consider.
In SQL Server, foreign key constraints compare values to determine whether the constraint is violated. If
columns do not have matching values in both tables, an error occurs. Consider the values in Figure
4-13
. he
values are almost the same but vary by hours and minutes. Because of this, you cannot put a SQL foreign key
constraint between these tables.
Figure 4-13.
Time- and date-based foreign keys
