Add Update Row Sources

An Update Row Source can be either a view or a table.

Update Row Source Views

An Update Row Source can be a view in the Transform database that is used in Source rules that process against the data in the Transform Target Source table.

The benefits of using a view as an Update Row Source are:

  • Source rules process against current data from the sdb database
  • Processing times are shorter as the data is not imported into an st* table in the Transform staging database
  • Rules can only update the Add Row st* table

Add Update Row Sources that are Views

If data must originate from a Source other than the Add Row Source table, add the Update Row Source and then specify the Update Row Source connection to the Add Row Source table using relationship joins.

An Update Row Source must have a relationship with an Add Row Source. Refer to Add Relationship Joins to Sources for more information.

NOTE: By default, an Update Row Source is added as a view. An Update Row Source can also be a table. Refer to Add Update Row Sources that are Tables for more information.

If the view doesn’t exist in the database, an Update Row Source view is autogenerated using the table name entered in the Source Database Object field on the Target Sources (Update Row) page. The view name is stored in the ST Table field on the Target Sources page’s Vertical View.

NOTE: An Add Row Source can have as many Update Row Sources as needed but an Update Row Source cannot have an Update Row Source.

NOTE: The actions available on the Field Mappings page are limited for an Update Row Source to:

  • Copy
  • Not Used
  • Rule
  • Manual Rule

No other options display in the ACTION list box when an Update Row Source is selected.

NOTE: When mapping an Update Row Source on the Field Mappings page, the Target Relationship ID on the Vertical View of the Field Mappings page for the Update Row Source is required.

To add an Update Row Source that is a view in Map:

  1. Click ProcessArea in the Navigation pane.

  2. Click the Targets icon on the Process Area Launch page.

  3. Click the Sources icon for a Target.

  4. Click the Update Row Sources icon for a Source.

  5. Click Add on the Target Sources (Update Row) page.

    View the field descriptions for the Target Sources (Update Row) page

  6. Select the Source from the Source ID list box.

    NOTE: These are Sources that have been added to the Target in Target Design.

  7. Select the Source table from the Source DATABASE OBJECT list box.

  8. Click Save.

NOTE: The ACTIVE check box is enabled by default. If unchecked, the Source cannot be used in a relationship and therefore cannot be used as an Update Row Source.

After the Update Row Source has been added in Map, review the Source priority on the Target Sources page in Transform to ensure Update Row Sources process before the Add Row Source.

In Transform, the Update Row Source view is registered with a Documentation status.

Update Row Source Tables

An Update Row Source can also be a table. A snapshot of the data is copied from the Source database into a Transform staging database.

Rules can be created for this Update Row Source table which convert the data before it is processed by the main Source rule. These pre-processing rules are run prior to the rules run on the Add Row Source (as determined by the Target Source priority in Transform). In other words, after the pre-processing rules have run, the main Source rules run on the converted data in the Update Row Source table.

The benefits of using a table as an Update Row Source are:

  • Data is always copied from the sdb database into a Transform staging database
  • Snapshot data prevents deadlocks if other processing is currently using the sdb database
  • Processing results can be verified quickly by reviewing the local snapshot table
  • Rules can be processed to alter data before updating the Add Row Source

Add Update Row Sources that are Tables

Create an Update Row Source table for use by pre-processing rules or to create a snapshot of data for result validation.

The Update Row Source table is created using Map and SQL AutoGen. It then must be further configured in Transform.

To create an Update Row Source table in Map:

  1. Add an Update Row Source to an Add Row Source in Map by following the steps in Add Update Row Sources that are Views.

  2. Verify or add relationship joins if needed.

    NOTE: Relationship joins are required between Add Row and Update Row Source and are created automatically if the Add Row Source table has a primary key and the key also exists on the Update Row Source table. In this case, the left table in the join is the ST table of the Add Row Source and the right table is the Rule Source Table.
    If the Add Row Source does not have a primary key and/or the primary key columns on the Add Row Source do not exist on the Update Row Source database Object, the relationship setting the Rule Source Table to the ST Table for the Update Row Source is created, but the user must create the join manually. Refer to Add Relationship Joins to Sources for more information.

  3. After verifying or adding relationship joins, click Vertical View for the Update Row Source on the Target Sources (Update Row) page.

  4. Click Edit.

    View the field descriptions for the Target Sources (Update Row) page

  5. Select Table in the ST Object Type list box.

  6. Click Save.

  7. Click the back arrow to return to the Target Sources (Update Row) page’s Horizontal View.

  8. Click the Mappings icon for the Update Row Source.

  9. Select the field used in the relationship join and click Edit.

  10. Select Copy in the ACTION list box.

  11. Select the Source table in the Source TABLE field.

  12. Select the field name to copy the data to in the Source FIELD field.

  13. Click Save.

  14. Click Submit on the Page toolbar; Map creates the SQL Objects (depending on the Action selected) and sends a notification to the Developer assigned to the Target or Source to review the mapping on the Mapping Approval page. Refer to Approve or Reject Mappings for more information.

To create an Update Row Source table and rules In SQL Automation:

  1. Build Target Tables
  2. Build Source Tables
  3. Build an Update Row Source Table
  4. Create Rules for an Update Row Source

In Transform, the Update Row Source table is registered with an Active status.

Review the Source priority on the Target Sources page in Transform to ensure the Update Row Source processes before the Add Row Source.