| Oracle® Warehouse Builder Sources and Targets Guide 11g Release 2 (11.2) Part Number E10582-05 |
|
|
PDF · Mobi · ePub |
| Oracle® Warehouse Builder Sources and Targets Guide 11g Release 2 (11.2) Part Number E10582-05 |
|
|
PDF · Mobi · ePub |
Oracle Warehouse Builder can integrate with Oracle's enterprise applications including E-Business Suite, PeopleSoft, and Siebel. Metadata for objects in the applications' underlying database schemas, such as tables, views and sequences, can be imported into a project, and then used like any other database objects in ETL mappings as sources and targets.
This chapter contains the following topics:
"Importing Metadata from Oracle E-Business Suite Applications"
"Importing Metadata from Applications Implemented on non-Oracle Databases"
Oracle E-Business Suite of applications provide comprehensive business solutions that can be implemented across a wide variety of industry functions including customer relationship management (CRM), project management, supply chain management (SCM), product life cycle management, financial management, and so on.
Contact the database administrator for the E-Business Suite application 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 Oracle Warehouse Builder Installation and Administration Guide. If not, you must provide the DBA with a list of the tables, views, sequences, and keys from which you want to extract data. This is so that the DBA can grant object access privileges for these tables, views, sequences, and keys to the specific user. The user must be granted object privilege because the objects are accessed using PL/SQL in the extraction mappings.
Depending on the preference of the DBA, there may be a single user who extracts the metadata and the data. Or, there may be two separate users to access the metadata and data respectively.
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. The E-Business Suite module contains nodes for tables, views, and sequences. You can also create a User Folder within the module to organize the objects that you import. See "Creating User Folders" for more information about User Folders.
To import E-Business Suite metadata:
Create an E-Business Suite module as described in "Creating an E-Business Suite Module".
Import metadata from the E-Business Suite application as described in "Importing E-Business Suite Metadata".
To create an E-Business Suite module
Right-click Oracle E-Business Suite under the Applications node and select New Oracle EBS Module.
The Create Module Wizard is displayed.
Click Next to display the Name and Description page.
Specify a name and an optional description for the source module. Also select the access method. You can either make a native heterogeneous connectivity or a Gateways connection. Click Next.
The Connection Information page is displayed.
Specify the connection information for the E-Business Suite source module and click Next.
Ensure that the location associated with the 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 the location on the Connection Information page.
To create a new location, click Edit on the Connection Information page of the Create Module Wizard. The Edit Non-Oracle Location dialog box is displayed.
See "Edit Non-Oracle Location Dialog Box" for details of the values to be entered in this dialog box.
On the Summary page, review the options entered on the previous wizard pages. Click Back to modify any selections. Click Finish to create the E-Business Suite source module.
Use the Edit Non-Oracle Location dialog box to create a location for the source module.
Provide a name for the location.
Provide an optional description.
Lists the connections available to access a location. You cannot change the connection type after creating a location.
HOST:PORT:SERVICE: Makes a connection using the Easy Connect Naming method, which requires no prior setup:
User Name: The database user credential that has permission to access the schema location.
When connecting to an application that does not have user names, enter any text as a mock user name.
Password: The password associated with user name.
When connecting to an application that does not require a password, enter any text as a mock password.
Host: The name of the system where the application is installed.
If the application is installed on the same system as the Oracle Warehouse Builder, you can enter localhost instead of the computer name.
Port: The SQL port number to access the application.
Service Name: The service name of the application.
Use Global Name: The unique name of the application, which is composed of the application name and the domain in the form application_name.application_domain. Select this option when connecting to an application on a different network.
Database Link: Makes a connection to another database using an existing database link. Select this method only when you do not have privileges that enable you to make a direct connection. You cannot deploy to a location that uses a database link.
A database link is a schema object that contains information for connecting to a remote database. Database links are used in distributed database environments and enable a client to access two physical databases as one logical database.
From Location: An existing location where the database link is defined.
Database Link: The object name of the database link.
SQL*NET Connection: Makes a connection using a net service name previously defined using a tool such as Oracle Net Configuration Assistant. The net service name provides a convenient alias for the connection information.
User Name: The user credential that has permission to access the schema location.
When connecting to an application that does not have user names, enter any text as a mock user name.
Password: The password associated with user name.
When connecting to an application that does not require a password, enter any text as a mock password.
Net Service Name: The name of the predefined connection.
Use Global Name: The unique name of the database, which is composed of the database name and the domain in the form application_name.application_domain. Select this option when connecting to an application on a different network.
The schema where the source data is stored or the target objects are deployed. The schema must be registered. By default, it is the User Name schema.
When connecting to a type of application that does not have schemas, leave this field empty.
To import metadata into the module:
Right-click the E-Business Suite source module and select Import, and then Database Objects.
The Import Metadata Wizard is displayed.
Complete the following tasks using the wizard.
The Import Metadata Wizard includes a Filter Information page that enables you to select the metadata. Oracle Warehouse Builder provides two filtering methods:
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".
This filter enables you to search by the object type such as tables, views, and sequences or to search by entering 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 Object Type".
Select a filtering method and click Next to proceed with the importing of metadata.
To filter metadata by the business domain:
Select Business Domain and click Browse to open the Business Domain Hierarchy dialog box.
The Business Domain Hierarchy dialog box lists the available E-Business Suite business domains.
Note:
The time it takes to list the business domains depends on the network location of the E-Business Suite application server, the type of LAN used, and the size of the E-Business Suite application database.Use the Business Domain Hierarchy dialog box to select the E-Business Suite business domains that contain the metadata objects you want to import.
Select a business domain and click Show Entities.
The Folder dialog box displays a list of objects available in the selected business domain.
Review this dialog box to ensure that you are selecting the required objects and click OK to go back to the Business Domain Hierarchy dialog box.
Some business domains can contain more than 1000 objects. The time required to import such a large amount of metadata can vary depending on the network connection speed and the processing power of the source and target systems. It is recommended that you review the list of objects and import only the ones that are required.
Click OK.
The wizard displays the Filter Information page with the E-Business Suite business domain displayed in the Business Domain field.
Select Object Type.
Select the type of objects you want to import. You can select Tables, Views, and Sequences.
To select specific objects, enter 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, to search the business domain for tables whose names contain the word CURRENCY, then type %CURRENCY%. To refine the search to include only tables named CURRENCY and followed by a single digit, then type %CURRENCY_.
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:
Move the objects from the available list to the selected list.
The Import Wizard also enables you to choose 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.Click Next.
If you select One Level or All Levels, then the Confirm Import Selection dialog box is displayed if other related tables are to be imported due to foreign keys or other dependencies.
Review this dialog box to ensure that you are selecting the required tables.
Click OK.
The selected objects appear in the right pane of the Object Selection page.
Click Next.
The Import Wizard displays the Summary and Import page.
The Import Metadata 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 entering a new description.
Review the information about the Summary and Import page and click Finish to import the selected objects.
The E-Business Suite integrator reads the table definitions from the E-Business Suite application server and creates the metadata objects in the module.
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, especially if you are connecting servers located in different local area networks (LANs).
When the import completes, the Import Results dialog box is displayed. Click OK to finish importing or click Undo to cancel the import.
Oracle's PeopleSoft Enterprise applications provide comprehensive business solutions in a wide variety of industry functions including Human Resource Management System (HRMS), Financials, Customer Relationship Management (CRM), and Material Management. A PeopleSoft application consists of numerous modules, each pertaining to a specific area in an enterprise.
To import metadata from PeopleSoft applications, create a PeopleSoft module from the Projects Navigator. After creating the module, you can import metadata definitions from the PeopleSoft application using the Import Metadata Wizard. This wizard enables you to filter the objects you want to import and verify those objects. The PeopleSoft module contains nodes for tables, views, and sequences. You can also create a User Folder within the module to organize the objects that you import. You can import metadata for tables, views, and sequences.
To import PeopleSoft metadata:
Create a PeopleSoft module as described in "Creating a PeopleSoft Module".
Import metadata from the PeopleSoft application as described in "Importing PeopleSoft Metadata".
To create a PeopleSoft module
Right-click PeopleSoft under the Applications node and select New Peoplesoft Module.
The Create Module Wizard is displayed.
Oracle Warehouse Builder displays the Welcome page for the Import Metadata Wizard.
Click Next to display the Name and Description page.
Specify a name and an optional description for the source module. Also specify whether you want to connect through native database connectivity (using JDBC drivers) or through Oracle Gateways.
If you select Oracle Gateways, then select the database from which the metadata is to be imported. Click Next.
The Connection Information page is displayed.
Specify the connection information for the PeopleSoft source module and click Next.
Ensure that the location associated with the 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.
To create a new location, click Edit on the Connection Information page of the Create Module Wizard. This opens the Edit Non-Oracle Location dialog box. See "Edit Non-Oracle Location Dialog Box" for more information about the values to be entered in the dialog box.
On the Summary page, review the options entered on the previous wizard pages. Click Back to modify any selections. Click Finish to create the PeopleSoft source module.
See Also:
Chapter 6, "Connecting to Data Sources Through JDBC" for more information about native database connectivity.Chapter 4, "Connecting to Non-Oracle Data Sources Through Gateways" for more information about Gateways connectivity.
To import metadata into the module:
Right-click the PeopleSoft module and select Import, Database Objects.
The Import Metadata Wizard is displayed.
Complete the following tasks:
The Import Metadata Wizard includes a Filter Information page that enables you to select the metadata. Oracle Warehouse Builder provides two filtering methods:
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".
This filter enables you to search by the object type such as tables, views, and sequences or to search by entering text string information in the field provided on 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 Object Type".
Select a filtering method and click Next to proceed with the importing of metadata.
To filter by the business domain:
Select Business Domain and click Browse to open the Business Domain Hierarchy dialog box.
The Import Metadata Wizard displays Loading Progress dialog box while it is retrieving the business domains.
The Business Domain Hierarchy dialog box lists the available PeopleSoft business domains.
Note:
The time it takes to list the business domains depends on the network location of the PeopleSoft application server, the type of network used, or the size of the PeopleSoft application database.Use the Business Domain Hierarchy dialog box to select the PeopleSoft business domains that contain the metadata objects you want to import.
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.
Review this dialog box to ensure that you are selecting the required objects.
Some business domains can contain more than 1000 objects. The time it takes to import such a large amount of metadata can vary depending on the network connection speed and the processing power of the source and target systems. It is recommended that you review the list of objects and import only the ones that are required.
Click OK.
The wizard displays the Filter Information page with the PeopleSoft business domain displayed in the Business Domain field.
Select Object Type.
In the Object Type section, select the types of objects you want to import. You can select Tables, Views, and Sequences.
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, to search the business domain for tables whose names contain the word CURRENCY, then type %CURRENCY%. To refine the search to include only tables named CURRENCY and followed by a single digit, then type %CURRENCY_.
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:
Move the objects from the Available list to the Selected list.
The Import Wizard also enables you to choose 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.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.
Click OK.
The selected objects appear in the Selected pane of the Object Selection page.
Click Next.
The wizard displays the Summary and Import page.
The Import Metadata 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 entering a new description.
Review the information about 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 available objects including tables, views, and sequences and the connection between the PeopleSoft application server and the workspace. Importing a large number of objects can result in delays so it is recommended that you import in smaller batches.
When the import completes, the Import Results dialog box is displayed. Click OK to finish importing metadata.
Oracle's Siebel applications provide Customer Relationship Management (CRM) solutions. Oracle Warehouse Builder provides a connector for Siebel systems that enables you to extract both metadata and data from Siebel applications.
The Siebel connector enables you to connect to any Siebel application, read its metadata, import the metadata into Oracle Warehouse Builder, and extract data from the system.
Before you import metadata definitions from Siebel, you must create a Siebel source 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. The Siebel module contains nodes for tables, views, and sequences. You can also create a User Folder within the Siebel module to organize the objects that you import. You can import metadata for tables, views, and sequences.
To import metadata definitions from Siebel:
Create a Siebel source module, as described in "Creating a Siebel Source Module".
Import metadata from Siebel, as described in "Importing Siebel Metadata".
From the Projects Navigator, click the Applications node to expand it.
Right-click Siebel and select New Siebel Module.
The Create Module Wizard is displayed.
Specify a name and an optional description for the source module. Also specify whether you want to connect through native database connectivity (using JDBC drivers) or through Oracle Gateways.
If you select Oracle Gateways, then select the database from which the metadata is to be imported. Click Next.
The Connection Information page is displayed.
Specify a name and an optional description for the Siebel source module and click Next.
The Connection Information page is displayed.
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 to open the Edit non-Oracle dialog box. See "Edit Non-Oracle Location Dialog Box" for more details.
On the Summary page, review the options entered on the previous pages. Click Back to modify any selections. Click Finish to create the Siebel source module.
See Also:
Chapter 6, "Connecting to Data Sources Through JDBC" for more information about native database connectivity.Chapter 4, "Connecting to Non-Oracle Data Sources Through Gateways" for more information about Gateways connectivity.
Right-click the Siebel source module into which you want to import metadata and select Import, Database Objects.
Oracle Warehouse Builder displays the Welcome page for the Import Metadata Wizard.
Click Next.
The Filter Information page is displayed.
Select the objects to be imported and click Next.
You can search for specific object types such as tables, sequences, and views. Oracle Warehouse Builder also enables you to specify the name of the object to be imported. You can search for specific object names by entering 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.
On the Objects Selection page, select the objects to be imported into the Siebel module and click Next.
You can choose 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.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.
You can import metadata from PeopleSoft and Siebel applications that are implemented on non-Oracle databases such as DB2, SQL Server, Sybase, Informix, and so on.
With earlier releases of Oracle Warehouse Builder, you required an Oracle Gateways connection to import metadata from an application such as PeopleSoft implemented on a non-Oracle database, such as SQL Server. With Oracle Warehouse Builder 11g Release 2 (11.2), you can define a CMI definition and use a native heterogeneous connectivity using JDBC drivers to import from these applications.
See "Connecting to a PeopleSoft Application Implemented on SQL Server" for more information about using a CMI definition to import metadata from a PeopleSoft application implemented on SQL Server.
On the Locations Navigator, click an application to see the databases from which you can import metadata for that application as shown in Figure 8-1. Create a location based on the database on which the application is implemented. For example,
To define a location corresponding to a PeopleSoft application on SQL Server:
On the Locations Navigator, navigate to Applications, PeopleSoft.
Right-click SQL Server and select New SQL Server PeopleSoft Location.
The Create SQL Server PeopleSoft Location dialog box is displayed.
Provide the connection details corresponding to the SQL Server database. See "Connecting to SQL Server Database" for more information about the details to be specified to connect to an SQL Server database.
Figure 8-1 The Locations Navigator for the Oracle Applications
To import metadata from a PeopleSoft application that is implemented on a SQL Server database, first define a CMI definition for PeopleSoft on SQL Server.
To leverage on a CMI mechanism, you must create a CMI_DEFINITION for the PeopleSoft application.
CMI definitions must be created from the root context. You can switch to the root context only from the OMB Plus console. You cannot switch to the root context using the OMB*Plus view from within the Oracle Warehouse Builder UI. Therefore it is recommended that you use the OMB Plus console for the implementation.
For more information about OMB*Plus scripting, see Oracle Warehouse Builder API and Scripting Reference.
See Also:
Oracle Warehouse Builder OMB*Plus Command Reference for a list of all OMB*Plus commands.To use the OMB Plus console on a Windows system, select Start, then All Programs, <OWB>, Warehouse Builder, and then OMB Plus.
To switch to the root context, use the following command:
OMBCONNECT <repository user>/<password>@<host>:<port number>:<service name>
For example,
OMBCONNECT rep_user/password@localhost:1521:orcl
Where rep_user/password is the user name/password to connect to the repository, localhost indicates a local installation, 1521 is the port number, and orcl is the service name of the database.
Example 8-1 lists the .tcl script to create a CMI. Run the script from the OMB Plus console.
Example 8-1 Script to Create a CMI Definition
set platformname SQLSERVER
set application PEOPLESOFT
set cmi $platformname\_$application
puts "Creating Custom Application Metadata Import"
OMBCREATE CMI_DEFINITION '$cmi' USING DEFINITION_FILE 'c:\\cmi\\platformdef4_miv.xml'
OMBALTER CMI_DEFINITION '$cmi' SET PROPERTIES (MIV_TYPE) VALUES ('Applications')
puts "Created Custom Application Metadata Import"
puts " - $application on $platformname"
puts ""
Save the changes using the command OMBCOMMIT.
The file platformdef4_miv.xml contains the CMI_DEFINITION for PeopleSoft on SQL Server as listed in Example 8-2. Ensure that you provide the correct file location for the .xml file in the .tcl script listed in Example 8-1.
Example 8-2 CMI Definition File for PeopleSoft on SQL Server
<?xml version="1.0"?>
<miv>
<miv_tables type="SQLStatement" default="true" >
select
REC.RECNAME as TABLE_NAME,
TAB.TABLE_NAME as RES_NAME,
rtrim(REC.RECDESCR) as TABLE_DESC
from
PSRECDEFN REC left outer join
PS_EO_BCOWNRID_VW M on ( REC.OBJECTOWNERID=M.OBJECTOWNERID),
INFORMATION_SCHEMA.TABLES TAB
where (REC.RECNAME =TAB.TABLE_NAME or 'PS_'+REC.RECNAME =TAB.TABLE_NAME)
and REC.RECTYPE =0 </miv_tables>
<miv_columns type="SQLStatement" default="true" />
<miv_capabilities type="ResultSet">
<table_supported>true</table_supported>
<view_supported>false</view_supported>
<sequence_supported>false</sequence_supported>
<table_name_filter_supported>false</table_name_filter_supported>
<view_name_filter_supported>false</view_name_filter_supported>
<sequence_name_filter_supported>false</sequence_name_filter_supported>
<business_area_supported>false</business_area_supported>
<business_area_table_supported>false</business_area_table_supported>
<business_area_view_supported>false</business_area_view_supported>
<business_area_sequence_supported>false</business_area_sequence_supported>
<application_owner_supported>true</application_owner_supported>
<table_fklevel_supported>false</table_fklevel_supported>
<reimport_supported>true</reimport_supported>
<data_object_at_leaf_levels>true</data_object_at_leaf_levels>
<multiple_tree_supported>false</multiple_tree_supported>
<function_supported>false</function_supported>
<function_name_filter_supported>false</function_name_filter_supported>
</miv_capabilities>
</miv>
This MIV file is created using elements defined in an XML schema definition (XSD) file.
After you have created a CMI definition, create a PeopleSoft module from the Projects Navigator to connect to the PeopleSoft application implemented on the SQL Server database.
CMI_DEFINITION must have it's name defined as platform_application to work. The illustration creates an example called SQLSERVER_PEOPLESOFT. You get the list of platform names using the command OMBLIST PLATFORMS.
To create the PeopleSoft module:
Under the Application node, right-click PeopleSoft and select New PeopleSoft Module.
The Create Module Wizard is displayed.
On the Name and Description page, provide a name and an optional description. Select the access method to be Native Database Connection. Also select SQL Server as the platform. This option is now available because you have created a CMI definition for PeopleSoft on SQL Server. Click Next.
The Connection Information page is displayed.
Click Edit to define the location details of the PeopleSoft application on SQL Server. See "Creating a SQL Server Module" for more information about the connection details to an SQL Server database.
You require the JDBC drivers to connect to the SQL Server. For more information about the JDBC driver requirements, see "Connecting to SQL Server Database".
After creating the module, you can import metadata from the PeopleSoft application.
|
 Copyright © 2000, 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|