Adding Data Targets

In this section:

You are going to add two data targets and specify the options you want to use when loading data into them.

The newly created target will be updated by other flows.


Top of page

x
Map Columns in an Existing Data Target

To specify the first data target:

  1. From the navigation pane, drag the synonym dmrpts from the ibisamp directory into the workspace, to the right of the SQL object. (The position to the right of the SQL object makes it a data target.)
  2. Once you have added the data target to the data flow, you can specify how incoming data should be handled during the loading process.

    Right-click the dmrpts target object and select Properties. For this exercise, you will accept the default Load Type, which is Insert/Update.

    Properties Window

    Tip: If you intend to run a flow more than once, it is advisable to clear the target before starting the run. This technique generally enhances performance in subsequent runs. In the Prior to Load Option in the Target Properties window, select the clearing option that suits the adapter type being used for your data targets. For example, for a relational table the option is Truncate table; for a FOCUS/FDS or XFOCUS table the option is Drop table.

  3. Click X to close the properties window.

    The next step is to map the data source columns you are extracting to the data target columns.

  4. Right-click dmrpts and this time select Target Transformations. The Transformations window opens.
  5. Click the Automap Automap Buttonbutton.

    The five columns with identical names and data types are mapped and moved to the Expression tab. Note that the mapped columns are also checked in green in the Target Columns list.

    Transformations Window

  6. Two target column names are unchecked so you need to create mappings or transformations for them. In the Target Columns list, YRMTH contains year and month data. You will map it to the YEARMONTH column in the Selected Columns list.
    1. Under Target Columns, click YRMTH.
    2. Under Selected Columns, click YEARMONTH.
    3. Click the = sign to move the mapping into the grid in the Expressions tab.
  7. For the PROFIT column you will build a transformation based on the definition of PROFIT as the difference between LINEPRICE and LINECOGS. As you can see, the data target is another place where we can apply transformations.

    Under Target Columns, double-click PROFIT to move it to the Target columns list in the Expressions tab.

    Transformations Window

  8. Double-click PROFIT in the Expressions tab to open the Transformations Calculator. To create the expression in the calculator's expression box:
    1. Double-click LINEPRICE in the tree.
    2. Click the minus sign (-) on the calculator pad.
    3. Double-click LINECOGS in the Source Columns list.

    The Transformation Calculator should look like this:

    Transformations Calculator

  9. Click OK to complete the expression and close the calculator.

    The Transformation window now looks like this:

    Transformations Window

  10. It is good practice to validate the incoming data. To do that, let's load only those records with a quantity greater than or equal to ten. Records that don't meet this validation criterion can be logged to a file for additional processing or for review at a later date.

    Click the Validates tab in the Transformation window, then click the Insert Transforms button to open the Transformation Calculator.

  11. To build the validation expression, double-click QUANTITY in the tree, then select GE from the Relation drop-down menu, followed by inputting 10 in the Value field. Click OK. Your goal is to see which rows pass the validation test.

    Validates Tab

  12. Now, let's test the validation and transformations to ensure that they are syntactically correct and performing the desired calculations. The test retrieves some rows from the server, applies the transformations, and displays the results.

    Click the Test Transforms button in the upper right corner, above the Validates tab.

    The result of the transformation test appears.

    Test Transforms Window

    Note: The number of records retrieved will depend on the Run Options set from Tools and Options and the database you choose for your sample data.

    In the Test1 column, the number 1 represents rows that will be accepted based on the validation test; the 0s represent rows that will be rejected because QUANTITY is less than 10.

    Tip: You have been working in the Transformations window so testing from there is the simplest method, but you can also test the transformation by double-clicking the Target object and clicking the Test Transforms tab.

  13. Notice that Profits are positive in the answer set, which means that the transformation is working. It also satisfies the test condition so you can move on to the next data target, which you will create from scratch.
  14. Click Close to close the Test Transformation window, and click OK to close the Transformations window.

Top of page

x
Create a Data Target

DataMigrator can load multiple data targets in a single data flow. Let's add another target object into the data flow. This time, the data target you need does not exist so you will create it using the columns in the SQL SELECT statement that you defined for the SQL Select Columns object.

This target table will be used by line managers for competitive analysis. You will create the base table here, but other flows can update it with additional information.

  1. Right-click in the workspace to the right of the SQL object and choose Add target, followed by New. A new data target appears to the right of the SQL object.
  2. Right-click the new target and select Properties.

    The Target Properties window opens.

  3. To define target properties:
    1. Choose the same type of Adapter you used for the data sources, in our example, MS SQL Server.
    2. Change the Synonym directory to salesdemo.
    3. Name the synonym for the new data target linerpts.
    4. Retain the default Load Type, Insert/Update.

      The target properties window should look like this:

      Properties Window

    5. Close the window.
  4. Now, right-click the new data target and select Target Transformations.
  5. In the table you are creating, PROD_NUM, STORE_CODE, and YEARMONTH are all keys. You can click each one or click YEARMONTH to identify it, and those above it, as keys. The Transformations window looks like this:

    Transformations Window

  6. Click OK to return to the Data Flow tab, which now contains two data sources and two data targets.

    Data Flow Example

Next Step: You are ready to save the data flow. Both data targets, dmrpts and linerpts, will be loaded based on your specifications.


Top of page

x
Save and Name the Data Flow

Click the Save button located above the workspace, or select File, and Save.

The Save Procedure As window opens. Select which application directory to save the flow to using the Save In drop-down menu, and enter a flow name in the Name field. For this example, we will save our flow in the salesdemo application directory and name the flow salesdb.

Next Step: You are ready to run the data flow.


iWay Software