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

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
Unknown said…

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

21cssIndia 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
Unknown said…
It was very nice article and it is very useful to Cognos learners.We also provide Cub training software online training.
What is the use of v_duplicateKey?
Unknown said…
Great article Power Bi online training you can see more on it.
soumya said…
thank you for providing the valuable information regarding cognose course information ..keep update with your blogs...once check it out

Cognos Tm1 Online Training Hyderabad
Unknown said…
"Knowledge is Power" your Blog is empowering the IT candidates with such a Functional Information…Sincere Appreciation From Monstercourses..
Cognos Online Training
Tejuteju said…
Thank you.Well it was nice post and very helpful information onCognos TM1 Online Training Hyderabad>


Mounika said…
Nice post. By reading your blog, i get inspired and this provides some useful information. Thank you for posting this exclusive post for our vision. 
python training in OMR
python training in Bangalore
python training in rajajinagar
Python training in btm
Python training in usa
Unknown said…
Nice information, valuable and excellent design, as share good stuff with good ideas and concepts, lots of great information and inspiration, both of which I need, thanks to offer such a helpful information here.

Blueprism training in btm

Blueprism online training
Mounika said…
This is very good content you share on this blog. it's very informative and provide me future related information.
python online training
python training in OMR
python training course in chennai
mathimathi said…
Hi to all, the blog has really the dreadful information I really enjoyed a lot.
IOS Training in Chennai |
iOS Training Institutes in Chennai |
iOS Training
Anonymous said…
Well done! Pleasant post! This truly helps me to discover the solutions for my inquiry. Trusting, that you will keep posting articles having heaps of valuable data. You're the best! 
angularjs Training in marathahalli

angularjs interview questions and answers

angularjs Training in bangalore

angularjs Training in bangalore

angularjs online Training

angularjs Training in marathahalli
sunshineprofe said…
I get so much lately it’s driving me insane, so any assistance is very much appreciated.
iosh course in chennai
priya said…
I have read a few of the articles on your website now, and I really like your style of blogging. I added it to my favourites blog site list and will be checking back soon.

Microsoft Azure online training
Selenium online training
Java online training
Java Script online training
Share Point online training
ASTSTraining said…
Thank you for sharing valuable information.This article is very useful for me valuable info about
Cognos Online Training.keep updating.........
Andersonfrank said…
Great information about the reporting tool but why did you miss these ones please mention it...
ppchero
wordstream
Best PPC Reporting tool in 2019
tech news said…
It’s hard to come by well-informed people for this topic, however, you seem like you know what you’re talking about! Thanks
Gadgets
deiva said…
"Great post! I am actually getting ready to across this information, It's very helpful for this blog.Also great with all of the valuable information you have Keep up the good work you are doing well.
Digital Marketing Training Course in Chennai | Digital Marketing Training Course in Anna Nagar | Digital Marketing Training Course in OMR | Digital Marketing Training Course in Porur | Digital Marketing Training Course in Tambaram | Digital Marketing Training Course in Velachery

"
anish said…
Excellent and very cool idea and the subject at the top of magnificence and I am happy to this post..Interesting post! Thanks for writing it.What's wrong with this kind of post exactly? It follows your previous guideline for post length as well as clarity.
Oracle Training | Online Course | Certification in chennai | Oracle Training | Online Course | Certification in bangalore | Oracle Training | Online Course | Certification in hyderabad | Oracle Training | Online Course | Certification in pune | Oracle Training | Online Course | Certification in coimbatore
Super site! I am Loving it!! Will return once more, Im taking your food additionally, Thanks.
Data Science Institute in Bangalore
360digitmg said…

This is a wonderful article, Given so much info in it, These type of articles keeps the users interest in the website, and keep on sharing more ... good luck.
data science training in ecil
360digitmg said…
Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
artificial intelligence training in ecil
Gaurav said…
Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.
data science using python and r programming indore
abid said…

Thank you for helping people get the information they need. Great stuff as usual. Keep up the great work!!!
about us





Nice article and thanks for sharing with us. Its very informative




AI Training in Hyderabad



Nice article and thanks for sharing with us. Its very informative



Plots in TUKKUGUDA
Devi said…
Infycle Technologies, the No.1 software training institute in Chennai offers the No.1 Big Data course in Chennai for tech professionals and students at the best offers. In addition to the Big Data course, other in-demand courses such as Python, Selenium, Oracle, Java, Python, Power BI, Digital Marketing also will be trained with 100% practical classes. After the completion of training, the trainees will be sent for placement interviews in the top MNC's. Call 7504633633 to get more info and a free demo.Best Big Data Course in Chennai | Infycle Technologies
Infycle Technologies, the No.1 software training institute in Chennai offers the Selenium course in Chennai for tech professionals, freshers, and students at the best offers. In addition to the Selenium, other in-demand courses such as Python, Big Data, Oracle, Java, Python, Power BI, Digital Marketing, Cyber Security also will be trained with hands-on practical classes. After the completion of training, the trainees will be sent for placement interviews in the top companies. Call 7504633633 to get more info and a free demo.
data science said…


Great to become visiting your weblog once more, it has been a very long time for me. Pleasantly this article i've been sat tight for such a long time. I will require this post to add up to my task in the school, and it has identical subject along with your review. Much appreciated, great offer. data science course in nagpur
I read that Post and got it fine and informative. Please share more like that...
full stack developer course