Data Warehouse Time Dimension

What is a DataWarehouse TIme Dimension?

Very much of what we're interested in reporting on from a data warehouse is dependent on time. Time is critical for everything involving payment or receipt of money, for example. If £100 is paid into your account, you want to know when. Is it there now? Will it be paid tommorow? Next month? Next year? It makes a big difference. People talk about "temporal" and "non-temporal" databases, but most of the interesting data is "temporal".

Most data warehouses have a time dimension. This is usually at the level of a day - a specific date - or a specific time down to the nearest second. Of course we sometimes need different time intervals, such as weeks, months or milliseconds.

We already discussed the Calendar Dimension. Here we'll look at the time of day dimension. We could just use a timestamp (or datetime2 if you are using SQL Server) data-type in the fact table. All the SQL implementations support functions that will pick out parts of a timestamp, but it is much more efficient, when such functions are needed frequently, to get a big part of the work done once, up front, so you don't have to do it in most of your queries. This makes the queries simpler and reduces any chance of error.

You might also wonder why we split the date and time in the dimensions, when SQL provides a timestamp data-type that includes date and time. There are several reasons. First, if we created every time, to the nearest second, over a ten year period, we would have 317 million rows in our dimension. This would take a lot more space than we need to and would slow down our queries somewhat. Secondly we will want to put some extra attributes on the time-of-day, such as whether that time is day or night, whether it is morning or afternoon, if it is a rush-hour time (morning or evening), and so on. For these classifications we don't care about the date, they will be the same for every date, so we would have a lot of duplication. Generally, for a data warehouse, which we are only reading, or querying, most of the time, separate date and time dimensions meet the reporting needs more effectively. We will show some examples, later.

See the download page to get data and tools to build your time_of_day dimension. This is what you can do with the downloads:

The Easy Way

The time_of_day.csv data file has times in the format hh:mm:ss from 00:00:00 to 23:59:59, with some basic attributes (day/night, am/pm).

The build_time_of_day.sql script:

creates the table time_of_day
loads the time_of_day.csv file

More Columns

If you need more columns you can adapt the Java source code and/or the scripts as you please. For example, in a car insurance company that installs a box to track the car, you might like to search for driving between 01:00am and 05:00am, because accidents are more likely to happen when driving at these times. You could add a risk factor to each time to make it easier to write such queries.