Informatica - Can anyone tell me how to create time dimension? Do we have to use type1,2,or3 for creating

GeekInterview.com > Tech FAQs > Informatica

Go To First | Previous Question | Next Question

Informatica | Question 133 of 313

Can anyone tell me how to create time dimension? Do we have to use type1,2,or3 for creating time dimension or we can just create it by using only expression transf and a date column from the source?
Thanks in advance.

Total Answers and Comments: 3

Last Update: March 23, 2007 Asked by: monica

Sponsored Links

Best Rated Answer

—

No best answer available. Please pick the good answer available or submit your answer.

January 30, 2007 07:10:18

bhagya

RE: Can anyone tell me how to create time dimensi...

How to create Time Dimension in Informatica?

Is this answer useful? Yes | No

March 05, 2007 06:11:59

Asha

RE: Can anyone tell me how to create time dimensi...

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

Is this answer useful? Yes | No

March 23, 2007 04:58:37

mithunmca

Member Since: January 2007 Contribution: 7

RE: Can anyone tell me how to create time dimensi...

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

Is this answer useful? Yes | No

Go To Top