| Oracle® Warehouse Builder User's Guide 10g Release 2 (10.2.0.2) Part Number B28223-05 |
|
|
View PDF |
| Oracle® Warehouse Builder User's Guide 10g Release 2 (10.2.0.2) Part Number B28223-05 |
|
|
View PDF |
Warehouse Builder enables you to interpret and extract metadata from custom and packaged applications and databases into its repository.
This chapter contains the following topics:
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 plan 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: one able to access metadata and another able to access data.
You can create a module in Warehouse Builder to store data from the E-Business Suite database. The Create Module wizard enables you to create a new module.
To create a new E-Business Suite source module:
From the Project Explorer, expand the Applications node.
Right-click the ORACLE_EBUSINESS_SUITE node and select New.
Warehouse Builder displays the Welcome page for the Create Module Wizard.
Click Next.
The wizard displays the Name and Description page.
Provide the following information in this page:
Name of the module: Type a unique name for the module between 1 and 30 alphanumeric characters. Spaces are not allowed.
Status of the module: Select a status for the module from the drop-down list: Development, Quality Assurance, Production.
Selecting one of these options can help you document the warehouse design version.
Description: Type a description of the module you are creating (Optional).
Click Next.
The wizard displays the Connection Information page, which contains the following fields:
Source Type: Specify the data source. The data source can either be an Oracle Database or it can be an external source accessed using a gateway.
Location: Specify the location of the data source. Click Edit to open the Edit Location dialog. Use this dialog to specify the connection details of the E-Business Suite database. For more information, see Connecting to an E-Business Suite Database.
Directory: Specify the directory where Oracle Gateway can access the source data, if the source is not an Oracle Database.
Import after finish: Select this option if you wish to import the data immediately after creating the module.
Note:
If you do not wish to import after finish, then you do not have to necessarily provide the connection details while creating an E-Business Suite database module. In this case, you will have to specify the connection details when you attempt to import data into the module.Click Next.
The wizard displays the Summary page, which provides details of the values you entered in the previous pages.
Click Finish.
The wizard creates and inserts the new module under the ORACLE_EBUSINESS_SUITE node in the Project Explorer.
Specify the connection type. Based on the connection type, you will need to provide the connection details. The connection type can be one of the following:
HOST:PORT:SERVICE: Makes a connection using the Easy Connect Naming method, which requires no prior setup. For more information on the Easy Connect Naming method, refer the Oracle Database Net Services Administrator's Guide. Provide the following connection details:
User Name: The database user credential that has permission to access the schema location. When connecting to a database that does not have user names, enter any text as a mock user name and password.
Host: The name of the system where the database is installed. If Warehouse Builder client is installed on the same system as an Oracle Database, you can enter localhost instead of the system name.
Port: The SQL port number for the database.
Service Name: The service name of the database.
Use Global Name: The unique name of the database, which is composed of the database name and the domain in the form database_name.database_domain. Select this option if the database is accessed through a network connection.
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, such as Warehouse Builder, to access two physical databases as one logical database. 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 not available for BI Beans or Discoverer locations. Provide the following connection details:
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. This method of connecting is ideal for RAC installations. Provide the following connection details:
User Name: The database user credential that has permission to access the schema location. When connecting to a database that does not have user names, enter any text as a mock user name and 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 database_name.database_domain. Select this option if the database is accessed through a network connection.
Specify the schema where the source data is stored or the target objects will be deployed. The schema must be registered with Warehouse Builder. By default, it is the User Name schema.
Specify the version number of the Oracle Database. This is not required for non-Oracle Database locations.
Click Test Connection to verify that the connection information you provided are correct.
Click OK to go back to the Connection Information page of the Create Module wizard.
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.
Perform the following steps to import E-Business Suite metadata:
From the Warehouse Builder Project Explorer, expand the Applications node and then the ORACLE_EBUSINESS_SUITE node.
Right-click the E-Business Suite source module into which you want to import metadata and select Import from the pop-up menu.
Warehouse Builder displays the welcome page for the Import Metadata Wizard.
Click Next.
Complete the following tasks:
The Import Metadata Wizard includes a Filter Information page that enables you to select the metadata. 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 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.
Choose a filtering method and click Next to proceed with the importing of metadata.
Filtering E-Business Suite Metadata by Business Domain
Select Business Domain and click Browse to open the Business Component Hierarchy dialog.
The Business Component Hierarchy dialog lists the available E-Business Suite business domains.
Note:
It may take two to ten 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 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 displays a list of objects available in the selected business domain.
Review this dialog to ensure that you are selecting an appropriate number of objects and click OK to go back to the Business Component Hierarchy dialog.
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.
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
Select Text String, where object.
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_.
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 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.Click Next.
If you select One Level or All Levels, the Confirm Import Selection dialog appears.
Review this dialog to ensure that you are selecting an appropriate number of tables.
Click OK.
The selected objects appear in the right pane of the Object Selection page.
Click Next.
The wizard displays the Summary and Import page.
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 Warehouse Builder repository.
The time it takes to import the E-Business Suite metadata to the Warehouse Builder repository depends on the size and number of tables and the connection between the E-Business Suite application server and the repository. Importing 500 or more objects could take one to three hours or more, especially if you are connecting servers in separate LANs.
When the Import completes, the Import Results dialog displays. Click OK to finish importing.
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 create a module in Warehouse Builder to store data from a PeopleSoft database. The Create Module wizard enables you to create a new module.
To create a new PeopleSoft source module:
From the Project Explorer, expand the Applications node.
Right-click the PEOPLESOFT8_9 node and select New.
Warehouse Builder displays the Welcome page for the Create Module wizard.
Click Next.
The wizard displays the Name and Description page.
Provide the following information in this page:
Name of the module: Type a unique name for the module between 1 and 30 alphanumeric characters. Spaces are not allowed.
Status of the module: Select a status for the module from the drop-down list: Development, Quality Assurance, Production.
Selecting one of these options can help you document the warehouse design version.
Description: Type a description of the module you are creating (Optional).
Click Next.
The wizard displays the Connection Information page, which contains the following fields:
Source Type: Specify the data source. The data source can either be an Oracle Database or it can be an external source accessed using a gateway.
Location: Specify the location of the data source. Click Edit to open the Edit Location dialog. Use this dialog to specify the connection details of the PeopleSoft database. For more information, see Connecting to PeopleSoft Database.
Directory: Specify the directory where Oracle Gateway can access the source data, if the source is not an Oracle Database.
Import after finish: Select this option if you wish to import the data immediately after creating the module.
Note:
If you do not wish to import after finish, then you do not have to necessarily provide the connection details while creating a PeopleSoft database module. In this case, you will have to specify the connection details when you attempt to import data into the module.Click Next.
The wizard displays the Summary page, which provides details of the values you entered in the previous pages.
Click Finish.
The wizard creates and inserts the new module under the PEOPLESOFT8_9 node in the Project Explorer.
Specify the connection type. Based on the connection type, you will need to provide the connection details. The connection type can be one of the following:
HOST:PORT:SERVICE: Makes a connection using the Easy Connect Naming method, which requires no prior setup. For more information on the Easy Connect Naming method, refer the Oracle Database Net Services Administrator's Guide. Provide the following connection details:
User Name: The database user credential that has permission to access the schema location. When connecting to a database that does not have user names, enter any text as a mock user name and password.
Host: The name of the system where the database is installed. If Warehouse Builder client is installed on the same system as an Oracle Database, you can enter localhost instead of the system name.
Port: The SQL port number for the database.
Service Name: The service name of the database.
Use Global Name: The unique name of the database, which is composed of the database name and the domain in the form database_name.database_domain. Select this option if the database is accessed through a network connection.
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, such as Warehouse Builder, to access two physical databases as one logical database. 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 not available for BI Beans or Discoverer locations. Provide the following connection details:
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. This method of connecting is ideal for RAC installations. Provide the following connection details:
User Name: The database user credential that has permission to access the schema location. When connecting to a database that does not have user names, enter any text as a mock user name and 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 database_name.database_domain. Select this option if the database is accessed through a network connection.
Specify the schema where the source data is stored or the target objects will be deployed. The schema must be registered with Warehouse Builder. By default, it is the User Name schema.
Specify the version number of the Oracle Database. This is not required for non-Oracle Database locations.
Click Test Connection to verify that the connection information you provided are correct.
Click OK to go back to the Connection Information page of the Create Module wizard.
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.
Perform the following steps to import PeopleSoft metadata:
From the Warehouse Builder Project Explorer, expand the Applications node and then the PEOPLESOFT8_9 node.
Right-click the PeopleSoft source module into which you want to import metadata and select Import from the pop-up menu.
Warehouse Builder displays the welcome page for the Import Metadata Wizard.
Click Next.
Complete the following tasks:
The Import Metadata Wizard includes a Filter Information page that enables you to select the metadata. 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 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.
Choose a filtering method and click Next to proceed with the importing of metadata.
Filtering PeopleSoft Metadata by Business Domain
Select Business Domain and click Browse to open the Business Component Hierarchy dialog.
The Import Metadata Wizard displays Loading Progress Dialog while it is retrieving the business domains.
The Business Component Hierarchy dialog lists the available PeopleSoft business domains.
Note:
It may take two to ten 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 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.
Review this dialog to ensure that you are selecting an appropriate number of 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.
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
Select Text String, where object.
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_.
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 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.Click Next.
If you select One Level or All Levels, the Confirm Import Selection dialog appears.
Review this dialog to ensure that you are selecting an appropriate number of tables.
Click OK.
The selected objects appear in the right pane of the Object Selection page.
Click Next.
The wizard displays the Summary and Import page.
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 integrator reads the table definitions from the PeopleSoft application server and creates the metadata objects in the Warehouse Builder repository.
The time it takes to import the PeopleSoft metadata to the Warehouse Builder repository depends on the size and number of tables and the connection between the PeopleSoft application server and the repository. Importing 500 or more objects could take one to three hours or more, especially if you are connecting servers in separate LANs.
When the Import completes, the Import Results dialog displays. Click OK to finish importing.
Many companies implement SAP ERP system and Warehouse Builder enables easy access to the data in these SAP systems.
This section describes how you can extract data from an SAP system. It describes why you require an SAP connector, how to import metadata from SAP tables, use them in a mapping, generate ABAP code for the mappings, and deploy them to a SAP system. The chapter also describes the various methods by which you can extract data from the SAP system and load this data into a target table on the Warehouse Builder system.
This section contains the following topics:
An SAP R/3 system operates differently compared to SQL based systems like EBusiness Suite and PeopleSoft.
The major differences include:
The native data manipulation language is ABAP, which is a proprietary SAP language.
Table names are cryptic compared to those in SQL based ERP systems.
In addition to database tables, SAP contains logical tables called pool tables and cluster tables. These tables contain multiple physical tables and must be managed differently.
The SAP connector assists you in managing all these issues. Furthermore, the SAP connector allows you to comply with the administrative and security processes of the SAP environment.
For information about the SAP R/3 versions supported by Oracle Warehouse Builder 10g Release 2, log in to https://metalink.oracle.com, and navigate to the Certify link.
This section provides a brief overview of the different types of tables in SAP, and how data is organized within an SAP system. The section consists of the following topics:
With the SAP Connector, you can import metadata definitions for the following SAP table types:
Transparent: A transparent table is a database table that stores data. You can access the table from non-SAP systems as well, for example, using SQL statements. However, Warehouse Builder uses ABAP code to access transparent tables.
Cluster: A cluster table is usually used to store control data. It can also be used to store temporary data or documentation. Because cluster tables are data dictionary tables and not database tables, you can only access these tables using ABAP.
Pooled: This is a logical table that must be assigned to a table pool in the database. A table pool consists of multiple pooled tables. A pooled table is used to store control data such as program parameters. You require ABAP code to access pooled tables.
SAP application systems logically group tables under different business domains. In SAP, a business domain is an organizational unit in an enterprise that groups product and market areas. For example, the Financial Accounting (FI) business domain represents data describing financial accounting transactions. These transactions might include General Ledger Accounting, Accounts Payable, Accounts Receivable, and Closing and Reporting.
When you import SAP definitions, you can use a graphical navigation tree in the Business Domain Hierarchy dialog box to search the business domain structure in the SAP source application. This navigation tree enables you to select SAP tables from the SAP application server.
Moving data from an SAP system to an Oracle database using Warehouse Builder consists of the following tasks:
Connecting to the SAP system.
Importing metadata from SAP data objects.
Creating an extraction mapping in Warehouse Builder that defines:
The SAP source tables from which data is to be imported
The transformation operators that operate on the source tables to import data based on certain criteria
The target table in Warehouse Builder to store the data imported from the SAP source tables
Deploying the mapping.
This creates the ABAP code for the mapping.
Starting the mapping.
This results in the following sequence of tasks, all of which are performed automatically by Warehouse Builder:
Transfer of the ABAP code to the SAP server.
Compiling of the ABAP code.
Execution of the ABAP code, which results in the generation of a data file (this file has a .dat extension).
Transfer of the data file to the Warehouse Builder server using FTP.
Loading data from the data file into the target table in Warehouse Builder. The loading takes place using SQL*Loader.
To access SAP data from non-SAP systems, you typically use a function module to execute an ABAP program that extracts the data. A function module in SAP is a procedure that is defined in a special ABAP program known as function group. Once defined, the function module can then be called from any ABAP program.
SAP contains a predefined function module called RFC_ABAP_INSTALL_AND_RUN to execute ABAP code. To upload the Warehouse Builder generated ABAP code and execute it in SAP, you need access rights to this function module.
Alternately, you can ask the SAP administrator to create a customized function module that executes a specific ABAP program. You can then use this function module to execute the ABAP code generated by Warehouse Builder.
Data retrieval from the SAP system can be Completely Managed By Warehouse Builder, Managed By Warehouse Builder With SAP Verification, or Manual. This depends on whether the SAP administrator provides the Warehouse Builder user with access rights to the predefined function module RFC_ABAP_INSTALL_AND_RUN or whether the SAP administrator creates a customized function module to execute the ABAP code.
Completely Managed By Warehouse Builder
In this mechanism, Warehouse Builder has access to upload and execute the generated ABAP using the default function module RFC_ABAP_INSTALL_AND_RUN, and to use FTP to import the generated data file from the SAP system.
Thus the entire process of retrieving data from the SAP system and creating a target table is managed by the Warehouse Builder and can be completely automated. It is therefore the simplest method of data retrieval. See "Automated System" for more details on implementing this data retrieval mechanism.
Managed By Warehouse Builder With SAP Verification
In this mechanism, as a Warehouse Builder user, you do not have access rights to the default function module RFC_ABAP_INSTALL_AND_RUN that executes the ABAP code in the SAP system. Instead the SAP administrator first verifies the ABAP code that you generate using Warehouse Builder, and then creates a customized function module to execute this ABAP code. You can then run the ABAP code on the SAP system using this customized function module.
See "Semi Automated System" for more details on implementing this data retrieval mechanism.
Manual
In this method, as a Warehouse Builder user, you cannot directly run the ABAP code on the SAP system. Instead, you generate the ABAP code for the mapping, and send it to the SAP administrator, who runs the code on the SAP system. You then import the generated data file using FTP and load the target table.
The tasks involved in retrieving data using FTP and creating the Oracle table are implemented using a Process Flow. See "Manual System" for more details on implementing this system.
As a Warehouse Builder user, you need to be aware of certain restrictions while trying to import data from an SAP system.
Since the SAP and Oracle Warehouse Builder systems are totally independent systems, as a Warehouse Builder user, you may only have restricted access rights to the SAP data (especially in the production environment). You will therefore have to interact with the SAP administrator to extract data from the system.
Access rights to the SAP system is most often determined by whether it is the development, test, or the production environment. Each of the data retrieval mechanisms can be implemented in the development, test, or production environment depending on the privileges granted by the SAP system administrator.
Development Environment
Typically, in the development environment, the SAP administrator gives you access rights to use the predefined function module RFC_ABAP_INSTALL_AND_RUN. Therefore, in this environment, you can implement a completely Automated System for data retrieval.
Test and Production Environment
Typically, in the test and production environments, the SAP administrator may not give you access rights to use the predefined function module RFC_ABAP_INSTALL_AND_RUN. Instead, the SAP administrator verifies the ABAP code, and either creates a customized function module that you can use, or runs the ABAP code on the SAP system, and allows you to extract the resultant data. You can therefore implement either a Semi Automated System or a Manual System for data retrieval.
A typical data retrieval system may therefore consist of any of the three mechanisms implemented in the different environments.
Scenario 1
You run the automated system in the SAP development environment. Once you verify the ABAP code in this environment, you then move the ABAP code to the SAP test environment and test the code using a customized function module. You then finally move this to the SAP production environment.
This implementation is recommended by Oracle, as it automates and simplifies the data retrieval task.
Scenario 2
Depending on the access rights to the development, test, and production environments, you implement any one of the data retrieval mechanisms in each of the environments.
The following sections provide details of the tasks involved in retrieving data from an SAP system:
To connect to an SAP system from Warehouse Builder, you require certain SAP-specific DLL files. Once you establish connection, you can then import metadata from SAP tables into SAP modules in Warehouse Builder.
This section contains the following topics:
Different sets of files are required depending on whether you are working on a Windows or a Unix system.
Files Required In Windows
The SAP Connector requires a dynamic link library file named librfc32.dll to use remote function calls on the client computer. You must copy librfc32.dll to the location specified in java.library.path on your client system.
To find this location, click MyComputer, Properties, and then click Advanced. Next click Environment Variables, and under System variables, check the locations specified for the variable Path.
You can copy the librfc32.dll file to any one of the multiple locations specified in Path. One of the locations will correspond to OWB_ORACLE_HOME, and is therefore the preferred location. This location is usually OWB_ORACLE_HOME\owb\bin.
See Table 18-1 for the list of files required in Windows.
Table 18-1 Required Files for Windows
| Required Files | Path | Description |
|---|---|---|
|
librfc32.dll |
|
This file is available on the SAP Application Installation CD. |
|
sapjcorfc.dll |
|
Copy this file to the same location where you placed |
|
sapjco.jar |
|
Restart the client after copying these files.
Files Required In Unix
The SAP Connector requires a dynamic link library file named librfccm.so to use remote function calls on the client computer. You need to copy this file to the location specified by the Unix environment variable path LD_LIBRARY_PATH on your client system.
By default, OWB_ORACLE_HOME/owb/bin/admin is the location specified in LD_LIBRARY_PATH. If it is not, then ensure that you add OWB_ORACLE_HOME\owb\bin\admin to LD_LIBRARY_PATH.
See Table 18-2 for the list of files required in Unix.
Table 18-2 Required Files for Unix
| Required Files | Path | Description |
|---|---|---|
|
librfcccm.so |
|
This file is available on the SAP Application Installation CD. |
|
libsapjcorfc.so |
|
Copy this file to the same location where you placed |
|
sapjco.jar |
|
Restart the client after copying these files.
Note:
Different versions of SAP R/3 might require different versions of the DLL, SO, and JAR files. The correct versions are available in the SAP installation CD. The files can also be downloaded from:The most common errors while connecting to an SAP system are listed in Table 18-3:
Table 18-3 SAP Connection Errors
| Error Message | Possible Reason |
|---|---|
|
Connection failed.You are not authorized to logon to the target system (error code 1). |
Incorrect User Name or Password to connect to the SAP server. |
|
Connection failed. Connect to SAP gateway failed. |
Incorrect Application Server, System Number, or Client details. |
|
Some Location Details are missing. Please verify the location information is completely specified. |
Missing DLL files, or DLL files placed in the wrong location. |
|
Missing |
Missing |
Note:
If you create an SAP source module and import SAP tables but cannot see the columns in the tables, then you have an incompatible
librfc32.dll file. Download the correct version of the DLL file from the SAP Website.Use the Create Module Wizard to create an SAP source module that stores data from an SAP source.
To create a SAP Module:
Right-click SAP and select New SAP.
The Create Module Wizard is displayed.
On the Name and Description page, provide a name for the SAP module. Select the module status and optionally also provide a description. Click Next.
On the Connection Information page, either select from an existing location or click Edit to open the Edit SAP Location dialog box. Specify the details as described in "Connecting to an SAP System". Click Next.
On the Summary page, click Finish.
A new sap module is now available on the Projects Navigator.
Note:
Before you create a SAP location, ensure that you have all the necessary information. You can provide the location information either while creating the module or before importing metadata into the module. You need the following information to create the location: server name, user name, password, system number, and client number. Obtain these details from your system administrator.When you set the connection information, you can choose one the following connection types:
A remote function call enables you to call a function module on a remote system. This method requires specific IP Address information for the SAP application server.
SAP Remote Function Call (SAPRFC.INI)
You can also specify the connection information in a file called SAPRFC.INI, and copy this file to the following location: OWB_ORACLE_HOME\owb\bin\admin.
Using the SAPRFC.INI file requires prior knowledge of ABAP parameters, as you need to specify the values for certain parameters to make a SAP connection, and is not the recommended connection method if you are not familiar with ABAP.
Note:
TheSAPRFC.INI file comes with the SAP installation CD.The Create Module Wizard creates the module for you based on the metadata contained in the SAP application server.
Select one of the following connection types:
Remote Function Call (RFC)
This is the recommended connection type, and is selected by default in Warehouse Builder.
SAP Remote Function Call (SAPRFC.INI)
For more information about these connection types, see "Creating SAP Module Definitions".
Type the connection information in the appropriate fields. The fields displayed on this page depend on the connection type you choose.
Note:
Ensure that you have copied the DLL files to the right location. For more information, see "Required Files For SAP Connector".You must obtain the connection information to your SAP Application server from your system administrator before you can complete this step.
RFC Connection type requires the following connection information:
Application Server: The alias name or the IP address of the SAP application server.
System Number: The SAP system number. This must be provided by the SAP system administrator.
Client: The SAP client number. This must be provided by the SAP system administrator.
User Name: The user name with access rights to the SAP system. This name is supplied by the SAP system administrator.
Language: EN for English or DE for German. If you select DE, the description text displays in German and all other text displays in English.
SAPRFC connection type requires the following connection information:
RFC Destination: Type the alias for the SAP connection information.
In addition, both the connection types require the following connection information if the ABAP code is to be executed in SAP using a function module and the data file is to be transferred by FTP to Warehouse Builder:
Host Login User Name: A valid user name on the system that hosts the SAP application server. This user must have access rights to copy the data file using FTP.
FTP Directory: The directory in the SAP server that stores the data file generated when the ABAP report is executed. For systems where the FTP directory structure is identical to the operating system directory structure, this field can be left blank. For systems where the file system directory structure is mapped to the FTP directory structure, enter the FTP directory path that is mapped to staging file directory in the file system directory structure. For example, on a computer that runs Windows, the staging file directory "C:\temp" is mapped to "/" in the FTP directory structure, then enter "/" in this field.
Execution Function Module: In a SAP instance, if a remote function module other than the SAP delivered function module: RFC_ABAP_INSTALL_AND_RUN is used to remotely execute ABAP reports through RFC connections, then enter the remote function module name here.
Click Test Connection to verify that the connection information you provided are correct.
Click OK to go back to the Connection Information page of the Create Module wizard.
Once you establish a connection with the SAP server, you can import metadata from SAP tables.
This section contains the following topics:
After creating the SAP source module, you can import metadata definitions from SAP tables using the Import Metadata Wizard. This wizard enables you to filter the SAP tables to import, verify those tables, and reimport them. You can import metadata for transparent tables, cluster tables, or pool tables.
Perform the following steps to import SAP metadata:
From the Project Explorer, expand the Applications node.
Right-click the SAP source module into which you want to import metadata and select Import.
Warehouse Builder displays the Welcome page for the Import Metadata Wizard.
Click Next.
Complete the following tasks:
You can filter objects to import by business domain or by text strings. Select a filtering method and click Next.
Select Business Domain and click Browse to display the SAP R/3 Business Domain Hierarchy dialog box.
The Import Metadata wizard displays the Loading Progress dialog box while it is retrieving the business domains.
The Business Domain Hierarchy dialog box lists the available SAP business domains.
Note:
It may take a few minutes to list the SAP business domains depending on factors such as the network location of the SAP application server, the type of LAN used, and the size of the SAP application database.Use the Business Domain Hierarchy dialog box to select the SAP business domains that contain the metadata tables you want to import.
Select a folder and click Show Tables to view the tables available in a business domain.
The Import Wizard displays a list of tables in the selected business domain in the Folder dialog box.
Review this dialog box to ensure that you are selecting the required tables.
Some business domains can contain more than 1000 tables. Importing such a large amount of metadata can take time, depending on the network connection speed and the processing power of the source and target systems.
Click OK.
The wizard displays the Filter Information page with the SAP business domain displayed in the Business Domain field.
Select Text String, where object and use the Name matches or Description matches entry field to type a string and obtain matching tables from the SAP data source.
The Description matches field is case sensitive, the Name matches field is not.
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 descriptions contain the word CURRENCY, then select Description matches and type %CURRENCY%. You can also search for tables by their names.
Specify the number of tables you want to import in the Maximum number of objects displayed field.
The Object Selection page contains a description of the tables and enables you to select the tables you want to import into the SAP module. To select the tables:
Move the tables from the available list to the selected list.
The Import Metadata Wizard also enables you to choose whether you want to import tables with foreign key relationships for each table that you choose to import. You can select one of the following:
None: Import only the tables in the Selected list.
One Level: Import the tables in the Selected list and any tables linked to them directly through a foreign key relationship.
All Levels: Import the tables in the Selected list and all tables linked to them hrough foreign key relationships.
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.
Click OK.
The selected tables appear in the Selected list of the Table Selection page.
Click Next.
The wizard displays the Summary and Import page.
The wizard imports the definitions for the selected tables from the SAP Application Server, stores them in the SAP source module, and then displays the Summary and Import page.
You can edit the descriptions for each table by selecting the Description field and typing a new description.
Review the information about the Summary and Import page and click Finish.
The SAP Connector reads the table definitions from the SAP application server and creates the metadata objects in the workspace.
The time it takes to import the SAP metadata into the workspace depends on the size and number of tables and the connection between the SAP application server and the workspace. It is a best practice to import small batches of tables to allow better performance.
When the import completes, the Import Results dialog box displays. Click OK to finish importing metadata.
To reimport SAP tables, follow the importing procedure using the Import Metadata Wizard. Prior to starting the import, the wizard checks the source for tables with the same name as those you are importing. The tables that have already been imported appear in bold in the Object Selection page. On the Summary and Import page, the Action column indicates that these tables will be reimported. The wizard then activates the Advanced Synchronize Options button so that you can control the reimport options.
Note:
If you wish to undo the reimport, click Undo. This ensures that no changes are made to the existing metadata.With SAP tables, you cannot view the data after you import the metadata from these tables. However, you can get a good insight about the data that is stored in the tables by viewing the Column Descriptions and the Constraints Details.
Column Descriptions
You can view the column description of each of the columns in a table. This is valuable because the column names in SAP can be non-descriptive, and difficult to interpret if you have not previously seen the data in the table.
To view the descriptions, double-click the table to open the object editor for the table, and then click the Columns editor.
Constraints Details
The other benefit of data object editor is that you can get information about the primary and foreign keys within the table. To view the key constraints, click the Constraints editor.
Note:
It is also a useful practice to display the business names of the SAP tables in the Projects Navigator. Business names provide a description of the tables and are therefore more intuitive than the physical names. To view the business names for tables in Warehouse Builder, from the main menu, click Tools, Preferences, OWB, Naming, and then select Business Names in the Naming Mode field.After importing metadata from SAP tables, you must define the extraction mapping to extract data from the SAP system.
You can use the Mapping Editor to create a mapping containing SAP tables. Creating a mapping with SAP tables is similar to creating mappings with other database objects. However, there are restrictions on the operators that can be used in the mapping. You can only use Table, Filter, Joiner, and Mapping Input Parameter mapping operators in a mapping containing SAP tables.
A typical SAP extraction mapping consists of one or more SAP source tables (transparent, cluster, or pooled), one or more filter or joiner operators, and a non-SAP target table (typically an Oracle table) to store the imported data.
Note:
The source table is always an SAP table. Note that you cannot have both SAP and non-SAP (Oracle) source tables in a mapping, but the staging table is an Oracle table.This section contains the following topics:
To add an SAP table to a mapping:
On the Mapping Editor drag and drop the required SAP table onto the Mapping Editor canvas.
The editor places a Table operator on the mapping canvas to represent the SAP table.
Use the Operator properties panel of the Mapping Editor to set the SQL*Loader properties for the tables in the mapping.
To set the loading type for an SAP Source Table:
On the Mapping Editor, select the SAP source table. The Table Operator Properties panel displays the properties of the SAP table operator.
Select a loading type from the Loading Type list. With ABAP code as the language for the mapping, the SQL*Loader code is generated as indicated in Table 18-4.
Use the Configuration Properties dialog box to define the code generation language as described in Setting the Language Parameter.
Set ABAP specific parameters, and the directory and initialization file settings in the Configuration Properties dialog box as described in Setting the Runtime Parameters.
Setting the Language Parameter
This parameter enables you to choose the type of code you want to generate for a mapping. For mappings containing SAP source tables, Warehouse Builder enables you to select either PL/SQL or ABAP.
If the SAP system uses a non-Oracle database to store data, then you must select ABAP to generate code. If the SAP data is stored on an Oracle database, then you can specify PL/SQL. However, with PL/SQL, you cannot extract pool or cluster tables. Therefore, in all instances it is desirable to set the language to ABAP.
Setting the Runtime Parameters
With the language set to ABAP, you can expand the Runtime Parameters node in the Configuration Properties dialog box to display settings specific to ABAP code generation.
Some of these settings come with preset properties that optimize code generation. It is recommended that these settings be retained, as altering them may slow down the code generation process.
The following Runtime parameters are available for SAP mappings:
Background Job: Select this option if you wish to run the ABAP report as a background job in the SAP system. Enable this option for the longer running jobs. Foreground batch jobs that run for a long duration are considered hanging in SAP after a certain time. Therefore it is ideal to have background job running for such extracts.
File Delimiter for Staging File: Specifies the column separator in a SQL data file.
Data File Name: Specifies the name of the data file that is generated when the ABAP code for the mapping is run in the SAP system.
SQL Join Collapsing: Specifies the following hint, if possible, to generate ABAP code.
SELECT < > INTO < > FROM (T1 as T1 inner join T2 as T2) ON <condition >
The default setting is TRUE.
Primary Foreign Key for Join: Specifies the primary key to be used for a join.
ABAP Report Name: Specifies the name of the ABAP report generated by the mapping. This is required only when you are running a custom function module to execute the ABAP code.
SAP System Version: Specifies the SAP system version number to which you want to deploy the ABAP code. The characteristics of the generated ABAP code depends on the version number. For MySAP ERP and all other versions, select SAP R/3 4.7. Note that different ABAP code is generated for versions prior to 4.7.
Staging File Directory: Specifies the location of the directory in the SAP system where the data file generated by ABAP code resides.
SAP Location: The location of the SAP instance from where the data can be extracted.
Use Select Single: Indicates whether Select Single is generated, if possible.
Nested Loop: Specifies a hint to generate nested loop code for a join, if possible.
Specify a name for the control file. By default, the control file name is the same as the data file name specified in the Data File Name field. This means that the ABAP code will generate a single control file containing both the SQL*Loader control information and the data (since the log files are the same). The control file and the data file can be given different names, in which case different files are generated for the control information and data, and both the files are transferrred by FTP.
You need to set this parameter only if the mapping contains the Joiner operator, and you wish to explicitly specify the driving table. Unlike SQL, ABAP code generation is rule based. Therefore, you must design the mapping in such a way that the tables are loaded in the right order. Or you can explicitly specify the order in which the tables have to be joined. To do this, from the Configuration Properties dialog box, expand Table Operators, and then for each table, specify the Join Rank. The driving table must have the Join Rank value set to 1, with increasing values for the subsequent tables.
You can also let Warehouse Builder decide the driving table, as well as the order of joining the other tables. In such cases, do not enter values for Join Rank.
After designing the extraction mapping, you must validate, generate, and deploy the mapping, as you do with all mappings in Warehouse Builder.
To generate the script for the SAP mapping:
Right-click the SAP mapping and select Generate.
The Generation Results window is displayed.
On the Script tab, select the script name and select View Code.
The generated code is displayed in the Code Viewer.
You can edit, print, or save the file using the code editor. Close the Code Viewer to return to the Generation Results window.
To save the file, click Save as File and save the ABAP program to your hard drive.
After you generate the SAP mapping, you must deploy the mapping to create the logical objects in the target location. To deploy an SAP mapping, right-click the mapping and select Deploy. You can also deploy the mapping from Control Center Manager.
When an SAP mapping is deployed, an ABAP mapping is created and stored in the Warehouse Builder runtime schema. Warehouse Builder also saves the ABAP file under OWB_ORACLE_HOME\owb\deployed_files, where OWB_ORACLE_HOME is the location of the Oracle home directory of your Warehouse Builder installation. Note that if you are using the Warehouse Builder installation that comes with Oracle Database, then this is the same as the database home.
Depending on whether data retrieval from the SAP system is fully automated, semi-automated, or manual, you need to carry out the subsequent tasks. This section consists of the following topics:
In a completely automated system, as a Warehouse Builder user you have access to the predefined function module in the SAP system. This allows you to execute any ABAP code and extract data directly from the SAP system without being dependent on the SAP administrator, as shown in Figure 18-1.
Because there is no dependence, you can automate the process of sending the ABAP code to the SAP system and importing the data file from the SAP system. Warehouse Builder will then use FTP to transfer the data file to the Warehouse Builder system, and load the target file with the imported data using SQL*Loader.
An automated system works as follows:
You design the extraction mapping and generate the ABAP code for this mapping.
Before deploying the mapping, ensure that you have set the following configuration properties for the mapping:
ABAP Report Name: The file that stores the ABAP code generated for the mapping.
SAP Location: The location on the SAP system from where data is extracted.
Data File Name: Name of the data file to store the data generated by the execution of ABAP code.
Also ensure that you have provided the following additional connection details for the SAP location:
Execution Function Module: Provide the name of the predefined SAP function module. Upon execution, this function module will take the ABAP report name as the parameter, and execute the ABAP code.
FTP Directory: The directory on the Warehouse Builder system. The data file generated upon the execution of the function module will be transferred to this directory using FTP. Note that this requires an FTP server to be located in the SAP system.
Also provide a username who has write permissions on the FTP directory.
You then start the mapping, following which the following tasks are automatically performed:
Warehouse Builder deploys the ABAP and uses RFC_ABAP_INSTALL_AND_RUN to both load the ABAP and execute it in SAP.
The ABAP code is sent to the SAP system using a Remote Function Call (RFC).
In the SAP system, the ABAP code extracts data from the source tables and creates a data file.
This data file is stored in the location specified by Staging File Directory.
Warehouse Builder uses FTP to transfer this data file back to the Warehouse Builder system.
The file is stored in the location specified in the FTP Directory field.
Using SQL*Loader, Warehouse Builder loads the target table in the mapping with the data from the data file.
The advantage of this system is that you can create a fully automated end-to-end solution to extract SAP data. As a user, you just create the extraction mapping and run it from Warehouse Builder, which then creates the ABAP code, sends it to the SAP system, extracts the resultant data file, and loads the target table with the extracted data.
In a semi automated system, as a Warehouse Builder user, you do not have access to the predefined function module RFC_ABAP_INSTALL_AND_RUN, and therefore cannot use this function module to execute ABAP code. Instead, you must create an extraction mapping, deploy it, and then send the ABAP code to the SAP administrator who verifies the code before allowing you to run it in the SAP system, as shown in Figure 18-2.
Figure 18-2 Semi Automated Implementation
A semi automated system works as follows:
You design the extraction mapping and generate the ABAP report for this mapping.
You can then test this report in the development environment.
You then send the ABAP report to the SAP administrator, who tests the report, and loads it to the SAP repository in the production environment.
The SAP administrator can create a new report or use the same report that you send.
If the SAP administrator creates a new report, then obtain the name of the new report and use it in your mapping to extract data from the production environment.
Before you run the mapping in the production environment, ensure that you have set the following configuration properties for the mapping:
ABAP Report Name: The SAP administrator will provide the name of the ABAP report after verifying the ABAP code. You will then use this report name to extract data.
SAP Location: The location on the SAP system from where data is extracted.
Data File Name: Name of the data file to store the data generated during execution of ABAP code.
Also ensure that you have provided the following additional connection details for the SAP location:
Execution Function Module: Provide the name of the custom function module created by the SAP administrator. On execution, this function module takes the ABAP report name as the parameter, and executes the ABAP code. You must obtain the function module name from the SAP administrator.
FTP Directory: A directory on the SAP system. The data file generated by the execution of the ABAP code is saved to this directory. Warehouse Builder will import the data file using FTP. Note that the FTP server resides on the SAP system.
Also provide a username who has Read permissions on the FTP directory.
In the production environment, when you run the mapping, Warehouse Builder sends the ABAP report name and the custom function module to the SAP system using a Remote Function Call (RFC).
In the SAP system, the ABAP code gets executed and a data file is generated. Note that the ABAP code gets executed only if the ABAP report name and the function module is available.
This data file is stored in the location specified by Staging File Directory.
Warehouse Builder imports the data file using FTP. Note that an FTP server must be available on the SAP server.
Warehouse Builder uses SQL*Loader to load the target table with data from the data file.
In a manual system, your role as a Warehouse Builder user is restricted to generating the ABAP code for the mapping, and sending the ABAP code to the SAP administrator. The tasks involved in this system are:
You create an extraction mapping, and generate the ABAP code for the mapping.
While designing the mapping, make sure that you specify the Data File Name to store the data file.
You send the ABAP code to the SAP administrator.
The SAP administrator executes the ABAP code in the SAP system.
On execution of the code, a data file is generated.
You can then create a Process Flow to import the data file. The Process Flow may typically consist of the following activities:
A File Exists activity checks for the availability of the data file.
If the file exists, then an FTP activity transfers the file to the Warehouse Builder system.
If the file does not exist, then it must wait till the file is made available, and then perform an FTP.
Using SQL*Loader, the target table is loaded with data from the data file.
In most production environments, the SAP administrator may not allow any other user to access the SAP system. In such cases, implementing the manual system may be the only viable option.
Using SQL*Loader in the Process Flow
To use SQL*Loader in the process flow, insert an SQL*Plus activity. To use the SQL*Loader, use the HOST command. Once you insert the SQL*Plus activity, insert the following value for SCRIPT:
HOST sqlldr ${Target.User}/${Target.Password} CONTROL=${Working.RootPath}\C.CTL
quit
Insert the relevant value for the control (.ctl) file name.
Then configure the path settings for the SQL*Plus activity. To do this, right-click the process flow and select Configure.
Under SQL*Plus Activities, expand the SQLPLUS node and provide the required values under Path Settings.
Deployed Location refers to the location of the target table. Working location refers to the location of the control file.
|
 Copyright © 2000, 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|