See also 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 warehoses 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.
Here we'll start with the most common time dimension and that is a calendar, classifying data into days. We could just use a date data-type in the fact table. All the SQL implementations support functions that will pick out parts of a date, 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.
See the download page to get data and tools to build your calendar dimension. This is what you can do with the downloads:
The calendar.csv data file has dates from 01-Jan-2009 to 31-Dec-2022. If this covers your needs, you can simply load this.
The build_calendar.sql script:
If you need a different date range, you can create your own calendar data:
java -jar GenerateCalendar.jar calendar.csv 2009-01-01 2022-12-31
The parameters are:
file: name (or path) of csv file to be created, containing the dates for the calendar dimension
from_date: earliest date in the calendar dimension. Must be in format yyyy-mm-dd
to_date: latest date in the calendar dimension. Must be in format yyyy-mm-dd
If you need more columns (such as the account closing date each month for your organisation, or public holidays in other countries, for example). You can adapt the Java source code and/or the scripts as you please.