bhagya

• Jan 30th, 2007

 

How to create Time Dimension in Informatica?

Asha

• Mar 5th, 2007

 

hi monica,

we can create time dimension in 2 different ways

1) using Oracle
 In oracle database, we have to create a procedure...to load the time  and calendar dimension.By just executing EXEC procedure name(with parameter list) we can load the time dimension at database level.
2) using  Informatica
we can create a stored procedure at database level and can include it in the informatica mapping  using  stored procedure transformation.

In our project, we have loaded Time dimension at database level only which is very easy and fast.


Thanks
Asha

mithunmca

 Profile Answers by mithunmca 

• Mar 23rd, 2007

 

hi the below procedure would create a time dimension in oracle

--Procedure  to calendar dimension type1, FROM the trancation file.

CREATE OR REPLACE PROCEDURE cal_file
is

-- Declare a pl/sql table to get the values  FROM trancation file.

type tab is table of cust_file.INVOICE_DATE%type INDEX BY
   BINARY_INTEGER;

--Declare the variables.   

vtab tab;
vstdt date;
vtgdt date;
vcount number;

BEGIN

--Collect all the rows FROMthe trancation file to the pl/sql table .

SELECT           distinct INVOICE_DATE bulk collect INTO vtab FROM cust_file;

--initialize the loop  

FOR  I IN 1..vtab.count

LOOP

--Check if the target has any record

SELECT          
count(*) INTO vcount
FROM
CALENDaR_FILE_D1
WHERE
vdate=vtab(I);

-- If there is no record then insert all the source records to the target.

IF
vcount=0

THEN
vstdt:=trunc(vtab(i),'mon');
vtgdt:=last_day(vtab(i));


LOOP

insert INTO calendar_file_d1
values (date_id.nextval,vstdt,
to_number(to_char(vstdt,'ww')),
to_char(vstdt,'mon'),
to_number(to_char(vstdt,'q')),
to_number(to_char(vstdt,'yyyy')));
vstdt :=vstdt+1;
EXIT

WHEN
vstdt>vtgdt;
END LOOP;
END IF;
END LOOP;
END;

note I :-plz take this as a model and replace all the table name's to the one's u have created in oracle

note II :- plz use the in out parameters i.e pass the date in to the sp and get the out the Id  .

For Informatica :-
                             In ur mapping take a stored procedure transformation and call this stored procedure that’s it .

if not clear plz reply i would send the complete project so that u can understand