Procedure to create a simple Time Dimension Table seed data

Here is how to create a simple time dimesion table and to populate it with seed data.

CREATE TABLE “CUBE_DATA”.”W_DAY_D”
(    “ROW_WID” NUMBER(10,0),
“DAY_DATE” DATE,
“YEAR_NUM” NUMBER(10,0),
“MONTH_NAME” VARCHAR2(30 BYTE),
“MONTH_NUMBER” NUMBER(10,0),
“QUARTER_NAME” VARCHAR2(30 BYTE),
“QUARTER_NUMBER” NUMBER(10,0),
“WEAK_YR” NUMBER(10,0),
“WEAK_MTH” NUMBER(10,0),
“MONTH_AGO_WID” NUMBER(10,0),
“QUARTER_AGO_WID” NUMBER(10,0),
“YEAR_AGO_WID” NUMBER(10,0),
“FISCAL_YEAR_NUM” NUMBER(10,0),
“FISCAL_MONTH_NUMBER” NUMBER(10,0),
“FISCAL_QUARTER_NAME” VARCHAR2(30 BYTE),
“FISCAL_QUARTER_NUMBER” NUMBER(10,0),
“FISCAL_WEEK_YR” NUMBER(10,0)
)

create or replace
PROCEDURE W_DAY_D_PROC
( BEGIN_YEAR IN NUMBER
, END_YEAR IN NUMBER,FY_DIFF IN NUMBER
)
AS
/* FY_DIFF is the diiference between Calendar year and Fiscal Year. For example
if the Fiscal year starts in Aug then the difference is 5 */
V_BEGIN_DATE DATE;
V_END_DATE DATE;
V_YEAR NUMBER;
V_ROW_WID NUMBER;
V_MONTH_NAME VARCHAR(30);
V_MONTH_NUMBER NUMBER;
V_QUARTER_NAME VARCHAR(30);
V_QUARTER_NUMBER NUMBER;
V_WEAK_YR NUMBER;
V_WEAK_MTH NUMBER;
————————Ago Variables——————————————-
V_MONTH_AGO_DAY DATE;
V_QUARTER_AGO_DAY DATE;
V_YEAR_AGO_DAY DATE;
V_MONTH_AGO_WID NUMBER(10);
V_QUARTER_AGO_WID NUMBER(10);
V_YEAR_AGO_WID NUMBER(10);

——————— Fiscal Year Variables ————————————
V_FY_YEAR NUMBER;
V_FY_MONTH_NUMBER NUMBER;
V_FY_QUARTER_NAME VARCHAR(30);
V_FY_QUARTER_NUMBER NUMBER;
V_FY_WEEK_YR NUMBER;
V_FY_DATE DATE;
BEGIN

V_BEGIN_DATE := TO_DATE(TO_CHAR(BEGIN_YEAR*10000+100+1),’YYYY/MM/DD’);
V_END_DATE := TO_DATE(TO_CHAR(END_YEAR*10000+1200+31),’YYYY/MM/DD’);

LOOP
————– Calendar Date Information ————————————-
V_YEAR := TO_NUMBER(TO_CHAR(V_BEGIN_DATE,’YYYY’));
V_ROW_WID := TO_NUMBER(TO_CHAR(V_BEGIN_DATE,’YYYYMMDD’));
V_MONTH_NAME := TO_CHAR(V_BEGIN_DATE,’Month’);
V_MONTH_NUMBER := TO_NUMBER(TO_CHAR(V_BEGIN_DATE,’MM’));
V_WEAK_YR := TO_NUMBER(TO_CHAR(V_BEGIN_DATE,’WW’));
V_WEAK_MTH := TO_NUMBER(TO_CHAR(V_BEGIN_DATE,’W’));
V_MONTH_AGO_DAY := ADD_MONTHS(V_BEGIN_DATE,-1);
V_MONTH_AGO_WID := TO_NUMBER(TO_CHAR(V_MONTH_AGO_DAY,’YYYYMMDD’));
V_QUARTER_AGO_DAY := ADD_MONTHS(V_BEGIN_DATE,-3);
V_QUARTER_AGO_WID := TO_NUMBER(TO_CHAR(V_QUARTER_AGO_DAY,’YYYYMMDD’));
V_YEAR_AGO_DAY := ADD_MONTHS(V_BEGIN_DATE,-12);
V_YEAR_AGO_WID := TO_NUMBER(TO_CHAR(V_YEAR_AGO_DAY,’YYYYMMDD’));

CASE
WHEN V_MONTH_NUMBER IN (1,2,3) THEN V_QUARTER_NUMBER := 1;
WHEN V_MONTH_NUMBER IN (4,5,6) THEN V_QUARTER_NUMBER := 2;
WHEN V_MONTH_NUMBER IN (7,8,9) THEN V_QUARTER_NUMBER := 3;
ELSE V_QUARTER_NUMBER := 4;
END CASE;

V_QUARTER_NAME := TO_CHAR(V_YEAR)||’ Q’||TO_CHAR(V_QUARTER_NUMBER) ;

——– Fiscal Date Information ———————————————-

V_FY_DATE := ADD_MONTHS (V_BEGIN_DATE,FY_DIFF);
V_FY_YEAR := TO_NUMBER(TO_CHAR(V_FY_DATE,’YYYY’));
V_FY_MONTH_NUMBER := TO_NUMBER(TO_CHAR(V_FY_DATE,’MM’));
V_FY_WEEK_YR := TO_NUMBER(TO_CHAR(V_FY_DATE,’WW’));

CASE
WHEN V_FY_MONTH_NUMBER IN (1,2,3) THEN V_FY_QUARTER_NUMBER := 1;
WHEN V_FY_MONTH_NUMBER IN (4,5,6) THEN V_FY_QUARTER_NUMBER := 2;
WHEN V_FY_MONTH_NUMBER IN (7,8,9) THEN V_FY_QUARTER_NUMBER := 3;
ELSE V_FY_QUARTER_NUMBER := 4;
END CASE;

V_FY_QUARTER_NAME := TO_CHAR(V_FY_YEAR)||’ Q’||TO_CHAR(V_FY_QUARTER_NUMBER) ;

INSERT INTO W_DAY_D(ROW_WID,DAY_DATE,YEAR_NUM, MONTH_NAME,MONTH_NUMBER,QUARTER_NAME,QUARTER_NUMBER,
WEAK_YR,WEAK_MTH,MONTH_AGO_WID,QUARTER_AGO_WID,YEAR_AGO_WID,FISCAL_YEAR_NUM,FISCAL_MONTH_NUMBER,
FISCAL_QUARTER_NAME,FISCAL_QUARTER_NUMBER,FISCAL_WEEK_YR)
VALUES (V_ROW_WID, V_BEGIN_DATE,V_YEAR,V_MONTH_NAME,V_MONTH_NUMBER,V_QUARTER_NAME,V_QUARTER_NUMBER,
V_WEAK_YR,V_WEAK_MTH,V_MONTH_AGO_WID,V_QUARTER_AGO_WID,V_YEAR_AGO_WID,V_FY_YEAR,V_FY_MONTH_NUMBER,
V_FY_QUARTER_NAME,V_FY_QUARTER_NUMBER,V_FY_WEEK_YR);

IF V_BEGIN_DATE >= V_END_DATE THEN
EXIT;
END IF;
V_BEGIN_DATE := V_BEGIN_DATE + 1;
END LOOP;
COMMIT;

END W_DAY_D_PROC;

Advertisements