Today I had to create a date dimension. This is a dimension that provides us with a list of records that contains the weekdays, the number of a month etc.
Let me give a screenshot of these records:
This table helps us to translate a date (like 08 – 25 – 2011) into a day in the week, like Monday, Tuesday. Such translation is by no means trivial. A table that contains the dates, along with, say, weekday names helps us in this task.
I encountered beautiful scripts to create such a table. I want to refer to http://www.dwhworld.com/2010/11/date-dimension-sql-scripts-oracle/ to see such a script. Similarly take a look at http://kimballgroup.forumotion.net/t52-date-dimension-in-oracle-with-one-sql-statement. Or http://oracleolap.blogspot.nl/2011/01/script-for-time-dimension-table.html. Or http://code.google.com/p/oraclenerd/source/browse/trunk/misc/sql/tables/times.tab . Only one problem: only Oracle scripts.
For some other DBMS it is also easy to find a script that creates a date dimension. For SQL Server, I noticed http://www.sqlbook.com/Data-Warehousing/Date-Dimension-SQL-script-18.aspx. This script was really helpful.
For MySQL, I found http://www.dwhworld.com/2010/08/date-dimension-sql-scripts-mysql/. This script was somewhat cumbersome but with some determination I got a nice table of dates in MySQL.
I also studied the scripts somewhat to see how they worked. The general idea is straightforward in the scripts.
The first step is to create a table. Let us give the an Oracle script:
CREATE TABLE Date_D( DateKey Integer , DateValue Date , Day Char(10 ), DayOfWeek Integer, DayOfMonth Integer, DayOfYear Integer, PreviousDay date, NextDay date, WeekOfYear Integer, Month Char(10 ), MonthOfYear Integer, QuarterOfYear Integer, Year Integer );
In a second step, a long table is created that only contains dates. In Oracle this can be done with a statement like:
select level from dual connect by level <= 5000;
This creates 5000 records ranging from 1 to 5000.
The 1, 2, 3 is subsequently translated into dates by:
select TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(level,'day') datevalue from dual connect by level <= 5000
This provides a range of dates starting from 1 Jan 2010, 2 Jan 2010 etc .
This is stored in the target table with:
INSERT INTO Date_D(datevalue) SELECT datevalue FROM ( select TO_DATE('31/12/2009','DD/MM/YYYY') + NUMTODSINTERVAL(level,'day') datevalue from dual connect by level <= 5000);
In a third step, extensive use is made of the date functions to derive the requested fields: the name of day, the weeknumber, the number of the month etc. One then updates the columns with the datevalue that is already loaded. As an example:
update date_d set DateKey = to_number(to_char(datevalue, 'YYYYMMDD'));
and the remainder of the columns is updated with:
update date_d set Day = TO_CHAR(datevalue,'Day'), DayOfWeek = to_number(TO_CHAR(datevalue,'D')), DayOfMonth = to_number(TO_CHAR(datevalue,'DD')), DayOfYear = to_number(TO_CHAR(datevalue,'DDD')), PreviousDay=datevalue - 1, NextDay = datevalue + 1, WeekOfYear = to_number(TO_CHAR(datevalue+1,'IW')), Month = TO_CHAR(datevalue,'Month'), MonthofYear = to_number(TO_CHAR(datevalue,'MM')), QuarterOfYear = to_number(TO_CHAR(datevalue,'Q')), Year = to_number(TO_CHAR(datevalue,'YYYY'));
However, I had to create such a table on a Teradata DBMS. Unfortunately I could not find such a script for Teradata. Ok, I then created such a script myself from the date dimensions I created so far on Oracle, SQL Server and MySQL. Hereby I present you such a script with insert statements only. Click here to get the Script. Have fun with it. It only contains statements that Teradata understands: only numerics and strings.