Table of Contents

Search

  1. Preface
  2. Product Overview
  3. Before You Begin
  4. Tutorial Lesson 1
  5. Tutorial Lesson 2
  6. Tutorial Lesson 3
  7. Tutorial Lesson 4
  8. Tutorial Lesson 5
  9. Tutorial Lesson 6
  10. Appendix A: Naming Conventions
  11. Appendix B: Glossary

Getting Started

Getting Started

Creating a Lookup Transformation

Creating a Lookup Transformation

The source table in this mapping includes information about the manufacturer ID. However, you want the manufacturer name in the target table to make the summary data easier to read. In the following steps, you use a Lookup transformation to find each manufacturer name in the MANUFACTURERS table based on the manufacturer ID in the source table.
  1. Create a Lookup transformation and name it LKP_Manufacturers.
    The naming convention for Lookup transformations is LKP_
    TransformationName
    .
    A dialog box prompts you to identify the source or target database to provide data for the lookup. When you run a session, the Integration Service must access the lookup table.
  2. Click
    Source
    .
  3. Select the MANUFACTURERS table from the list and click
    OK
    .
  4. Click
    Done
    to close the
    Create Transformation
    dialog box.
    The Designer adds the transformation.
    Use source and target definitions in the repository to identify a lookup source for the Lookup transformation. Alternatively, you can import a lookup source.
  5. Open the Lookup transformation.
  6. Add an input port, IN_MANUFACTURER_ID, with the same datatype as MANUFACTURER_ID.
    In a later step, you connect the MANUFACTURER_ID port from the Aggregator transformation to this input port. IN_MANUFACTURER_ID receives MANUFACTURER_ID values from the Aggregator transformation. When the Lookup transformation receives a new value through this input port, it looks up the matching value from MANUFACTURERS.
    By default, the Lookup transformation queries and stores the contents of the lookup table before the rest of the transformation runs, so it performs the join through a local copy of the table that it has cached.
  7. Click the
    Condition
    tab, and click the
    Add
    button.
    An entry for the first condition in the lookup appears. Each row represents one condition in the WHERE clause that the Integration Service generates when querying records.
  8. Verify the settings for the condition.
    The following table describes the settings for the condition:
    Lookup Table Column
    Operator
    Transformation Port
    MANUFACTURER_ID
    =
    IN_MANUFACTURER_ID
    If the datatypes, including precision and scale, of these two columns do not match, the Designer displays a message and marks the mapping invalid.
  9. View the
    Properties
    tab.
    Do not change settings in this section of the dialog box.
  10. Click
    OK
    .
    You now have a Lookup transformation that reads values from the MANUFACTURERS table and performs lookups using values passed through the IN_MANUFACTURER_ID input port. The final step is to connect this Lookup transformation to the rest of the mapping.
  11. Click
    Layout
    Link Columns
    .
  12. Connect the MANUFACTURER_ID output port from the Aggregator transformation to the IN_MANUFACTURER_ID input port in the Lookup transformation.
  13. Connect the MANUFACTURER_ID port in the LKP_MANUFACTURERS Lookup transformation object to the MANUFACTURER_ID port in the T_ITEM_SUMMARY target defintion object.
  14. Click
    Repository
    Save
    .

0 COMMENTS

We’d like to hear from you!