new informatica scenarios

INFORMATICA SCENARIOS

Convert single row from source to three rows in target

Scenario1:

We have a source table containing 3 columns : Col1, Col2 and Col3. There is only 1 row in the table as follows:

Col1	Col2	Col3
a	b	c

There is target table containg only 1 column Col. Design a mapping so that the target table contains 3 rows as follows:

Col

a

b

c

Without using normaliser transformation.

Solution:

Create 3 expression transformations exp_1,exp_2 and exp_3 with 1 port each. Connect col1 from Source Qualifier to port in exp_1.Connect col2 from Source Qualifier to port in exp_2.Connect col3 from source qualifier to port in exp_3. Make 3 instances of the target. Connect port from exp_1 to target_1. Connect port from exp_2 to target_2 and connect port from exp_3 to target_3.

Split the non-key columns to separate tables with key column in both

Scenario 2:

Split the non-key columns to separate tables with key column in both  / How to split the data of source table column-wise with respect to primary key. See the source and target tables below.

source table: ID is the key column, Name and Phone No are non-key columns

ID	Name	Phone No
10	AAA	123
20	BBB	234
30	CCC	434
40	DDD	343
50	EEE	442

Target Table 1

ID	Name
10	AAA
20	BBB
30	CCC
40	DDD
50	EEE

Target Table 2

ID	Phone No
10	123
20	234
30	434
40	343
50	442

Solution:

Step  1: Source qualifier: get the source table to the mapping area. See image below.

Step 2:  Drag all the port from (from the previous step) to the Aggregator transformation and group by the key column. Since we have to split the columns to two different tables with the key column in each, so we are going use two expression transformation, each will take the key column and one non-key column. Connect  aggregator transformation with each of the expression transformation as follows.

Step 3: We need another set of  aggregator to be associated with each of the expression tranformation from the previous step.

Step 4: In the final step connect the aggregators with the two target tables as follows.

Here is the iconic view of the entire mapping.

Separating duplicate and non-duplicate rows to separate tables

Scenario 3:

How to segregate the duplicate and distinct rows from source table to separate target tables?

source table:

COL1	COL2	COL3
a	b	c
x	y	z
a	b	c
r	f	u
a	b	c
v	f	r
v	f	r

Target Table 1: Table containing all the unique rows

COL1	COL2	COL3
a	b	c
x	y	z
r	f	u
v	f	r

Target Table 2: Table containing all the duplicate rows

COL1	COL2	COL3
a	b	c
a	b	c
v	f	r

Solution:

Step  1: Drag  the source to mapping and connect it to an aggregator transformation.

Step  2: In aggregator transformation, group by the key column and add a new port  call it count_rec to count  the key column.

Step  3: connect  a router to the  aggregator from the previous step.In router make two groups one named "original" and another as "duplicate"
In original write count_rec=1 and in duplicate write count_rec>1.

The picture below depicting group name and the filter conditions

Step 4: Connect two group to corresponding target table.

Retrieving first and last record from a table/file

Scenario 4:

How to get first and last record from a table/file?

Solution:

Step  1: Drag and drop ports from source qualifier to two rank transformations.

Step  2: Create a reusable sequence generator having start value 1 and connect the next value to both rank transformations.

Step  3: Set rank properties as follows

In Rank1

In Rank2

Step  4: Make two instances of the target.

Step  5: Connect the output port to target.

Sending first half record to target

Scenario 6: How to send first half record to target?

Solution:

Step:1 Drag and drop the source to mapping.

SRC TO TARGET MAPPING

Step:2 In source-Qualifier  , go to property and write the SQL query like

1.select * from emp where rownum <= ( select count(*)/2 fromemp)

SRC QUALIFIER SQL QUERY

Step:3 Then connect to target.

Now you are ready to run the mapping to see it in action.

Sending second half record to target

Scenario 8: How to send second  half record to target?

Solution 
Step 1: Drag and drop the source to mapping.

SRC TO TGT MAPPING

Step 2: In source-Qualifier  , go to propery and write the SQL query like

1.select * from emp minus select * from emp where rownum <= ( select count(*)/2 from emp))

.

SRC QUALIFIER SQL QUERY

Step:3 Then connect to target, and run mapping to see the results.

Sending alternate record to target

Scenario 9: How to send alternate record to target?
Or
Sending Odd numbered records to one target and even numbered records to another target.

Solution:

Step 1: Drag the source and connect to an expression transformation.
Step2: Add the next value of a sequence generator to expression transformation.                                                                                       

SCR TO SEQ MAPPING

Step 3: In expression transformation make two port, one is "odd" and another "even".
And Write the expression like below

EXPRESSION PROPERTY

Step 4: Connect a router transformation to expression.
Make two group in router.
And give condition  Like below

RTR PROPERTY

Step 5: Then send the two group to different targets.

The entire mapping is as below

FINAL MAPPING VIEW SCENARIO 9

Separate the original records in target

 Scenario 10: How to separate  the original records from source table to separate target table by using rank  transformation ?

Source Table

col1	col2	col3
a	b	c
x	y	z
a	b	c
r	f	u
a	b	c
v	f	r
v	f	r

Target Table

Col1	Col2	Col3
a	b	c
x	y	z
r	f	u
v	f	r

Solution: 
Step 1: Bring the source to mapping.

SRC TO RANK MAPPING

Step 2: Connect the rank to source.
Step 3: In rank, set the property like this.

RANK PROPERTY

Step 4: Then send it to target.
Run the session to see the result.

Get top 5 records to target without using rank

Scenario 12: How to get top 5 records to target without using rank ?

Solution:

1. Drag the source to mapping and connect it to sorter transformation.

2. Arrange the salary in descending order in sorter as follows and send the record to expression.

SORTER PROPERTIES

3. Add the next value of sequence generator to expression.(start the value from 1 in sequence generator).

SORTER TO EXP MAPPING

4. Connect the expression transformation to a filter or router. In the property set the condition as follows-

5. Finally connect to the target.

FINAL MAPPING SC12

Concatenation of duplicate value by comma separation

Scenario: You have two columns in source table T1, in which the col2 may contain duplicate values.All the duplicate values in col2 of  will be transformed as comma separated in the column col2  of target table T2.

Source Table: T1

Col1	Col2
a	x
b	y
c	z
a	m
b	n

Target Table: T2

col1	col2
a	x,m
b	y,n
c	z

Solution:

1. We have to use the following transformation as below.
   First connect a sorter transformation to source and make col1 as key and its order is ascending. After that connect it to an expression transformation.
   
2. In Expression make four new port and give them name as in picture below.
   
3. In concat_val write expression like as describe bellow and send it to an aggregator
   
4. In aggregator group it by col1 and send it to target
   
5. Finally run the session.

Target table rows , with each row as sum of all previous rows from source table.

Scenario: How to produce rows in target table with every row  as sum of all previous rows in source table ? See the source and target table to understand the scenario.

SOURCE TABLE
id	Sal
1	200
2	300
3	500
4	560

TARGET TABLE
Id	Sal
1	200
2	500
3	1000
4	1560

1. Pull the source to mapping and then connect it to expression.
   
2. In expression add one column and make it output(sal1) and sal port as input only.
   We will make use of a function named cume() to solve our problem, rather using any complex mapping.  Write the expression in sal1 as cume(sal) and send the output rows to target.

finally run the session...

Produce files as target with dynamic names

Scenario:How to generate file name dynamically  with name of sys date ?

Solution:

1. Drag your target file to target designer and add a column as show on the picture. It’s not a normal column .click on the ‘add file name to the table’ property. (I have given a red mark there)
2. Then drag your source to mapping area and connect it to an expression transformation.
   
3. In expression transformation add a new port as string data type and make it output port.
   
4. In that output port write the condition like describe as bellow and then map it in to filename port of target. Also send other ports to target. Finally run the session. You will find two file one with sys date and other one is ‘.out’ file which one you can delete.
5. 

Validating all mapping in repository

Scenario:How validate all mapping in repository ?

Solution:

1. In repository go to menu “tool” then “queries”. Query Browser dialog box will appear.Then click on new button.
   
   
2. In Query Editor,  choose folder name and object type as I have shown in the picture.
   
3. After that, execute it (by clicking the blue arrow button).
4. Query results window will appear. You select single mapping (by selecting single one) or whole mapping (by pressing Ctrl + A) and go to "tools" then "validate" option to validate it.
   

Using mapping parameter and variable in mapping

Scenario:How to use mapping parameter and variable in mapping ?

Solution:

1. Go to mapping then parameter and variable tab in the Informatica designer.Give name as $$v1, type choose parameter (You can also choose variable), data type as integer and give initial value as 20.
   
2. Create a mapping as shown in the figure( I have considered a simple scenario where a particular department id will be filtered to the target).
   
3. In filter set deptno=$$v1 (that means only dept no 20 record will go to the target.)
   
4. Mapping parameter value can’t change throughout the session but variable can be changed. We can change variable value by using text file. I’ll show it in next scenario.