✍ Kashif M
Trends

Monday, 9 September 2013


Combining Facts and Dimensions in Informatica


By on September 09, 2013




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.
Combining Facts and Dimensions in Informatica
Combining Facts and Dimensions in Informatica






In a separate mapping use unconnected lookup transformations to link to these dimensions.
Combining Facts and Dimensions in Informatica
Combining Facts and Dimensions in Informatica


Combining Facts and Dimensions in Informatica
Combining Facts and Dimensions in Informatica








T_LKP_TIME
Combining Facts and Dimensions in Informatica
Combining Facts and Dimensions in Informatica






Condition
Combining Facts and Dimensions in Informatica
Combining Facts and Dimensions in Informatica






LKP_LOC
Combining Facts and Dimensions in Informatica
Combining Facts and Dimensions in Informatica



Condition
Combining Facts and Dimensions in Informatica
Combining Facts and Dimensions in Informatica






LKP_LOC_ID
Combining Facts and Dimensions in Informatica
Combining Facts and Dimensions in Informatica






Condition


Combining Facts and Dimensions in Informatica
Combining Facts and Dimensions in Informatica




Combining Facts and Dimensions in Informatica
Combining Facts and Dimensions in Informatica



Kashif
mkashu: Combining Facts and Dimensions in Informatica
Review : Kashif | Kashif
Update: September 09, 2013 | Rating: 4.5

Comment for "Combining Facts and Dimensions in Informatica"

0 comments

Post a Comment

Blog Archive