Define the fields to be normalized on the Normalized Fields tab. You can also include other incoming fields that you want to use in the mapping.
When you define normalized fields, you can create fields manually or select fields from a list of incoming fields. You can also create field groups when the source data contains multiple-occuring groups of fields. Group-level fields can contain fields and other groups.
The following table describes the properties that you configure for normalized fields:
Property
Description
Name
Field or group name.
In advanced mode or SQL ELT mode, this property is always the field name.
Level
Field or group level. Fields in the same group occur beneath a field with a lower level number. When each field is the same level, the transformation contains no groups.
In advanced mode or SQL ELT mode, the value is always 1.
Occurs
Number of instances of the field or group in the source row.
Type
The field datatype. The datatype can be either String or Number. In advanced mode, you can use any primitive data type.
Doesn't apply to group-level fields.
Precision
Total number of digits in a number. For example, the number 123.45 has a precision of 5.The precision must be greater than or equal to 1.
Doesn't apply to group-level fields.
Scale
Number of digits to the right of the decimal point of a number. For example, the number 123.45 has a scale of 2. Scale must be greater than or equal to 0. The scale of a number must be less than its precision. The scale may cause zero-filling or truncation with rounding. For example, a scale of 5 causes 123.45 to be expressed as "123.45000" while 123.456789 becomes "123.45679".
Doesn't apply to group-level fields.
In advanced mode, the Normalizer transformation produces multiple output groups. Otherwise, the Normalizer transformation produces only one output group.
When incoming fields include multiple-occurring fields without a corresponding category field, you can create a field to define the occurs for the data. For example, to represent three fields with different types of income, you can create an Income field and set the occurs value to 3.
Occurs configuration
Configure the occurs value for a normalized field to define the number of instances the field or group of fields occurs in incoming data.
To define a multiple-occurring field or group, set the occurs value to an integer greater than one. When you set an occurs value to greater than one, the Normalizer transformation creates a generated column ID field for the field or group-level field. The Normalizer transformation also creates a generated key field for all normalized data.
The Normalizer transformation also uses the occurs value to create a corresponding set of output fields. The output fields display on the Field Mapping tab of the Normalizer transformation. The naming convention for the output fields is <occurs field name>_<occurs number>.
To define a single-occurring field, set the occurs value for the field to one. Define a single-occurring field to include incoming fields that do not need to be normalized in the normalized fields list.
Field groups
You can configure field groups when you define the fields to be normalized. A field group defines a group of multiple-occuring fields in the source. The Normalizer transformation returns a row for each group occurrence instead of for each field occurrence.
Configure field groups when the source contains groups of multiple-occuring fields.
When a group of fields occurs multiple times in the source row, you can add child groups to configure the data hierarchy. The Normalizer transformation returns a row for each child group occurance multiplied by the parent group occurs value.
For example, a parent group that occurs four times contains a child group that occurs three times. The child group contains two fields that each occur once. The Normalizer transformation returns each field 12 times.
The following image shows the Normalized Fields tab with parent and child groups:
The field level identifies the hierarchy of fields. Fields in the same group have the same level number and display sequentially below the group-level field. You can define up to ten levels in a group.
Note: You can't configure field groups in advanced mode or SQL ELT mode.
Configuring field groups
Configure field groups when the incoming data contains groups of multiple-occuring fields or multiple-occuring groups of fields.
1To add a group, click Add and select Add Group.
2In the New Group dialog, enter the group name and set the occurs value, and then click OK.
3To add a child group, in the row that contains the parent group, click Actions > Create a New Group.
4In the New Group dialog, enter the group name and set the occurs value, and then click OK.
5 To add a field to a group, in the row that contains the group, perform one of the following actions:
- Click Actions > Create a New Field.
- Click Actions > Select From Incoming Fields.
6For newly created fields, enter the field metadata and set occurs value in the New Field dialog, and then click OK.
You can also edit the metadata and occurs value for fields created from incoming fields.
Unmatched groups of multiple-occurring fields
You can normalize more than one group of multiple-occurring fields in a Normalizer transformation. When you include more than one group and the occurs values do not match, configure the mapping to avoid validation errors.
Use one of the following methods to process groups of multiple-occurring fields with different occurs values.
Write the normalized data to different targets
You can use multiple-occurring fields with different occurs values when you write the normalized data to different targets.
For example, the source data includes an Expenses field with four occurs and an Income field with three occurs. You can configure the mapping to write the normalized expense data to one target and to write the normalized income data to a different target.
Use the same occurs value for multiple occurring fields
You can configure the multiple-occurring fields to use the same number of occurs, and then use the generated fields that you need. When you use the same number of occurs for multiple-occurring fields, you can write the normalized data to the same target.
For example, when the source data includes an Expenses field with four occurs and an Income field with three occurs, you can configure both fields to have four occurs.
When you configure the Normalizer field mappings, you can connect the four expense fields and the three income fields, leaving the unnecessary income output field unused. Then, you can configure the mapping to write all normalized data to the same target.
Generated keys
The Normalizer transformation generates key values for normalized data.
Generated keys fields appear on the Normalized Fields tab when you configure the field to have more than one occurrence.
The mapping task generates the following fields for normalized data.
Generated Key
A key value that the task generates each time it processes an incoming row. When a task runs, it starts the generated key with one and increments by one for each processed row.
The Normalizer transformation uses one generated key field for all data to be normalized.
The naming convention for the Normalizer generated key is GK_<redefined_field_name>.
Note: The generated key is not applicable in advanced mode.
Generated Column ID
A column ID value that represents the instance of the multiple-occurring data. For example, if an Expenses field that includes four occurs, the task uses values 1 through 4 to represent each type of occurring data.
The Normalizer transformation uses a generated column ID for each field configured to occur more than one time.
The naming convention for the Normalizer generated key is GCID_<redefined_field_name>.
An advanced cluster processes the generated column ID field as a bigint. The Data Integration Server processes the ID as an integer.
