Mapping a Custom Table to an Interface Table for Siebel EIM

This chapter describes how to map a custom table to an interface table for Siebel EIM. It includes the following topics:

Overview of Using Siebel EIM for Bulk Import and Export of Data

This topic describes an overview of using Siebel Enterprise Integration Manager for bulk import and export of data. It contains the following information:

Note: For information about how to use Siebel EIM, see Siebel Enterprise Integration Manager Administration Guide.

About Interface Tables

Siebel Enterprise Integration Manager (EIM) is a server component in the Siebel EAI component group that uses interface tables to transfer data between the Siebel database and other corporate data sources.

An interface table is an intermediate database table that provides a staging area between the Siebel database and other databases. It includes the following qualities:

A Siebel administrator uses it to perform bulk imports, exports, updates, and deletes.
The name of an interface table begin with the EIM_ prefix.
The Type property of an interface table is set to Interface.
A database administrator typically uses a third-party tool to enter values in an interface table. SQL Loader is an example of a third-party tool.

To use EIM to enter values in custom extension tables and extension columns, you create mappings between the new columns and EIM interface tables. You use the EIM Table Mapping Wizard to create these mappings. For more information, see Mapping a Custom Table to an Interface Table.

Object Types That Enterprise Integration Manager Uses

The following image describes the objects and relationships that EIM uses.

EIM Interface Table Object Type

An EIM interface table is an object that provides an alternative representation of a table. It includes some of the same properties as a table plus other properties of an interface table.

EIM Interface Table Column Object Type

An EIM interface table column is an object that provides an alternative representation of a column. It contains all the properties of a column in addition to some properties that are specific to EIM.

The following image describes how the child columns of an interface table are the same as the child columns of a table. The EIM_PRI_LST price list interface table is an example.

Child Columns of an Interface Table Are the Same as Child Columns of a Table

EIM Table Mapping Object Type

An EIM table mapping is an object that references a data table that the parent EIM interface table object definition updates. One EIM interface table can update one or more data tables.

The following image describes how the Destination Table property of each EIM table mapping object identifies the name of the data table to update.

Example of How an EIM Interface Table References a Data Table

Interface Table User Key Usage Object Type

An interface table user key usage is an object that provides support for alternative user keys for base tables. It defines the use of a nontraditional user key for a base table that are specific to an interface table.

Caution: Do not modify the object definition of an interface table user key usage. Any modification can adversely affect performance and operation.

Attribute Mapping Object Type

An attribute mapping is an object that identifies a column in a data table that EIM updates. This column resides in the destination table that is defined in the parent EIM table mapping. An attribute mapping includes the following properties:

Interface Table Data Column. Identifies the column in the interface table that supplies the data.
Base Table Attribute Column. Identifies the column in the destination table that receives the data.

If you add an extension column to a table, and if an interface table must provide data to the extension table, then you must add a corresponding attribute mapping.

The following image includes an example of how an EIM table mapping references a data table.

Foreign Key Mapping Object Type

A foreign key mapping is an object that identifies a foreign key column in the destination table. EIM pulls data from an interface table and enters it into this foreign key column. EIM stores a foreign key as a numeric row ID value in a data table. To use data from an interface table in a foreign key, you must map the interface column to a combination of user key columns in the destination table rather than directly to the foreign key column.

The following image includes an example of how a foreign key map references a data table. To access the row, Siebel CRM uses a combination of attribute columns in the destination table of the foreign key. EIM gets the foreign key value from that row. A foreign key mapping is not a one-to-one column mapping from an interface table to a destination table. The numeric foreign key does not exist in the interface table, so you cannot map it.

Foreign Key Mapping Column Object Type

A foreign key mapping column is an object that does the following:

To locate rows in the table that the foreign key references, identifies one of the attribute columns EIM uses. EIM combines values from the user key columns to form a key that uniquely identifies rows in that table.
Identifies the user key columns so EIM can get foreign key values during an import or export.

User Key Object Type

A user key is an object that provides a set of attribute columns and related information that specifies how EIM can access the table rows. For more information, see How a User Key Creates a Unique Set of Values.

User Key Column Object Type

A user key column is an object can be an attribute or a foreign key. In most situations user key columns constitute the columns in the user key index with the exception of the CONFLICT_ID column. A user key index typically includes a _U1 suffix.

User Key Attribute Object Type

A user key attribute is an object that the parent user key specifies in the set of attribute columns that collectively identifies rows in the grandparent table. The column name is defined in the Name property of the user key attribute.

User Key Attribute Join Object Type

A user key attribute join is an object that specifies a join operation that EIM can use to convert a user key attribute that is a foreign key to another table into attribute column values in that table.

For example, the S_PROD_INT products table includes the S_PROD_INT_U1 user key. This user key references the following columns:

PROD_NAME
PROD_VENDOR
PROD_VEN_LOC

EIM gets the PROD_NAME column from the S_PROD_INT table. No join is required.

EIM must use a join to get the PROD_VENDOR and PROD_VEN_LOC columns from the S_ORG_EXT accounts table. EIM uses a join on VENDR_OU_ID, which is a foreign key from the S_PROD_INT table to the S_ORG_EXT table.

Mapping a Custom Table to an Interface Table

This topic describes how to map a custom table to an interface table. It includes the following information:

Mapping a Custom Table to an Interface Table Using the EIM Table Mapping Wizard

To map custom columns and tables to a predefined EIM interface table, you use the EIM Table Mapping Wizard.

To map a custom table to an interface table

Make sure the table you must map is the appropriate type, includes a user key attribute, and that Siebel CRM supports the mapping.
For more information, see Guidelines for Using the EIM Table Mapping Wizard
In Siebel Tools, display all child object types of the EIM Interface Table object type.
For more information, see Displaying Object Types You Use to Configure Siebel CRM.
in the Object Explorer, click Table.
In the Tables list, locate the table that must reference an EIM table.
This table is the primary table where EIM imports data from the predefined interface table.
Right-click the record, and then click EIM Table Mapping.
Siebel Tools displays the Interface Table Mapping dialog box. It enters data into the Base Table name window of this dialog box. It gets this data from the table you located in Step 4.
In the Enter Column Name Prefix window, enter a prefix.
Siebel Tools does the following:
- If a prefix does not already exist for the EIM table, then Siebel Tools adds the new prefix that you enter for the EIM interface table columns that reference the table.
- If a prefix already exists for the EIM table, then Siebel Tools uses the existing prefix.
If you specify a prefix, then the EIM Table Mapping Wizard adds this prefix to the new columns and makes the column properties uneditable.
In the Select the Interface Table window, choose a value from the list, and then click Next.
Siebel Tools constrains the list you use to choose the EIM interface table. It displays only the interface tables that include a foreign key relationship with your new custom. Siebel Tools sorts this list by EIM table name. If the Exist field of the interface table is Y, then the EIM table is already mapped to the base table. If you extend a predefined Siebel table, then a table with a Y in the Exist field is an ideal candidate for EIM mapping.
Click Finish to accept the configuration and create the EIM Interface Table object.
Siebel Tools begins the mapping, which might take several minutes. For more information, see Relations That the EIM Table Mapping Wizard Creates.
To verify the mappings, do the following:
1. In the Object Explorer, click EIM Interface Table.
2. In the EIM Tables list, run a query for all modified records.
  When you run the query, make sure the Changed property contains a check mark. Leave all other properties empty.
3. To verify the mapping, examine child objects.
4. Identify any new mappings that are not necessary.
If any new mappings are not necessary, then do the following:
1. Deactivate the unnecessary mappings.
2. Rename or delete the ORACLE_HOME\bin\diccache.dat file on the Siebel Server.
3. Run the following query to review any more columns that Siebel Tools created:
```
T_*
```

Relations That the EIM Table Mapping Wizard Creates

The following image describes the relations that exist between objects that the EIM Table Mapping Wizard creates. The wizard maps objects and adds child objects to the predefined EIM interface table object. For more information about EIM objects that the wizard creates, see Objects You Use with Enterprise Integration Manager.

Guidelines for Using the EIM Table Mapping Wizard

If you use the EIM Table Mapping Wizard, then use the following guidelines:

You must set the Type property for any table you use with the EIM Table Mapping Wizard to one of the following values:
- Data (Public)
- Data (Intersection)
- Extension
- Extension (Siebel)
You cannot use the EIM Table Mapping Wizard with a custom table because no EIM table exists to choose in the EIM Table Mapping Wizard.
You can map a single column in an interface table to multiple base tables or extension tables. Do not map multiple interface table columns to a single column in a target table because it can create ambiguity for EIM.
EIM does not validate an interface table or a column definition. EIM validates a list of values against the lists of values that are defined for the base columns where the values are mapped.

Restrictions on Adding or Modifying EIM Mappings

The following table describes restrictions on adding or modifying EIM mappings.

Table Restrictions on Adding and Modifying EIM Mappings

From	To	Restriction
Interface table column	Base column	Supported if predefined mappings exist from the interface table to the data table.
Interface table extension column	Base column	Supported if no other mappings exist to the base column. Use with caution.
Interface table column	Extension table column	Supported if predefined mappings exist from the interface table to the base table of the extension table.
Interface table extension column	Extension table column

Starting the EIM Table Mapping Wizard for a Table That Does Not Use the Foreign Key

To start the EIM Table Mapping Wizard for a Siebel base table that does not use the foreign key as part of the user key, you must create a temporary column, and then run the wizard. For more information, see Article ID 507151.1 on My Oracle Support.

To start the EIM Table Mapping Wizard for a table that does not use the foreign key

Create a temporary column. Use properties described in the following table.

Property	Value
Inactive	Contains a check mark.
User Key Sequence	<> NULL
Foreign Key Table Name	Choose the target table for the interface table. In many, but not all, situations, this table is the parent table of the temporary column.

Property

Value

Inactive

Contains a check mark.

User Key Sequence

<> NULL

Foreign Key Table Name

Choose the target table for the interface table.

In many, but not all, situations, this table is the parent table of the temporary column.

Run the EIM Table Mapping Wizard.
By creating the temporary column, The EIM Table Mapping Wizard lists predefined EIM interface tables that are already mapped to this table as the target or destination table. The wizard lists the EIM tables that Siebel CRM maps to the tables that this table uses as a foreign key. The foreign key must be part of the Traditional U1 Index user key of this table.
For more information, see Mapping a Custom Table to an Interface Table.
After the EIM Table Mapping Wizard finishes, delete the column you created in Step 1.

Deactivating Instead of Deleting an EIM Attribute Mapping

Do not delete any attribute mapping. Instead, you can deactivate an attribute mapping if you no longer require it.

To deactivate instead of deleting an EIM attribute mapping

In Siebel Tools, display all child object types of the EIM Interface Table object type.
For more information, see Displaying Object Types You Use to Configure Siebel CRM.
In the Object Explorer, click EIM Interface Table.
In the EIM Interface Tables list, locate the table that contains the attribute mapping you must modify.
In the Object Explorer, expand the EIM Interface Table tree, and then click EIM Table Mapping.
In the EIM Table Mappings list, locate the table mapping that contains the attribute mapping you must modify.
In the Object Explorer, expand the EIM Table Mapping tree, and then click Attribute Mapping.
In the Attribute Mappings list, locate the attribute mapping you must modify.
Make sure the Inactive property contains a check mark.

Modifying Data from NULL to No Match Row Id

If a primary child column includes no match, then Siebel CRM labels the columns differently depending on how you load data:

If you load data through EIM and a primary child column includes no match, then EIM labels the column with NULL.
If you load data through the Siebel client and a primary child column includes no match, then Siebel CRM labels the column with No Match Row Id.

You must fix this problem.

To modify data from NULL to No Match Row Id

In the Siebel client, open the record set.
Manually step through each record that EIM created.

Siebel CRM replaces each instance of a NULL value with No Match Row Id.

23Mapping a Custom Table to an Interface Table for Siebel EIM