A lookup relationship associates data that is equivalent across business objects but may be represented in different ways. The following sections describe the steps for using lookup relationships:
Lookup relationship definitions differ from identity relationship definitions in that the participant types are not business objects but of the type Data (the first selection in the participant types list). For more information on how to create a relationship definition for a lookup relationship, see "Defining lookup relationships"..
For example, suppose you create a lookup relationship called StatAdtp for the AddressType values. In Figure 100, each box represents a participant in the StatAdtp lookup relationship. Notice that each participant in this relationship is of type Data.
Figure 100. The StatAdtp lookup
relationship definition
Because a lookup relationship does not indicate which attributes are being related, you can use one lookup relationship definition for transforming several attributes. In fact, you can use one lookup relationship definition for every attribute that requires a lookup, regardless of the business object being transformed. However, because only one set of tables is created for each relationship definition, using one relationship definition for all lookup relationships would make the tables large and hard to maintain.
A better strategy might be to create one lookup relationship definition per common unit of data, such as country code or status. This way, each set of relationship tables contains information related by meaning. Relationships defined this way are also more modular because you can add new participants, as you support new collaborations or applications, and reuse the same relationship definition. For instance, suppose you create a lookup relationship definition for country code to transform Clarify_Site business objects to SAP_Customer. Later on, if you add new collaborations or a new application, you can reuse the same relationship definition for every transformation involving a country code.
When you deploy the lookup relationship definition with the option Create Schema enabled, Interchange Server Express generates a relationship table (also called a lookup table) for each participant. Each lookup table has a name of the form:
RelationshipDefName_ParticipantDefName
When you deploy the StatAdtp relationship definition (see Figure 100) with the option Create Schema enabled, Interchange Server Express generates the following two lookup tables:
A lookup table contains a column for the relationship instance ID (INSTANCEID) and its associated participant instance data (data). Figure 101 shows the lookup tables for the PsftAdtp and SAPAdtp participants in the StatAdtp lookup relationship. These two lookup tables use the relationship instance ID to correlate the participants. For example, the instance ID of 116 correlates the PsftAdtp value of Fired and the SAPAdtp value of 04.
Figure 101. Relationship tables
for the CustLkUp lookup relationship
Unlike relationship tables that hold data for identity relationships, lookup tables do not get populated automatically. You must populate these tables by inserting data into their columns. You can populate a lookup table in either of the following ways:
You can insert participant data into a lookup table with the SQL statement INSERT. This method is useful when you need to add many rows of data to the lookup table. You can create the syntax for one INSERT statement and then use the editor to copy and paste this line as many times as you have rows to insert. In each line, you only have to edit the data to be inserted (usually in a VALUES clause of the INSERT statement).
To use the INSERT statement, you must know the name of the lookup relationship table and its columns. Table 71 shows the column names in a lookup table.
Table 71. Columns of a lookup table
Column in lookup table | Description |
---|---|
INSTANCEID | The relationship instance ID. |
data | The participant data |
STATUS | Set to zero (0) when the participant is active |
LOGICAL_STATE | Indicates whether the participant instance has been logically deleted (zero indicates "no") |
TSTAMP | Date of last modification for the participant instance. |
Suppose you want to add the participant data in to the relationship table that contains information for address type, shown in Table 72.
Table 72. Sample values for address type for PsftAdtp participant
INSTANCEID | STATUS | LOGICAL_STATE | TSTAMP | data |
---|---|---|---|---|
1 | 0 | 0 | current date | Home |
2 | 0 | 0 | current date | Mailing |
The following INSERT statements create the Table 72 participant data in the PstfAdtp lookup table:
INSERT INTO StatAdtp_PsftAdtp_T (INSTANCEID, STATUS, LOGICAL_STATE, TSTAMP, data) VALUES (1, 0, 0, getDate(), 'Home') INSERT INTO StatAdtp_PsftAdtp_T (INSTANCEID, STATUS, LOGICAL_STATE, TSTAMP, data) VALUES (2, 0, 0, getDate(), 'Mailing')
Relationship Manager is an IBM WebSphere business integration tool that graphically displays run-time data in a relationship table. Relationship Manager is useful when you only need to add a few rows to the lookup table. For more information on Relationship Manager, see the User Guide for WebSphere Business Integration Express and Express Plus for Item Synchronization.
Once you have created the relationship definition and participant definitions for the lookup relationship, you can customize the map transformation rule for performing the lookups. For information on using lookup relationships, see "Example 3 of using the Activity Editor".