Thursday 19 May 2011

Informatica Interview based Scenarios.

 

Hi readers on daily basis i’ll be posting few scenarios discussed in IT toolbox here in my blog as most of my readers wrote to me regarding few interview based scenario’s i’m posting the same here. In case any one needs any help with these scenarios please feel free to contact me .

Skipped record count:

Scenario 1: My source files as 5, 00,000 records. While fetching its skipping records due to data type and other issues. Finally it fetches only 1, 00,000 records. Through session properties we are considering 100000 as the source count.
But actually we are loosing 400000 records. How can I find the number or records that are skipped?

Solution: OPB_SESS_TASK_LOG there is a count for SRC_FAILED_ROWS

Scenario 2: Please provide different ways to achieve this..
How to normalize the following data:

id date

101 2/4/2008

101 4/4/2008

102 6/4/2008

102 4/4/2008

103 4/4/2008

104 8/4/2008

O/P - should have only one id with the min(date)

How to create a mapping for this ?

1 ---> Using Transformations

2 ---> Using SQ Override method..

Solution: You can use the rank transformation and select rank port for id and group by on date. In properties tab select bottom and number of ranks as '1' .

Scenario 3: My scenario is like I am loading records on a daily basis, target is not a truncate load suppose from source I am loading records like

ID|Name

101|Apple

102|Orange

102|Banana

but in target I am already having target record(ID 102 of 10 records), scenario I need is like I have to delete only Empid 102 of yesterday record and load today record(2 records)

How to achieve this in Informatica?

Solution: You can achieve your goal by taking the Look up on your target table and match on the basis of ID column. Then take an expression after your lookup and add a FLAG column. In that FLAG column check for the NULL value return from Look up. After expression take 2 filters and in one filter pass the records with NULL values and Insert those records into Target.

If the Value is not NULL then you can take a UPDATE strategy and Update the old row with the new one.

Scenario 4:

Informatica Sequence Generator Scenarios

1) I have following set of records

Id | Name

101 | ABC

102 | DEF

101 | AMERICA

103 | AFRICA

102 | JAPAN

103 | CHINA

I need to generate sequence and populate in Target in the following manner

SID | ID | NAME

1   |101 | ABC

2   |101 | AMERICA

1   |102 | DEF

2   |102 | JAPAN

1   |103 | AFRICA

2   |103 | CHINA

How to implement the same in Informatica?

Solution:

1 sort on Id

2 use expression t/f like below

V_cur_id -> v_pre_id

V_pre_id -> i_id -- i_id is input

V_seq_id -> iif(v_cur_id = v_pre_id, v_seq_id+1, 1) --default 0

O_seq_id -> v_seq_id.

Scenario 5 : I have my input as below : ( Using Expression)

10

10

10

20

20

30

O/P :

1

2

3

1

2

1

How do we obtain this using the Informatica?

Solution: first import source, then use a sorter transformation . sort it by ur column, then use a expression.

in expression make this column .

like this

1.column_num(coming from sorter)

2.current_num= check if column_num=previous_num,then add (first_value +1),else 1

3.first_value=current_num.

4.previous_num(new column)= column_num

pass current_num to target.

No comments :

Post a Comment