Last active
August 29, 2015 13:57
-
-
Save ivanrvpereira/9525807 to your computer and use it in GitHub Desktop.
MySQL procedure to load date dimension
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DELIMITER ;; | |
| CREATE PROCEDURE `p_load_dim_date`( | |
| p_from_date DATE | |
| , p_to_date DATE | |
| ) | |
| BEGIN | |
| DECLARE v_date DATE DEFAULT p_from_date; | |
| DECLARE v_month tinyint; | |
| CREATE TABLE IF NOT EXISTS dim_date ( | |
| date_key int primary key | |
| , date_value date | |
| , date_iso char(10) | |
| , year smallint | |
| , quarter tinyint | |
| , quarter_name char(2) | |
| , quarter_of_year char(6) | |
| , month tinyint | |
| , month_name varchar(10) | |
| , month_abbreviation varchar(10) | |
| , month_of_year int | |
| , week char(2) | |
| , week_of_year char(10) | |
| , day_of_month tinyint | |
| , day_of_year smallint | |
| , day_of_week smallint | |
| , day_name varchar(10) | |
| , day_abbreviation varchar(10) | |
| , is_weekend tinyint | |
| , is_weekday tinyint | |
| , is_today tinyint | |
| , is_yesterday tinyint | |
| , is_this_week tinyint | |
| , is_last_week tinyint | |
| , is_this_month tinyint | |
| , is_last_month tinyint | |
| , is_this_year tinyint | |
| , is_last_year tinyint | |
| ); | |
| WHILE v_date < p_to_date DO | |
| SET v_month := month(v_date); | |
| INSERT INTO dim_date( | |
| date_key | |
| , date_value | |
| , date_iso | |
| , year | |
| , quarter | |
| , quarter_name | |
| , quarter_of_year | |
| , month | |
| , month_name | |
| , month_abbreviation | |
| , month_of_year | |
| , week | |
| , week_of_year | |
| , day_of_month | |
| , day_of_year | |
| , day_of_week | |
| , day_name | |
| , day_abbreviation | |
| , is_weekend | |
| , is_weekday | |
| ) VALUES ( | |
| v_date + 0 | |
| , v_date | |
| , DATE_FORMAT(v_date, '%y-%c-%d') | |
| , year(v_date) | |
| , ((v_month - 1) DIV 3) + 1 | |
| , CONCAT('Q', ((v_month - 1) DIV 3) + 1) | |
| , CONCAT(year(v_date), CONCAT('Q', ((v_month - 1) DIV 3) + 1)) | |
| , v_month | |
| , DATE_FORMAT(v_date, '%M') | |
| , DATE_FORMAT(v_date, '%b') | |
| , CONCAT(year(v_date), DATE_FORMAT(v_date, '%m')) | |
| , DATE_FORMAT(v_date, '%u') | |
| , CONCAT(year(v_date), CONCAT('-W', DATE_FORMAT(v_date, '%u'))) | |
| , DATE_FORMAT(v_date, '%d') | |
| , DATE_FORMAT(v_date, '%j') | |
| , DATE_FORMAT(v_date, '%w') + 1 | |
| , DATE_FORMAT(v_date, '%W') | |
| , DATE_FORMAT(v_date, '%a') | |
| , IF(DATE_FORMAT(v_date, '%w') IN (0,6), 1, 0) | |
| , IF(DATE_FORMAT(v_date, '%w') IN (0,6), 0, 1) | |
| ); | |
| SET v_date := v_date + INTERVAL 1 DAY; | |
| END WHILE; | |
| END;; | |
| DELIMITER ; | |
| -- http://www.freeportmetrics.com/devblog/2012/11/02/how-to-quickly-add-date-dimension-to-pentaho-mondrian-olap-cube/ | |
| -- I added some fields like quarter_of_year, month_of_year and week_of_year |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Example to call procedure
CALL p_load_dim_date ("2010-01-01", "2014-01-01");