Datwarehousing/Oracle/Informatica/IDQ/MDM/Unix Plus interview questions: Informatica scenario based questions

Showing posts with label Informatica scenario based questions. Show all posts

Wednesday, 26 February 2014

Informatica scenario based questions 2/26/2014 02:34:00 pm

Informatica Scenario based interview questions (Convert columns into rows)

This is the one of most frequently asked informatica scenario based interview question.

We have source data like below in table

Output required is

I have seen rarely a candidate answer this question correctly. Their answer is mostly a crammed (by reading on various website) without understanding core concept of informatica transformations.

I will explain three methods to achieve this then we will find out which one is best method

1.       Router Transformation
2.       Normalizer Transformation
3.       Java Transformation

Router Transformation : This method take advantage of the fact that router is an active transformation and one row can fall into multiple groups i.e. if a row satisfies condition of three groups then it will fall into three groups and will produce three rows for one input row.

Step1)

Suppose we have a source with three input columns

Step2) Create an expression Transformation with field name FLD_OUTPUT to concatenate three fields separated by # sign

Step3) Create a router transformation with three output groups with condition

GROUP1 : substr(FLD_OUTPUT,1,instr(FLD_OUTPUT,'#')-1)=FLD1

GROUP2 : substr(FLD_OUTPUT,instr(FLD_OUTPUT,'#')+1,instr(FLD_OUTPUT,'#',2)-1)=FLD2

GROUP3: substr(FLD_OUTPUT,instr(FLD_OUTPUT,'#',3)+1)=FLD3

Purpose of this is that row will fall into three groups ..we are extracting string from concatenated string are comparing to respective field.i.e value A will be in first group and B in second group and so on…

Step4) Create union transformation to combine output from three output group from router..Take FLD1 from first group, FLD2 from second group ,FLD3 from third group

Output from union will be

Core Concept: if a row satisfies condition of three groups then it will fall into three groups and will produce three rows for one input row.

Easy isn’t it …No need to cram. We will now move to other methods…

Normalizer : Normalizer transformation has a property “Occurs : The number of instances of a column or group of columns in the source row. “

You can define number of occurrence of source field, Once you define number of occurrence then informatica automatically creates that number of input ports. In this case 3

Connect FLD1, FLD2 and FLD3 from source or expression in step2 of first method and FLD as output connected to target.

Output from normalizer will be

Core Concept : Normalizer transformation has a property “Occurs: The number of instances of a column or group of columns in the source row. “

Let us move to final and best method

Java Transformation : We will take advantage of property, Java transformation is most underutilized transformation of informatica, If used properly it can do magic

Generate Transaction

The transformation generates transaction rows. You can enable this property for active Java transformations.

Create a java transformation with one input and one output field

Now connect concatenated field (A#B#C) from expression in step2 of first method to FLD_INPUT of java transformation

Now go to java code and copy below

String[] str_fld_nm=FLD_INPUT.split("#");

for(int i=0; i

FLD_OUTPUT =str_fld_nm[i];

generateRow();

}

It is using java functionality of array to break input field and multiple fld and then taking advantage of generateRow() to produce multiple rows.

Now connect FLD_OUTPUT to target and it will be

Core concept : Java functionality to break concatenated string into multiple fields and generateRow() to produce multiple rows.

Now we have covered all three features. Let us see which one is best

Feature	Router	Normaliser	Java
Flexiblity	Low (As you have to hardcode number of fields)	Low (As you have to hardcode number of occurrence )	High (No Hard coding as it can handle any number of fields)
Ease	High as developer are comfortable with using router transformation	Medium as developer don’t use it frequently	Low. As most developer are not from java background. It is difficult for them to understand it

If you need xml of mapping then please send a mail to support@itnirvanas.com or lalits77@gmail.com

I request again …don’t cram answers..Instead understand the concepts..

Please make sure to have a look at our top informatica interview questions at
Top 100 Informatica Interview Questions .These can really make a difference in your interview.

Unknown 4

Tuesday, 29 October 2013

Informatica scenario based questions 10/29/2013 03:45:00 pm

Generating Dynamic Multiple Target files in Informatica

Recently we came across a scenario to generate multiple dynamic Multiple Target files in Informatica. We receive vendor data through Legacy database in below table

Invoice_ID

Invoice_No

Invoice_Amount

Vendor_Id

We need to separate all details related to one vendor in separate file so that we can pass data to third part vendors in separate file.

INVOICE_DETAILS

INVOICE_ID	INVOICE_NO	INVOICE_AMOUNT	VENDOR_ID
1	A01	100.00	10
2	A02	125.00	10
3	A03	150.00	10
4	B01	200.00	20
5	B02	500.00	20
6	D01	345.00	40
7	C01	230.00	30
8	D02	450.00	40
9	E01	40.00	50

In order to achieve this we need to follow below steps in mapping

Step1 ) Import INVOICE_DETAILS and drag into mapping and connect all port from Source qualifier to expression and do below sql override

SELECT INVOICE_DETAILS.INVOICE_ID, INVOICE_DETAILS.INVOICE_NO, INVOICE_DETAILS.INVOIC_AMOUNT, INVOICE_DETAILS.VENDOR_ID

FROM

INVOICE_DETAILS

order by VENDOR_ID

Step2) Create expression transformation with as below with 4 input port plus

FILE_NAME (Output Port)  'Vendor_'||VENDOR_ID||'Details.csv'

CHECK_VENDOR_ID(Variable Port)  iif (VENDOR_ID <> PREV_VENDOR_ID,1,0)

OUTPUT_VENDOR_ID (Output)  CHECK_VENDOR_ID

PREV_VENDOR_ID (Variable) VENDOR_ID

Step 3 ) Create Transaction Control Transformation (TC_GENERATE_FILES) with below logic

iif(OUTPUT_VENDOR_ID=1,TC_COMMIT_BEFORE,TC_CONTINUE_TRANSACTION)

Step 4 ) Create a File Target with same structure as source table and new field in target using option “Add FileName to this Table”

Connect FileName column from transaction control to FileName Port.

Step5) Run the workflow and below files will be created

Unknown 2

Wednesday, 23 October 2013

Informatica scenario based questions 10/23/2013 11:53:00 am

Informatica Scenario Based Interview Questions (Last 3 rows)

Interviewer asked “How will you get first 3 records from flat file source? “...You will say in your mind that’s simple

You reply “We can use variable in expression and increment it and the use filter transformation to pass just first two records”

Interviewer will come up with a new trick in his sleeve “How will you get last 3 records from flat file source? “

Déjà vu …J

You will think and try to build some logic on the fly and try to explain may we can use this transformation or some another transformation and will get bit confused.

I have tried to explain this with a simple example

Suppose we have customer data coming in below format and we want to have last 3 rows in target

Step1)

Create expression transformation (exp_Build_Logic) drag all three input ports and create three extra ports as below

Ø v_dummy (Output Port) –Assign Value 1 to it

Ø v_Total_Count (Variable Port) := v_Total_Count+1

Ø o_Total_Count := v_Total_Count

Step2) Create Aggregator transformation with all ports and do not choose any port as group by port

Step 3)

Create Joiner transformation jnr_Agg_Expression as Sorted Input (You need to choose this option as you are trying to use two sources originating from same source)

Take two ports from aggregator and rest from expression transformation and join on field v_dummy