Combining Facts and Dimensions in Informatica

First of all write a stored procedure to populate the Time Dimension Table.

CREATE OR REPLACE PROCEDURE TIME_DIM

IS

PV_start_date DATE := '01-Jan-1980';
\
v_end_date DATE := '31-DEC-2050';

v_week NUMBER := 0;

v_month NUMBER := 0;

v_quarter NUMBER := 0;

v_year NUMBER := 0;

BEGIN

WHILE v_start_date <= v_end_date

LOOP

v_week := TO_CHAR(V_start_date,'ww');

v_month := TO_CHAR(V_start_date,'mm');

v_quarter := TO_CHAR(V_start_date,'q');

v_year:= TO_CHAR(V_start_date,'yyyy');

INSERT INTO TIME_DIM_NEW (TIME_KEY, HIREDATE, WEEK, MONTH, QUARTER, YEAR)

VALUES (TIME_SEQ.NEXTVAL,v_start_date,v_week,v_quarter,v_year);

v_start_date:= v_start_date + 1;

END LOOP;

END;

First Project the Dimension Data.