DataMigrator 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.
In the DMC:
The Column Selection window opens.
The Properties dialog box opens.
The Transformations window opens.
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.
SUBSTR (50, SALESREP, 1, 16, 16, 'A16')
Click OK.
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.
Click OK.
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 the following image:
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 are also automatically generated when the data flow is run.
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.
There are two ways to confirm that the job ran correctly:
On the Flow tab, in the Reports group, click View Last Log.
or
Right-click the flow from the navigation pane, select Logs, and then click Last Log.
iWay Software |