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.
In the DMC:
The Column Selection window opens.
The erties 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')
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.
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:
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.
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.
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.
iWay Software |