Populating time dimension using DB2 script

Written by: Ankit G

Populating time dimension in a Data Warehouse can sometimes be a pain. While it can be done using the ETL tool, using a script can sometimes save from complexity involved in handling date time datatypes in ETL.

So here you have a sample DB2 code to create and load time dim at the hourly level. Please note that granularity can be changed while calling the stored procedure but before doing that logic needs to be changed to populate TIME_PK which act as the primary key of the table : 

CREATE TABLE ABC.TIME_DIM(
TIME_RANGE_START TIMESTAMP NOT NULL,
TIME_RANGE_END TIMESTAMP NOT NULL,
HOUR_KEY TIMESTAMP NOT NULL,
DAY_KEY TIMESTAMP NOT NULL,
WEEK_KEY TIMESTAMP NOT NULL,
MONTH_KEY TIMESTAMP NOT NULL,
QUARTER_KEY TIMESTAMP NOT NULL,
YEAR_KEY TIMESTAMP NOT NULL,
CURRENT_DATE DATE NOT NULL,
CURRENT_MINUTE SMALLINT,
CURRENT_HOUR SMALLINT,
CURRENT_DAY SMALLINT,
CURRENT_WEEK SMALLINT,
CURRENT_MONTH SMALLINT,
CURRENT_QUARTER SMALLINT,
CURRENT_YEAR SMALLINT,
DAY_OF_WEEK SMALLINT,
DAYS_IN_MONTH SMALLINT,
DAY_OF_YEAR SMALLINT,
WEEK_OF_MONTH SMALLINT,
WEEK_OF_QUARTER SMALLINT,
CANDLETIMESTAMP CHAR(16) NOT NULL,
END_CANDLETIMESTAMP CHAR(16) NOT NULL,
TIME_PK BIGINT
) ;

------------------------------------------------------------------------------------
create procedure ABC.CREATE_TIME_DIMENSION(IN startDate VARCHAR(32), IN endDate VARCHAR(32),
IN granularity SMALLINT)
LANGUAGE SQL
BEGIN
DECLARE loaddate TIMESTAMP;
DECLARE endloaddate TIMESTAMP;
DECLARE v_endDate TIMESTAMP;

DECLARE v_duplicateKey INTEGER;

-- duplicate key SQLSTATE
DECLARE duplicate_key CONDITION FOR SQLSTATE '23505';

-- Continue to the next row if we receive this error
DECLARE CONTINUE HANDLER FOR duplicate_key
-- The row insertion failed
SET v_duplicateKey = 1;

SET v_duplicateKey = 0;
SET loaddate = TIMESTAMP(startDate);
SET v_endDate = TIMESTAMP(endDate);

time_loop:
LOOP
SET loaddate = loaddate + granularity minutes;
SET endloaddate = loaddate + (granularity - 1) minutes;

INSERT into ABC.TIME_DIMENSION (
CANDLETIMESTAMP,
END_CANDLETIMESTAMP,
TIME_RANGE_START,
TIME_RANGE_END,
HOUR_KEY,
DAY_KEY,
WEEK_KEY,
MONTH_KEY,
QUARTER_KEY,
YEAR_KEY,
CURRENT_DATE,
CURRENT_MINUTE,
CURRENT_HOUR,
CURRENT_DAY,
CURRENT_WEEK,
CURRENT_MONTH,
CURRENT_QUARTER,
CURRENT_YEAR,
DAY_OF_WEEK,
DAYS_IN_MONTH,
DAY_OF_YEAR,
WEEK_OF_MONTH,
WEEK_OF_QUARTER,
TIME_PK BIGINT
)
VALUES
(
concat(concat(rtrim(char((YEAR(loaddate) - 1900)*1000000 + MONTH(loaddate)*10000 + DAY(loaddate)* 100 + HOUR(loaddate))),
rtrim(substr(char(100+ MINUTE(loaddate)),2,2))),'00000'),
concat(concat(rtrim(char((YEAR(endloaddate) - 1900)*1000000 + MONTH(endloaddate)*10000 + DAY(endloaddate)* 100 + HOUR(endloaddate))),
rtrim(substr(char(100+ MINUTE(endloaddate)),2,2))),'59999'),
loaddate - second(loaddate) seconds - microsecond (loaddate) microseconds,
endloaddate + 59 seconds + 999 microseconds,
loaddate - minute(loaddate) minutes - second(loaddate) seconds - microsecond (loaddate) microseconds,
loaddate - hour(loaddate) hours - minute(loaddate) minutes - second(loaddate) seconds - microsecond (loaddate) microseconds,
loaddate -(DAYOFWEEK(loaddate)-1) days - hour(loaddate) hours - minute(loaddate) minutes - second(loaddate) seconds - microsecond (loaddate) microseconds,
loaddate - (day(loaddate) -1 )days - hour(loaddate) hours - minute(loaddate) minutes - second(loaddate) seconds - microsecond (loaddate) microseconds,
timestamp(concat(concat(concat(rtrim(char(year(loaddate))), '-'),
rtrim(char(quarter(loaddate)*3-2))),'-1 00:00:00.0')),
loaddate - (month(loaddate) - 1) months - (day(loaddate) -1 )days - hour(loaddate) hours - minute(loaddate) minutes - second(loaddate) seconds - microsecond (loaddate) microseconds,
DATE(loaddate),
MINUTE(loaddate),
HOUR(loaddate),
DAY(loaddate),
WEEK(loaddate),
MONTH(loaddate),
QUARTER(loaddate),
YEAR(loaddate),
DAYOFWEEK(loaddate),
DAY((loaddate + 1 MONTH) - DAY(loaddate +1 MONTH) DAYS),
DAYOFYEAR(loaddate),
(DAY(loaddate)/7)+1,
WEEK(loaddate) - (QUARTER(loaddate) -1)*13,
(((((YEAR(loaddate)*100)+MONTH(loaddate))*100)+DAY(loaddate))*100)+HOUR(loaddate)
);

IF (loaddate >= v_endDate)
THEN LEAVE time_loop;
END IF;

END LOOP time_loop;

COMMIT;

END
@
-----------------------------------------------------------------------------------
call ABC.create_time_dimension('2009-01-01 00:00:00','2009-01-02 00:00:00',60);
------------------------------------------------------------------------------------