Chapter 10 Building Data Transformation Diagrams

Inserting a data lookup

A data lookup lets you find the corresponding value to a key column and store it into a new column of the outgoing data flow. The mapping between the key column and its value can be defined from database tables or using a predefined map of values.

Database mode

You need to:

• Select the Database mode in the General tab. This triggers the display of the Script tab.
• Select the source column corresponding to the value to replace in the General tab.
• Select the target column corresponding to the resulting value in the General tab.

Then, you have to select a data connection from the Data Connection list and define the SQL query that provides the replacing value in the Script tab.

Predefined mode

You need to:

• Select the Predefined mode in the General tab. This triggers the display of the Map Values tab.
• Select the source column corresponding to the value to replace in the General tab.
• Select the target column corresponding to the resulting value in the General tab.

Then, you have to define a map of values (value/label) in the Map Values tab. Each time the source column will equal to the value defined in the map, it will be replaced with the corresponding label.

You can modify the data structure columns of a data lookup.

To insert a data lookup in Database mode:

1. Select the Data Lookup tool in the palette, and create the corresponding object in the diagram.
2. Select the Data Flow tool, and draw a flow from the preceding step (a data input or action) to the data lookup to initialize it with the appropriate columns.
3. Double-click the data lookup symbol to open its property sheet. The Data Structure Columns tab is automatically completed with the values from the incoming flow.
4. Select the Database mode in the Mode list. This triggers the display of the Script tab.
5. Select the source column corresponding to the value to replace in the Source Column list and select the target column, which will contain the resulting value in the Target Column. The target column automatically displays in the Data Structure Columns tab and the source column is removed.
6. Click the Script tab and select a data connection from the Data Connection list to get access to the database.
7. Specify a SQL query in the textbox. The query will be executed on the database tables and will return two columns (a key column to search for a corresponding value and a value column to store the corresponding value).
   
8. Click OK to save your changes and return to the diagram.

To insert a data lookup in Predefined mode:

1. Select the Data Lookup tool in the palette, and create the corresponding object in the diagram.
2. Select the Data Flow tool, and draw a flow from the preceding step (a data input or action) to the data lookup to initialize it with the appropriate columns.
3. Double-click the data lookup symbol to open its property sheet. The Data Structure Columns tab is automatically completed with the values from the incoming flow.
4. Select the Predefined mode in the Mode list. This triggers the display of the Map Values tab.
5. Select the source column on which to execute the map of values in the Source Column list and create the target column, which will contain the resulting values in the Target Column. The target column automatically displays in the Data Structure Columns tab and the source column is removed.
6. Click the Map Values tab, click the Value column to specify a value, and click the Label column to specify the corresponding label. Each time the source column will equal to the value of the map, it will be replaced with the corresponding label.
   
7. Click OK to save your changes and return to the diagram.

Copyright (C) 2007. Sybase Inc. All rights reserved.