Populate Time Dimension in Data Warehouse

Stored Procedure for Time Dimension in Data Warehouse

This procedure will populate the Warehouse time Dimension. You need to give only start date and end date then it will generate the year,quarter, month, week (based on month),day name, day , week number for each day.

Note: This is for MySQL database only

  DELIMITER '/';

  DROP PROCEDURE IF EXISTS generatedate /

  CREATE PROCEDURE generatedate()

  BEGIN

  declare i DATE;

  set i = '2004-12-31'; //Starting Date

  DROP TABLE IF EXISTS DIM_DATE_TIME;

  create table DIM_DATE_TIME(dates DATE ,year integer(4),quarter varchar(2),month        varchar(10),monthnumber int(2),week varchar(5),day int(2),dayname varchar(10),quarterno    int(2),weekno int(2));

  set autocommit=0;

  while i < '2011-01-01' // Ending Date

  DO

  SET i = DATE_ADD(i, INTERVAL 1 DAY);

  insert into DIM_DATE_TIME   values(DATE(i),YEAR(i),concat('Q',QUARTER(i)),MONTHNAME(i),MONTH(i),concat('Week',WEE K(i, 5) - WEEK(DATE_SUB(i, INTERVAL DAYOFMONTH(i) - 1 DAY), 5) + 1),DAY(i),DAYNAME(i),QUARTER(i),WEEK(i, 5) - WEEK(DATE_SUB(i, INTERVAL DAYOFMONTH(i) - 1 DAY), 5) + 1);

  END WHILE;

  COMMIT;

  END;/

  DELIMITER ;

call the proceure:
call generatedate();

mysql> select * from DIM_DATE_TIME limit 1\G
*************************** 1. row ***************************
dates: 2005-01-01
year: 2005
quarter: Q1
month: January
monthnumber: 1
week: Week1
day: 1
dayname: Saturday
quarterno: 1
weekno: 1

1 row in set (0.00 sec)