Generating a Surrogate Key Using Slowly Changing Dimensions

DataMigrator now includes support for automatically generating surrogate keys when used in a dimension table. This support is only available for relational databases and cannot be used with FOCUS or flat file targets.

For details about Slowly Changing Dimension support, see Target Properties Pane for Relational Targets.

Refer to the sample data flow DMSURSCD for the complete example.


Top of page

Example: Generating a Surrogate Key Using Slowly Changing Dimensions in a Data Flow

In the DMC:

  1. Click Tools, then Options, and select Column Management, as shown in following image.

    Column Management Options

  2. Check SCDType to display it on the column gird, and click OK.
  3. Right-click an application directory in the navigation pane and choose New, then Flow. The Data Flow tab opens in the right pane, with the SQL object displayed.
  4. Drag the data source object DMSALE from the ibisamp application directory in the navigation pane into the workspace, to the left of the SQL object.
  5. Right-click the SQL object and select Column Selection.

    The Column Selection window opens.

  6. Select EMPID, PLANT, and SALESREP in the Available Columns list and click the > arrow to add them to the Selected Columns list. Click OK.
  7. Drag the data target object DMREPS from the ibisamp application directory into the workspace, to the right of the SQL object.
  8. Right-click the target object DMREPS, and select Properties.

    The erties dialog box opens.

  9. If the DMSURRG flow has been run previously, clear the target using the Prior to Load Option by selecting Delete all rows from table or Truncate Table.
  10. To enable Slowly Changing Dimension support, for Load Options select Slowly Changing Dimensions, as shown in following image.

    Target Properties Window

  11. Right-click the target object and select Target Transformations.

    The Transformations window opens.

  12. Click the Automap button to map EMPID and PLANT. This moves them to the Expressions list.

    To map FNAME and LNAME, use substring to extract the necessary characters from the SALESREP source column of which the first 16 characters are first name and the next 34 characters are last name.

  13. Double-click FNAME and LNAME in the target columns list. This moves the columns to the Expressions list.
  14. Double-click the Transform Expression field for FNAME and type the following expression:
    SUBSTR (50, SALESREP, 1, 16, 16, 'A16')
  15. Double-click the Transform Expression field for LNAME and type the following expression:
    SUBSTR (50, SALESREP, 17, 50, 34, 'A34')

    Note: While the creation of a surrogate key does not require the use of the SUBSTR function it is a useful routine that can be used in a number of different situations.

  16. To map STATE, double-click STATE in the Target Columns list. This moves the column to the Expressions list.
  17. Double-click the Transform Expression field for STATE and type
    DECODE PLANT (BOS MA DAL TX LA CA ORL FL SEA WA STL MO)

    This converts a three character city name to its two character state code.

    When you have completed the transformations, the Transformations window should appear as shown in following image:

    Transformations Window

    The target table DMREPS has been created with slowly changing dimension attributes assigned for each of its columns. Since REPNO is a key column and the type is blank it becomes the surrogate key and no transformation is required. The values for ACTIVE is also automatically generated when the data flow is run.

  18. Test the transformations by clicking the Test transforms button. Click Close. The following image shows the Test Transformations window.

    Test Transformations Window

  19. Click OK to close the Transformations window.
  20. Save the data flow as DMXSURSCD.
  21. Click the Run button, and select Run from the drop-down menu.

When the data flow run is complete, verify that the target was loaded properly and that the surrogate key was generated by right-clicking the DMREPS object and selecting Toggle, and then Sample Data.

Note that the REPNO surrogate key values are assigned sequentially. However since PLANT and STATE are defined as SCD Type II, an employee is assigned a new surrogate key if the plant they are associated with changes. The ACTIVE flag is set to 1 for their current plant. The following image shows the DMREPS window with the Sample Data tab selected.

Sample Data

To confirm that the job ran correctly, click the View Last Log icon above the workspace or right-click the flow from the navigation pane and select Logs and then Last Log. The following image shows the log of the most recently run procedure.

Server Log Messages


iWay Software