•
Active and Connected Transformation.
•
The Normalizer transformation normalizes records
from COBOL and relational sources, allowing us to organize the data.
•
Use a Normalizer transformation instead of the
Source Qualifier transformation when we normalize a COBOL source.
•
We can also use the Normalizer transformation
with relational sources to create multiple rows from a single row of data.
Example 1:
•
To create 4 records of every employee in EMP
table.
•
EMP will be source table.
•
Create target table Normalizer_Multiple_Records.
Structure same as EMP and datatype of HIREDATE as VARCHAR2.
•
Create shortcuts as necessary.
Creating Mapping:
•
Open folder where we want to create the mapping.
•
Click Tools --> Mapping Designer.
•
Click Mapping--> Create--> Give name. Ex:
m_ Normalizer_Multiple_Records
•
Drag EMP and Target table.
•
Transformation-->Create-->Select
Expression--> Give name, click create, done.
•
Pass all ports from SQ_EMP to Expression
transformation.
•
Transformation--> Create--> Select
Normalizer--> Give name, create & done.
•
Try dragging ports from Expression to
Normalizer. Not Possible.
•
Edit Normalizer and Normalizer Tab. Add columns.
Columns equal to columns in EMP table and datatype also same.
•
Normalizer doesn’t have DATETIME datatype. So
convert HIREDATE to char in expression t/f. Create output port out_hdate and do
the conversion.
•
Connect ports from Expression to Normalizer.
•
Edit Normalizer and Normalizer Tab. As EMPNO
identifies source records and we want 4 records of every employee, give OCCUR
for EMPNO as 4.
14. Click
Apply and then OK.
15. Add
link as shown in mapping below:
16.
Mapping èValidate
17.
Repository èSave
• Make
session and workflow.
• Give
connection information for source and target table.
• Run
workflow and see result.
Example
2: To break rows into columns
Source:
Roll_Number
Name ENG HINDI MATHS
100 Amit
78 76 90
101 Rahul
76 78 87
102
Jessie 65 98 79
Target :
Roll_Number
Name Marks
100 Amit
78
100 Amit
76
100 Amit
90
101 Rahul
76
101 Rahul
78
101 Rahul
87
102
Jessie 65
102
Jessie 98
102
Jessie 79
• Make
source as a flat file. Import it and create target table.
• Create
Mapping as before. In Normalizer tab, create only 3 ports Roll_Number, Name and
Marks as there are 3 columns in target table.
• Also as
we have 3 marks in source, give Occurs as 3 for Marks in Normalizer tab.
• Connect
accordingly and connect to target.
•
Validate and Save
• Make
Session and workflow and Run it. Give Source File Directory and Source File
name for source flat file in source properties in mapping tab of session.
• See the
result.
No comments:
Post a Comment
Thank you :
- kareem