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.

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, point to 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 dialog box.
  4. Select the Company,ID (STORE_CODE) and Poduct,Number (PROD_NUM) columns and click the double arrows button to add them to the Selected Columns section.
  5. Select Group By from the Aggregate drop-down menu for both columns.
  6. Select the QUANTITY and Line,Total (LINEPRICE) columns and click the double arrows button to add them to the Selected Columns.
  7. Select Sum from the Aggregate drop-down menu for these two columns.
  8. Click OK to close the Column Selection dialog box.
  9. Right-click the workspace to the right of the SQL object, point to Add Target, and then click Existing.

    The Select Synonym dialog box opens.

  10. Select the ibisamp directory.

    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 dialog box 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. When you have selected the columns, the Lookup window should appear, as shown in the following image.

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

    The Lookup Field window opens.

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

    The Lookup dialog box opens.

  21. Click OK.

    The expression is completed in the Transformation Calculator window.

    The Transformation Calculator should now appear.

  22. Click OK.

    The STORENAME source transformation is added to the Transformations dialog box.

  23. 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

  24. Click OK to close the Source Transformations dialog box.
  25. Save the data flow as DMXLKUPS.
  26. 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 Expand, and clicking the Sample Data tab.

Note:
  • The first four rows have a blank store name because the DMORD table contains store codes that do not exist in DMCOMP.
  • Your output may vary depending on your database.

WebFOCUS

Feedback