Informatica Scenario Based Interview Questions with Answers - Part 1

1. How to generate sequence numbers using expression transformation?

Solution:
In the expression transformation, create a variable port and increment it by 1. Then assign
the variable port to an output port. In the expression transformation, the ports are
!"count#!"count$1
%"count#!"count
2. &esign a mapping to load the first ' rows from a flat file into a target?
Solution:
(ou have to assign row numbers to each record. )enerate the row numbers either using
the expression transformation as mentioned above or use sequence generator
transformation.
Then pass the output to filter transformation and specify the filter condition as %"count
*#'
3. &esign a mapping to load the last ' rows from a flat file into a target?
Solution:
+onsider the source has the following data.
col
a
b
c
d
e
Step1: (ou have to assign row numbers to each record. )enerate the row numbers using
the expression transformation as mentioned above and call the row number generated
port as %"count. +reate a &,--( output port in the same expression transformation
and assign 1 to that port. .o that, the &,--( output port always return 1 for each row.
In the expression transformation, the ports are
!"count#!"count$1
%"count#!"count
%"dummy#1
The output of expression transformation will be
col, o"count, o"dummy
a, 1, 1
b, /, 1
c, ', 1
d, 0, 1
e, 1, 1
Step2: 2ass the output of expression transformation to aggregator and do not specify any
group by condition. +reate an output port %"total"records in the aggregator and assign
%"count port to it. The aggregator will return the last row by default. The output of
aggregator contains the &,--( port which has value 1 and %"total"records port which
has the value of total number of records in the source.
In the aggregator transformation, the ports are
%"dummy
%"count
%"total"records#%"count
The output of aggregator transformation will be
%"total"records, %"dummy
1, 1
Step3: 2ass the output of expression transformation, aggregator transformation to 3oiner
transformation and 3oin on the &,--( port. In the 3oiner transformation chec4 the
property sorted input, then only you can connect both expression and aggregator to 3oiner
transformation.
In the 3oiner transformation, the 3oin condition will be
%"dummy 5port from aggregator transformation6 # %"dummy 5port from expression
transformation6
The output of 3oiner transformation will be
col, o"count, o"total"records
a, 1, 1
b, /, 1
c, ', 1
d, 0, 1
e, 1, 1
Step: 7ow pass the ouput of 3oiner transformation to filter transformation and specify
the filter condition as %"total"records 5port from aggregator68%"count5port from
expression6 *#/
In the filter transformation, the filter condition will be
%"total"records 8 %"count *#/
The output of filter transformation will be
col o"count, o"total"records
c, ', 1
d, 0, 1
e, 1, 1
. &esign a mapping to load the first record from a flat file into one table 9, the last
record from a flat file into table : and the remaining records into table +?
Solution:
This is similar to the above problem; the first ' steps are same. In the last step instead of
using the filter transformation, you have to use router transformation. In the router
transformation create two output groups.
In the first group, the condition should be %"count#1 and connect the corresponding
output group to table 9. In the second group, the condition should be
%"count#%"total"records and connect the corresponding output group to table :. The
output of default group should be connected to table +.
!. +onsider the following products data which contain duplicate rows.
9
:
+
+
:
&
:
Q1. &esign a mapping to load all unique products in one table and the duplicate rows in
another table.
The first table should contain the following output
9
&
The second target should contain the following output
:
:
:
+
+
Solution:
,se sorter transformation and sort the products data. 2ass the output to an expression
transformation and create a dummy port %"dummy and assign 1 to that port. .o that, the
&,--( output port always return 1 for each row.
The output of expression transformation will be
2roduct, %"dummy
9, 1
:, 1
:, 1
:, 1
+, 1
+, 1
&, 1
2ass the output of expression transformation to an aggregator transformation. +hec4 the
group by on product port. In the aggreagtor, create an output port
%"count"of"each"product and write an expression count5product6.
The output of aggregator will be
2roduct, %"count"of"each"product
9, 1
:, '
+, /
&, 1
7ow pass the output of expression transformation, aggregator transformation to 3oiner
transformation and 3oin on the products port. In the 3oiner transformation chec4 the
property sorted input, then only you can connect both expression and aggregator to 3oiner
transformation.
The output of 3oiner will be
product, %"dummy, %"count"of"each"product
9, 1, 1
:, 1, '
:, 1, '
:, 1, '
+, 1, /
+, 1, /
&, 1, 1
7ow pass the output of 3oiner to a router transformation, create one group and specify the
group condition as %"dummy#%"count"of"each"product. Then connect this group to
one table. +onnect the output of default group to another table.
Q2. &esign a mapping to load each product once into one table and the remaining
products which are duplicated into another table.
The first table should contain the following output
9
:
+
&
The second table should contain the following output
:
:
+
Solution:
,se sorter transformation and sort the products data. 2ass the output to an expression
transformation and create a variable port,!"curr"product, and assign product port to it.
Then create a !"count port and in the expression editor write
II<5!"curr"product#!"prev"product, !"count$1,16. +reate one more variable port
!"prev"port and assign product port to it. 7ow create an output port %"count port and
assign !"count port to it.
In the expression transformation, the ports are
2roduct
!"curr"product#product
!"count#II<5!"curr"product#!"prev"product,!"count$1,16
!"prev"product#product
%"count#!"count
The output of expression transformation will be
2roduct, %"count
9, 1
:, 1
:, /
:, '
+, 1
+, /
&, 1
7ow 2ass the output of expression transformation to a router transformation, create one
group and specify the condition as %"count#1. Then connect this group to one table.
+onnect the output of default group to another table.
1. +onsider the following employees data as source
employee"id, salary
1=, 1===
/=, /===
'=, '===
0=, 1===
Q1. &esign a mapping to load the cumulative sum of salaries of employees into target
table?
The target table data should loo4 li4e as
employee"id, salary, cumulative"sum
1=, 1===, 1===
/=, /===, '===
'=, '===, >===
0=, 1===, 11===
Solution:
+onnect the source ?ualifier to expression transformation. In the expression
transformation, create a variable port !"cum"sal and in the expression editor write
!"cum"sal$salary. +reate an output port %"cum"sal and assign !"cum"sal to it.
Q2. &esign a mapping to get the pervious row salary for the current row. If there is no
pervious row exists for the current row, then the pervious row salary should be displayed
as null.
The output should loo4 li4e as
employee"id, salary, pre"row"salary
1=, 1===, 7ull
/=, /===, 1===
'=, '===, /===
0=, 1===, '===
Solution:
+onnect the source ?ualifier to expression transformation. In the expression
transformation, create a variable port !"count and increment it by one for each row
entering the expression transformation. 9lso create !"salary variable port and assign
the expression II<5!"count#1,7,@@,!"prev"salary6 to it . Then create one more
variable port !"prev"salary and assign .alary to it. 7ow create output port
%"prev"salary and assign !"salary to it. +onnect the expression transformation to the
target ports.
In the expression transformation, the ports will be
employee"id
salary
!"count#!"count$1
!"salary#II<5!"count#1,7,@@,!"prev"salary6
!"prev"salary#salary
%"prev"salary#!"salary
Q3. &esign a mapping to get the next row salary for the current row. If there is no next
row for the current row, then the next row salary should be displayed as null.
The output should loo4 li4e as
employee"id, salary, next"row"salary
1=, 1===, /===
/=, /===, '===
'=, '===, 1===
0=, 1===, 7ull
Solution:
Step1: +onnect the source qualifier to two expression transformation. In each expression
transformation, create a variable port !"count and in the expression editor write
!"count$1. 7ow create an output port %"count in each expression transformation. In the
first expression transformation, assign !"count to %"count. In the second expression
transformation assign !"count81 to %"count.
In the first expression transformation, the ports will be
employee"id
salary
!"count#!"count$1
%"count#!"count
In the second expression transformation, the ports will be
employee"id
salary
!"count#!"count$1
%"count#!"count81
Step2: +onnect both the expression transformations to 3oiner transformation and 3oin
them on the port %"count. +onsider the first expression transformation as -aster and
second one as detail. In the 3oiner specify the 3oin type as &etail %uter Aoin. In the 3oiner
transformation chec4 the property sorted input, then only you can connect both
expression transformations to 3oiner transformation.
Step3: 2ass the output of 3oiner transformation to a target table. <rom the 3oiner, connect
the employee"id, salary which are obtained from the first expression transformation to
the employee"id, salary ports in target table. Then from the 3oiner, connect the salary
which is obtained from the second expression transformaiton to the next"row"salary port
in the target table.
Q. &esign a mapping to find the sum of salaries of all employees and this sum should
repeat for all the rows.
The output should loo4 li4e as
employee"id, salary, salary"sum
1=, 1===, 11===
/=, /===, 11===
'=, '===, 11===
0=, 1===, 11===
Solution:
Step1: +onnect the source qualifier to the expression transformation. In the expression
transformation, create a dummy port and assign value 1 to it.
In the expression transformation, the ports will be
employee"id
salary
%"dummy#1
Step2: 2ass the output of expression transformation to aggregator. +reate a new port
%"sum"salary and in the expression editor write .,-5salary6. &o not specify group by
on any port.
In the aggregator transformation, the ports will be
salary
%"dummy
%"sum"salary#.,-5salary6
Step3: 2ass the output of expression transformation, aggregator transformation to 3oiner
transformation and 3oin on the &,--( port. In the 3oiner transformation chec4 the
property sorted input, then only you can connect both expression and aggregator to 3oiner
transformation.
Step: 2ass the output of 3oiner to the target table.
2. +onsider the following employees table as source
department"no, employee"name
/=, B
1=, 9
1=, &
/=, 2
1=, :
1=, +
/=, ?
/=, .
Q1. &esign a mapping to load a target table with the following values from the above
source?
department"no, employee"list
1=, 9
1=, 9,:
1=, 9,:,+
1=, 9,:,+,&
/=, 9,:,+,&,2
/=, 9,:,+,&,2,?
/=, 9,:,+,&,2,?,B
/=, 9,:,+,&,2,?,B,.
Solution:
Step1: ,se a sorter transformation and sort the data using the sort 4ey as department"no
and then pass the output to the expression transformation. In the expression
transformation, the ports will be
department"no
employee"name
!"employee"list # II<5I.7,@@5!"employee"list6,employee"name,!"employee"listCCD,DCC
employee"name6
%"employee"list # !"employee"list
Step2: 7ow connect the expression transformation to a target table.
Q2. &esign a mapping to load a target table with the following values from the above
source?
department"no, employee"list
1=, 9
1=, 9,:
1=, 9,:,+
1=, 9,:,+,&
/=, 2
/=, 2,?
/=, 2,?,B
/=, 2,?,B,.
Solution:
Step1: ,se a sorter transformation and sort the data using the sort 4ey as department"no
and then pass the output to the expression transformation. In the expression
transformation, the ports will be
department"no
employee"name
!"curr"deptno#department"no
!"employee"list # II<5!"curr"deptnoE #
!"prev"deptno,employee"name,!"employee"listCCD,DCCemployee"name6
!"prev"deptno#department"no
%"employee"list # !"employee"list
Step2: 7ow connect the expression transformation to a target table.
Q3. &esign a mapping to load a target table with the following values from the above
source?
department"no, employee"names
1=, 9,:,+,&
/=, 2,?,B,.
Solution:
The first step is same as the above problem. 2ass the output of expression to an
aggregator transformation and specify the group by as department"no. 7ow connect the
aggregator transformation to a target table.
1. +onsider the following product types data as the source.
2roduct"id, product"type
1=, video
1=, 9udio
/=, 9udio
'=, 9udio
0=, 9udio
1=, 9udio
1=, -ovie
/=, -ovie
'=, -ovie
0=, -ovie
1=, -ovie
>=, -ovie
9ssume that there are only ' product types are available in the source. The source
contains 1/ records and you dont 4now how many products are available in each
product type.
Q1. &esign a mapping to select F products in such a way that ' products should
be selected from video, ' products should be selected from 9udio and the
remaining ' products should be selected from -ovie.
Solution:
Step1: ,se sorter transformation and sort the data using the 4ey as product"type.
Step2: +onnect the sorter transformation to an expression transformation. In the
expression transformation, the ports will be
product"id
product"type
!"curr"prod"type#product"type
!"count # II<5!"curr"prod"type # !"prev"prod"type,!"count$1,16
!"prev"prod"type#product"type
%"count#!"count
Step3: 7ow connect the expression transformaion to a filter transformation and
specify the filter condition as %"count*#'. 2ass the output of filter to a target
table.
Q2. In the above problem ?1, if the number of products in a particular product
type are less than ', then you wont get the total F records in the target table. <or
example, see the videos type in the source data. 7ow design a mapping in such
way that even if the number of products in a particular product type are less than
', then you have to get those less number of records from another porduc types.
<or example If the number of products in videos are 1, then the reamaining /
records should come from audios or movies. .o, the total number of records in the
target table should always be F.
Solution:
The first two steps are same as above.
Step3: +onnect the expression transformation to a sorter transformation and sort
the data using the 4ey as %"count. The ports in soter transformation will be
product"id
product"type
%"count 5sort 4ey6
Step3: &iscard %"count port and connect the sorter transformation to an
expression transformation. The ports in expression transformation will be
product"id
product"type
!"count#!"count$1
%"prod"count#!"count
Step: +onnect the expression to a filter transformation and specify the filter
condition as %"prod"count*#F. +onnect the filter transformation to a target table.
2. &esign a mapping to convert column data into row data without using the
normaliGer transformation.
The source data loo4s li4e
col1, col/, col'
a, b, c
d, e, f
The target table data should loo4 li4e
+ol
a
b
c
d
e
f
Solution:
+reate three expression transformations with one port each. +onnect col1 from
.ource ?ualifier to port in first expression transformation. +onnect col/ from
.ource ?ualifier to port in second expression transformation. +onnect col' from
source qualifier to port in third expression transformation. +reate a union
transformation with three input groups and each input group should have one port.
7ow connect the expression transformations to the input groups and connect the
union transformation to the target table.
3. &esign a mapping to convert row data into column data.
The source data loo4s li4e
id, value
1=, a
1=, b
1=, c
/=, d
/=, e
/=, f
The target table data should loo4 li4e
id, col1, col/, col'
1=, a, b, c
/=, d, e, f
Solution:
Step1: ,se sorter transformation and sort the data using id port as the 4ey. Then
connect the sorter transformation to the expression transformation.
Step2: In the expression transformation, create the ports and assign the
expressions as mentioned below.
id
value
!"curr"id#id
!"count# II<5v"curr"id#!"prev"id,!"count$1,16
!"prev"id#id
%"col1# II<5!"count#1,value,7,@@6
%"col/# II<5!"count#/,value,7,@@6
%"col'# II<5!"count#',value,7,@@6
Step3: +onnect the expression transformation to aggregator transformation. In the
aggregator transforamtion, create the ports and assign the expressions as
mentioned below.
id 5specify group by on this port6
%"col1
%"col/
%"col'
col1#-9H5%"col16
col/#-9H5%"col/6
col'#-9H5%"col'6
Stpe: 7ow connect the ports id, col1, col/, col' from aggregator transformation
to the target table.
Ta4e a loo4 at the following tree structure diagram. <rom the tree structure, you can
easily derive the parent8child relationship between the elements. <or example, : is parent
of & and I.
The above tree structure data is represented in a table as shown below.
c1, c/, c', c0
9, :, &, H
9, :, &, I
9, :, I, 7,@@
9, +, <, 7,@@
9, +, ), 7,@@
Here in this table, column +1 is parent of column +/, column +/ is parent of column
+', column +' is parent of column +0.
Q1. &esign a mapping to load the target table with the below data. Here you need to
generate sequence numbers for each element and then you have to get the parent id.
9s the element J9J is at root, it does not have any parent and its parent"id is 7,@@.
id, element, parent"id
1, 9, 7,@@
/, :, 1
', +, 1
0, &, /
1, I, /
>, <, '
K, ), '
L, H, 0
F, I, 0
I have provided the solution for this problem in %racle .ql query. If you are interested
you can +lic4 Here to see the solution.
Q2. This is an extension to the problem ?1. @et say column +/ has null for all the
rows, then +1 becomes the parent of +' and c' is parent of +0. @et say both columns
c/ and c' has null for all the rows. Then c1 becomes the parent of c0. &esign a
mapping to accommodate these type of null conditions.
Q1. The source data contains only column DidD. It will have sequence numbers from 1 to
1===. The source data loo4s li4e as
Id
1
/
'
0
1
>
K
L
....
1===
+reate a wor4flow to load only the <ibonacci numbers in the target table. The target table
data should loo4 li4e as
Id
1
/
'
1
L
1'
.....
In <ibonacci series each subsequent number is the sum of previous two numbers. Here
assume that the first two numbers of the fibonacci series are 1 and /.
Solution:
.TI21 &rag the source to the mapping designer and then in the .ource ?ualifier
Transformation properties, set the number of sorted ports to one. This will sort the source
data in ascending order. .o that we will get the numbers in sequence as 1, /, ', ....1===
.TI2/ +onnect the .ource ?ualifier Transformation to the Ixpression Transformation.
In the Ixpression Transformation, create three variable ports and one output port. 9ssign
the expressions to the ports as shown below.
2orts in Ixpression Transformation
id
v"sum # v"prev"val1 $ v"prev"val/
v"prev"val1 # II<5id#1 or id#/,1, II<5v"sum # id, v"prev"val/, v"prev"val16 6
v"prev"val/ # II<5id#1 or id #/, /, II<5v"sum#id, v"sum, v"prev"val/6 6
o"flag # II<5id#1 or id#/,1, II<5 v"sum#id,1,=6 6
.TI2' 7ow connect the Ixpression Transformation to the <ilter Transformation and
specify the <ilter +ondition as o"flag#1
.TI20 +onnect the <ilter Transformation to the Target Table.
Q2. The source table contains two columns JidJ and JvalJ. The source data loo4s li4e as
below
id val
1 a,b,c
/ pq,m,n
' asG,ro,liqt
Here the JvalJ column contains comma delimited data and has three fields in that column.
+reate a wor4flow to split the fields in MvalN column to separate rows. The output should
loo4 li4e as below.
id val
1 a
1 b
1 c
/ pq
/ m
/ n
' asG
' ro
' liqt
Solution:
.TI21 +onnect three .ource ?ualifier transformations to the .ource &efinition
.TI2/ 7ow connect all the three .ource ?ualifier transformations to the ,nion
Transformation. Then connect the ,nion Transformation to the .orter
Transformation. In the sorter transformation sort the data based on Id port in
ascending order.
.TI2' 2ass the output of .orter Transformation to the Ixpression Transformation.
The ports in Ixpression Transformation are
id 5inputOoutput port
val 5input port6
v"currend"id 5variable port6 # id
v"count 5variable port6 # II<5v"current"idE#v"previous"id,1,v"count$16
v"previous"id 5variable port6 # id
o"val 5output port6 # &I+%&I5v"count, 1,
.,:.TB5val, 1, I7.TB5val,D,D,1,1681 6,
/,
.,:.TB5val, I7.TB5val,D,D,1,16$1, I7.TB5val,D,D,1,/68I7.TB5val,D,D,1,16816,
',
.,:.TB5val, I7.TB5val,D,D,1,/6$16,
7,@@
6
.TI20 7ow pass the output of Ixpression Transformation to the Target definition.
+onnect id, o"val ports of Ixpression Transformation to the id, val ports of Target
&efinition.
<or those who are interested to solve this problem in oracle sql, +lic4 Here. The
oracle sql query provides a dynamic solution where the JvalJ column can have
varying number of fields in each row.
The transformations which used mostly are listed in the below table. +lic4 on the
transforamtion to see the interview questions on the particular transformation.
9ggregator 9ctiveO+onnected
Ixpression 2assiveO+onnected
<ilter 9ctiveO+onnected
Aoiner 9ctiveO+onnected
@oo4up 2assiveO+onnected or ,nconnected
7ormaliGer 9ctiveO+onnected
Ban4 9ctiveO+onnected
Bouter 9ctiveO+onnected
.equence )enerator 2assiveO+onnected
.orter 9ctiveO+onnected
.ource ?ualifier 9ctiveO+onnected
.?@ 9ctive or 2assiveO+onnected
.tored 2rocedure 2assiveO+onnected or ,nconnected
Transaction +ontrol 9ctiveO+onnected
,nion 9ctiveO+onnected
,pdate .trategy 9ctiveO+onnected
1. Phat is a transformation?
9 transformation is a repository ob3ect that generates, modifies, or passes data.
/. Phat is an active transformation?
9n active transformation is the one which changes the number of rows that pass
through it.
Ixample <ilter transformation
'. Phat is a passive transformation?
9 passive transformation is the one which does not change the number of rows that
pass through it.
Ixample Ixpression transformation
0. Phat is a connected transformation?
9 connected transformation is connected to the data flow or connected to the other
transformations in the mapping pipeline.
Ixample sorter transformation
1. Phat is an unconnected transformation?
9n unconnected transformation is not connected to other transformations in the
mapping. 9n unconnected transformation is called within another transformation and
returns a value to that transformation.
Ixample ,nconnected loo4up transformation, unconnected stored procedure
transformation
>. Phat are multi8group transformations?
Transformations having multiple input and output groups are called multi8group
transformations.
Ixamples +ustom, HTT2, Aoiner, Bouter, ,nion, ,nstructured &ata, H-@ source
qualifier, H-@ Target definition, H-@ parser, H-@ generator
K. @ist out all the transformations which use cache?
9ggregator, Aoiner, @oo4up, Ban4, .orter
L. Phat is bloc4ing transformation?
Transformation which bloc4s the input rows are called bloc4ing transformation.
Ixample +ustom transformation, unsorted 3oiner
F. Phat is a reusable transformation?
9 reusable transformation is the one which can be used in multiple mappings.
Beusable transformation is created in transformation developer.
1=. How do you promote a non8reusable transformation to reusable transformation?
Idit the transformation and chec4 the -a4e Beusable option
11. How to create a non8reusable instance of reusable transformations?
In the navigator, select an existing transformation and drag the transformation into the
mapping wor4space. Hold down the +trl 4ey before you release the transformation.
1/. Phich transformation can be created only as reusable transformation but not as
non8reusable transformation?
Ixternal procedure transformation.
1. Phat is a union transformation?
9 union transformation is used merge data from multiple sources similar to the ,7I%7
9@@ .?@ statement to combine the results from two or more .?@ statements.
/. 9s union transformation gives ,7I%7 9@@ output, how you will get the ,7I%7
output?
2ass the output of union transformation to a sorter transformation. In the properties of
sorter transformation chec4 the option select distinct. 9lternatively you can pass the
output of union transformation to aggregator transformation and in the aggregator
transformation specify all ports as group by ports.
'. Phat are the guidelines to be followed while using union transformation?
The following rules and guidelines need to be ta4en care while wor4ing with union
transformation
(ou can create multiple input groups, but only one output group.
9ll input groups and the output group must have matching ports. The precision,
datatype, and scale must be identical across all groups.
The ,nion transformation does not remove duplicate rows. To remove duplicate
rows, you must add another transformation such as a Bouter or <ilter
transformation.
(ou cannot use a .equence )enerator or ,pdate .trategy transformation upstream
from a ,nion transformation.
The ,nion transformation does not generate transactions.
0. Phy union transformation is an active transformation?
,nion is an active transformation because it combines two or more data streams into
one. Though the total number of rows passing into the ,nion is the same as the total
number of rows passing out of it, and the sequence of rows from any given input
stream is preserved in the output, the positions of the rows are not preserved, i.e. row
number 1 from input stream 1 might not be row number 1 in the output stream. ,nion
does not even guarantee that the output is repeatable
1. Phat is a transaction control transformation?
9 transaction is a set of rows bound by a commit or rollbac4 of rows. The transaction
control transformation is used to commit or rollbac4 a group of rows.
/. Phat is the commit type if you have a transaction control transformation in the
mapping?
The commit type is Juser8definedJ.
'. Phat are the different transaction levels available in transaction control
transformation?
The following are the transaction levels or built8in variables
T+"+%7TI7,I"TB97.9+TI%7 The Integration .ervice does not perform
any transaction change for this row. This is the default value of the expression.
T+"+%--IT":I<%BI The Integration .ervice commits the transaction,
begins a new transaction, and writes the current row to the target. The current row
is in the new transaction.
T+"+%--IT"9<TIB The Integration .ervice writes the current row to the
target, commits the transaction, and begins a new transaction. The current row is
in the committed transaction.
T+"B%@@:9+Q":I<%BI The Integration .ervice rolls bac4 the current
transaction, begins a new transaction, and writes the current row to the target. The
current row is in the new transaction.
T+"B%@@:9+Q"9<TIB The Integration .ervice writes the current row to the
target, rolls bac4 the transaction, and begins a new transaction. The current row is
in the rolled bac4 transaction.
1. Phat is a sorter transformation?
.orter transformation is used to sort the data. (ou can sort the data either in
ascending or descending order according to a specified sort 4ey.
/. Phy sorter is an active transformation?
9s sorter transformation can suppress the duplicate records in the source, it is
called an active transformation.
'. How to improve the performance of a session using sorter transformation?
.ort the data using sorter transformation before passing in to aggregator or 3oiner
transformation. 9s the data is sorted, the integration service uses the memory to
do aggregate and 3oin operations and does not use cache files to process the data.
1. Phat is an expression transformation?
9n expression transformation is used to calculate values in a single row.
Ixample salary$1===
2. How to generate sequence numbers using expression transformation?
+reate a variable port in expression transformation and increment it by
one for every row. 9ssign this variable port to an output port.
3. +onsider the following employees data as source?
employee"id, salary
1=, 1===
/=, /===
'=, '===
0=, 1===
Q1. &esign a mapping to load the cumulative sum of salaries of
employees into target table?
The target table data should loo4 li4e as
employee"id, salary, cumulative"sum
1=, 1===, 1===
/=, /===, '===
'=, '===, >===
0=, 1===, 11===
Q2. &esign a mapping to get the pervious row salary for the current row.
If there is no pervious row exists for the current row, then the pervious
row salary should be displayed as null.
The output should loo4 li4e as
employee"id, salary, pre"row"salary
1=, 1===, 7ull
/=, /===, 1===
'=, '===, /===
0=, 1===, '===
. +onsider the following employees table as source
department"no, employee"name
/=, B
1=, 9
1=, &
/=, 2
1=, :
1=, +
/=, ?
/=, .
Q1. &esign a mapping to load a target table with the following values
from the above source?
department"no, employee"list
1=, 9
1=, 9,:
1=, 9,:,+
1=, 9,:,+,&
/=, 9,:,+,&,2
/=, 9,:,+,&,2,?
/=, 9,:,+,&,2,?,B
/=, 9,:,+,&,2,?,B,.
Q2. &esign a mapping to load a target table with the following values
from the above source?
department"no, employee"list
1=, 9
1=, 9,:
1=, 9,:,+
1=, 9,:,+,&
/=, 2
/=, 2,?
/=, 2,?,B
/=, 2,?,B,.
1. +onsider the following employees data as source
employee"id, salary
1=, 1===
/=, /===
'=, '===
0=, 1===
Q1. &esign a mapping to load the cumulative sum of salaries of
employees into target table?
The target table data should loo4 li4e as
employee"id, salary, cumulative"sum
1=, 1===, 1===
/=, /===, '===
'=, '===, >===
0=, 1===, 11===
Solution:
+onnect the source ?ualifier to expression transformation. In the
expression transformation, create a variable port !"cum"sal and in the
expression editor write !"cum"sal$salary. +reate an output port
%"cum"sal and assign !"cum"sal to it.
Q2. &esign a mapping to get the pervious row salary for the current row.
If there is no pervious row exists for the current row, then the pervious
row salary should be displayed as null.
The output should loo4 li4e as
employee"id, salary, pre"row"salary
1=, 1===, 7ull
/=, /===, 1===
'=, '===, /===
0=, 1===, '===
Solution:
+onnect the source ?ualifier to expression transformation. In the
expression transformation, create a variable port !"count and increment it
by one for each row entering the expression transformation. 9lso
create !"salary variable port and assign the expression
II<5!"count#1,7,@@,!"prev"salary6 to it . Then create one more
variable port !"prev"salary and assign .alary to it. 7ow create output
port %"prev"salary and assign !"salary to it. +onnect the expression
transformation to the target ports.
In the expression transformation, the ports will be
employee"id
salary
!"count#!"count$1
!"salary#II<5!"count#1,7,@@,!"prev"salary6
!"prev"salary#salary
%"prev"salary#!"salary
Q3. &esign a mapping to get the next row salary for the current row. If
there is no next row for the current row, then the next row salary should be
displayed as null.
The output should loo4 li4e as
employee"id, salary, next"row"salary
1=, 1===, /===
/=, /===, '===
'=, '===, 1===
0=, 1===, 7ull
Solution:
Step1: +onnect the source qualifier to two expression transformation. In
each expression transformation, create a variable port !"count and in the
expression editor write !"count$1. 7ow create an output port %"count in
each expression transformation. In the first expression transformation,
assign !"count to %"count. In the second expression transformation
assign !"count81 to %"count.
In the first expression transformation, the ports will be
employee"id
salary
!"count#!"count$1
%"count#!"count
In the second expression transformation, the ports will be
employee"id
salary
!"count#!"count$1
%"count#!"count81
Step2: +onnect both the expression transformations to 3oiner
transformation and 3oin them on the port %"count. +onsider the first
expression transformation as -aster and second one as detail. In the 3oiner
specify the 3oin type as &etail %uter Aoin. In the 3oiner transformation
chec4 the property sorted input, then only you can connect both expression
transformations to 3oiner transformation.
Step3: 2ass the output of 3oiner transformation to a target table. <rom the
3oiner, connect the employee"id, salary which are obtained from the first
expression transformation to the employee"id, salary ports in target table.
Then from the 3oiner, connect the salary which is obtained from the second
expression transformaiton to the next"row"salary port in the target table.
Q. &esign a mapping to find the sum of salaries of all employees and this
sum should repeat for all the rows.
The output should loo4 li4e as
employee"id, salary, salary"sum
1=, 1===, 11===
/=, /===, 11===
'=, '===, 11===
0=, 1===, 11===
Solution:
Step1: +onnect the source qualifier to the expression transformation. In
the expression transformation, create a dummy port and assign value 1 to
it.
In the expression transformation, the ports will be
employee"id
salary
%"dummy#1
Step2: 2ass the output of expression transformation to aggregator. +reate
a new port %"sum"salary and in the expression editor write .,-5salary6.
&o not specify group by on any port.
In the aggregator transformation, the ports will be
salary
%"dummy
%"sum"salary#.,-5salary6
Step3: 2ass the output of expression transformation, aggregator
transformation to 3oiner transformation and 3oin on the &,--( port. In
the 3oiner transformation chec4 the property sorted input, then only you
can connect both expression and aggregator to 3oiner transformation.
Step: 2ass the output of 3oiner to the target table.
2. +onsider the following employees table as source
department"no, employee"name
/=, B
1=, 9
1=, &
/=, 2
1=, :
1=, +
/=, ?
/=, .
Q1. &esign a mapping to load a target table with the following values
from the above source?
department"no, employee"list
1=, 9
1=, 9,:
1=, 9,:,+
1=, 9,:,+,&
/=, 9,:,+,&,2
/=, 9,:,+,&,2,?
/=, 9,:,+,&,2,?,B
/=, 9,:,+,&,2,?,B,.
Solution:
Step1: ,se a sorter transformation and sort the data using the sort 4ey as
department"no and then pass the output to the expression transformation.
In the expression transformation, the ports will be
department"no
employee"name
!"employee"list #
II<5I.7,@@5!"employee"list6,employee"name,!"employee"listCCD,DCC
employee"name6
%"employee"list # !"employee"list
Step2: 7ow connect the expression transformation to a target table.
Q2. &esign a mapping to load a target table with the following values
from the above source?
department"no, employee"list
1=, 9
1=, 9,:
1=, 9,:,+
1=, 9,:,+,&
/=, 2
/=, 2,?
/=, 2,?,B
/=, 2,?,B,.
Solution:
Step1: ,se a sorter transformation and sort the data using the sort 4ey as
department"no and then pass the output to the expression transformation.
In the expression transformation, the ports will be
department"no
employee"name
!"curr"deptno#department"no
!"employee"list # II<5!"curr"deptnoE #
!"prev"deptno,employee"name,!"employee"listCCD,DCCemployee"name6
!"prev"deptno#department"no
%"employee"list # !"employee"list
Step2: 7ow connect the expression transformation to a target table.
Q3. &esign a mapping to load a target table with the following values
from the above source?
department"no, employee"names
1=, 9,:,+,&
/=, 2,?,B,.
Solution:
The first step is same as the above problem. 2ass the output of expression
to an aggregator transformation and specify the group by as
department"no. 7ow connect the aggregator transformation to a target
table.
#######################################################
#######################################################
#######################################################
#######################################################
#######################################################
1. Phat is .?@ transformation?
.?@ transformation process .?@ queries midstream in a pipeline and you can insert,
update, delete and retrieve rows from a database.

/. How do you configure a .?@ transformation?
The following options are required to configure .?@ transformation
-ode .pecifies the mode in which .?@ transformation runs. .?@ transformation
supports two modes. They are script mode and query mode.
&atabase type The type of database that .?@ transformation connects to.
+onnection type 2ass database connection to the .?@ transformation at run time
or specify a connection ob3ect.

'. Phat are the different modes in which a .?@ transformation runs?
.?@ transformation runs in two modes. They are
.cript mode The .?@ transformation runs scripts that are externally located. (ou
can pass a script name to the transformation with each input row. The .?@
transformation outputs one row for each input row.
?uery mode The .?@ transformation executes a query that you define in a query
editor. (ou can pass parameters to the query to define dynamic queries. (ou can
output multiple rows when the query has a .I@I+T statement.

0. In which cases the .?@ transformation becomes a passive transformation and active
transformation?
If you run the .?@ transformation in script mode, then it becomes passive
transformation. If you run the .?@ transformation in the query mode and the query has a
.I@I+T statement, then it becomes an active transformation.

1. Phen you configure an .?@ transformation to run in script mode, what are the ports
that the designer adds to the .?@ transformation?
The designer adds the following ports to the .?@ transformation in script mode
.cript7ame This is an input port. .cript7ame receives the name of the script to
execute the current row.
.criptBesult This is an output port. .criptBesult returns 29..I& if the script
execution succeeds for the row. %therwise it returns <9I@I&.
.criptIrror This is an output port. .criptIrror returns the errors that occur when a
script fails for a row.


>. Phat are the types of .?@ queries you can specify in the .?@ transformation when
you use it in query mode.
.tatic .?@ query The query statement does not change, but you can use query
parameters to change the data. The integration service prepares the query once
and runs the query for all input rows.
&ynamic .?@ query The query statement can be changed. The integration
service prepares a query for each input row.

K. Phat are the types of connections to connect the .?@ transformation to the database
available?
.tatic connection +onfigure the connection ob3ect tin the session. (ou must first
create the connection ob3ect in wor4flow manager.
@ogical connection 2ass a connection name to the .?@ transformation as input
data at run time. (ou must first create the connection ob3ect in wor4flow manager.
<ull database connection 2ass the connect string, user name, password and other
connection information to .?@ transformation input ports at run time.

L. How do you find the number of rows inserted, updated or deleted in a table?
(ou can enable the 7umBows9ffected output port to return the number of rows affected
by the I7.IBT, ,2&9TI or &I@ITI query statements in each input row. This
7umBows9ffected option wor4s in query mode.

F. Phat will be the output of 7umBows9ffected port for a .I@I+T statement?
The 7umBows9ffected outout is Gero for the .I@I+T statement.

1=. Phen you enable the 7umBows9ffected output port in script mode, what will be the
output?
In script mode, the 7umBows9ffected port always returns 7,@@.

11. How do you limit the number of rows returned by the select statement?
(ou can limit the number of rows by configuring the -ax %utput Bow +ount
property. To configure unlimited output rows, set -ax %utput Bow +ount to
Gero.
#######################################################
#######################################################
#######################################################
#######################################################
#######################################################
1. Phat is a loo4up transformation?
9 loo4up transformation is used to loo4 up data in a flat file, relational table, view, and
synonym.
/. Phat are the tas4s of a loo4up transformation?
The loo4up transformation is used to perform the following tas4s?
)et a related value Betrieve a value from the loo4up table based on a value in the
source.
2erform a calculation Betrieve a value from a loo4up table and use it in a
calculation.
,pdate slowly changing dimension tables &etermine whether rows exist in a
target.
'. How do you configure a loo4up transformation?
+onfigure the loo4up transformation to perform the following types of loo4ups
Belational or flat file loo4up
2ipeline loo4up
+onnected or unconnected loo4up
+ached or uncached loo4up
0. Phat is a pipeline loo4up transformation?
9 pipeline loo4up transformation is used to perform loo4up on application sources such
as A-., -.-? or .92. 9 pipeline loo4up transformation has a source qualifier as the
loo4ups source.
1. Phat is connected and unconnected loo4up transformation?
9 connected loo4up transformation is connected the transformations in the
mapping pipeline. It receives source data, performs a loo4up and returns data to
the pipeline.
9n unconnected loo4up transformation is not connected to the other
transformations in the mapping pipeline. 9 transformation in the pipeline calls the
unconnected loo4up with a @Q2 expression.
>. Phat are the differences between connected and unconnected loo4up transformation?
+onnected loo4up transformation receives input values directly from the pipeline.
,nconnected loo4up transformation receives input values from the result of a
@Q2 expression in another transformation.
+onnected loo4up transformation can be configured as dynamic or static cache.
,nconnected loo4up transformation can be configured only as static cache.
+onnected loo4up transformation can return multiple columns from the same row
or insert into the dynamic loo4up cache. ,nconnected loo4up transformation can
return one column from each row.
If there is no match for the loo4up condition, connected loo4up transformation
returns default value for all output ports. If you configure dynamic caching, the
Integration .ervice inserts rows into the cache or leaves it unchanged. If there is
no match for the loo4up condition, the unconnected loo4up transformation returns
null.
In a connected loo4up transformation, the cache includes the loo4up source
columns in the loo4up condition and the loo4up source columns that are output
ports. In an unconnected loo4up transformation, the cache includes all
loo4upOoutput ports in the loo4up condition and the loo4upOreturn port.
+onnected loo4up transformation passes multiple output values to another
transformation. ,nconnected loo4up transformation passes one output value to
another transformation.
+onnected loo4up transformation supports user8defined values. ,nconnected
loo4up transformation does not support user8defined default values.
K. How do you handle multiple matches in loo4up transformation? or what is J@oo4up
2olicy on -ultiple -atchJ?
J@oo4up 2olicy on -ultiple -atchJ option is used to determine which rows that the
loo4up transformation returns when it finds multiple rows that match the loo4up
condition. (ou can select loo4up to return first or last row or any matching row or to
report an error.
L. Phat is J%utput %ld !alue on ,pdateJ?
This option is used when dynamic cache is enabled. Phen this option is enabled, the
integration service outputs old values out of the loo4upOoutput ports. Phen the
Integration .ervice updates a row in the cache, it outputs the value that existed in the
loo4up cache before it updated the row based on the input data. Phen the Integration
.ervice inserts a new row in the cache, it outputs null values. Phen you disable this
property, the Integration .ervice outputs the same values out of the loo4upOoutput and
inputOoutput ports.
F. Phat is JInsert Ilse ,pdateJ and J,pdate Ilse InsertJ?
These options are used when dynamic cache is enabled.
Insert Ilse ,pdate option applies to rows entering the loo4up transformation with
the row type of insert. Phen this option is enabled the integration service inserts
new rows in the cache and updates existing rows when disabled, the Integration
.ervice does not update existing rows.
,pdate Ilse Insert option applies to rows entering the loo4up transformation with
the row type of update. Phen this option is enabled, the Integration .ervice
updates existing rows, and inserts a new row if it is new. Phen disabled, the
Integration .ervice does not insert new rows.
1=. Phat are the options available to configure a loo4up cache?
The following options can be used to configure a loo4up cache
2ersistent cache
Becache from loo4up source
.tatic cache
&ynamic cache
.hared +ache
2re8build loo4up cache
11. Phat is a cached loo4up transformation and uncached loo4up transformation?
+ached loo4up transformation The Integration .ervice builds a cache in memory
when it processes the first row of data in a cached @oo4up transformation. The
Integration .ervice stores condition values in the index cache and output values in
the data cache. The Integration .ervice queries the cache for each row that enters
the transformation.
,ncached loo4up transformation <or each row that enters the loo4up
transformation, the Integration .ervice queries the loo4up source and returns a
value. The integration service does not build a cache.
1/. How the integration service builds the caches for connected loo4up transformation?
The Integration .ervice builds the loo4up caches for connected loo4up transformation in
the following ways
.equential cache The Integration .ervice builds loo4up caches sequentially. The
Integration .ervice builds the cache in memory when it processes the first row of
the data in a cached loo4up transformation.
+oncurrent caches The Integration .ervice builds loo4up caches concurrently. It
does not need to wait for data to reach the @oo4up transformation.
1'. How the integration service builds the caches for unconnected loo4up transformation?
The Integration .ervice builds caches for unconnected @oo4up transformations as
sequentially.
10. Phat is a dynamic cache?
The dynamic cache represents the data in the target. The Integration .ervice builds the
cache when it processes the first loo4up request. It queries the cache based on the loo4up
condition for each row that passes into the transformation. The Integration .ervice
updates the loo4up cache as it passes rows to the target. The integration service either
inserts the row in the cache or updates the row in the cache or ma4es no change to the
cache.
11. Phen you use a dynamic cache, do you need to associate each loo4up port with the
input port?
(es. (ou need to associate each loo4upOoutput port with the inputOoutput port or a
sequence I&. The Integration .ervice uses the data in the associated port to insert or
update rows in the loo4up cache.
1>. Phat are the different values returned by 7ew@oo4upBow port?
The different values are
= 8 Integration .ervice does not update or insert the row in the cache.
1 8 Integration .ervice inserts the row into the cache.
/ 8 Integration .ervice updates the row in the cache.
1K. Phat is a persistent cache?
If the loo4up source does not change between session runs, then you can improve the
performance by creating a persistent cache for the source. Phen a session runs for the
first time, the integration service creates the cache files and saves them to dis4 instead of
deleting them. The next time when the session runs, the integration service builds the
memory from the cache file.
1L. Phat is a shared cache?
(ou can configure multiple @oo4up transformations in a mapping to share a single
loo4up cache. The Integration .ervice builds the cache when it processes the first @oo4up
transformation. It uses the same cache to perform loo4ups for subsequent @oo4up
transformations that share the cache.
1F. Phat is unnamed cache and named cache?
,nnamed cache Phen @oo4up transformations in a mapping have compatible
caching structures, the Integration .ervice shares the cache by default. (ou can
only share static unnamed caches.
7amed cache ,se a persistent named cache when you want to share a cache file
across mappings or share a dynamic and a static cache. The caching structures
must match or be compatible with a named cache. (ou can share static and
dynamic named caches.
/=. How do you improve the performance of loo4up transformation?
+reate an index on the columns used in the loo4up condition
2lace conditions with equality operator first
+ache small loo4up tables.
Aoin tables in the database If the source and the loo4up table are in the same
database, 3oin the tables in the database rather than using a loo4up transformation.
,se persistent cache for static loo4ups.
9void %B&IB :( on all columns in the loo4up source. .pecify explicitly the
%B&IB :y clause on the required columns.
<or flat file loo4ups, provide .orted files as loo4up source.
1. Phat is an update strategy transformation?
,pdate strategy transformation is used to flag source rows for insert, update, delete or
re3ect within a mapping. :ased on this flagging each row will be either inserted or
updated or deleted from the target. 9lternatively the row can be re3ected.
/. Phy update strategy is an active transformation?
9s update strategy transformation can re3ect rows, it is called as an active transformation.
'. Phat are the constants used in update strategy transformation for flagging the rows?
&&"I7.IBT is used for inserting the rows. The numeric value is =.
&&",2&9TI is used for updating the rows. The numeric value is 1.
&&"&I@ITI is used for deleting the rows. The numeric value is /.
&&"BIAI+T is used for re3ecting the rows. The numeric value is '.
0. If you place an aggregator after the update strategy transformation, how the output of
aggregator will be affected?
The update strategy transformation flags the rows for insert, update and delete of re3ect
before you perform aggregate calculation. How you flag a particular row determines how
the aggregator transformation treats any values in that row used in the calculation. <or
example, if you flag a row for delete and then later use the row to calculate the sum, the
integration service subtracts the value appearing in this row. If the row had been flagged
for insert, the integration service would add its value to the sum.
1. How to update the target table without using update strategy transformation?
In the session properties, there is an option DTreat .ource Bows 9sD. ,sing this option you
can specify whether all the source rows need to be inserted, updated or deleted.
>. If you have an update strategy transformation in the mapping, what should be the value
selected for DTreat .ource Bows 9sD option in session properties?
The value selected for the option is D&ata &rivenD. The integration service follows the
instructions coded in the update strategy transformation.
K. If you have an update strategy transformation in the mapping and you did not selected
the value D&ata &rivenD for DTreat .ource Bows 9sD option in session, then how the session
will behave?
If you do not choose &ata &riven when a mapping contains an ,pdate .trategy or
+ustom transformation, the Por4flow -anager displays a warning. Phen you run the
session, the Integration .ervice does not follow instructions in the ,pdate .trategy
transformation in the mapping to determine how to flag rows.
L. In which files the data re3ected by update strategy transformation will be written?
If the update strategy transformation is configured to <orward Be3ected Bows then the
integration service forwards the re3ected rows to next transformation and writes them to
the session re3ect file. If you do not select the forward re3ect rows option, the integration
service drops re3ected rows and writes them to the session log file. If you enable row
error handling, the Integration .ervice writes the re3ected rows and the dropped rows to
the row error logs. It does not generate a re3ect file.
1. Phat is a stored procedure?
9 stored procedure is a precompiled collection of database procedural statements. .tored
procedures are stored and run within the database.
/. )ive some examples where a stored procedure is used?
The stored procedure can be used to do the following tas4s
+hec4 the status of a target database before loading data into it.
&etermine if enough space exists in a database.
2erform a specialiGed calculation.
&rop and recreate indexes.
'. Phat is a connected stored procedure transformation?
The stored procedure transformation is connected to the other transformations in the
mapping pipeline.
0. In which scenarios a connected stored procedure transformation is used?
Bun a stored procedure every time a row passes through the mapping.
2ass parameters to the stored procedure and receive multiple output parameters.
1. Phat is an unconnected stored procedure transformation?
The stored procedure transformation is not connected directly to the flow of the mapping.
It either runs before or after the session or is called by an expression in another
transformation in the mapping.
>. In which scenarios an unconnected stored procedure transformation is used?
Bun a stored procedure before or after a session
Bun a stored procedure once during a mapping, such as pre or post8session.
Bun a stored procedure based on data that passes through the mapping, such as
when a specific port does not contain a null value.
Bun nested stored procedures.
+all multiple times within a mapping.
K. Phat are the options available to specify when the stored procedure transformation
needs to be run?
The following options describe when the stored procedure transformation runs
7ormal The stored procedure runs where the transformation exists in the
mapping on a row8by8row basis. This is useful for calling the stored procedure for
each row of data that passes through the mapping, such as running a calculation
against an input port. +onnected stored procedures run only in normal mode.
2re8load of the .ource :efore the session retrieves data from the source, the
stored procedure runs. This is useful for verifying the existence of tables or
performing 3oins of data in a temporary table.
2ost8load of the .ource 9fter the session retrieves data from the source, the
stored procedure runs. This is useful for removing temporary tables.
2re8load of the Target :efore the session sends data to the target, the stored
procedure runs. This is useful for verifying target tables or dis4 space on the target
system.
2ost8load of the Target 9fter the session sends data to the target, the stored
procedure runs. This is useful for re8creating indexes on the database.
9 connected stored procedure transformation runs only in 7ormal mode. 9 unconnected
stored procedure transformation runs in all the above modes.
L. Phat is execution order in stored procedure transformation?
The order in which the Integration .ervice calls the stored procedure used in the
transformation, relative to any other stored procedures in the same mapping. %nly used
when the .tored 2rocedure Type is set to anything except 7ormal and more than one
stored procedure exists.
F. Phat is 2B%+"BI.,@T in stored procedure transformation?
2B%+"BI.,@T is a system variable, where the output of an unconnected stored
procedure transformation is assigned by default.
1=. Phat are the parameter types in a stored procedure?
There are three types of parameters exist in a stored procedure
I7 Input passed to the stored procedure
%,T %utput returned from the stored procedure
I7%,T &efines the parameter as both input and output. %nly %racle supports
this parameter type.
1. Phat is a source qualifier transformation?
9 source qualifier represents the rows that the integration service reads when it runs a
session. .ource qualifier is an active transformation.
/. Phy you need a source qualifier transformation?
The source qualifier transformation converts the source data types into informatica native
data types.
'. Phat are the different tas4s a source qualifier can do?
Aoin two or more tables originating from the same source 5homogeneous sources6
database.
<ilter the rows.
.ort the data
.electing distinct values from the source
+reate custom query
.pecify a pre8sql and post8sql
0. Phat is the default 3oin in source qualifier transformation?
The source qualifier transformation 3oins the tables based on the primary 4ey8foreign 4ey
relationship.
1. How to create a custom 3oin in source qualifier transformation?
Phen there is no primary 4ey8foreign 4ey relationship between the tables, you can
specify a custom 3oin using the Duser8defined 3oinD option in the properties tab of source
qualifier.
>. How to 3oin heterogeneous sources and flat files?
,se 3oiner transformation to 3oin heterogeneous sources and flat files
K. How do you configure a source qualifier transformation?
.?@ ?uery
,ser8&efined Aoin
.ource <ilter
7umber of .orted 2orts
.elect &istinct
2re8.?@
2ost8.?@
1. Phat is a sequence generator transformation?
9 .equence generator transformation generates numeric values. .equence generator
transformation is a passive transformation.
/. Phat is the use of a sequence generator transformation?
9 sequence generator is used to create unique primary 4ey values, replace missing
primary 4ey values or cycle through a sequential range of numbers.
'. Phat are the ports in sequence generator transformation?
9 sequence generator contains two output ports. They are +,BB!9@ and 7IHT!9@.
0. Phat is the maximum number of sequence that a sequence generator can generate?
The maximum value is F,//','K/,='>,L10,KK1,L=K
1. Phen you connect both the 7IHT!9@ and +,BB!9@ ports to a target, what will be
the output values of these ports?
The output values are
7IHT!9@ +,BB!9@
1 /
/ '
' 0
0 1
1 >
>. Phat will be the output value, if you connect only +,BB!9@ to the target without
connecting 7IHT!9@?
The integration service passes a constant value for each row.
K. Phat will be the value of +,BB!9@ in a sequence generator transformation?
+,BB!9@ is the sum of J7IHT!9@J and JIncrement :yJ !alue.
L. Phat is the number of cached values set to default for a sequence generator
transformation?
<or non8reusable sequence generators, the number of cached values is set to Gero.
<or reusable sequence generators, the number of cached values is set to 1===.
F. How do you configure a sequence generator transformation?
The following properties need to be configured for a sequence generator transformation
.tart !alue
Increment :y
Ind !alue
+urrent !alue
+ycle
7umber of +ached !alues
1. Phat is a router transformation?
9 router is used to filter the rows in a mapping. ,nli4e filter transformation, you can
specify one or more conditions in a router transformation. Bouter is an active
transformation.
/. How to improve the performance of a session using router transformation?
,se router transformation in a mapping instead of creating multiple filter transformations
to perform the same tas4. The router transformation is more efficient in this case. Phen
you use a router transformation in a mapping, the integration service processes the
incoming data only once. Phen you use multiple filter transformations, the integration
service processes the incoming data for each transformation.
'. Phat are the different groups in router transformation?
The router transformation has the following types of groups
Input
%utput
0. How many types of output groups are there?
There are two types of output groups
,ser8defined group
&efault group
1. Phere you specify the filter conditions in the router transformation?
(ou can creat the group filter conditions in the groups tab using the expression editor.
>. +an you connect ports of two output groups from router transformation to a single
target?
7o. (ou cannot connect more than one output group to one target or a single input group
transformation.
1. Phat is ran4 transformation?
9 ran4 transformation is used to select top or bottom ran4 of data. This
means, it selects the largest or smallest numeric value in a port or group.
Ban4 transformation also selects the strings at the top or bottom of a
session sort order. Ban4 transformation is an active transformation.
/. Phat is ran4 cache?
The integration service compares input rows in the data cache, if the input
row out8ran4s a cached row, the integration service replaces the cached
row with the input row. If you configure the ran4 transformation to ran4
across multiple groups, the integration service ran4s incrementally for
each group it finds. The integration service stores group information in
index cache and row data in data cache.
'. Phat is B97QI7&IH port?
The designer creates B97QI7&IH port for each ran4 transformation. The
integration service uses the ran4 index port to store the ran4ing position
for each row in a group.
0. How do you specify the number of rows you want to ran4 in a ran4
transformation?
In the ran4 transformation properties, there is an option D7umber of Ban4sD
for specifying the number of rows you wants to ran4.
1. How to select either top or bottom ran4ing for a column?
In the ran4 transformation properties, there is an option DTopO:ottomD for
selecting the top or bottom ran4ing for a column.
>. +an we specify ran4ing on more than one port?
7o. Pe can specify to ran4 the data based on only one port. In the ports
tab, you have to chec4 the B option for designating the port as a ran4 port
and this option can be chec4ed only on one port.
1. Phat is normaliGer transformation?
The normaliGer transformation receives a row that contains multiple8occurring columns
and retruns a row for each instance of the multiple8occurring data. This means it converts
column data in to row data. 7ormaliGer is an active transformation.
/. Phich transformation is required to process the cobol sources?
.ince the cobol sources contain denormalGed data, normaliGer transformation is used to
normaliGe the cobol sources.
'. Phat is generated 4ey and generated column id in a normaliGer transformation?
The integration service increments the generated 4ey sequence number each time
it process a source row. Phen the source row contains a multiple8occurring
column or a multiple8occurring group of columns, the normaliGer transformation
returns a row for each occurrence. Iach row contains the same generated 4ey
value.
The normaliGer transformation has a generated column I& 5)+I&6 port for each
multiple8occurring column. The )+I& is an index for the instance of the multiple8
occurring data. <or example, if a column occurs ' times in a source record, the
normaliGer returns a value of 1,/ or ' in the generated column I&.
0. Phat is !.9-?
!.9- 5!irtual .torage 9ccess -ethod6 is a file access method for an I:- mainframe
operating system. !.9- organiGe records in indexed or sequential flat files.
1. Phat is !.9- normaliGer transformation?
The !.9- normaliGer transformation is the source qualifier transformation for a
+%:%@ source definition. 9 +%:%@ source is flat file that can contain multiple8
occurring data and multiple types of records in the same file.
>. Phat is pipeline normaliGer transformation?
2ipeline normaliGer transformation processes multiple8occurring data from relational
tables or flat files.
K. Phat is occurs clause and redefines clause in normaliGer transformation?
%ccurs clause is specified when the source row has a multiple8occurring columns.
9 redefines clause is specified when the source has rows of multiple columns.
1. Phat is a 3oiner transformation?
9 3oiner transformation 3oins two heterogeneous sources. (ou can also 3oin the data from
the same source. The 3oiner transformation 3oins sources with at least one matching
column. The 3oiner uses a condition that matches one or more 3oins of columns between
the two sources.
/. How many 3oiner transformations are required to 3oin n sources?
To 3oin n sources n81 3oiner transformations are required.
'. Phat are the limitations of 3oiner transformation?
(ou cannot use a 3oiner transformation when input pipeline contains an update
strategy transformation.
(ou cannot use a 3oiner if you connect a sequence generator transformation
directly before the 3oiner.
0. Phat are the different types of 3oins?
7ormal 3oin In a normal 3oin, the integration service discards all the rows from
the master and detail source that do not match the 3oin condition.
-aster outer 3oin 9 master outer 3oin 4eeps all the rows of data from the detail
source and the matching rows from the master source. It discards the unmatched
rows from the master source.
&etail outer 3oin 9 detail outer 3oin 4eeps all the rows of data from the master
source and the matching rows from the detail source. It discards the unmatched
rows from the detail source.
<ull outer 3oin 9 full outer 3oin 4eeps all rows of data from both the master and
detail rows.
1. Phat is 3oiner cache?
Phen the integration service processes a 3oiner transformation, it reads the rows from
master source and builds the index and data cached. Then the integration service reads the
detail source and performs the 3oin. In case of sorted 3oiner, the integration service reads
both sources 5master and detail6 concurrently and builds the cache based on the master
rows.
>. How to improve the performance of 3oiner transformation?
Aoin sorted data whenever possible.
<or an unsorted Aoiner transformation, designate the source with fewer rows as
the master source.
<or a sorted Aoiner transformation, designate the source with fewer duplicate 4ey
values as the master source.
K. Phy 3oiner is a bloc4ing transformation?
Phen the integration service processes an unsorted 3oiner transformation, it reads all
master rows before it reads the detail rows. To ensure it reads all master rows before the
detail rows, the integration service bloc4s all the details source while it caches rows from
the master source. 9s it bloc4s the detail source, the unsorted 3oiner is called a bloc4ing
transformation.
L. Phat are the settings used to configure the 3oiner transformation
-aster and detail source
Type of 3oin
Aoin condition
1. Phat is a filter transformation?
9 filter transformation is used to filter out the rows in mapping. The filter
transformation allows the rows that meet the filter condition to pass through and
drops the rows that do not meet the condition. <ilter transformation is an active
transformation.
/. +an we specify more than one filter condition in a filter transformation?
Pe can only specify one condition in the filter transformation. To specify more
than one condition, we have to use router transformation?
'. In which case a filter transformation acts as passive transformation?
If the filter condition is set to TB,I, then it passes all the rows without filtering
any data. In this case, the filter transformation acts as passive transformation.
0. +an we concatenate ports from more than one transformation into the filter
transformation?
7o. The input ports for the filter must come from a single transformation.
1. How to filter the null values and spaces?
,se the I.7,@@ and I.".29+I. functions
Ixample II<5I.7,@@5commission6,<9@.I,TB,I6
>. How session performance can be improved by using filter transformation?
Qeep the filter transformation as close as possible to the sources in the mapping.
This allows the unwanted data to be discarded and the integration service
processes only the required rows. If the source is relational source, use the source
qualifier to filter the rows.
1. Phat is aggregator transformation?
9ggregator transformation performs aggregate calculations li4e sum, average, count etc.
It is an active transformation, changes the number of rows in the pipeline. ,nli4e
expression transformation 5performs calculations on a row8by8row basis6, an aggregator
transformation performs calculations on group of rows.
/. Phat is aggregate cache?
The integration service creates index and data cache in memory to process the aggregator
transformation and stores the data group in index cache, row data in data cache. If the
integration service requires more space, it stores the overflow values in cache files.
'. How can we improve performance of aggregate transformation?
,se sorted input .ort the data before passing into aggregator. The integration
service uses memory to process the aggregator transformation and it does not use
cache memory.
<ilter the unwanted data before aggregating.
@imit the number of inputOoutput or output ports to reduce the amount of data the
aggregator transformation stores in the data cache.
0. Phat are the different types of aggregate functions?
The different types of aggregate functions are listed below
9!)
+%,7T
<IB.T
@9.T
-9H
-I&I97
-I7
2IB+I7TI@I
.T&&I!
.,-
!9BI97+I
1. Phy cannot you use both single level and nested aggregate functions in a single
aggregate transformation?
The nested aggregate function returns only one output row, whereas the single level
aggregate function returns more than one row. .ince the number of rows returned are
not same, you cannot use both single level and nested aggregate functions in the same
transformation. If you include both the single level and nested functions in the same
aggregator, the designer mar4s the mapping or mapplet as invalid. .o, you need to
create separate aggregator transformations.
>. ,p to how many levels, you can nest the aggregate functions?
Pe can nest up to two levels only.
Ixample -9H5 .,-5 ITI- 6 6
K. Phat is incremental aggregation?
The integration service performs aggregate calculations and then stores the data in
historical cache. 7ext time when you run the session, the integration service reads
only new data and uses the historical cache to perform new aggregation calculations
incrementally.
L. Phy cannot we use sorted input option for incremental aggregation?
In incremental aggregation, the aggregate calculations are stored in historical cache
on the server. In this historical cache the data need not be in sorted order. If you give
sorted input, the records come as presorted for that particular run but in the historical
cache the data may not be in the sorted order. That is why this option is not allowed.
F. How the 7,@@ values are handled in 9ggregator?
(ou can configure the integration service to treat null values in aggregator functions
as 7,@@ or Gero. :y default the integration service treats null values as 7,@@ in
aggregate functions.