This blog is designed for Online Informatica
Training and Informatica Scenarios.
Online Informatica Training | Informatica
Online Training | Informatica Power Center
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.
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
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
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
Rani
7000
Output:
Eno
Ename Sal
1
Ravi
2000
2
Vinay 3000
3
Kiran 4000
1
Ramu 5000
2 Krishna
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 dept dept
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
My Contact Details:
Name: Venkat
Email:
informaticatrainer.expertise@gmail.com
