6 Integrating with Applications

Warehouse Builder enables you to use integrate with applications such as SAP, Siebel, Peoplesoft, and Oracle E-Business Suite. This chapter describes how to import metadata from these applications.

This chapter includes the following topics:

Integrating with E-Business Suite

Warehouse Builder enables you to import metadata stored in an E-Business Suite database using the Import Metadata Wizard.

Before You Begin

Contact the database administrator for the E-Business Suite database and request a user name and password for accessing the APPS schema. The DBA may have previously created a user by running the script owbebs.sql as described in the Oracle Warehouse Builder Installation and Administration Guide. If not, you will need to provide the DBA with a list of the tables, views, sequences, and keys from which you want to extract data.

Depending on the preference of the DBA, there may be a single user who extracts both, the metadata as well as the data. Or, there may be two separate users to access the metadata and data respectively.

Importing E-Business Suite Metadata Definitions

After creating the E-Business Suite source module, you can import metadata definitions from E-Business Suite objects using the Import Metadata Wizard. This wizard enables you to filter the E-Business Suite objects you want to import and verify those objects. You can import metadata for tables, views, and sequences.

To import E-Business Suite metadata:

  1. From the Project Explorer, expand the Applications node.

  2. If you have not already done so, create an E-Business Suite module and that will contain the imported metadata.

    To create an E-Business Suite module, right-click ORACLE_EBUSINESS_SUITE under the Applications node and select New. The Create Module Wizard is displayed. Follow the prompts in the wizard. Click Help on a wizard page for more information about that page.

    Ensure that the location associated with the E-Business Suite module contains information needed to connect to the E-Business Suite source. If you created a location earlier, associate that location with the module being created by selecting that location on the Connection Information page. Or create a new location by clicking Edit on the Connection Information page of the Create Module Wizard. For more information about the details to be entered on this page, click Help.

  3. Right-click the E-Business Suite source module into which you want to import metadata and select Import.

    Warehouse Builder displays the Welcome page for the Import Metadata Wizard.

  4. Click Next.

  5. Complete the following tasks:

Filtering E-Business Suite Metadata

The Import Metadata Wizard includes a Filter Information page that enables you to select the metadata. Warehouse Builder provides two filtering methods:

  • Business Domain

    This filter enables you to browse E-Business Suite business domains to locate the metadata you want to import. You can view a list of objects contained in the business domain and the names of the objects in the E-Business Suite application. For more information, see "Filtering E-Business Suite Metadata by Business Domain".

  • Text String Matching

    This filter enables you to search tables, views, and sequences by typing text string information in the field provided in the Filter Information page. This is a more specific search method if you are familiar with the contents of your E-Business Suite application database. For more information, see "Filtering E-Business Suite Metadata by Text String".

Select a filtering method and click Next to proceed with the importing of metadata.

Filtering E-Business Suite Metadata by Business Domain
  1. Select Business Domain and click Browse to open the Business Component Hierarchy dialog box.

  2. The Business Component Hierarchy dialog box lists the available E-Business Suite business domains.

    Note:

    It may take between 2 and 10 minutes to list the business domains depending on the network location of the E-Business Suite application server, the type of LAN used, or the size of the E-Business Suite application database.

    Use the Business Component Hierarchy dialog box to select the E-Business Suite business domains that contain the metadata objects you want to import.

  3. Select a business domain and click Show Entities.

    The Folder dialog box displays a list of objects available in the selected business domain.

  4. Review this dialog box to ensure that you are selecting the required objects and click OK to go back to the Business Component Hierarchy dialog box.

    Some business domains can contain more than 1000 objects. Importing such a large amount of metadata can take from one to three hours or more, depending on the network connection speed and the processing power of the source and target systems.

  5. Click OK.

    The wizard displays the Filter Information page with the E-Business Suite business domain displayed in the Business Domain field.

Filtering E-Business Suite Metadata by Text String
  1. Select Text String, where object.

  2. Select the objects you wish to import. You can select Tables, Views, and Sequences.

    If you wish to select specific objects, type the object name in the text field. Create a filter for object selection by using the wildcard characters (%) for zero or more matching characters, and (_) for a single matching character.

    For example, if you want to search the business domain for tables whose names contain the word CURRENCY, then type %CURRENCY%. If you want to refine the search to include only tables named CURRENCY and followed by a single digit, then type %CURRENCY_.

Selecting the Objects

The Object Selection page contains a description of the objects and enables you to select the objects you want to import into the E-Business Suite module. To select the objects:

  1. Move the objects from the available list to the selected list.

    The Import Wizard also enables you to choose whether you want to import tables with foreign key relationships for each object that you choose to import. You can select one of the following:

    None: Import only the objects in the Selected list.

    One Level: Import the objects in the Selected list and any tables linked to it directly through a foreign key relationship.

    All Levels: Import the objects in the Selected list and all tables linked to it through foreign key relationships.

    The foreign key level you select is the same for all tables selected for importing.

    Note:

    Selecting All Levels increases the time it takes to import the metadata because you are directing the wizard to import tables that are related to each other through foreign key constraints. Select this option only if it is necessary.
  2. Click Next.

    If you select One Level or All Levels, the Confirm Import Selection dialog box is displayed.

    Review this dialog box to ensure that you are selecting the required tables.

  3. Click OK.

    The selected objects appear in the right pane of the Object Selection page.

  4. Click Next.

    The wizard displays the Summary and Import page.

Reviewing Import Summary

The wizard imports definitions for the selected objects from the E-Business Suite Application Server, stores them in the E-Business Suite source module, and then displays the Summary and Import page.

You can edit the descriptions for each object by selecting the description field and typing a new description.

Review the information on the Summary and Import page and click Finish.

The E-Business Suite integrator reads the table definitions from the E-Business Suite application server and creates the metadata objects in the workspace.

The time it takes to import the E-Business Suite metadata to the workspace depends on the size and number of tables and the connection between the E-Business Suite application server and the workspace. Importing 500 or more objects could take one to three hours or more, especially if you are connecting servers in separate Local Area Networks (LANs).

When the import completes, the Import Results dialog box displays. Click OK to finish importing.

Integrating with PeopleSoft

PeopleSoft applications provide ERP solutions. A PeopleSoft application consists of numerous modules, each pertaining to a specific area in an enterprise, such as Human Resource Management System (HRMS), Financials, and Material Management. You can use the Import Metadata Wizard to import metadata from Peoplesoft applications into Warehouse Builder.

Importing PeopleSoft Metadata Definitions

After creating the PeopleSoft source module, you can import metadata definitions from PeopleSoft objects using the Import Metadata Wizard. This wizard enables you to filter the PeopleSoft objects you want to import and verify those objects. You can import metadata for tables, views, and sequences.

To import PeopleSoft metadata:

  1. From the Project Explorer, expand the Applications node.

  2. If you have not already done so, create a Peoplesoft module that will contain the imported metadata.

    Right-click PEOPLESOFT8_9 and select New. The Create Module wizard is displayed. Click Help on a wizard page for more information about the page.

    Ensure that the location associated with the PeopleSoft module contains information needed to connect to the PeopleSoft source. If you created a location earlier, associate that location with the module being created by selecting the location on the Connection Information page. Or create a new location by clicking Edit on the Connection Information page of the Create Module Wizard. For more information about the details to be entered on this page, click Help.

  3. Right-click the PeopleSoft source module into which you want to import metadata and select Import.

    Warehouse Builder displays the Welcome page for the Import Metadata Wizard.

  4. Click Next.

  5. Complete the following tasks:

Filtering PeopleSoft Metadata

The Import Metadata Wizard includes a Filter Information page that enables you to select the metadata. Warehouse Builder provides two filtering methods:

  • Business Domain

    This filter enables you to browse PeopleSoft business domains to locate the metadata you want to import. You can view a list of objects contained in the business domain. For more information, see "Filtering PeopleSoft Metadata by Business Domain".

  • Text String Matching

    This filter enables you to search tables, views, and sequences by typing text string information in the field provided in the Filter Information page. This is a more specific search method if you are familiar with the contents of your PeopleSoft application database. For more information, see "Filtering PeopleSoft Metadata by Text String".

Select a filtering method and click Next to proceed with the importing of metadata.

Filtering PeopleSoft Metadata by Business Domain
  1. Select Business Domain and click Browse to open the Business Component Hierarchy dialog box.

    The Import Metadata Wizard displays Loading Progress dialog box while it is retrieving the business domains.

  2. The Business Component Hierarchy dialog box lists the available PeopleSoft business domains.

    Note:

    It may take between 2 and 10 minutes to list the business domains depending on the network location of the PeopleSoft application server, the type of LAN used, or the size of the PeopleSoft application database.

    Use the Business Component Hierarchy dialog box to select the PeopleSoft business domains that contain the metadata objects you want to import.

  3. Select a folder and click Show Entities.

    The Import Wizard displays a list of objects in the selected business domain in the Folder dialog box.

  4. Review this dialog box to ensure that you are selecting the required objects.

    Some business domains can contain more than 1000 objects. Importing such a large amount of metadata can take from one to three hours or more, depending on the network connection speed and the processing power of the source and target systems.

  5. Click OK.

    The wizard displays the Filter Information page with the PeopleSoft business domain displayed in the Business Domain field.

Filtering PeopleSoft Metadata by Text String
  1. Select Text String, where object.

  2. In the Object Type section, select the types of objects you wish to import. You can select Tables, Views, and Sequences.

    If you wish to select specific objects, type the object name in the text field. Create a filter for object selection by using the wildcard characters (%) for zero or more matching characters, and (_) for a single matching character.

    For example, if you want to search the business domain for tables whose names contain the word CURRENCY, then type %CURRENCY%. If you want to refine the search to include only tables named CURRENCY and followed by a single digit, then type %CURRENCY_.

Selecting the Objects

The Object Selection page contains a description of the objects and enables you to select the objects you want to import into the PeopleSoft module. To select the objects:

  1. Move the objects from the Available list to the Selected list.

    The Import Wizard also enables you to choose whether you want to import tables with foreign key relationships for each object that you choose to import. You can select one of the following:

    None: Import only the objects in the Selected list.

    One Level: Import the objects in the Selected list and any tables linked to it directly through a foreign key relationship.

    All Levels: Import the objects in the Selected list and all tables linked to it through foreign key relationships.

    The foreign key level you select is the same for all tables selected for importing.

    Note:

    Selecting All Levels increases the time it takes to import the metadata because you are directing the wizard to import tables that are related to each other through foreign key constraints. Select this option only if it is necessary.
  2. Click Next.

    If you select One Level or All Levels, the Confirm Import Selection dialog box is displayed.

    Review this dialog box to ensure that you are selecting an appropriate number of tables.

  3. Click OK.

    The selected objects appear in the Selected pane of the Object Selection page.

  4. Click Next.

    The wizard displays the Summary and Import page.

Reviewing Import Summary

The wizard imports definitions for the selected tables from the PeopleSoft Application Server, stores them in the PeopleSoft source module, and then displays the Summary and Import page.

You can edit the descriptions for each object by selecting the description field and typing a new description.

Review the information on the Summary and Import page and click Finish.

The PeopleSoft Connector reads the table definitions from the PeopleSoft application server and creates the metadata objects in the workspace.

The time taken to import PeopleSoft metadata to the workspace depends on the size and number of tables and the connection between the PeopleSoft application server and the workspace. Importing 500 or more objects could take one to three hours or more, especially if you are connecting to servers in separate Local Area Networks (LANs).

When the import completes, the Import Results dialog box displays. Click OK to finish importing metadata.

Integrating with Siebel

Siebel applications provide Customer Relationship Management (CRM) solutions. Warehouse Builder provides a Connector for Siebel systems that enables you to extract both metadata and data from your Siebel systems.

The Siebel Connector enables you to connect to any Siebel application, read its metadata, import the metadata into Warehouse Builder, and extract data from the system.

Importing Siebel Metadata Definitions

Before you import metadata definitions from Siebel, you must create a Siebel module. You can then import metadata definitions from Siebel using the Import Metadata Wizard. This wizard enables you to filter the Siebel objects you want to import and verify those objects. You can import metadata for tables, views, and sequences.

To import metadata definitions from Siebel:

  1. Create a Siebel source module, as described in "Creating a Siebel Source Module".

  2. Import metadata from Siebel, as described in "Importing Siebel Metadata".

Creating a Siebel Source Module

  1. From the Project Explorer, expand the Applications node.

  2. Right-click Siebel and select New.

    The Create Module wizard is displayed.

  3. Click Next to display the Name and Description page.

  4. Specify a name and an optional description for the Siebel source module and click Next.

    The Connection Information page is displayed.

  5. Specify the connection information for the Siebel source module and click Next.

    Ensure that the location associated with the Siebel module contains information needed to connect to the Siebel source. If you created a location earlier, associate that location with the module being created by selecting the location on the Connection Information page. Or create a new location by clicking Edit on the Connection Information page of the Create Module Wizard.

    For more information about the details to be entered on this page, click Help.

  6. On the Summary page, review the options entered on the previous wizard pages. Click Back to modify any selections. Click Finish to create the Siebel source module.

Importing Siebel Metadata

  1. Right-click the Siebel source module into which you want to import metadata and select Import.

    Warehouse Builder displays the Welcome page for the Import Metadata Wizard.

  2. Click Next.

    The Filter Information page is displayed.

  3. Select the objects to be imported and click Next.

    Warehouse Builder enables you to select objects using Text String matching. You can search for tables, views, and sequences by typing text string information in the field provided in the Filter Information page. This is a more specific search method if you are familiar with the contents of your Siebel application database. For more information, see "Filtering Siebel Metadata by Text String"

  4. On the Objects Selection page, select the objects to be imported into the Siebel module and click Next.

    You can choose whether you want to import tables with foreign key relationships for each object that you choose to import using the following options on this page:

    None: Import only the objects in the Selected list.

    One Level: Import the objects in the Selected list and any tables linked to it directly through a foreign key relationship.

    All Levels: Import the objects in the Selected list and all tables linked to it through foreign key relationships.

    The foreign key level you select is the same for all tables selected for importing.

    Note:

    Selecting All Levels increases the time it takes to import the metadata because you are directing the wizard to import tables that are related to each other through foreign key constraints. Select this option only if it is necessary.
  5. Review the summary information and click Finish to complete the import. To modify any selections, click Back.

After you import metadata for tables, views, or sequences from Siebel applications, you can use these objects in mappings.

Filtering Siebel Metadata by Text String

  1. Select Text String, where object.

  2. In the Object Type section, select the objects you wish to import. You can select Tables, Views, and Sequences.

    If you wish to select specific objects, type the object name in the text field. Create a filter for object selection by using the wildcard characters (%) for zero or more matching characters, and (_) for a single matching character.

    For example, if you want to search for tables whose names contain the word CURRENCY, then type %CURRENCY%. If you want to refine the search to include only tables named CURRENCY and followed by a single digit, then type %CURRENCY_.