The Load Manager Transformation Engine

It can happen that there are differences between the column structure of a table in the source schema and its equivalent in the target schema. By default, the Load Manager rule assumes that the structure of the tables in the source and target schemas are identical. In other words, it is assumed that the columns in both tables have the same name and also have the same datatype. If this is not the case, the data being transferred must be adjusted during the copy process otherwise errors will be generated.

The transformation engine is a tool in the Load Manager rule which can manipulate the column information as necessary. The transformation engine modifies the data stream as it is read from the source table and inserted into the target table. It can remove data columns, add data items and perform function based modifications such as TO_STRING or TO_DATE on the existing values.

The following page discusses the components and usage of the transformation engine for a table. There is also a useful tutorial on the subject located here: [Using the Transformation Engine Tutorial (local) (internet)]

Activating the Transformation Engine

The Transformation Engine Button on the Load Manager Rule Form

Each table included in the load process can have a transformation associated with it. The transformation engine for that table is activated by pressing the elipsis button (...) in the Transformation Engine column on the Edit Load Manager Rule form. The screen shot above displays part of the Edit Load Manager rule form and shows the transformation engine button for each table. Note that tables which have no transformation operations specified for them have a value of Default in the transformation engine column, while those which have data modifications applied will change to a value of Custom.

Configuring the Transformation Engine

The Transformation Engine Form for a Table

Once the transformation engine for a table has been activated, the Transformation Engine form will display - as shown in the screens shot above. The Transformation engine contains two columns. The column on the left represents the columns in the target table and the column on the right contains the names of the columns in the source table. It is assumed, by default, that these columns are identical in name and datatype. If the column structure of the table is not identical, then the data may need to be transformed during the load process. The following types of transformation are possible

A column is present in the source table but not in the target.

No transformation action is required, the data in the source column will be ignored during the transformation.

A column in the target table has a different name from that in the source table.

By default the load manager will assume the column name in the source table is identical to that in the target table. If the column in the source table has a different name then just type that name into the box in the right hand column (the source column) of the top panel on the Transformation Engine form. For example, if a column is called EMP_NO in the target and EMP_ID in the source then just drop the mouse cursor on the text EMP_NO in the right hand column and manually change it to a value of EMP_ID.

A column is present in the target table but not in the source.

If the target column is permitted to contain null values then it can simply be deleted from the transformation engine. The NULL value will be inserted by default during the load process. To delete a column select it in both sides of the top panel and press the Delete button. This will cause the data contained in the source table to be ignored during the load process. If the column is NOT NULL then it must be filled with values. In this case a suitable constant value can be entered by dropping the mouse on the column name in the source side of the top panel and entering a value. In the example above a NULL value is inserted into the target POSTCODE_ID column for every row loaded into the target table.

The column has a different datatype in the target than in the source table.

In this case the data must be transformed by entering in suitable SQL Code. The code which should be entered is the clause in a select statement which would perform the transformation task. For example, assume there is a table named INVOICE and it contains a column named INVOICE_NO. If the INVOICE_NO column datatype in the source table is VARCHAR2 and the datatype in the target is a NUMBER then a TO_NUMBER operation must be performed on the data as it is loaded. The text for the INVOICE_NO column could be edited on the source side to be TO_NUMBER(INVOICE_NO). This would transform the data appropriately. Functionally a statement similar to the the one shown below is being executed during the load process. Any SQL transformation clause that would work in the pseudocode statement below would be permitted.

INSERT INTO TARGET.INVOICE (col1, col1, INVOICE_NO) select col1, col2, TO_NUMBER(INVOICE_NO) from SOURCE.INVOICE;

Important Note: The transformation engine for a table can be reset to the default value by pressing the Set to Defaults button on the Transformation Engine form. In particular, this button will restore any columns in the target table which were removed from the load process and make the assumption that the source table structure is identical to that of the source. It is important to be aware that the restored target column information will simply be that which is currently known to the Rule Controller. Pressing the Set to Defaults button does not cause the Transformation Engine form to contact the target schema and refresh the table structure - it only retrieves what the structure the Rule Controller thinks is in the target schema. If a full refresh of the table structure is required, then use the tools on the Edit Rule Controller form Options tab to refresh the table structure in the Rule Controller first.