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

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 click Properties. For this exercise, you will accept the default Load Type, Insert/Update, as shown in the following image.

    Target Properties

    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, click Target Transformations. The Transformations window opens.
  5. Click the Automap Automap Button button.

    The five columns with identical names and data types are mapped and moved to the Expression tab. Note that the mapped columns are also selected in green in the Target Columns list, as shown in the following image.

  6. Two target column names are deselected, 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 equal 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, as shown in the following image.


  8. To open the Transformations Calculator, double-click PROFIT in the Expressions tab . To create the expression in the expression box of the calculator:
    1. In the tree, double-click LINEPRICE.
    2. On the calculator pad, click the minus sign (-.
    3. In the Source Columns list, double-click LINECOGS.

    The Transformation Calculator should look like the following image:

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

    The Transformation window now looks like the following image:


  10. Scroll to the right to see the last column. It may be useful to validate the incoming data. To do that, load only those records with a quantity greater than or equal to ten. Records that do not 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, and then select GE from the Relation drop-down menu. Input 10 into the Value field. Click OK. Your goal is to see which rows pass the validation test.
  12. Now, let us 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, as shown in the following image.

    Test transformations

    Note: The number of records retrieved will depend on the Run Options set from Tools and Options, and on 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: Since you have been working in the Transformations window, testing from there is the simplest method, but you can also test the transformation by right-clicking the Target object, selecting Toggle, 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. Close the Test Transformation window, and click OK to close the Transformations window.

Top of page

Create a Data Target

DataMigrator can load multiple data targets in a single data flow. Let us 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, select Add target, and then click New. A new data target appears to the right of the SQL object.
  2. Right-click the new target and click 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. In this case, it was MS SQL Server.
    2. Change the Synonym salesdemo.
    3. Name the synonym for the new data target linerpts.
    4. Retain the default Load Type of Insert/Update.

      The target properties window should look like the following image:

      Target properties

    5. Close the panel.
  4. Right-click the new data target and click Target Transformations.
  5. In the table you are creating, PROD_NUM, STORE_CODE, and YEARMONTH are all keys. Click each one to identify them, and those above it, as keys. The Transformations window looks like the following image:


  6. Click OK to return to the Data Flow tab, which now contains two data sources and two data targets, as shown in the following image.

    Data flow

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

Save and Name the Data Flow

Click the Save button in the Quick Access Toolbar.

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 it salesdb.

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

iWay Software