![\"\"](\"http:\/\/62.131.51.129\/wp-content\/uploads\/2012\/09\/DateDimension.jpg\" "\\"jcl\\"")
<\/a><\/p>\nThis 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.<\/p>\n
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.<\/p>\n
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.
\nFor 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.<\/p>\n
I also studied the scripts somewhat to see how they worked. The general idea is straightforward in the scripts.
\nThe first step is to create a table. Let us give the an Oracle script:<\/p>\n
\nCREATE TABLE Date_D(\nDateKey Integer ,\nDateValue Date ,\nDay Char(10 ),\nDayOfWeek Integer,\nDayOfMonth Integer,\nDayOfYear Integer,\nPreviousDay date,\nNextDay date,\nWeekOfYear Integer,\nMonth Char(10 ),\nMonthOfYear Integer,\nQuarterOfYear Integer,\nYear Integer\n);\n<\/pre>\nIn a second step, a long table is created that only contains dates. In Oracle this can be done with a statement like:<\/p>\n
\nselect level\nfrom dual\nconnect by level <= 5000;\n<\/pre>\nThis creates 5000 records ranging from 1 to 5000.<\/p>\n
The 1, 2, 3 is subsequently translated into dates by:<\/p>\n
\nselect TO_DATE('31\/12\/2009','DD\/MM\/YYYY') +\n NUMTODSINTERVAL(level,'day') datevalue\nfrom dual\nconnect by level <= 5000\n<\/pre>\nThis provides a range of dates starting from 1 Jan 2010, 2 Jan 2010 etc .<\/p>\n
This is stored in the target table with:<\/p>\n
\nINSERT INTO Date_D(datevalue)\nSELECT\ndatevalue\nFROM (\nselect TO_DATE('31\/12\/2009','DD\/MM\/YYYY') + \nNUMTODSINTERVAL(level,'day') datevalue\nfrom dual\nconnect by level <= 5000);\n<\/pre>\nIn 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:<\/p>\n
\nupdate date_d\nset DateKey = to_number(to_char(datevalue, 'YYYYMMDD'));\n<\/pre>\nand the remainder of the columns is updated with:<\/p>\n
\nupdate date_d\nset \nDay = TO_CHAR(datevalue,'Day'),\nDayOfWeek = to_number(TO_CHAR(datevalue,'D')),\nDayOfMonth = to_number(TO_CHAR(datevalue,'DD')), \nDayOfYear = to_number(TO_CHAR(datevalue,'DDD')),\nPreviousDay=datevalue - 1,\nNextDay = datevalue + 1, \nWeekOfYear = to_number(TO_CHAR(datevalue+1,'IW')),\nMonth = TO_CHAR(datevalue,'Month'), \nMonthofYear = to_number(TO_CHAR(datevalue,'MM')),\nQuarterOfYear = to_number(TO_CHAR(datevalue,'Q')),\nYear = to_number(TO_CHAR(datevalue,'YYYY'));\n<\/pre>\nHowever, 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<\/a>. Have fun with it. It only contains statements that Teradata understands: only numerics and strings.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-257","post","type-post","status-publish","format-standard","hentry","category-nice-to-know"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/257","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=257"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/257\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=257"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=257"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=257"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}