NORMALIZER TRANSFORMATION

1. NORMALIZER TRANSFORMATION Basics and Advanced Concepts

2. Overview Normalization is the process of organizing data. In database terms, this includes creating normalized tables and establishing relationships between those tables according to rules designed to both protect the data and make the database more flexible by eliminating redundancy and inconsistent dependencies.

3. Overview You can use a single Normalizer transformation to handle multiple levels of denormalization in the same record. For example, a single record might contain two different detail record sets. Rather than using two Normalizer transformations to handle the two different detail record sets, you handle both normalizations in the same transformation.

4. Normalizer Ports When you create a Normalizer for a COBOL source, or in the mapping pipeline, the Designer identifies the OCCURS and REDEFINES statements and generates the following columns: Generated key. One port for each REDEFINES clause. Generated Column ID. One port for each OCCURS clause.

5. Generated Key The Designer generates a port for each REDEFINES clause to specify the generated key. You can use the generated key as a primary key column in the target table and to create a primary-foreign key relationship. The naming convention for the Normalizer generated key is: GK_<redefined_field_name> The Normalizer GK columns tell you the order of records in a REDEFINES clause. For example, if a COBOL file has 10 records, when you run the workflow, the PowerCenter Server numbers the first record 1, the second record 2, and so on.

6. Generated Column ID The Designer generates a port for each OCCURS clause to specify the positional index within an OCCURS clause. You can use the generated column ID to create a primary-foreign key relationship. The naming convention for the Normalizer generated column ID is: GCID_<occuring_field_name>

7. Adding a COBOL Source to a Mapping When you add a COBOL source to a mapping, the Mapping Designer inserts and configures a Normalizer transformation. The Normalizer transformation identifies the nested records within the COBOL source and displays them accordingly. To add a COBOL source to a mapping: In the Designer, create a new mapping or open an existing one. Click and drag an imported COBOL source definition into the mapping. If the Designer does not create a Normalizer transformation by default, manually create the Normalizer transformation. For example, when you add the COBOL source to a mapping, the Designer adds a Normalizer transformation and connects it to the COBOL source definition.

8. Add COBOL Source to a Mapping If you connect the ports directly from the Normalizer transformation to targets, you connect the records from Generated keys (HST_MTH), represented in the Normalizer transformation, to their own target definition, distinct from any other target that may appear in the mapping. Open the new Normalizer transformation. Select the Ports tab and review the ports in the Normalizer transformation.

9. Add COBOL Source to a Mapping Click the Normalizer tab to review the original organization of the COBOL source. This tab contains the same information as in the Columns tab of the source definition for this COBOL source. However, you cannot modify the field definitions in the Normalizer transformation. If you need to make modifications, open the source definition in the Source Analyzer.

10. Add COBOL Source to a Mapping Select the Properties tab and enter the following settings:

11. To add a COBOL source to a mapping Click OK. Connect the Normalizer transformation to the rest of the mapping. If you have denormalized data for which the Normalizer transformation has created key values, connect the ports representing the repeated data and the output port for the generated keys to a different portion of the data flow in the mapping. Ultimately, you may want to write these values to different targets.

12. To add a Normalizer transformation to a mapping In the Mapping Designer, choose Transformation-Create. Select Normalizer transformation. Enter a name for the Normalizer transformation. Click Create. The naming convention for Normalizer transformations is NRM_TransformationName. The Designer creates the Normalizer transformation. If your mapping contains a COBOL source, and you do not have the option set to automatically create a source qualifier, the Create Normalizer Transformation dialog box displays. For more information about this option, see �Using the Designer� in the Designer Guide.

13. Continuation�.. If the create Normalizer Transformation dialog box displays, select the Normalizer transformation type. Select the source for this transformation. Click OK. Open the new Normalizer transformation. Select the Normalizer tab and add new output ports. Add a port corresponding to each column in the source record that contains denormalized data. The new ports only allow the number or string data types. You can create only new ports in the Normalizer tab, not the Ports tab.

14. To add a Normalizer transformation to a mapping Using the level controls in the Normalizer transformation, identify which ports belong to the master and detail records. Adjust these ports so that the level setting for detail ports is higher than the level setting for the master record. For example, if ports from the master record are at level 1, the detail ports are at level 2. When you adjust the level setting for the first detail port, the Normalizer transformation creates a heading for the detail record. Enter the number of times detail records repeat within each master record.

15. To add a Normalizer transformation to a mapping After configuring the output ports, click Apply. The Normalizer transformation creates all the input and output ports needed to connect master and detail records to the rest of the mapping. In addition, the Normalizer transformation creates a generated key column for joining master and detail records. When you run a session, the PowerCenter Server generates unique IDs for these columns.

16. To add a Normalizer transformation to a mapping Select the Properties tab and enter the following settings:

17. To add a Normalizer transformation to a mapping Click OK. Connect the Normalizer transformation to the rest of the mapping. Choose Repository-Save.

18. COBOL SOURCE (VSAM)

19. RELATIONAL SOURCE

23. Thank You !!!