| Oracle® Warehouse Builder User's Guide 10g Release 1 (10.1) Part Number B12146-02 |
|
|
View PDF |
| Oracle® Warehouse Builder User's Guide 10g Release 1 (10.1) Part Number B12146-02 |
|
|
View PDF |
The Warehouse Builder SAP Integrator enables you to import metadata object definitions from SAP Application data sources into the Warehouse Builder repository. This chapter describes how to use SAP objects in a mapping, generate PL/SQL and ABAP code for the mappings, and deploy them to a target. This chapter also describes how to extract and load SAP data into your target.
This chapter contains the following topics:
The Warehouse Builder SAP Integrator enables you to connect to SAP application source systems and import the SAP source definitions into a project in the Warehouse Builder repository.
You can then generate ABAP or PL/SQL code to extract, transform, and load data from SAP R/3 3.x and R/3 4.x systems to your target system.
SAP application systems logically group database and metadata objects under different business areas. In SAP, a business area is an organizational unit in an enterprise that groups product and market areas. For example, the Financial Accounting (FI) business area 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 into Warehouse Builder, you can use a graphical navigation tree in the Business Component Hierarchy dialog to search the business area structure in the SAP source application. This navigation tree enables you to select SAP metadata objects from the SAP application server.
The SAP integrator enables you to import metadata for SAP Business Areas or any of their related ABAP Dictionary objects.
With the SAP integrator, you can import definitions and generate deployment code for the following SAP table types:
Transparent: A transparent table is first defined in the ABAP Dictionary and then created in the database. You can also use transparent tables independently of the R/3 System. You can generate either PL/SQL or ABAP code for transparent tables.
Cluster: A cluster table is an ABAP Dictionary table type. It contains information pertaining to any group of database tables and it is not created in the SAP database. Because cluster tables are data dictionary tables and not database tables, you can only generate ABAP code.
Pooled: The data from several tables is stored together as a table pool in the database. Pooled tables exist in the ABAP Dictionary and are not known to the database. You can only generate ABAP code for pooled tables.
The Warehouse Builder SAP Integrator requires a dynamic link library file named librfc32.dll in order to use remote function calls on the client machine. This file is available on the SAP Application Installation CD. You need to copy this file to the following Warehouse Builder directory on your client system:
X:\ORACLE_HOME\bin\admin
where ÒX:Ó is the drive containing the Warehouse Builder client and Ò\ORACLE_HOMEÓ is the Oracle home path name for Warehouse Builder.
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. Check the version or build number of your .dll file from your NT Explorer window.
The following version is currently supported in Warehouse Builder:
File Version: 4640,5,123,2956
Build: Wednesday, August 09 23:46:33 2000
File Size: 1,945,138 bytes
Product Version: 46D,123
You can locate this version of the .dll file on the Installation CD.
The Warehouse Builder SAP Integrator requires a dynamic link library file named librfccm.so in order to use remote function calls on the client machine. This file is available on the SAP Application Installation CD. You need to copy this file to the following Warehouse Builder directory on your client system:
X:\ORACLE_HOME\bin\admin
where ÒX:Ó is the drive containing the Warehouse Builder client and Ò\ORACLE_HOMEÓ is the Oracle home path name for Warehouse Builder.
You also need to add X:\ORACLE_HOME\bin\admin to the Unix environment variable path: LD_LIBRARY_PATH.
The New Module Wizard enables you to define an SAP source module. Warehouse Builder uses this definition to store imported metadata from your source SAP application.
This section contains the following topics:
For related information, see:
SAP Application 3.0 documentation
Using the New Module Wizard, you can choose SAP R/3 version 3.x or SAP R/3 version 4.x system types as your source. After you select the application version, you need to set the connection information between the Warehouse Builder repository and the SAP application server. You can edit this information using the module property sheet.
Note:
To create SAP Module definitions, you must first obtain the connection information to your SAP Application server from your system administrator.When you set the connection information, you can choose the following connection types:
This is the default connection type. A remote function call locates a function module running in a system different from that of the caller. The remote function can also be called from within the same system (as a remote call), but usually the caller and the called are located in different systems. This method requires specific IP Address information for the SAP application server.
SAP Remote Function Call (SAPRFC.INI)
SAP can use its own initialization file to track the IP Address information for you. The SAPRFC.INI enables remote calls between two SAP Systems (R/3 or R/4), or between an SAP System and a non-SAP System. This method is useful when you know the SAP-specific connection information and want to automate the IP connection information.
Note:
To use the SAPRFC.INI connection type, the file SAPRFC.INI must be installed in this directory:X:\ORACLE_HOME\wbapp
where X:\ORACLE_HOME is the Warehouse Builder Oracle home path. This file is available in the SAP Application client installation CD. Consult your system administrator for more information.
The New Module Wizard creates the module for you based upon the metadata contained in the SAP application server.
To create an SAP source module:
From the Warehouse Builder console navigation tree, expand the Applications node.
Right-click the SAP node and select Create SAP R3 Source Module.
Warehouse Builder displays the Welcome page for the New Module Wizard.
Click Next.
The wizard displays the Name page as shown in Figure 21-1.
Provide the following information in the Name 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 Data Source Information Page.
Select the correct version of your SAP application from the drop-down list: SAP R/3 3.x or SAP R/3 4.x.
If the librfc32.dll file is missing, an error message displays, as shown in Figure 21-2.
You must load the librfc32.dll file before you can proceed. For more information, see "Required Files For Windows" and "Required Files For Unix".
Click Next.
The wizard displays the Connection Information page for an RFC Connection, as shown in Figure 21-3.
Select one of the following connection types:
Remote Function Call (RFC) is the default connection type. For more information, see Remote Function Call (RFC).
SAP Remote Function Call (SAPRFC.INI). For more information, see SAP Remote Function Call (SAPRFC.INI).
Type the connection information in the appropriate fields. The fields displayed on this page depend on the connection type you choose.
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, as shown in Figure 21-4:
Application Server: Type the alias name or the IP address of the SAP application server.
System Number: Type the SAP system number for SAP user interface login. This number is required in the SAP application configuration and is supplied by the SAP system administrator.
Client: Type the SAP client number. This number is required in the SAP application configuration and is supplied by the SAP system administrator.
User Name: Type the user name for the SAP user interface. This name is required in the SAP application configuration and 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.INI File connection type requires the following connection information:
RFC Destination: Type the alias for the SAP connection information.
Client: Type the SAP client number.
User Name: Type the SAP user name for the SAP user interface.
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.
Click Next.
The wizard displays the Logon dialog for the SAP application, as shown in Figure 21-5.
Type the user name and password for SAP user interface and click Logon.
If the SAP application version number you provide on the Data Source Information page does not match the application version for the source system, an error message displays. Click Back to return to the Data Source Information page and correct the application version.
If no error message appears, the wizard logs onto the SAP application and displays the Finish page.
To proceed directly to the Import Metadata Wizard, check the box at the bottom of the page. You can also choose to import metadata at a later time and leave the box unchecked.
Click Finish.
The wizard creates the new SAP source module and inserts its name under the Applications node in the project navigation tree.
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 objects you want to import, verify those objects, and re-import them. You can import metadata for transparent tables, cluster tables, or pool tables.
This section contains the following topics:
If you choose to proceed to the Import Metadata Wizard wizard directly from the New Module Wizard Finish page, then the wizard opens its welcome page. If choose to import metadata at a later time, then you must open the wizard from the SAP module that you created.
To open the Import Metadata Wizard:
From the Warehouse Builder navigation tree, expand the Applications node and then the SAP node.
Right-click the SAP 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.
The Import Metadata Wizard includes a filter page that enables you to select the metadata. This page offers two filtering methods:
Business Component
This method opens the Business Component Hierarchy tree. Browse the SAP business areas to locate the metadata you want to import. You can view a list of tables contained in the Business Area and the names of the tables in the SAP application.
Text String Matching
Search for tables by typing text string information in fields provided in the Filter Information page. This is a more direct search method if you are familiar with the contents of your SAP application database.
To filter SAP metadata by Business Component:
Select Business Component and click Browse to display the SAP R/3 Business Component Hierarchy dialog.
It may take two to ten minutes to open this dialog depending upon the network location of the SAP application server, the type of LAN used, or the size of the SAP application database.
The Import Metadata Wizard displays the Loading business component tree dialog.
Click OK.
The wizard displays the Business Component Hierarchy dialog as shown in Figure 21-6.
Use this dialog to select the SAP Business Areas containing the metadata objects that you want to import.
Select a folder and click Show Tables to view the tables available in a business component.
The Import Wizard displays a list of tables in the selected business component in the Folder dialog as shown in Figure 21-7.
Review this dialog to ensure that you are selecting an appropriate number of tables.
Some Business Components can contain more than 1000 tables. Importing such a large amount of metadata can take from one to three hours or more, depending upon 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 Area displayed in the Business Component field.
Select Text String where object and then choose the Name matches entry field or the Description matches entry field to type a string and obtain matching tables from the SAP data source.
Although the Name matches field is not case sensitive, the Description matches field is case sensitive.
You must type a text string in the selected Text String entry field. It cannot be left empty.
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 area named A0 for tables whose names contain the word CURRENCY, then type A0%CURRENCY%. If you want to refine the search to include only tables named CURRENCY and followed by a single digit, then type A0%CURRENCY_.
Specify the number of tables you want to import in the Maximum number of objects displayed field.
Click Next.
The wizard displays the Object Selection page with a description for each table, as shown in Figure 21-8.
The Import Wizard also enables you to choose whether you want to import tables with foreign key relationships for each table that you choose to import.
Select tables from the Available Objects list and move them to the Selected Objects list using the arrow buttons.
The SAP Integrator only imports definitions for tables. The tables appear in the Selected Objects list in the Object Selection page.
Select the Import Foreign Key Level:
None: Import only the objects in the Selected Objects list.
One Level: Import the objects in the Selected Objects list and any tables linked to it directly through a foreign key relationship.
All Levels: Import the objects in the Selected Objects 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.
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 for your situation.
Click Next.
If the radio button on the Object Selection page for the foreign key level is set to One Level or All Levels, the Confirm Import Selection dialog appears as shown in Figure 21-9.
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 imports 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 as shown in Figure 21-10.
Review the information on the Summary and Import page.
You can edit the descriptions for each table by selecting the description field and typing a new description.
Click Finish.
The SAP integrator reads the table definitions from the SAP application server and creates the metadata objects in the Warehouse Builder repository.
The time it takes to import the SAP metadata to the Warehouse Builder repository depends on the size and number of tables and the connection between the SAP 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 as shown in Figure 21-11.
Click OK.
To re-import SAP objects, you 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. Re-imported table names appear in bold in the summary and import page. The wizard then activates the Advanced Reconcile Options button so that you can control the re-import options.
You must update existing SAP source module definitions whenever you upgrade SAP application versions, migrate SAP servers, and change network connection configurations. You also need to check this information when you re-import metadata.
You can update an SAP module by editing its properties using the Module Properties dialog.
To update SAP object definition:
From the Warehouse Builder navigation tree, expand the Applications node and then the SAP node.
Right-click the SAP source object and select Properties from the pop-up list.
The Module Properties dialog displays.
Select the appropriate tab to edit the SAP object properties.
Name: Use this tab to modify the unique identification of the module. Use the Name tab to:
Edit the name of the SAP object. The name assigns a unique value to an object and contains between 1 and 30 alphanumeric characters. The 30 character limit applies only to Physical Name Mode.
If necessary, change the status of the SAP object. Select Development, Quality Assurance, or Production.
Edit the description of the SAP object. This field can contain up to 4000 characters. For example, you can use this field to note the purpose of the module and how it relates to the information required by the end-users of the project.
Source: This tab is read-only. Use this page to view the following information:
Application Type: Indicates the application type corresponding to the data source. For example, SAP R/3 4.x.
System Type: Indicates the type of database system (SAP Application) used to host the source data.
Integrator used to access the data source: Displays the Warehouse Builder integrator that was used to connect to the source data.
Connection: Edit the information on this tab only if the network connection information for the SAP application server has changed.
After you define the SAP source module and import the metadata, you can define the ETL mappings to extract and load the data from your SAP source to the target. The Warehouse Builder SAP Integrator features a special mapping tool for SAP objects. Warehouse Builder enables you to configure mappings to generate ABAP or PL/SQL code to deploy your metadata.
This section contains the following topics:
You can use the Mapping Editor to define mappings for SAP sources. While SAP mappings are similar to other types of mappings, there are some important differences:
The SAP Integrator in Warehouse Builder enables you to use the Copy and Map Wizard to define a target operator. This wizard is only available for SAP transparent tables.
Only the Table, Cube, Dimension, Filter, and Joiner mapping operators are available for SAP objects.
To add an SAP object to a mapping:
From the toolbox, drop the Mapping Table icon onto the Mapping Editor canvas.
The Add Mapping Table dialog displays as shown in Figure 21-12.
Choose Select from existing repository tables and bind.
The field at the bottom of the dialog displays a list of SAP tables whose definitions were previously imported into the SAP source module.
Select a source table name and click OK.
The editor places a mapping table on the mapping canvas to represent the SAP table.
You can choose to use the Copy and Map wizard to define a mapping target. Or you can define it as you would with any other type of mapping operator.
When you select an SAP operator as a source, the SAP integrator enables an option to use the Copy and Map wizard to define the target object to which you want to map from an SAP source. This wizard enables you to create a target object whose attributes are identical to those of the SAP source. The Copy and Map wizard enables you to perform three functions:
Copy the selected source object
Create a target object based on the source object definitions
Map the source object to the new target object.
You can use the Copy and Map wizard only when you select a single table as a source. If you select more than one table, then you must define the mapping target manually.
To use the Copy and Map Wizard:
In the Add Mapping Table dialog, select the SAP table you want to use as the source.
Check the option to Proceed to the Copy and Map wizard after adding component.
Click OK.
The Copy and Map Wizard welcome page displays.
Click Next.
The wizard displays the Target Name page.
Type the following information:
Physical Name: A unique physical name for the target object you are creating. The name can contain 1 to 30 characters, no spaces are allowed.
Object Type: Only tables that map from SAP source tables can be created. The choice in this field is preselected and is read-only.
Application: The target application that contains the target object. The choice in this field is preselected and is read-only.
Description: Optional field used to describe the target object you are creating. This field can contain file information up to 2 MB in size.
Click Next.
The Source Columns page displays as shown in Figure 21-13.
By default, this page displays and selects all columns in the source table as defined in the SAP application. You can deselect columns in one of the following ways:
Click the check box next to the column.
Click Deselect All to deselect all the columns within the table.
You must select at least one column to copy. You can select columns in one of the following ways:
Click the check box next to the column.
Click Select All to copy all the columns within the table.
Click Next.
The Target Columns page displays as shown in Figure 21-14.
Use this page to customize the selected columns or add new ones to your target table. The first two columns within the table display the physical and business names of the columns. You can modify the physical names of a column. The default physical names are copied from the source physical names. You can edit the following fields:
Target Columns (Physical): To change the physical name of a target column, click the value and edit the text. To add a new column, click Add and type a new in the name field. If you add a new column, you must name it.
Position: Change the position of the columns by dragging the row header up or down.
Data Type: Change the data type for each column. It is automatically translated to an Oracle data type.
Not Null: (optional) A check indicates that the column cannot contain a NULL value. Use this option for unique and primary keys for loads that require constraint checking.
Click Add to add a new column within your target object. The Remove option is only enabled for columns you create. If you do not want to copy a column defined in the source object, click Back to return to the Source Columns page and deselect the column.
Click Generate target physical name from source physical name to copy the physical target column names from the physical source column names.
Click Generate target physical name from source business name to copy the physical target column names from the logical source column names. The business names can exceed the Oracle physical name limit of 30 characters.
Click OK to rename the column automatically or click Cancel to rename it yourself.
If you rename a target column, then the new name is preserved regardless of how you choose to generate the target physical name. The renamed target column supersedes the generated target column name. For example, the source physical name of a table is MANDT and its business name is MANDT_Client. If you change the default target physical name from MANDT to MANDT_Client_Name, then this name is preserved.
Click Next.
The Summary and Copy page displays. Verify the information on this page.
Click Finish.
The Mapping Editor displays the source table mapped to the newly created target table. The bound target table displays under the TABLES node in the Warehouse Builder navigation tree.
Configuring a mapping containing an SAP source is the similar to configuring a mapping containing any other source:
Use the Operator properties window to set the loading properties.
Use the Configuration properties window to define the code generation properties.
If you intend to generate ABAP code, you set directory and initialization file settings in the Configuration properties window.
To set the loading type for an SAP operator:
From the Mapping Editor, right-click the SAP source operator and select Operator Properties from the popup menu.
The Operator properties window displays as shown in Figure 21-15.
Select a loading type from the Loading Type drop-down list. If you specify ABAP code as the step type for the mapping, Warehouse Builder generates SQL*Loader code as indicated in Table 21-1.
Close the Operator Properties window to save the setting.
This parameter enables you to choose the type of code you want to generate for your SAP mappings. If your source includes clustered or pooled tables, then you must select ABAP as the generated code.
To choose the step type:
Right-click the mapping and select Configure from the popup menu.
The Configuration Properties window displays as shown in Figure 21-16.
Click the Step Type field and click the ... button.
The Step Type dialog displays as shown in Figure 21-17.
From the drop-down list, select the type of code you want to generate: ABAP or PL/SQL scripts (available for transparent tables only).
Click OK.
The Configuration Properties window displays.
If you set the step type to ABAP, then you can expand the Runtime Parameters node on the Configuration Properties window to display settings specific to ABAP code Generation, as shown in Figure 21-18. These settings come with preset properties that optimize code generation and should not be changed. If you alter these settings, you may slow the code generation process.
The following lists the runtime parameters available for SAP mappings:
SAP System Version: Specifies the SAP system version number to which you want to deploy the ABAP code.
Staging File Directory: Specifies the location of the directory where the data generated by ABAP code resides.
Data File Name: Specifies the name of the data file created during code generation.
Control File Name: Specifies the name of the control file created during code generation.
Log File Name: Specifies the log file name created during code generation. This file is useful for debugging purposes.
File Delimiter for Staging File: Specifies the column separator in a SQL data file.
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.
Nested Loop: Specifies a hint to generate nested loop code for a join, if possible
Use Single Select: Indicates whether Warehouse Builder generates Select Single..., if possible.
Use Internal Table for Lookup only Table: Specifies whether you want to generate code using internal table, if possible.
Before loading SAP data into the repository, you must first check the Physical Configuration properties window to ensure that the directory parameters are set correctly.
The following list describes the parameters related to generating ABAP code to the Generation Target Directories:
ABAP Directory: Sets the storage location for ABAP scripts. The default is abap\.
ABAP Extension: Sets the file name extension for ABAP scripts. The default is .abap
ABAP Run Parameter File: Sets the run parameter file suffix for the parameter script in an ABAP job. The default is _run.ini.
ABAP Spool Directory: Sets the location where ABAP scripts are buffered during script generation processing. The default is abap\log\
You can generate PL/SQL code for a mapping containing an SAP transparent table just as you generated code for any other PL/SQL mapping in Warehouse Builder. However, you must generate ABAP code for pooled and cluster tables.
Warehouse Builder validates and generates the scripts required to create and populate the SAP source object.
When you generate code, a single script is generated for each physical object you want to create. For example, there is one script for each index you are creating. This is useful if you need to re-deploy a single object at a later time without re-deploying the entire warehouse.
For related information, see Chapter 13, "Deploying Target Systems"
To generate the scripts for SAP mappings:
From the Warehouse Module Editor menu, choose Module then Generate.
The Generation Mode dialog displays as shown in Figure 21-19.
Select a generation mode and click OK.
The Generation Results dialog displays as shown in Figure 21-20.
Click View Code.
The generated code displays in the Code Viewer as shown in Figure 21-21.
You can edit, print, or save the file using the code editor. Close the window to return to the Generation Results dialog.
From the Generation Results dialog, click Save as File to save the ABAP code to your hard drive.
The File System Deployment dialog displays as shown in Figure 21-22.
Click Save to save the generated scripts to a file system. You can save the ABAP code with any file extension. You can use the suffix .abap (for example, MAP1.abap) or any other naming convention.
When you generate an ABAP code for an SAP mapping, Warehouse Builder creates an ABAP program that loads the data. You must run this program from the SAP user interface. The program uploads the generated code and executes it on your SAP system. You can then load the data into your Warehouse Builder staging area before using SQL*Loader to upload the data into your warehouse tables.
To upload and execute the ABAP code on your SAP system using the SAP user interface:
Open the SAP user interface and specify op-code SE38.
Create a program to execute the ABAP code (for example, ZOWBTEST1). For detailed instructions on creating a program, refer to your SAP documentation. If you already have a program created for testing purposes, you can use it to execute the ABAP code.
The default selection is set to Source Code.
Figure 21-23 shows the SAP ABAP editor.
Click Change.
From the ABAP Editor screen, choose Utilities, then Upload/Download, and then Upload, as shown in Figure 21-24.
The Import from a Local File dialog displays as shown in Figure 21-25.
Specify the location of the ABAP code generated by Warehouse Builder.
Click Transfer.
Press F8 to execute the ABAP code. Or you can also choose Program and then Check before selecting Program and then Execute to run the code.
The ABAP code generated in Warehouse Builder is executed in the SAP application server.
Use FTP to fetch data from the SAP application server and send it to the Warehouse Builder staging area.
Use SQL*Loader to upload data into your warehouse tables. The following is an example of a command line:
SQLLDR USERID=scott/tiger CONTROL=abap_datactlfile.dat LOG=yourlogfile.log
Deployment of PL/SQL scripts for SAP transparent tables is the same as deployment of PL/SQL scripts for Oracle database sources. The PL/SQL scripts run in your Oracle data warehouse and perform remote queries to extract table data from the SAP application.
For related information, see Chapter 13, "Deploying Target Systems"
