Target Design

Append Utility Columns to All Tables

A utility field (also called a zField) does not exist as a field in the target system; rather, it is added in Target Design to support processing. Rules and reports can reference the field, but they cannot be registered to the field. An example of a utility field is a zLoaded flag that identifies whether that record has been loaded, or whether the record previously existed. For ad hoc problem solving, Designers may want to perform a calculation or derivation that is specific to the individual migration project. In these cases, add utility fields on the Target Fields page in Target Design, one-by-one as zFields.

When utility fields are visible in Map (through source or target rule mapping), details of how the field is populated are captured, and the build completion for rules can be tracked. Ad hoc utility fields must be added manually on a target-by-target basis. To control whether the field appears in field mapping, set the Visibility to Source, Target, Both or None. We recommend that the field is marked as either Source or Target, depending on where the rules will be written. In Map, document how those fields are populated. Refer to Allow Mapping of Utility Columns for more information.

If all target tables in a migration project require a utility field, rather than manually adding them to each table, use the Append Utility Columns page. These fields are appended across every wave, so only use this feature after careful consideration.

A Designer can add Utility columns that must be appended to all target and/or source tables.

NOTE: The zSource column is installed with the Stewardship Tier and cannot be deleted.

NOTE: If a user deletes an append column from the Append Utility Columns page, if it has already been synced with Map, the column still appears on the target table. To remove the append column in this case, set the column to inactive.

NOTE: If a user deletes a column from the Append Utility Columns page that has already been added to a target table using SQL AutoGen, that column must be manually deleted from the target table.

NOTE: For changes to Target Design to appear in Map, users must sync to Map.

To append columns to tables in Target Design:

  1. Select Configuration > Append Utility Columns in the Navigation pane.
  2. Click Add.

    View the field descriptions for the Append Utility Columns page

  3. Enter the name of the column to append to the table in the COLUMN field.
  4. Enter a user-friendly name in the DESCRIPTION field.

    NOTE: When the Append Utility columns are added to the target table, the description is added in the column Display Name.

  1. Enter a value in the PRIORITY field to indicate the order the field should be appended to the table.
  2. Enable or disable the ACTIVE check box as needed.

    :NOTE: The ACTIVE check box is enabled by default and indicates that the column will be appended to the target or source table as indicated.

  3. Enable the KEY check box if the column should be appended to the table as a key column.
  4. Select the column’s data type, such as NVARCHAR or DECIMAL, from the DATA TYPE list box.
  5. Enter the maximum number of characters that can be stored in the field in the LENGTH field.
  6. Enter the number of decimal places allowed in the field in the DECIMALS field.

    NOTE: If the data type for the column is DECIMAL, this field is required.

  7. Select a default value to use for the column in the DEFAULT VALUE list box.

    NOTE: Default values are added on the Utility Field Defaults page. Refer to Add Utility Field Defaults for more information.

  8. Enable or disable the TARGET APPEND check box as needed.

    NOTE: The TARGET APPEND check box is checked by default and indicates that the column will be appended to all target tables.

  9. Enable or disable the SOURCE APPEND check box as needed.

    :NOTE: The SOURCE APPEND check box is checked by default and indicates that the column will be appended to all source tables.

  10. Click Save.