ETL Scenarios

Scenario 1:
Convert single row from source to three rows in target: 
We have a source table containing 3 columns: Col1, Col2 and Col3. There is only 1 row in the table as follows:

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

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.

For details refer below video:

Scenario 2:

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


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 transformation 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.

For details look into below video: