Date Dimension - Oracle

In this post, I will create a script that I use quite on multiple clients, and is used in all Data Wharehouse that I developed, it is the Date Dimension.

"A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision making process." (INMON, 1990)

Therefore, DW is time-variant, then the time is a constant in our projects. Nothing better than having a script in hand that will help you a lot in creating your models.

The scripts were created using Oracle 11gR2 database.

Let's start!

DROP TABLE DIM_TIME_DATE;
CREATE TABLE "DIM_TIME_DATE" 
(
"SK_DATE" INTEGER, 
"DATE" DATE, 
"NR_MONTH" INTEGER, 
"DS_MONTH" VARCHAR2(36 BYTE), 
"AB_MONTH" CHAR(3 BYTE), 
"ID_MONTH" INTEGER, 
"NR_QUARTER" INTEGER, 
"DS_QUARTER" CHAR(2 BYTE), 
"ID_QUARTER" INTEGER, 
"DS_YEAR" CHAR(4 BYTE),
"ID_YEAR" INTEGER,
"LOAD_DATE" DATE
);

With our table created, we will insert the data into it.

DECLARE
V_START DATE := TO_DATE('01/01/1950','DD/MM/YYYY'); --ALTER HERE YOUR START DATE
V_END   DATE := TO_DATE('31/12/2015','DD/MM/YYYY'); --ALTER HERE YOUR END DATE

BEGIN

EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT =   ''DD/MM/YYYY'''; -- ALTER HERE YOUR DATE MASK
--EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_LANGUAGE = ''BRAZILIAN PORTUGUESE'''; -- ALTER HERE YOUR REGION LANGUAGE
EXECUTE IMMEDIATE 'TRUNCATE TABLE DIM_TIME_DATA';

WHILE V_START <= V_END 
LOOP
INSERT INTO DIM_TIME_DATE
VALUES 
(
  --SK_DATE
  TO_NUMBER(TO_CHAR(V_START,'YYYYQMMDD')), 
  --DATE
  TO_DATE(V_START,'DD/MM/YYYY'),
  --NR_MONTH
  TO_NUMBER(TO_CHAR(V_START,'MM')),
  --DS_MONTH
  TO_CHAR(V_START,'Month'),
  --AB_MONTH
  TO_CHAR(V_START,'MON'),
  --ID_MONTH
  TO_NUMBER(TO_CHAR(V_START,'YYYY') || TO_CHAR(V_START,'Q') || TO_CHAR(V_START,'MM')),
  --NR_QUARTER
  TO_NUMBER(TO_CHAR(V_START,'Q')),
  --DS_QUARTER
  'T'||TO_CHAR(V_START,'Q'),
  --ID_QUARTER
  TO_NUMBER(TO_CHAR(V_START,'YYYY') || TO_CHAR(V_START,'Q')),
  --DS_YEAR
  TO_CHAR(V_START,'YYYY'),
  --ID_YEAR
  TO_NUMBER(TO_CHAR(V_START,'YYYY')),
  --LOAD_DATE
  TO_DATE(SYSDATE,'DD/MM/YYYY')
);
V_START := V_START+1;
END LOOP;
COMMIT;
END;
/

Guithub file:
https://github.com/igorgentil/PLSQL/blob/master/SCRIPT_DIM_DATE.sql

Our dimension is created, populated and ready to use.

We can expand it more, there are several time objects that we can explore, such as week number of year, weekday, weekend, at last, we can create a really large big dimension and caters to various management issues. But as I explained, this dimension will meet most of your projects.

Soon I'll post a script to create a timetable dimension. It's that simple.

Until next.