This blog is designed for Online Informatica Training and Informatica Scenarios.

Online Informatica Training | Informatica Online Training | Informatica Power Center Training.

Trainer (Myself) has 11+Years of IT experience. Trained on informatica online more than 1000 IT and Non-IT people. Online Informatica Training classes designed with real time examples and scenarios, which helps you to become more competitive with other informatica professionals.

Online Informatica Training has the flexibility to record each class, which helps you to see and master on a particular topic whenever required. For more information on course content, duration, fee and currently running batches, please visit the below link.

https://informaticaonlinetraing.blogspot.com

Informatica Scenarios PART-I

1. How to load first record to first target, second record to second target and third record to third target again fourth record to first target fifth record to second target and sixth record to third target and so on?

Solution:

Mapping Flow:  Source-> Source Qualifier->Seq Generator->Expression->Router->3 targets

a) Drag your source into the mapping designer workspace, automatically source qualifier comes.

b) Create expression transformation and connect all ports from source qualifier to expression

c) Create sequence generator and connect nextval port to expression.

d) Create router and connect all the ports from expression to router.

e) Create below 3 groups in router.

   

       group1         mod(nextval,3)=1

       group 2        mod(nextval,3)=2

       group 3        mod(nextval,3)=0

f) Connect 3 groups to 3 targets

For complete Sequence Generator explanation click the below link
https://www.youtube.com/watch?v=A5_U3P7K2o0

2. How to skip first and last record from a flat file?

Solution:

Mapping Flow: Source-> Source Qualifier -> Filter -> Target

a) You can skip first record by selecting the property "Number Of Initial Rows to Skip" set to 1. Set this property in the session level under "Set file properties" or in the source itself under source analyzer tool.

b) You can skip last record by using filter transformation in the mapping. Suppose your last record is starting with value "Footer". Give the below filter condition. Assume first port name is description.

                  substr(description1,5)!=upper(FOOTER)

          

3. How to skip first and last record from a table?

Solution:

Mapping Flow: Source -> Source Qualifier -> Filter -> Target

a) Drag your source into the mapping designer workspace, automatically source qualifier comes.

b) Create sequence generator transformation and filter transformations.

c) Connect sequence generator to filter. Below is the filter condition, assume your last record is starting with value "Footer" and first port is description.

                nexval!=1 and  substr(description1,5)!=upper(FOOTER)

d) Connect all the ports from filter to target.

4. How to load only first and last record from a flat file/table?

Solution:

Mapping Flow: Source -> Source Qualifier -> Filter -> Target

a) Drag your source into the mapping designer workspace, automatically source qualifier comes.

b) Create sequence generator transformation and filter transformations.

c) Connect sequence generator to filter. Below is the filter condition, assume your last record is starting with value "Footer" and first port is description.

                nexval=1 and  substr(description1,5)=upper(FOOTER)

d) Connect all the ports from filter to target.

5. How to load only top n records from a table/flat file?

Solution:

Mapping Flow: Source -> Source Qualifier -> Filter -> Target

a) Drag your source into the mapping designer workspace, automatically source qualifier comes.

b) Create sequence generator transformation and filter transformations.

c) Connect sequence generator to filter. Below is the filter condition.               

nexval<=n    (Eg:- n=3 if you want to load top 3 records)

d) Connect all the ports from filter to target.

Informatica Scenarios PART-II

1. How to load unique records to one table and duplicate records to another table?

Input

100   ravi    2000

101   vinay 3000

100   ravi    2000

Output

Target1                                         Target2

100 ravi 2000                         100 ravi    2000

                                                 101  vinay 3000

 Solution:

Mapping Flow: Source-> Source Qualifier -> Sorter-> Expression -> Router -> Two Targets

a) Drag your source into the mapping designer workspace, automatically source qualifier comes.

b) Connect all ports from source qualifier to sorter. Select key port in the sorter to sort the data.

c) Connect all ports from sorter to expression.

d) Create below variable ports and output ports in expression

            V_CHECK              IIF(EID!=V_EID,1,0)

            V_EID                    EID

            O_CHECK             V_CHECK

 e) Connect all ports from expression to router and create two groups in router.

                     group1                             O_CHECK=1

                     group2                             O_CHECK=0

e) Connect two groups from router to two targets

2. How to load only unique records to one target and entire duplicates to another target?

Input

100   ravi    2000

101   vinay 3000

100   ravi    2000

Output

Target1                                         Target2

100 ravi 2000                         101 vinay 3000

100 ravi  2000

Solution:

Mapping Flow: Source-> Source Qualifier -> Aggregator

                            Source-> Source Qualifier -> Joiner -> Router -> Two Targets

a) Drag the same source twice into the mapping designer workspace.

b) Create aggregator transformation. From one source qualifier connect to aggregator. Set group by port on eno. Create one output port.

                                     Cnt                 Count(*)

c) Create joiner, the inputs for joiner are, aggregator and another source qualifier. Join condition eno=eno1. Join type Normal Join.

d) Create router transformation. Create below two groups. Connect all ports from joiner to router.

                                   group1             Cnt=1

                                   group2             Cnt=2

e) Connect two groups from router to two targets

For complete aggregator transformation explation click the below link
https://www.youtube.com/watch?v=SS0-XdluF1Q

3. How to generate a sequence with out using sequence generator transformation?

Solution:

Option1:

You can generate a sequence using variable ports in expression

                          V_ID         V_ID+1

                          O_ID         V_ID

Option2:

Create one output port in the expression and assign below function

                          CUME(1)

4. How to move first half records to one target table another half records to another target table?

Solution:

Mapping Flow: Source-> Source Qualifier -> Aggregator

                            Source-> Source Qualifier -> Sequence Generator->  Expression -> Joiner -> Router -> Two Targets                                

a) Drag the same source twice into the mapping designer workspace.

b) Create aggregator transformation. From one source qualifier connect to aggregator. Don't select any group by port. Create one output port and one dummy output port and set value 1.

                                     Cnt                 Count(*)

                                     Dummy              1

c) Create sequence generator and expression. Connect all ports from another source qualifier to expression. Connect sequence generator to expression. Create one output port dummy in the expression and set to 1.

c) Create joiner, the inputs for joiner are, aggregator and another source qualifier. Join condition dummy=dummy1. Join type Normal Join.

d) Create router transformation. Create below two groups. Connect all ports from joiner to router.

                                   group1             Nextval<=Cnt/2

                                   group2             Nextval>Cnt/2

e) Connect two groups from router to two targets

5. How do you generate cumulative sum for a department using informatica transformations?

Input :

dno  esal

10    2000

10    3000

10    4000

20    3000

20    4000

20    5000

Output:

10   2000

10   5000

10   9000

20   3000

20   7000

20    13000

Solution:

Mapping Flow: Source-> Source Qualifier -> Sorter-> Expression -> Target

a) Drag your source into the mapping designer workspace, automatically source qualifier comes.

b) Connect all ports from source qualifier to sorter. Select key ports (dno, esal) in the sorter to sort the data.

c) Connect all ports from sorter to expression.

d) Create below variable ports and output ports in expression

            V_C_SAL              IIF(DNO!=V_DNO,ESAL,V_C_SAL+ESAL)

            V_DNO                 DNO

            O_C_SAL             V_C_SAL

 e) Connect all ports from expression to target. Use output port O_C_SAL to generate cumulative sum.

  

Informatica Scenarios PART-III

1. How do you get previous row salary to the current row?

Solution:

Mapping Flow: Source -> Source Qualifier -> Sequence Generator -> Expression -> Target

a) Drag your source into the mapping designer workspace, automatically source qualifier comes.

b) Create sequence generator and connect nextval port to expression.

c) Create expression transformation and connect all ports from source qualifier to expression. Below are the ports in expression.

                             ENAME

                             ESAL

                             V_PRE_SAL         IIF(NEXTVAL=1,NULL,V_SAL)

                             V_SAL                   SAL

                             O_PRE_SAL         V_PRE_SAL

c) Connect all ports from expression to target.

2. How do you get next row salary to the current row?

Solution:

Mapping Flow: Source -> Source Qualifier -> Target

a) Drag your source into the mapping designer workspace, automatically source qualifier comes.

b) Double click on source qualifier, select properties tab, select sql query and write the below query.

        SELECT ENO,ENAME,ESAL,LEAD(ESAL,1,NULL) OVER(ORDER BY ENO DESC) AS NEXT_SAL FROM EMPLOYEE

c) Connect all ports from source qualifier to target.

3. How do you generate multiple flat files dynamically based on city?

Solution:

Mapping Flow: Source -> Source Qualifier -> Sorter -> Expression -> Transaction Control -> Target

a) In target designer tool, double click on target, select columns, select file name port. Drag your source into the mapping designer workspace, automatically source qualifier comes.

b) Connect all the ports from source qualifier to sorter. Select key column as city.

c) Connect all the ports from sorter to expression and create below variable and output ports.

                            V_CHECK       IIF(CITY=V_CITY,1,0)

                            V_CITY           CITY

                            O_CITY           V_CHECK

  

d) Connect all the ports from expression to transaction control transformation. Give the below condition.

                          

                     IIF(O_CITY=0,TC_COMMIT_BEFORE,TC_CONTINUE_TRANSACTION)

e) Connect all ports from transaction control to target. Connect CITY port to file name port in the target.

4. How do you convert columns to rows?

Input:

Business            Q1_Amt             Q2_Amt           Q3_Amt              Q4_Amt

   Sales                     100                    200                     300                       400

Output:

 Business              Amt             Quarter

     Sales                100                    1

     Sales                200                    2

     Sales                300                    3

     Sales                400                    4

Solution:

Mapping Flow: Source -> Source Qualifier -> Normalizer -> Target

a) Drag your source into the mapping designer workspace, automatically source qualifier comes.

b) Create normalizer transformation. Double click on normalizer tab. Create below two ports.

                   Port        Data type        Precision    Occurs

               Business    String                10                   0

               Amt            Integer              10                   4

c) Connect all ports from source qualifier to normalizer.

d) Connect all ports from normalizer to target.

5. How do you generate cyclic sequence numbers?

Input:

Ename        Sal

Ravi             2000

Vinay           3000

Kiran            4000

Ramu           5000

Krishna        6000

Rani              7000

Output:

Eno             Ename        Sal

 1                 Ravi             2000

 2                 Vinay           3000

 3                 Kiran            4000

 1                 Ramu           5000

 2                 Krishna        6000

 3                 Rani              7000

Solution:

Mapping Flow: Source-> Source Qualifier -> Sequence Generator -> Target

a) Drag your source into the mapping designer workspace, automatically source qualifier comes.

 b) Create sequence generator transformation and connect nextval port to target Eno port. Remaining all ports connect from SQ to target.

c) Double Click on Sequnce Generator, select properties tab and set below things.

             Current Value   1

             Start     Value    1

             End        Value    1

             Cycle      Enable

Informatica Scenarios PART-IV

1. Convert the following input to output

Input:

ID  Desc

1   a

2   b

1    c

2    a

3    c

Output:

1 ac

2 ba

3  c

Solution:

Mapping Flow: Source-> Source Qualifier -> Sorter ->Expression -> Aggregator -> Target

a) Drag your source into the mapping designer workspace, automatically source qualifier comes.

b) Create sorter transformation and select ID as key port.

c)  Create expression transformation and select all ports from sorter to expression. Create below variable and output ports.

                     V_CHECK            IIF(ID=VID,V_CHECK||DESC,DESC)

                     V_ID                   ID

                     O_CHECK            V_CHECK

d) Create aggregator transformation and select ID as group by port, connect all ports from expression to aggregator.

e) Connect all ports from aggregator to target.

2. How do you populate source file name in the target table, if you are loading table from multiple files?

Solution:

Mapping Flow:  Source-> Source Qualifier-> Target

a) Select source in source analyzer tool. Double click on source, select properties tab. Select the check box "Currently processed file name".

b) Copy all ports from source to source qualifier.

c) Connect all ports from source qualifier to target.

3. How do you create oracle tables using informatica mapping?

Solution:

Mapping Flow:  Source-> Source Qualifier->SQL Transformation-> target

a) Create one dummy source and one dummy target .

b) Drag source and targets into mapping designer workspace.

c) Create SQL transformation. Select script mode. In the script need to place "create table statements...". Give the path of the script in the SQL transformation.

d) Connect the ports from SQL transformation to target.

4. How do you update or delete target table data with out using update strategy transformation?

Solution:

Option1: In the session level set the property "Treat Source Row As" Update. In the mapping tab, select target, only select update checkbox option if you want to update the data, only select delete checkbox option if you want to delete the data. In this case target table should have key column.

Option2: In the session level set the property "Treat Source Row As" Update. In the mapping select your target. Double click on the target, select update override. Write update or delete statement.

5. I have two target table A and B, want to load data into target table B only if data exist in target table A

Solution:

Mapping Flow:  Source-> Source Qualifier-> Expression -> Lookup-> Filter -> Target

a) Drag your source into the mapping designer workspace, automatically source qualifier comes.

b) Connect all ports from source qualifier to expression. Create one dummy output port in expression and set value to 1.

c) Take a lookup on target table A and write lookup override.

          Select 1 dummy, count(*) cnt from A

d) Connect the expression dummy port to lookup and compare dummy=dummy1 and get the cnt as output.

e) Connect all ports from expression and take cnt from lookup and give the below filter condition.

                         cnt!=0

f) Connect all ports from filter to target.

Informatica Scenarios PART-V

1. How to load 3rd highest salary of employee from each department?

Solution:

Mapping Flow:  Source-> Source Qualifier->Rank->Filter-> Target

a) Drag your source into the mapping designer workspace, automatically source qualifier comes.

b) Create rank transformation and connect all ports from source qualifier to rank. Rank index port automatically comes when you create rank. Select Sal as rank port. Select DNO as group by port. Set below properties in rank transformation.

                        Top/Bottom: Top

               Number of Ranks:  3

c) Create filter transformation and connect all ports from rank to filter. Give the below condition in the filter transformation.

   

                         rank_index=3

d) Connect all ports from filter transformation to target.

      

2. How to convert rows to columns?

Input:

ENO ENAME        DNO          VALUE

100   ravi                   10             2000  (Salary)

100   ravi                   10             Male  (Gender)

100   ravi                   10             Sales  (Department Name)

Output:-

ENO   ENAME   DNO  SALARY  GENDER  DNAME

100    ravi       10     2000       Male       Sales

Solution:

Mapping Flow: Source-> Source Qualifier -> Sorter -> Expression -> Aggregator -> Expression -> Target

a) Drag your source into the mapping designer workspace, automatically source qualifier comes.

b) Connect all ports from source qualifier to sorter. Sort the data based on eno, ename and dno.

c) Connect all ports from sorter to expression.

d) Create below variable ports and output ports in expression

            V_CHECK              IIF(ENO=V_ENO AND ENAME=V_ENNAME AND 

                                                 DNO=V_DNO,V_CHECK||~||VALUE,VALUE)

            V_ENO                  ENO

            V_ENAME             ENAME

            V_DNO                  DNO

            O_CHECK             V_CHECK

 e) Create aggregator transformation. Connect the ports from expression to aggregator. By default it returns last row. The last row contains concatenated fields. (O_CHECK=2000~Male~Sales)

                 

f)    Connect all ports from aggregator to expression. In the expression apply substr and split the fileds.

g) Connect all ports from expression to target.

3. How do you set dependency between one workflow to another workflow?

Solution:

a) At the end of the first workflow create command task. In the command task give the below command. Create zero byte file.

                         touch dummy.txt

b) At the beginning of the second workflow create event_wait task and look for the above file. Even second workflow starts before completion of first workflow, event_wati task is only in running state and succeeds only the above file is created.

4. I have several departments in my source. When i run my session first time it should load dept 10 records, second time dept 20 records, third time dept 30 record and so on, with out touching infa code or touching parameter file between session runs.

Solution:

Mapping Flow:  Source-> Source Qualifier-> Filter-> Target

a) Drag your source into the mapping designer workspace, automatically source qualifier comes.

b) From the menu bar select mappings->Parameters and variables. Select variable and define like below.

                         $$DNO                 VARIABLE

c) Create filter transformation and give the below condition

                          DNO=SETVARIABLE($$DNO,$$DNO+10)

By default $$DNO=0. After you run the first time, the variable value will be stored in informatica repository as 10. Next time you run the variable value will be stored in informatica repository as 20 and so on. SETVARIABLE is a pre defined function to set the value of the variable.

d) Connect all the ports from filter to target

5. How do you move invalid date records to one target table and other records to another target table?

Solution:

Mapping Flow:  Source-> Source Qualifier->Router->2 targets

a) Drag your source into the mapping designer workspace, automatically source qualifier comes.

b) Create router and connect all the ports from expression to router.

c) Create below 3 groups in router.

   

       group1         IS_DATE(DATE,'YYYYMMDD')=0

       group2         IS_DATE(DATE,'YYYYMMDD')<>0

d) Connect 2 groups to 2 targets

200 Informatica Interview Questions And Answers

https://www.youtube.com/watch?v=yCpo-cHUNjA

My Contact Details:

Name: Venkat

Email:  informaticatrainer.expertise@gmail.com

Mobile: 91-8008829289