Business Intelligence, Analytics and Cognos

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, D