A Configuring ODI-Based Initial Loads against a Non-Oracle Target Database

This appendix provides instructions as an example of how to set up a data server for a non-Oracle database.

This chapter includes the following section:

• Section A.1, "Configuring ODI-Based Initial Loads Against a Non-Oracle Target Database"

A.1 Configuring ODI-Based Initial Loads Against a Non-Oracle Target Database

Oracle Data Integrator (ODI) is a bulk-loading tool used to load data from one system to another. ODI is used in the Order to Cash: Siebel CRM - EBS integration for the initial bulk load of assets, customers, products, and price lists using Oracle E-Business Suite (Oracle EBS) as the source system and Siebel Customer Relationship Management (Siebel CRM) as the target system.

ODI provides the flexibility to use source and target systems from a variety of database technologies such as Oracle, MS-SQL, Sybase, DB2, and so on. The code generated for a given source-target combination can be used for any other combination of database systems with little or no modification.

This section describes how to use the ODI maps that are built against an Oracle target database for different database systems. In this section, the Customer bulk load is used as an example. You can follow similar instructions for all of the other initial loads.

Frequently Used Terms

Here are the definitions of terms used in this section:

• Topology: This is a graphical user interface (GUI)-based interface of ODI that makes physical connections to the data objects, such as Database, XML files, and so forth.

  • Physical Topology: This defines the connections to the database and file locations for XML files, as shown in Figure A-1.

    This includes DB user, DB password, SID, Host, Port and so forth. You must modify the physical topology to point to the new Source and Target locations, if they are different from what is specified in deploy.properties.

  • Logical Topology: This is the logical binding from ODI to the data models of the Designer, as shown in Figure A-1.

  Figure A-1 Physical and Logical Topology

  
  

• Designer: This ODI interface defines the data models and the mappings between those data models.

  Data models are logical representations of ODI objects, such as tables, files, views, and so forth.

• Operator: This interface displays the run-time status of the bulk loads while they are being carried out.

A.1.1 Overview of Steps

1. Confirm that the bulk load code has been successfully imported into ODI.

2. Delete the Siebel data server from the Topology.

   See Using the Topology Manager, step 4.

3. Create a new Siebel data server under the proper technology.

   See Using the Topology Manager, step 5.

4. Make connections to the database and use the same Logical Schema name and the context that was used previously.

   See Using the Topology Manager, steps 6, 7, and 8.

5. Go to the Model tab in the Designer. Edit the Siebel model and change the technology to the one that is used for Siebel.

   See Using the Designer, step 2.

6. Import the appropriate load and integration knowledge modules, if not present.

   See Using the Designer, step 5.

7. On the Flow tab, make the appropriate changes for the load and integration knowledge modules wherever necessary.

   See Using the Designer, step 6.

8. Save your interfaces and run.

A.1.2 Using the Topology Manager

Follow these steps to set up the database in Topology Manager:

1. Confirm that the bulk load code has been successfully imported into ODI.

2. Log in to the Topology Manager.

   For this example, Siebel is using a database of technology X.

3. Go to the Physical Architecture tab.

   The Technology tab displays a list of technologies for the database, for example: Oracle, MS-SQL, and so on. Expand the Oracle tab and delete the Siebel data server.

4. Right-click the tab and choose the appropriate option from the menu to create a data server in the technology X tab.

5. Enter the appropriate database details (for example, DB username, password, URL, Port, and so forth).

6. Click the Test button to verify your database connection.

   After having successfully connected to the respective database, click OK or Apply, which takes you to a new window asking for the name of the schema where the Siebel tables are located. These fields are located on the Definition tab. Select the proper schema name from the drop-down menu. Use the same schema name for all references, for example, Work schema.

7. This new window has a Context tab.

   Browse this tab. In the top-left corner of this window, click the Insert icon. A new row appears that has two columns: Context and Logical Schema. Select the same context as the project context and give the same logical schema name as it was for the data server under Oracle. Ensure that you give the same logical schema, or else it is not able to connect to Siebel tables and ends in error while running bulk loads. Regarding O2C2, select the context as My_context from the menu and enter Logical schema name SEBL_SRVR_log_sch.

This completes the database setup.

A.1.3 Using the Designer

Follow these steps to use the Designer:

1. Log in to the Designer.

2. Open the Models tab and expand the Siebel model.

   Double-click to edit the Siebel model. Use the drop-down menu to change the technology from Oracle to X. Select the appropriate Siebel schema from the adjacent tab of the Logical Schema, if not selected.

3. Check whether the context from the Reverse tab matches with the context name of project in consideration.

4. Click OK to make your changes.

5. Edit the appropriate interfaces using Siebel tables both as Sources and as Targets.

6. Import the LKM SQL to SQL and "IKM SQL to SQL Append knowledge modules into the project.

7. In the interface screen, select the Flow tab.

   Edit the knowledge module for each of the Siebel tables situated both on the Source and on Target side. Change the existing KM to LKM SQL to SQL if the Siebel table is used as the source table and IKM SQL to SQL Append when the Siebel table is on the target side.

8. Save and exit the interface.

9. Regenerate the scenario for command-line execution.

A.1.4 Running the Customer Load: Example

This example is for the Customer initial load. Based on the previous steps, you can perform the steps as follows:

1. Log in to the Designer.

2. Open the Models tab.

   You should see this Model folder: OracleEbiz_To_Siebel_Model. Expand this folder. You should see the following models:

   • AIA Configuration Model

   • AIA Country Model

   • AIA Gender Model

   • AIA Initials Model

   • AIA State Model

   • OracleEbiz Model

   • Siebel Model

   • Xref Model

3. Double-click the Siebel Model to edit it.

   In the Definition tab of the Model window, change the technology from Oracle to X.

4. Ensure that you have selected SEBL_SRVR_log_sch as the logical schema in the Logical Schema tab.

   Click OK and ignore all the errors.

5. Open the Projects tab and expand the Project of the name CustomerInitialBulkLoad.

   Expand the folder Customer_Bulk_Load. Expand the Interfaces tab. The listed interfaces include:

   • 14_Ebiz_Cust_Eim_Account_Interface

   • 15_Ebiz_Cust_Eim_Contact_Interface

   • 16_Ebiz_Cust_Eim_Addr_Per_Interface

   • 17_Ebiz_Cust_Eim_Fn_Accnt1_Interfac

   • 18_Siebel_CUSTOMERPARTY_PARTYID_XRF

   • 19_Siebel_CUSTOMERPARTY_ACCOUNTID_X

   • 20_Siebel_CUSTOMERPARTY_ADDRESSID_X

   • 21_Sbl_CUSTMERPARTY_PARTYLOCATIONID

   • 22_Sbl_CUSTOMERPARTY_LOCATIONREFID

   • 23_Sbl_CUSTOMERPARTY_PARTYCONTACTID

   • 24_Sbl_CUSTOMERPARYTY_CONTACTID

   • 25_Sbl_CUSTOMERPARTY_CONTACT_COMMID

   • 26_Sbl_CUSTOMERPARTY_ACCOUNT_COMMID

6. Expand the Knowledge Module under Customer_Bulk_Load and right-click the Loading (LKM) option.

   Click the Import Knowledge Modules option. This opens a window that displays the current directory and knowledge modules present in that directory. Change the directory, if required. Select LKM SQL to SQL. Similarly, right-click the Integration (IKM) option and select IKM SQL to SQL Append.

7. After importing the Knowledge Modules, open each of the previously mentioned interfaces (from 14 to 26) one after the other and select the Flow tab in each of the Interfaces.

   1. For interfaces 14, 15, and 16, click Target+Staging Area.

      You see a window in the lower half, displaying the IKM used. Change the IKM to IKM SQL to SQL Append and save. In the same window, set FLOW_CONTROL and STATIC_CONTROL to NO.

   2. For interface 17, click EIM_ADDR_PER and select LKM SQL to SQL.

      Click Target+Staging Area. Change the IKM to IKM SQL to SQL Append and save. In the same window, set FLOW_CONTROL and STATIC_CONTROL to NO.

   3. For the rest of the interfaces, (18 to 26), in the Flow tab, select each box and change LKM to LKM SQL to SQL and IKM to IKM SQL to SQL Append (STATIC_CONTROL=NO and FLOW_CONTROL=NO).

   4. Save each interface after making changes in LKM and IKM.

8. Expand PACKAGE under Customer_Bulk_Load. The packages include:

   • OracleEbiz_to_XREF_to_EIM

   • Siebel_to_XREF

9. Expand: Package -> OracleEbiz_to_XREF_to_EIM -> Scenarios.

   Right-click the scenario ORACLEEBIZ_TO_XREF_TO_EIM and select Regenerate from the menu.

10. Expand: Package -> Siebel_to_XREF -> Scenarios.

    Right-click the scenario SIEBEL_TO_XREF and select Regenerate from the menu.

11. You can now run the packages OracleEbiz_to_XREF_to_EIM and Siebel_to_XREF.

Note:

You can also run the packages from the command line.