Don McMunn's FREEWARE "Date Dimension Tool Kit"

FREE Date Dimension Tool Kit - Includes: Table structure, Source data, SQL Server, day or date level, CSV, tab-delimited, Excel, 4-4-5 and Gregorian calendar for download, dim_date, dim_day, lookup, reference, Oracle, IPC Designs, LLC from 1900 to 2050
Don McMunn's FREEWARE Date Dimension Tool Kit
Version 2.14
Released: 17-Aug-2007

Why? What? Download Date Table Comments Home My Resume Free DPTK
Free ETL MD

   View Don McMunn's profile on LinkedIn
Please visit these handy sites as well...

Steve Hoberman's Data Modeling Zone

Barry Ralston's Microsoft BI blog
The www.timeanddate.com Calendar Generator
Enter year:
Visit the World Clock
The Calendar Zone
Kimball Group - Microsoft Data Warehouse Toolkit Tools
COMMENTS:

"Excellent work!"

"Thanks for sharing these queries with the license."

"This site was very helpful."

"I am on an Oracle 9.2 database and the ready-made .csv file saved me from having to write a PL/SQL program built around SYSDATE to get all the DATE columns. With over 60 columns there is every permutation of DATE that you could ever want. The whole process including finding this site took less than a day!"

"I'm getting into the Kimball stuff. You just saved me hours of work. Brilliant!"

"Thanks for the freeware. It really was a big help. You saved me many hours and days of manual calculations."

"Thanks for the tools."

Purpose:


Every data warehouse, reporting or business intelligence environment can benefit from a handy, dandy date lookup or reference table. AKA "a date dimension". This is grunt work at best, and frankly, I'm tired of doing it over and over at each new account, so...download and enjoy...I will!

What's in the box?
The Date Dimension Tool Kit contains the fundamental, pre-calculated Gregorian Calendar Date Dimension table source data for the range of January 1, 1900 to December 31, 2499. There are also source scripts for those who STILL want to D-I-Y, data models (both denormalized and 3-N-F), a US holiday calendar, an Excel spreadsheet to generate either a Gregorian or 4-4-5 fiscal calendar, and a some simple instruction to kick-start the process. What're you waiting for? ...read on, download and go do something more productive for your team, customer and yourself.
Custom Search
 
Errata:

13-Sep-2007==> The day of the week was reported to be off by one (Saturday January 1, 2000 showing as Sunday) in the Excel Calendar building spreadsheet. - Chris
14-Sep-2007==> I was unable to confirm the issue reported by "Chris" above. If anyone else experiences an issue with day of the week being off by one, please let me hear from you. Otherwise, I will consider it closed.

LEGAL STUFF:


DISCLAIMER:
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

LICENSE:
Creative Commons License
Date Dimension Kit by Don McMunn is licensed under a Creative Commons Attribution 3.0 License.
Based on a work at www.ipcdesigns.com.


From the base data elements in the files below, a large portion of additional attributes may be calculated via clever SQL or brute force such as begin/end parent time periods/keys, previous time periods; future time periods, etc...
These date-related attributes will make reporting and complex, date dependent ad-hoc queries easier to develop, maintain and extend.


The process of creating the essential date-based attributes of all database applications and reporting systems of record is a snap with this toolkit.

INSTRUCTIONAL GUIDE:
NOTE: You must have sufficient database permissions to create tables, indexes, select, update, delete and insert data to perform all these steps. You will also need to be able to uncompress a Zip file to access the source data files.
  1. Download the SQL Server DDL to create the dim_day table which will serve as the highly denormalized table structure for your date-specific attributes.
    Note:If your database is not SQL Server, you will need to make the necessary adjustments to bring the DDL into syntactical compliance with the requirements of your RDBMS. There is also an Oracle script embedded in the page below for now. If you create a new or modify a downloaded script for a new database, please share it with me and I will make it available here for everyone to share, netizen. :o)
  2. Choose to either download one of the pre-populated source files in the format of your choice (CSV, tab-delimited or MS-Access) The pre-populated files contain the majority of the data elements (except fiscal date-related columns) to be loaded into the dim_day table in a ready-to-load format.
    -OR-
    download the Sequential Day Numbers Primer File. Next, download the SQL Server DML script to create dim_date in an incremental fashion after loading the dim_day.date_key column with all the values from the Sequential Day Number Primer File with the ETL tool of your choice. These scripts also save you from the tedious hand-coding and debugging of the SQL DML commands required to populate dim_day yourself. Reviewing the DML script can also provide helpful examples should you need to alter the dim_day data structure or content to fit your specific requirements. This script helps you have a better understanding of the content of dim_day an how it was produced. Note: the DML statements are for SQL Server and will need to be updated to the syntax of your particular non-SQL Server RDBMS.
  3. Using your tool of choice connect to the target database to contain the dim_day table.
  4. Create the dim_day table (see CREATE TABLE script below for your database)
  5. Load the pre-populated file previously downloaded or begin executing each command in order from top to bottom found in the DML script. NOTE: IT IS NOT NECESSARY TO RUN THE DML SCRIPT IF YOU ARE LOADING ONE OF THE PRE-POPULATED FILES INTO DIM_DAY.
  6. Validate the resulting data contained in the dim_day table. If you have successfully populated all of the data contained in the one of the pre-populated files there will be 219,146 rows of data in dim_day after completion of all of the steps found in the DML script file. Each row corresponds to a single day between 01-Jan-1900 and 31-Dec-2499. Some example validation scripts will be added soon, so stay tuned.
Useful SQL scripts will be added to this site by me (and hopefully by the community, too!) to provide additional date related attributes. So, make sure to check back from time to time.

10-Jan-2008 - new script to populate the fiscal columns in dim_day for US Federal calendar


Downloads


NOTE: Each of the data source files below is compressed as a .ZIP file
Pre-calculated Date Dimension: Comma-separated Variable8.4Mb
Pre-calculated Date Dimension: Tab-delimited8.5Mb
Pre-calculated Date Dimension: MS Access 20008.6Mb
Sequential Day Numbers Primer File1.2Mb
ER/Win 3.x Data Model of dim_day155Kb
SQL Server DDL to create dim_date1Mb
PDF Viewable Data Model of dim_day 4Kb
SQL Server DML script to update dim_day from Primer File (above)7Kb
SQL Server DML script to update dim_day fiscal columns(run after script above)11Kb
Excel Macro to build a calendar year; supports both Gregorian and 4-4-5 style calendars150Kb
Excel spreadsheet with common US Holidays660Kb
Common US Holidays in Tab-delimitted text format155Kb
SQL Server DDL to generate normalized date hierarchy tables37Kb
SQL Server scripts to create both a denormalized and normalized pair of tables to contain the US Holidays9Kb
How to interview business people to document a business process12Kb
Stored procedure to show the status of all SQL Server jobs4Kb


SQL Table Definition for days:


(Click here for the full dim_day table creation script)
For SQL Server: 
CREATE TABLE dim_day 
(
  date_key int NOT NULL
, date_type varchar (20) NULL CONSTRAINT DF_dim_day_date_type Default('NORMAL')
, full_date datetime NULL
, day_num_of_week int NULL
, day_num_of_month int NULL
, day_num_of_quarter int NULL
, day_num_of_year int NULL
, day_num_absolute int NULL
, day_of_week_name varchar (10) NULL
, day_of_week_abbreviation varchar (3) NULL
, julian_day_num_of_year int NULL
, julian_day_num_absolute decimal(18, 0) NULL
, is_weekday char (1) NULL
, is_US_civil_holiday char (1) NULL CONSTRAINT DF_dim_day_holiday  Default('N')
, is_last_day_of_week char (1) NULL CONSTRAINT DF_dim_day_lastdow Default('N')
, is_last_day_of_month char (1) NULL CONSTRAINT DF_dim_day_lastdom Default('N')
, is_last_day_of_quarter char (1) NULL CONSTRAINT DF_dim_day_lastdoq Default('N')
, is_last_day_of_year char (1) NULL CONSTRAINT DF_dim_day_lastdoy Default('N')
, is_last_day_of_fiscal_month char (1) NULL CONSTRAINT DF_dim_day_lastdofm Default('N')
, is_last_day_of_fiscal_quarter char (1) NULL CONSTRAINT DF_dim_day_lastdofq Default('N')
, is_last_day_of_fiscal_year char (1) NULL CONSTRAINT DF_dim_day_lastdofy Default('N')
, prev_day_date datetime NULL
, prev_day_date_key int NULL
, same_weekday_year_ago_date datetime NULL
, same_weekday_year_ago_date_key int NULL
, week_of_year_begin_date datetime NULL
, week_of_year_begin_date_key int NULL
, week_of_year_end_date datetime NULL
, week_of_year_end_date_key int NULL
, week_of_month_begin_date datetime NULL
, week_of_month_begin_date_key int NULL
, week_of_month_end_date datetime NULL
, week_of_month_end_date_key int NULL
, week_of_quarter_begin_date datetime NULL
, week_of_quarter_begin_date_key int NULL
, week_of_quarter_end_date datetime NULL
, week_of_quarter_end_date_key int NULL
, week_num_of_month int NULL
, week_num_of_quarter int NULL
, week_num_of_year int NULL
, week_num_overall int NULL
, month_num_of_year int NULL
, month_num_overall int NULL
, month_name varchar (10) NULL
, month_name_abbreviation varchar (3) NULL
, month_begin_date datetime NULL
, month_begin_date_key int NULL
, month_end_date datetime NULL
, month_end_date_key int NULL
, quarter_num_of_year int NULL
, quarter_num_overall int NULL
, quarter_begin_date datetime NULL
, quarter_begin_date_key int NULL
, quarter_end_date datetime NULL
, quarter_end_date_key int NULL
, year_num int NULL
, year_begin_date datetime NULL
, year_begin_date_key int NULL
, year_end_date datetime NULL
, year_end_date_key int NULL
, YYYYMM varchar (6) NULL
, YYYYMMDD varchar (8) NULL
, DDMONYY varchar (7) NULL
, DDMONYYYY varchar (9) NULL
, fiscal_week_num_of_year int NULL
, fiscal_week_num_overall int NULL
, fiscal_week_begin_date_key int NULL
, fiscal_week_begin_date datetime NULL
, fiscal_week_end_date_key int NULL
, fiscal_week_end_date datetime NULL
, fiscal_month_num_of_year int NULL
, fiscal_month_num_overall int NULL
, fiscal_month_begin_date datetime NULL
, fiscal_month_begin_date_key int NULL
, fiscal_month_end_date datetime NULL
, fiscal_month_end_date_key int NULL
, fiscal_quarter_num_of_year int NULL
, fiscal_quarter_num_overall int NULL
, fiscal_quarter_begin_date datetime NULL
, fiscal_quarter_begin_date_key int NULL
, fiscal_quarter_end_date datetime NULL
, fiscal_quarter_end_date_key int NULL
, fiscal_year_num int NULL
, fiscal_year_begin_date datetime NULL
, fiscal_year_begin_date_key int NULL
, fiscal_year_end_date datetime NULL
, fiscal_year_end_date_key int NULL
) 
GO

ALTER TABLE dim_day 
ADD CONSTRAINT PK_dim_day PRIMARY KEY CLUSTERED 
  (
  date_key
  )
GO

For Oracle:
CREATE TABLE dim_day 
(
  date_key integer NOT NULL
, date_type varchar2 (20) NULL  DEFAULT 'NORMAL'
, full_date date NULL
, day_num_of_week integer NULL
, day_num_of_month integer NULL
, day_num_of_quarter integer NULL
, day_num_of_year integer NULL
, day_num_absolute integer NULL
, day_of_week_name varchar2 (10) NULL
, day_of_week_abbreviation varchar2 (3) NULL
, julian_day_num_of_year integer NULL
, julian_day_num_absolute decimal(18, 0) NULL
, is_weekday char (1) NULL
, is_US_civil_holiday char (1) NULL DEFAULT 'N'
, is_last_day_of_week char (1) NULL DEFAULT 'N'
, is_last_day_of_month char (1) NULL DEFAULT 'N' 
, is_last_day_of_quarter char (1) NULL DEFAULT 'N'
, is_last_day_of_year char (1) NULL DEFAULT 'N'
, is_last_day_of_fiscal_month char (1) NULL DEFAULT 'N'
, is_last_day_of_fiscal_quarter char (1) NULL DEFAULT 'N'
, is_last_day_of_fiscal_year char (1) NULL DEFAULT 'N'
, prev_day_date date NULL
, prev_day_date_key integer NULL
, same_weekday_year_ago_date date NULL
, same_weekday_year_ago_date_key integer NULL
, week_of_year_begin_date date NULL
, week_of_year_begin_date_key integer NULL
, week_of_year_end_date date NULL
, week_of_year_end_date_key integer NULL
, week_of_month_begin_date date NULL
, week_of_month_begin_date_key integer NULL
, week_of_month_end_date date NULL
, week_of_month_end_date_key integer NULL
, week_of_quarter_begin_date date NULL
, week_of_quarter_begin_date_key integer NULL
, week_of_quarter_end_date date NULL
, week_of_quarter_end_date_key integer NULL
, week_num_of_month integer NULL
, week_num_of_quarter integer NULL
, week_num_of_year integer NULL
, week_num_overall integer NULL
, month_num_of_year integer NULL
, month_num_overall integer NULL
, month_name varchar2 (10) NULL
, month_name_abbreviation varchar2 (3) NULL
, month_begin_date date NULL
, month_begin_date_key integer NULL
, month_end_date date NULL
, month_end_date_key integer NULL
, quarter_num_of_year integer NULL
, quarter_num_overall integer NULL
, quarter_begin_date date NULL
, quarter_begin_date_key integer NULL
, quarter_end_date date NULL
, quarter_end_date_key integer NULL
, year_num integer NULL
, year_begin_date date NULL
, year_begin_date_key integer NULL
, year_end_date date NULL
, year_end_date_key integer NULL
, YYYYMM varchar2 (6) NULL
, YYYYMMDD varchar2 (8) NULL
, DDMONYY varchar2 (7) NULL
, DDMONYYYY varchar2 (9) NULL
, fiscal_week_num_of_year integer NULL
, fiscal_week_num_overall integer NULL
, fiscal_week_begin_date_key integer NULL
, fiscal_week_begin_date date NULL
, fiscal_week_end_date_key integer NULL
, fiscal_week_end_date date NULL
, fiscal_month_num_of_year integer NULL
, fiscal_month_num_overall integer NULL
, fiscal_month_begin_date date NULL
, fiscal_month_begin_date_key integer NULL
, fiscal_month_end_date date NULL
, fiscal_month_end_date_key integer NULL
, fiscal_quarter_num_of_year integer NULL
, fiscal_quarter_num_overall integer NULL
, fiscal_quarter_begin_date date NULL
, fiscal_quarter_begin_date_key integer NULL
, fiscal_quarter_end_date date NULL
, fiscal_quarter_end_date_key integer NULL
, fiscal_year_num integer NULL
, fiscal_year_begin_date date NULL
, fiscal_year_begin_date_key integer NULL
, fiscal_year_end_date date NULL
, fiscal_year_end_date_key integer NULL
) 
;

ALTER TABLE dim_day 
ADD CONSTRAINT PK_dim_day PRIMARY KEY
  (
  date_key
  )
;



Column Definitions


(Alphabetical by column name)
date_key sequential number; 1 relative; surrogate key
date_type Normal; Unknown; Not Yet Occurred; Invalid; Missing
day_num_absolute number of days since Jan 1, 1900
day_num_of_month day number of the current day within the current month
day_num_of_quarter Day of calendar quarter associated with this day
day_num_of_week Range: 1-7; day number of the week; Sunday = 1
day_num_of_year 1-366; Jan 1 = 1;
day_of_week_abbreviation 3 character abbreviation of the day of the week
day_of_week_name Full name of the day of the week
DDMONYY An alternative string representation built by concatenating the left zero-padded 2-digit day of month,3-char month abbreviation, left zero-padded 2-digit year, of this day
DDMONYYYY An alternative string representation built by concatenating the left zero-padded 2-digit day of month,3-char month abbreviation, left zero-padded 4-digit year, of this day
fiscal_month_begin_date date associated with the first day of the fiscal month containing this day
fiscal_month_begin_date_key surrogate key of the date associated with the first day of the fiscal month containing this day
fiscal_month_end_date date associated with the last day of the fiscal month containing this day
fiscal_month_end_date_key surrogate key of the date associated with the last day of the fiscal month containing this day
fiscal_month_num_of_year range 1-13; representing the fiscal month number of the year containing this date
fiscal_month_num_overall the number of the fiscal month containing this day as elapsed since Jan 1, 1900
fiscal_quarter_begin_date first day of the fiscal quarter containing this day
fiscal_quarter_begin_date_key surrogage key of the first day of the fiscal quarter containing this day
fiscal_quarter_end_date last day of the fiscal quarter containing this day
fiscal_quarter_end_date_key surrogate key of the last day of the fiscal quarter containing this day
fiscal_quarter_num_of_year quarter number of the fiscal year containing this day
fiscal_quarter_num_overall the number of the fiscal quarter elapsed since Jan 1, 1900 containing this day
fiscal_week_begin_date the date of the first day of the fiscal week containing this day
fiscal_week_begin_date_key the surrogate key of the first day of the fiscal week containing this day
fiscal_week_end_date the date of the first day of the fiscal week containing this day
fiscal_week_end_date_key the surrogate key of the last day of the fiscal week containing this day
fiscal_week_num_of_year number of the fiscal week of the year associated with this day
fiscal_week_num_overall number of the absolute fiscal week since Jan 1, 1900 of the year associated with this day
fiscal_year_begin_date the date of the first day of the fiscal year containing this day
fiscal_year_begin_date_key the surrogate key of the date of the first day of the fiscal year containing this day
fiscal_year_end_date the date of the last day of the fiscal year containing this day
fiscal_year_end_date_key the date of the last day of the fiscal year containing this day
fiscal_year_num 1900+; The fiscal year number containing this day
full_date actual date of this day as of midnight
is_last_day_of_fiscal_month Y=this is the last day of the fiscal month
is_last_day_of_fiscal_quarter Y=this is the last day of the fiscal quarter
is_last_day_of_fiscal_year Y=this is the last day of the fiscal year
is_last_day_of_month Y=this is the last day of the calendar month
is_last_day_of_quarter Y=this is the last day of the quarter containing this day
is_last_day_of_week Y=today is the last day of the week
is_last_day_of_year Y=this is the last day of the calendar year
is_US_civil_holiday Y=date is a nataionally recognized holiday
is_weekday Y=weekday; N=weekend
julian_day_num_absolute the year * 1000000 + absolute julian days (absolute days since Jan 1, 1900) of this day
julian_day_num_of_year 1-366; absolute day of the year
month_begin_date date associated with the first day of the month containing this day
month_begin_date_key surrogate key of the date associated with the first day of the month containing this day
month_end_date date associated with the last day of the month containing this day
month_end_date_key surrogate key of the date associated with the last day of the month containing this day
month_name Full name of the calendar month
month_name_abbreviation 3-character abbreviation of the calendar month
month_num_of_year typically 1-12 for each month of the calendar year
month_num_overall number of months since Jan 1, 1900
prev_day_date date of the previous day
prev_day_date_key date key of the previous day
quarter_begin_date the date associated with the first day of the quarter containing this day
quarter_begin_date_key surrogate key of the date associated with the first day of the quarter containing this day
quarter_end_date the date associated with the last day of the quarter containing this day
quarter_end_date_key surrogate key of the date associated with the last day of the quarter containing this day
quarter_num_of_year range 1-4; representing which quarter of the current calendar year contains this day
quarter_num_overall absolute number of calendar quarter containing this day since Jan 1, 1900
same_weekday_year_ago_date date associated with today one year previous to today
same_weekday_year_ago_date_key surrogate key of date associated with today one year previous to today
week_num_of_month 1-5; the week number of the month containing this day
week_num_of_quarter range: 1-13;the week number of the calendar quarter containing this day
week_num_of_year 1-53; the number of the week of the year containing this day
week_num_overall 1+; the absolute week number since Jan 1, 1900 of the week containing this day
week_of_month_begin_date begin date of month-bounded week containing this day
week_of_month_begin_date_key surrogate key of begin date of month-bounded week containing this day
week_of_month_end_date end date of month-bounded week containing this day
week_of_month_end_date_key surrogate key of end date of month-bounded week containing this day
week_of_quarter_begin_date begin date of quarter-bounded week containing this day
week_of_quarter_begin_date_key surrogate key of begin date of quarter-bounded week containing this day
week_of_quarter_end_date end date of quarter-bounded week containing this day
week_of_quarter_end_date_key surrogate key of end date of quarter-bounded week containing this day
week_of_year_begin_date the date of the first day of the week containing this day
week_of_year_begin_date_key the surrogate key of the date of the first day of the week containing this day
week_of_year_end_date the date of the last day of the week containing this day
week_of_year_end_date_key the surrogate key of the date of the last day of the week of the year containing this day
year_begin_date date of the first day of the year containing this day
year_begin_date_key surrogate key of the date of the first day of the year containing this day
year_end_date date of the last day of the year containing this day
year_end_date_key surrogate key of the date of the last day of the year containing this day
year_num Range: 1900+
YYYYMM An alternative string built by concatenating the 4-digit year and left zero-filled month number of the yr/mo containing this day
YYYYMMDD An alternative string built by concatenating the 4-digit year and left zero-filled month number and left zero-filled day number of month of the this day

Send a comment:


Your feedback helps us improve the site. We do not spam.
Subject:
Name:
Message:
 
 
   Don McMunn is a professional, business-focused consultant specializing in data warehousing and
business intelligence design, development and delivery for over a decade with the
Fortune 500 and SMB segments. He may be reached at: dmcmunn at ipcdesigns dot com
 



Due to financial concerns, and in the interest of pursuing a "green earth" policy,
the light at the end of the tunnel has been turned off until further notice.
We apologize in advance for the continuing pain and suffering.
You are the   viewer


Search Engine Optimization and SEO Tools

Changing LINKS