Home > Development > Load Time dimension in the data warehouse

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

 

Advertisements
Categories: Development
  1. Sasank
    January 30, 2011 at 4:57 am

    Very nice post.. It really helped me..Thanks

    • January 30, 2011 at 3:15 pm

      Thanks fo reading. I am glad it helped you 🙂

      Regards,
      Maria

  2. Alper
    September 14, 2011 at 2:11 pm

    Thanks a lot, it was very useful!

  3. Fared Barwary
    January 31, 2012 at 2:30 pm

    Thanks a lot , Maria can you post the sql script of the time dimension for the cube ? your code is for dimension table , i need like this ..

    CREATE DIMENSION TIMES
    LEVEL YEAR IS TIMES_DIM.YEAR
    LEVEL QUARTER IS TIMES_DIM.QUARTER
    LEVEL MONTH IS TIMES_DIM.MONTH
    LEVEL DAY IS TIMES_DIM.DAY
    HIERARCHY TIMES_ROLLUP (
    DAY CHILD OF
    MONTH CHILD OF
    QUARTER CHILD OF
    YEAR
    )
    ATTRIBUTE MONTH DETERMINES TIMES_DIM.MONTH_NAME;

    • January 31, 2012 at 9:40 pm

      Hi Fared, In this case I use the time dimension as a table in the Oracle database.
      If you are using a BI tool, try to look after the TIMES dimension there.

      Regards,
      Maria

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: