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);
------------------------------------------------------------------------------------

4 comments:

9698ashu said...

Thank you very much for your goog information.
As I noted in the ... given information is very use full to every student who ever want to learn about http://www.bigclasses.com/cognos-online-training.html

emily said...

Iam very much interest to take training on online coaching.and the course about pega..for this i recommended you to this.

the best pega Online Training training

David h said...


Appreciation for nice Updates, I found something new and folks can get useful info about BEST INFORMATICA ONLINE TRAINING

vasudha dharani said...

Cognos Enterprise Planning Online Training
Cognos Online Training
Cognos TM1 Online Training Call Us-001-201-210-8616 21st Century Software Solutions Online Training 21st Century providing Online training and support on All Technologies. If you are seeking training and support you can reach me on 91-9000444287. Online training by real time Experts. Call us 001-309-200-3848 for online training 21cssindia provides cognos TM1 online training by real time experts along with certification. We also provide advanced training on cognos , cognos enterprise planning and many more software courses. For further details and demo call us +91 7386622889 http://www.21cssindia.com/courses/cognos-tm1-online-training-91.html contact@21cssindia.com