Many data warehousing applications make use of a "surrogate key" instead of using the keys from an operational system like an alphanumeric employee id or an integer employee number.
A surrogate key is usually an incrementing integer with no intrinsic meaning. One reason applications use surrogate keys is to improve the performance of JOINs.
You can build a data flow to load a table that has surrogate keys by using a self-referential transformation to generate the values for the surrogate key field.
In the following example, DMSALE, a sales table, is the data source, and the extracted records are loaded into a table named DMREPS. Values for the surrogate key REPNO are generated when the data flow is run.
Refer to the sample data flow DMSURRG for the complete example.
In the DMC:
The Column Selection window opens.
The erties dialog box opens.
The Transformations window opens.
The Transformation Calculator 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:
Note that REPNO is incremented by one for each record and that FNAME and LNAME are transformed correctly.
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. 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, select Logs and then Last Log. The following image shows the log of the most recently run procedure.
iWay Software |