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
