Estructura de la Dimensión Tiempo y Procedure de carga para MySQL

This post is based on this one of il-masacratore:Time: Dimension structure and loading script for SQLServer. 

As il-masacratore says usually there are a number of dimensions that are common to all DW. The Time dimension is one of them. 

The purpose of this post is to translate what has been done by il-masacratore (SQLServer) in order to be executed in MySQL.

Note that this structure and subsequent procedure, have exemplary and explanatory purposes, so each one can then create its own time dimension according to their needs and preferences. 

BUILDING THE STRUCTURE OF TIME DIMENSION  

CREATE TABLE 'dwventas. DIM_TIME' ( 
   'DateSK' int (11) NOT NULL, 
   'Date' date NOT NULL, 
   'Year' smallint (6) NOT NULL, 
   'Quarter' smallint (6) NOT NULL, 
   'Month' smallint (6) NOT NULL, 
   'Week' smallint (6) NOT NULL, 
   'Day' smallint (6) NOT NULL,  
   'WeekDay' smallint (6) NOT NULL, 
   'NQuarter' varchar (7) NOT NULL, 
   'NMonth' varchar (15) NOT NULL, 
   'NMonth3L' varchar (3) NOT NULL, 
   'NWeek' varchar (11) NOT NULL, 
   'NDay' varchar (15) NOT NULL, 
   'NeekDay' varchar (15) NOT NULL, 
  PRIMARY KEY ( `DateSK`) 
) ENGINE = MyISAM DEFAULT CHARSET = latin1 

 

CREATION OF THE LOADING PROCEDURE 

DELIMITER $ $

DROP PROCEDURE IF EXISTS ' dwretail.antDIM_TIME'$$ 
CREATE DEFINER = 'root'@'localhost' PROCEDURE 'antDIM_TIME' () 
BEGIN

delete from DIM_TIME; 

SELECT '2006-01-01 'INTO @ ds; 
SELECT '2009-07-29 'INTO @ de; 

while (@ds <= @ de) DO 

INSERT INTO DIM_TIME
  ( 
     DateSK, 
     Date, 
     Year
     Quarter, 
     Month 
     Week, 
     Day, 
     WeekDay, 
     NQuarter, 
     NMonth, 
     NMonth3L, 
     NWeek, 
     NDay, 
     NWeekDay
) 
SELECT year (@ ds) * 10000 + month (@ ds) * 100 + day (@ ds) as DateSK, 
     (@ ds) Date, 
     year (@ ds) Year,
     quarter (@ ds) Quarter, 
     month (@ ds) Month, 
     week (@ ds) Week, 
     RIGHT (concat ('0 ', day (@ ds)), 2) Day, 
     weekday (@ ds) WeekDay, 
     concat ( 'Q', quarter (@ ds ),'/', year (@ ds)) NQuarter, 
     monthname (@ ds) NMonth, 
     LEFT (monthname (@ ds), 3) NMonth3L, 
     concat ( 'Week', week (@ ds) ,'/', year (@ ds)) NWeek, 
     concat (RIGHT (concat ('0', day (@ ds)), 2),' ', monthname (@ ds)) NDay, 
    dayname (@ ds) NWeekDay;

set @ ds = DATE_ADD (@ de, INTERVAL 1 DAY); 

END WHILE;

END$$

DELIMITER; 

 

 

il-masacratore thanks for sharing!

 

I hope you find it useful.

Greetings