Load Time dimension in the data warehouse

 

The Time dimension exists in every data warehouse system and usually it is the first dimension we start with.  A very common task is to populate TIME dimension target table with data which is usually one time job. I developed a PL/SQL procedure to do this.

 

1. Create TIME dimension table

 

DROP TABLE D_TIME CASCADE CONSTRAINTS;

 

CREATE TABLE D_TIME
(
TIME_ID INTEGER  NOT NULL ,
CALENDAR_DATE DATE ,
YEAR_NUMBER INTEGER,
QUARTER_NUMBER INTEGER,
MONTH_NUMBER INTEGER,
WEEK_NUMBER INTEGER,
DAY_NUMBER INTEGER,
DAY_WEEKNAME VARCHAR2(20 BYTE),
MONTH_NAME VARCHAR2(20 BYTE),
QUARTER_NAME VARCHAR2(30 BYTE),
HALF_YEAR VARCHAR2(30 BYTE),
WEEKDAY VARCHAR2(20 BYTE),
YEAR_Q_NUM VARCHAR2(30 BYTE),
IS_LAST_DAY VARCHAR2(1 BYTE),
IS_LAST_FRIDAY VARCHAR2(1 BYTE),
DAY_YYYYMM VARCHAR2(6 BYTE),
DAY_FORM_SLASH VARCHAR2(10 BYTE),
PREV_DATE DATE,
NEXT_DATE DATE
) ;

 

ALTER TABLE D_TIME ADD CONSTRAINT D_TIME_PK PRIMARY KEY ( TIME_ID ) ;

 

 

2. Load Time dimension table with a PL/SQL Procedure which populates the TIME data given from – to period

 

CREATE OR REPLACE PROCEDURE LOAD_TIME_DIM(p_start_date IN DATE, p_end_date IN DATE) AS

 

v_seq_id NUMBER:= 0;
v_load_date DATE := p_start_date;
v_qurtername VARCHAR2(30);
v_halfyear VARCHAR2(30);
v_last_day VARCHAR2(1);
v_last_friday VARCHAR2(1);
BEGIN

LOOP
v_seq_id :=  v_seq_id + 1;
v_load_date := v_load_date + 1;

 

BEGIN
SELECT
CASE
WHEN TO_CHAR(v_load_date , ‘Q’) = ‘1’ THEN ‘FIRST QUARTER’
WHEN TO_CHAR(v_load_date , ‘Q’) = ‘2’ THEN  ‘SECOND QUARTER’
WHEN TO_CHAR(v_load_date , ‘Q’) = ‘3’ THEN  ‘THIRD QUARTER’
WHEN TO_CHAR(v_load_date , ‘Q’) = ‘4’ THEN  ‘FORTH QUARTER’
ELSE ‘N/A’
END quartername,
CASE
WHEN to_char(v_load_date, ‘MM’) IN (‘1′,’2′,’3′,’4′,’5′,’6′,’01’,’02’,’03’,’04’,’05’,’06’)  THEN ‘FIRST HALF YEAR’
WHEN to_char(v_load_date, ‘MM’) IN (‘7′,’8′,’9′,’10’,’11’,’12’,’07’,’08’,’09’) THEN ‘SECOND HALF YEAR’
ELSE ‘N/A’
END half_year,
DECODE(LAST_DAY(v_load_date) , v_load_date , ‘Y’,  ‘N’) is_last_day,
CASE
WHEN ( LAST_DAY(v_load_date) = v_load_date AND  TO_NUMBER(TO_CHAR(v_load_date, ‘D’)) = 5)   THEN ‘Y’
ELSE ‘N’
END is_last_friday
INTO
v_qurtername, v_halfyear, v_last_day, v_last_friday
FROM dual;
END;

INSERT INTO D_TIME
VALUES
( v_seq_id,
TRUNC(v_load_date),
EXTRACT(YEAR FROM v_load_date),
TO_NUMBER(TO_CHAR(v_load_date , ‘Q’)),
EXTRACT(MONTH FROM v_load_date),
TRUNC(TO_NUMBER( TO_CHAR(v_load_date ,’WW’))),
EXTRACT(DAY FROM v_load_date),
TO_CHAR(v_load_date , ‘DAY’),
TO_CHAR(v_load_date , ‘MONTH’) ,
v_qurtername,
v_halfyear,
DECODE(TO_CHAR(v_load_date, ‘D’), ‘7’, ‘WEEKEND’, ‘6’, ‘WEEKEND’ ,’WEEKDAY’),
TO_CHAR(v_load_date , ‘YYYY’) ||’ Q ‘ ||TRUNC(TO_NUMBER( TO_CHAR(v_load_date,’Q’)) ),
v_last_day,
v_last_friday,
TO_CHAR(v_load_date ,’YYYYMM’) ,
TO_CHAR(v_load_date , ‘YYYY / MM’),
TRUNC(sysdate) – 1,
TRUNC(sysdate) + 1
);

 

IF  v_load_date > p_end_date THEN
EXIT;
END IF;

END LOOP;

COMMIT;
END      LOAD_TIME_DIM;

 

 

Now we are ready to use it.

BEGIN
LOAD_TIME_DIM(TO_DATE(‘31.12.1979′,’DD.MM.YYYY’),TO_DATE(‘30.12.2030′,’DD.MM.YYYY’));
END;

 

 

Cheers,

Maria

 

Advertisement