Database Reference
In-Depth Information
Tracking Dates and Times
It should be noted that there have been debates about whether these tables should be called DimTime instead
of DimDate. Arguments for calling your table DimDate and not DimTime usually revolve around the question,
“What if I may want to have a separate table for tracking hours, minutes and seconds; wouldn't that table be
called DimTimes?” It sounds like a good argument, but let us examine it further.
Currently, the lowest level of detail in the DimDates table of Figure
4-11
is an individual day. Therefore, we
have 365 rows for each year of dates, or at least, for three out of four years we will. Ten years of dates can be stored
in a table with less than 4,000 rows, which in database terms is not that large a table.
However, what would happen if we added in hours, minutes and seconds? The size of the table would swell
to more than 31 million rows! This many rows are very likely to cause problems with performance. So, what are
our options?
One option would be to create a separate time dimension table that held hours, minutes, and seconds
columns. You would then link this table to the fact table just as you would the date dimension table. This can
work, but there is a simpler option.
Using DateTime Keys
In this second option, leave the date and time data just as it was in the OLTP design and then link the DimDate
table to the fact table based in this datetime column, instead of using an integer column (Figure
4-11
).
You can derive hours, minutes, and seconds quite easily from a datetime column. Because in most cases
hours, minutes, and seconds do not have additional descriptors associated with them, a simple datetime column
is all you need. You can then include a datetime column in your DimDate table to provide connectivity between
the dimension and fact tables (Figure
4-11
).
You still have additional associations for holidays. Using this design, it is easy to store time dimension values
down to the milliseconds without influencing the fact table size.
Having It All
There are times where having just a datetime column might not work for you. Consider requiring additional
descriptors for hours, such as lunchtime or second shift. One way of handling these descriptors is to include
them in the fact table along with the datetime column. You now have dates in a dimension table, times in the
fact table, and an hour description in the fact table as well. This hour description column will have considerable
redundancy, but it is better than having 31 million rows in your DimDate dimension table.
Of course, if you get too many descriptors, this still will not work, and you need to consider using a separate
time dimension table, as shown in Figure
4-12
.
Figure 4-12.
Time and date dimension tables
