Using Lookups to Create Multi-Table Flows Without Joins

ibi Data Migrator 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.

Creating a Data Flow Using Lookups

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

  1. In the ibi Data Migrator desktop interface, 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.
  4. Select the Company,ID (STORE_CODE) and Product,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 dropdown 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 dropdown menu for these two columns.
  8. Click OK to close the Column Selection dialog.
  9. Right-click the workspace to the right of the SQL object, point to Add Target, and then click Existing.

    The Select Synonym dialog 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 opens.

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

    The Select Synonym dialog 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. For Return Field, select Store Name.
  19. Click OK to close the Lookup window.
  20. Click OK.

    The expression is completed in the Transformation Calculator window.

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

  21. Click OK.

    The STORENAME source transformation is added to the Transformations dialog.

  22. Repeat the process 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

  23. Click OK to close the Source Transformations dialog.
  24. Save the data flow as DMXLKUPS.
  25. On the Flow tab, in the Run group, click Run, and then select Run from the dropdown 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.