Field Mapping
Configure the field mapping in a Hierarchy Builder transformation to define which relational elements link to schema elements to provide the hierarchical output. Configure the field mapping on the Field Mapping tab of the Properties panel. If you have more than one group, use the field mapping editor to define primary and foreign keys. You also use the field mapping editor to link relational fields to schema elements.
The following image shows the field mapping editor.
To the left, the field mapping editor shows the relational fields. To the right, the editor shows the schema elements. In this example, the transformation has two relational groups so you must designate primary and foreign keys. In this example, the CompanyName field is a primary key in the NewSource group. The Name field is the primary key for the NewSource1 group and the CompanyName field is the foreign key to the NewSource group.
A primary key is signified by the primary key icon in the Key column. A foreign key is signified by the foreign key icon.
To link a relational field to schema element, drag the relational element to the schema element. The Mapped Field column shows the relational field to which the schema element is mapped.
If the input relational fields constitute just one group, the data will be treated as denormalized input and there is no need to define primary or foreign keys.
Selecting a Primary or Foreign Key
Select a relational field to designate as a primary or foreign key.
1. In the Properties panel of the Hierarchy Builder transformation, click the Field Mapping tab.
The field mapping editor displays the transformation input elements and output fields. To the left, the editor shows the relational fields. To the right, the editor shows the schema elements.
2. To search for a relational field, enter the characters to search for in the Search field, and click Search.
3. To designate a relational field as a primary or foreign key, click the Key column of the field.
A key selector window appears.
4. Select whether the key is a primary key or foreign key.
5. If the key is foreign key, select the group to which the field relates as a foreign key.
6. To remove a primary or foreign key, click the Key column of the field and select Not a key.
7. To clear all the keys, click the action menu at the top of the Hierarchy Fields panel, and then select Clear Keys.
Selecting the Fields to Map
Link relational fields to schema elements to define how to convert the relational input fields to hierarchical output elements.
1. In the Properties panel of the Hierarchy Builder transformation, click the Field Mapping tab.
The field mapping editor displays the transformation input elements and output fields. To the right, the editor shows the schema elements. To the left, the editor shows the relational input fields.
2. To search for a relational field, enter the characters to search for in the Search field, and click Search.
3. To view child schema elements, expand a field.
4. To search for an element, enter the characters to search for in the Search field, and click Search.
5. To link a relational input field to a schema element, drag the relational field to the schema element.
6. To automatically link a relational input field to a schema element, select a schema element, click the action menu at the top of the Hierarchy Fields panel, and then select Map Selected.
7. To automatically unlink a relational input field to a schema element, select one of the following options:
- - Select a schema element. In the Action column, click the delete icon.
- - Select a schema element. Click the action menu at the top of the Hierarchy Fields panel and select Unmap Selected.
8. To clear all the links, click the action menu at the top of the Hierarchy Fields panel, and then select Clear Mapping.
