Using Lookups to Create Multi-Table Flows Without Joins

DataMigrator now allows you to build a single data flow that performs lookups into multiple tables using the DB_LOOKUP function without requiring explicit joins.

In the following example, DMORD is the primary source containing orders data, while DMCOMP and DMINV are the lookup sources. While DMORD contains store codes and product numbers, DMCOMP contains the store names for those store codes, and DMINV contains the product names for each product code.

Refer to the sample data flow DMLKUPS for the complete example.

For more information on improving lookup performance, see Improving Lookup Performance.


Top of page

Example: Creating a Data Flow Using Lookups

This example uses the DB_LOOKUP function to create two target transformations.

  1. In the DMC, right-click an application directory in the navigation pane, select New, and then click Flow. The Data Flow tab opens in the workspace, with the SQL object displayed.
  2. Drag the data source object DMORD from the ibisamp application directory in the navigation pane into the workspace, to the left of the SQL object.
  3. Double-click the SQL object to open the Column Selection window.
  4. Select the STORE_CODE and PROD_NUM columns by using Ctrl + click. Click the double arrows button to add them to the selected columns.
  5. Select Group By from the Aggregate drop-down menu.
  6. Select the QUANTITY and LINEPRICE columns by using Ctrl + click. Click the double arrows button to add them to the selected columns.
  7. Select Sum from the Aggregate drop-down menu.
  8. Click OK to close the Column Selection panel.
  9. Right-click the workspace to the right of the SQL object, select Add Target, and then click Existing.

    The Select Synonym window opens.

  10. Select the ibisamp directory from the Look drop-down menu.

    Select the dmstores synonym and click the Select button.

  11. Double-click the dmstores target object or right-click it and click Target Transformations.

    The Transformations window opens.

  12. Click the Automap button to map the identically named columns.
  13. Double-click STORENAME to add it to the transformation list, then click the calculator button.

    The Transformation Calculator opens.

  14. Click the Functions tab, expand the Data Source and Decoding folder, and double-click the DB_LOOKUP function.

    The prototype for DB_LOOKUP appears in the Expression window and the Lookup window opens.

  15. Select a synonym to use for the lookup by clicking the ellipsis Ellipses Button button after the Lookup Synonym field.

    The Select Synonym dialog box opens.

  16. Select the DMCOMP synonym and click Select.
  17. From the Lookup window, click the Add/Delete button.
  18. A lookup is similar to a join in that it uses columns from two different tables. Select STORE_CODE from the Source and Lookup Fields. Click the = button to add the lookup.

    When you have selected the columns, the Lookup window should appear, as shown in the following image.

    Lookup window

  19. Click OK to close the Lookup window.
  20. Click the ellipsis Ellipses Button button after Return Field.

    The Lookup Field window opens.

  21. Select STORENAME and click OK. Note that the column format is A20.

    The Lookup window opens, as shown in the following image.

    Lookup window

  22. Click OK.

    The expression is completed in the Transformation Calculator window.

    The Transformation Calculator should now appear, as shown in the following image.

    Transformation calculator

  23. Click OK.

    The STORENAME source transformation is added to the Transformations window.

  24. Repeat the process starting in step 4 to add another lookup, using the parameters listed and described in the following table:

    Parameter

    Column Name

    Lookup Synonym

    DMINV

    Source Column

    PROD_NUM

    Lookup Column

    PROD_NUM

    Return Value

    PRODNAME

  25. Click OK to close the Source Transformations window.
  26. Save the data flow as DMXLKUPS.
  27. On the Flow tab, in the Run group, click Run, and then select Run from the drop-down menu.

When the data flow run is complete, verify that the data was loaded properly by right-clicking the DMSTORES object, selecting Toggle, and clicking the Sample Data tab.

Note:


iWay Software