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_ltredefined_field_namegt
• 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_ltoccuring_field_namegt

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
20
(No Transcript)
21
(No Transcript)
22
(No Transcript)
23

• Thank You !!!