- 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.
- Click Apply and then OK.
- Add link as shown in mapping below:
- Mapping -> Validate
- 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
- 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.