How to: Create a Data Mart or Data Warehouse Time Dimension

A datamart or data warehouse will usually need a time dimension to use for date queries that look at periods, weeks, months, etc. Here is an example of how to generate one from the basic date entry and the Oracle date functions. The same idea can be used in DB2, Informix, Postgresql, etc.

Load base dates from Excel or something that can generate a date series, these dates are the base data of the rest of the time dimension that will be generated using the built in SQL functions in Oracle SQL. Export the Excel data into an ascii file and load the dimension_time.calendar_date field then run the following scripts to populate the time dimension attributes.

This is hardly an Oracle only solution. Informix, DB2, Sybase, SqlServer, etc., all have similar or the same SQL date and time functions that can be used to generate a time dimension.

####################################cut here#########################
#!/bin/ksh
# Baseline schema objects

. ../BASELINE/etc/baseline_env # environment file for Oracle

make_time () {
sqlplus << EOF
schema_owner/password

drop table schema_owner.dimension_time;

-- The time dimension for a company
-- calendar dates, holidays, etc. for business rules
create table schema_owner.dimension_time (
    calendar_date date,
    day_of_year_number number,
    day_of_week varchar2(9),    -- day name.
    week_number number,    -- accounting week number(also known as period).
    week_ending date,      -- ends on friday.
    accounting_month_end date,  -- last friday of month.
    calendar_month_end date,    -- last day of month.
    month_day_number number,    -- day of month.
    month varchar2(9),          -- month name.
    month_number number,        -- number of month in year.
    quarter_number number,      -- ends on accounting month end.
    holiday varchar(1)          -- flag 'Y' is holiday, 'N' is no holiday.
) tablespace time_data ;

GRANT SELECT ON schema_owner.dimension_time TO SELECT_ALL_TIME;
EOF

create index dimension_time_ix1
    on dimension_time(calendar_date) tablespace time_data nologging;
}

######################
# Main

make_time

###########################cut here###########################
#!/bin/ksh
# Load base dates from Excel or something that can generate a date series,
# export the Excel data into an ascii file and load the 
# dimension_time.calendar_date field then run the following to 
# populate the time dimension attributes.  

. ../BASELINE/etc/baseline_env # environment file for Oracle

sqlplus << EOF
schema_owner/password
-- Set the various time values with the DATE format and to_char function;

-- Weeks end on Friday.
update dimension_time set week_number = to_char((calendar_date +2),'IW');

-- other functions to populate other fields:
--select to_char((calendar_date),'W') from dimension_time;
--select to_char((calendar_date),'WW') from dimension_time;
--select to_char((calendar_date),'MONTH') from dimension_time;
--select to_char((calendar_date),'YYYY') from dimension_time;
--select to_char((calendar_date),'J') from dimension_time;
--select to_char((calendar_date),'D') from dimension_time;
--select to_char((calendar_date),'DD') from dimension_time;
--select to_char((calendar_date),'DDD') from dimension_time;
--select to_char((calendar_date),'DAY') from dimension_time
--               where calendar_date = '03-MAR-02';
EOF