| Oracle® Warehouse Builder User's Guide 11g Release 1 (11.1) Part Number B31278-01 |
|
|
View PDF |
| Oracle® Warehouse Builder User's Guide 11g Release 1 (11.1) Part Number B31278-01 |
|
|
View PDF |
In Oracle Warehouse Builder you can access data from a variety of sources. You can interpret and extract metadata from custom as well as packaged applications and databases. As a precursor to extracting any data set, you first import its metadata.
This chapter includes the following topics:
The source systems for a data warehouse are typically transaction processing applications. For example, a sales analysis data warehouse typically extracts data from an order entry system that records current order activities.Designing the extraction process can be problematic. If the source system is complex and poorly documented, then determining which data to extract can be difficult. Moreover, the source system typically cannot be modified, nor can its performance or availability be adjusted. You can overcome these problems by first importing the metadata.
Metadata is the data that describes the contents of a given object in a data set. For example, the metadata for a table would indicate the data type for each column. After you import the metadata into Warehouse Builder, you can annotate the metadata and design an extraction strategy independently from the transaction processing application.
Before you import source metadata into Warehouse Builder, first create a module that will contain these metadata definitions. The type of module you create depends on the source from which you are importing metadata. For example, to import metadata definitions from an Oracle database, create an Oracle module. To import metadata definitions from flat files, create a flat file module.
Table 4-1 lists the data storage systems and applications that Warehouse Builder 11.1 can access. The table lists the supported sources and targets for each Location node as displayed in the Connection Explorer.
Table 4-1 Sources and Targets Supported in Warehouse Builder 11.1
| Location Node in the Connection Explorer | Supported Sources | Supported Targets |
|---|---|---|
|
Databases/Oracle |
Oracle DB 8.1, 9.0, 9.2, 10.1, 10.2, 11.1 |
Oracle DB 9.2, 10.1, 10.2, 11.1 |
|
Databases/Non-Oracle |
Any database accessible through Oracle Heterogeneous Services, including but not limited to DB2, DRDA, Informix, SQL Server, Sybase, and Teradata. Any data store accessible through the ODBC Data Source Administrator, including but not limited to Excel and MS Access. See "Loading Data Stored in a Microsoft Excel File" and "Connecting to SQL Server and Importing Metadata". Oracle E-Business Suite, see "Integrating with E-Business Suite" PeopleSoft 8, 9, see "Integrating with PeopleSoft" Siebel, see "Integrating with Siebel" |
Any database accessible through Oracle Heterogeneous Services, including but not limited to DB2, DRDA, Informix, SQL Server, Sybase, and Teradata. Any data store accessible through the ODBC Data Source Administrator, including but not limited to Excel and MS Access. To load data into spreadsheets or third-party databases, first deploy to a comma-delimited or XML format flat file. |
|
Files |
Delimited and fixed-length flat files. |
Comma-delimited and XML format flat files. See "Defining Flat Files and External Tables" in the Warehouse Builder Online Help. |
|
Applications |
SAP R/3: 3.x, 4.0x, 4.6x, 4.7, 5.0; mySAP ERP 2004; mySAP ERP 2005 (with SAP NetWeaver 2004, SAP BASIS 700 Components) |
None |
|
Process Flows and Schedules/Oracle Workflow |
None |
Oracle Workflow 2.6.2, 2.6.3, 2.6.4, 11i |
|
Process Flows and Schedules/Concurrent Manager |
None |
In general, you can deploy a schedule in any Oracle database location, version 10g or later. To deploy a schedule in Concurrent Manager, version 11i or 12i is required. However, for both versions, you must select 11i as the version when you create a location in Warehouse Builder. |
|
Business Intelligence/Discoverer |
None |
Discoverer 10.1 |
|
Databases/Transportable Module Source |
See "Moving Large Volumes of Data" in the Warehouse Builder Online Help. |
N/A |
|
Databases/Transportable Module Target |
N/A |
See "Moving Large Volumes of Data" in the Warehouse Builder Online Help. |
Warehouse Builder communicates with non-Oracle systems using Oracle Database Heterogeneous Services and a complementary agent. Heterogeneous Services make a non-Oracle system appear as a remote Oracle Database server. The agent can be an Oracle Transparent Gateway or the generic connectivity agent included with Oracle Database.
A transparent gateway agent is a system-specific source. For example, for a Sybase data source, the agent is a Sybase-specific transparent gateway. You must install and configure this agent to support the communication between the two systems.
Generic connectivity is intended for low-end data integration solutions and the transfer of data is subject to the rules of specific ODBC or OLE DB drivers installed on the client computer. In this case, you do not need to purchase a separate transparent gateway; you can use the generic connectivity agent included with the Oracle Database server. You must still create and customize an initialization file for your generic connectivity agent.
Whether you want to import metadata from a table, file, or application, the general process is the same and you always import metadata through a module.
Review the list of supported sources and targets in Table 4-1 to determine if the source from which you want to extract data is supported in Warehouse Builder.
If you have not already done so, create a location and module for the source metadata as described in "Creating Modules".
Right-click the module and select Import.
Follow the prompts in the Metadata Import Wizard.
The wizard prompts you for information based on the type of source you selected. For more information, see "Using the Import Metadata Wizard".
(Optional) For Oracle data objects, view the data stored in the data object using the Data Viewer. Right-click the object and select Data.
Subsequent Steps
After successfully importing the metadata, you can design ETL logic to extract the data from the source, transform the data, and load it into a target schema.
Over a period of time, the source metadata may change. If this occurs, you can use Warehouse Builder to identify the ETL logic that would be impacted and potentially made invalid due to a change in metadata.
See Also:
"Managing Metadata Dependencies" in the Warehouse Builder Online Help
To introduce the changed metadata into Warehouse Builder, right-click the desired module and select Import. As described in "Reimporting Definitions from an Oracle Database", Warehouse Builder recognizes when you are reimporting metadata.
Assume that there are numerous flat files stored across two different drives and directories on your source system. In the Connection Explorer, you create two locations that reference the directories in which the source data is stored. Now in the Project Explorer, right-click the Files node and select New to create a new module. Repeat this for each of the two directories. For each of the two modules, select Import. A wizard directs you on how to import one or more files into each module.
Figure 4-1 provides a diagrammatic representation of accessing flat file data stored in different drives or directories on your source system. Each location maps to a particular directory on your source system.
Figure 4-1 Importing Data From Flat File Sources
Modules are grouping mechanisms in the Project Explorer that correspond to locations in the Connection Explorer. A single location can correspond to one or more modules. However, a given module can correspond to only a single location at a time.
The association of a module to a location enables you to perform certain actions more easily in Warehouse Builder. For example, you can reimport metadata by reusing an existing module. Furthermore, when you deploy ETL processes in subsequent steps, modules enable you to deploy related objects together such as process flows.
To create a module:
Expand the Project Explorer until you find the node for the appropriate metadata type.
For example, if the source data is stored in an Oracle Database, then expand the Databases node to view the Oracle node. If the source data is in an SAP R/3 system, expand the Applications node to view the SAP node.
Right-click the desired node and select New.
The Create Module wizard opens. The wizard determines the correct integrator to use to enable access to the data store you selected.
On the Name and Description page, provide a name and an optional description for the module.
Click Next.
The Connection Information page is displayed.
Provide details about the location that is associated with this module.
The contents of the Connection Information page depend on the type of module you create. For more information about providing information on this page, click Help.
Click Next to display the Summary page.
Review the information you provided and click Back to modify entered values.
Click Finish.
During the course of using Warehouse Builder, you may need to associate a module with a new location. For example, assuming your production environment utilizes different locations than your development environment, you need to reassociate the modules.
To change the location associated with a module:
In the Project Explorer, select the module.
Click the Configure icon.
The Configuration Properties dialog box is displayed.
In the Identification folder, select a new value for the Locations property.
Importing is also known as reverse engineering. It saves design time by bringing metadata definitions of existing database objects into Warehouse Builder. You use the Import Metadata Wizard to import metadata definitions into modules.
The Import Metadata Wizard supports importing of tables, views, materialized views, dimensions, cubes, external tables, sequences, user-defined types, and PL/SQL transformations directly or through object lookups using synonyms.
Importing a table includes importing its columns, primary keys, unique keys, and foreign keys, which enable import of secondary tables. When you import an external table, Warehouse Builder also imports the associated location and directory information for the associated flat file.
You can import metadata definitions either from the Oracle Database catalog or Designer/2000 (Oracle Designer).
This section contains the following topics:
Use the Import Metadata Wizard to import metadata from a database into a module. You can import metadata from an Oracle Database, a non-Oracle Database, or a Designer repository.
To import definitions from an Oracle Data Dictionary:
Right-click a data source module name and select Import.
The Welcome page of the Import Metadata Wizard is displayed. This page lists the steps to import object metadata. Click Next to proceed with the import.
If you did not specify the location details for the Oracle module, Warehouse Builder displays a warning dialog box. This dialog box informs you that you must first specify the location details. Click OK. The Edit Oracle Database Location dialog box for the Oracle module is displayed. Use this dialog box to specify the location information. Clicking OK on this dialog box displays the Welcome page of Import Metadata Wizard.
Complete the following pages:
Use the Filter Information page to limit the search of the data dictionary. Use one of the following methods to limit the search:
Selecting the Object Types The Object Type section displays the types of database objects that you can import. This include tables, dimensions, external tables, sequences, materialized views, cubes, views, PL/SQL transformations, and user-defined types. Select the types of objects you want to import. For example, to import three tables and one view, select Tables and Views.
Search Based on the Object Name Use the Only select objects that match the pattern option to type a search pattern. Warehouse Builder searches for objects whose names match the pattern specified. Use % as a wild card match for multiple characters and _ as a wild card match for a single character. For example, you can type a warehouse project name followed by a % to import objects that begin with that project name.
Click Next and Warehouse Builder retrieves names that meet the filter conditions from the data dictionary and displays the Object Selection page.
Select items to import from the Available list and click the right arrow to move them to the Selected list.
To search for specific items by name, click the Find Objects icon that displays as a flashlight.
To move all items to the Selected Objects list, click Move All.
Importing Dependent Objects The Import Metadata wizard enables you to import the dependent objects of the object being imported. If you are reimporting definitions, previously imported objects appear in bold.
Select one of the following options to specify if dependent objects should be included in the import:
None: Moves only the selected object to the Selected list. No dependencies are imported when you select this option.
One Level: Moves the selected object and the objects it references to the Selected list. This is the default selection.
All Levels: Moves the selected object and all its references, direct or indirect, to the Selected list.
Click Next and the Summary and Import page is displayed.
Importing Dimensions When you import a dimension that uses a relational implementation, the implementation table that stores the dimension data is not imported. You must explicitly import this table by moving the table from the Available list to the Selected list on the Object Selection page. Also, after the import, you must bind the dimension to its implementation table. For more information on how to perform binding, see "Binding" .
This page summarizes your selections in a spreadsheet listing the name, type of object, and whether the object will be reimported or created. Verify the contents of this page and add descriptions, if required, for each of the objects.
If the objects you selected on the Object Selection page already exist in the module into which you are attempting to import them, you can specify additional properties related to the reimport. Click Advanced Import Options to specify options related to reimporting objects. The Advanced Import Options dialog box is displayed. For more information on the contents of this dialog box, see "Advanced Import Options".
Click Finish to import the selected objects. The Importing Progress dialog box shows the progress of the import activity. After the import completes, the Import Results page is displayed.
This page summarizes the import and lists the objects and details about whether the object was created or synchronized.
Click OK to accept the changes. To save an MDL file associated with this import, click Save. Click Undo to cancel the import. Warehouse Builder stores the definitions in the database module from which you performed the import.
If you have existing flat files to use as sources, you can import and then sample the metadata from these flat files. Use the Import Metadata Wizard to import metadata from flat files. This metadata must be imported into a file module.
To import flat file definitions:
Establish network connectivity to the files you wish to import.
If you are accessing the data files directly, and if the client and the data files reside on different types of operating systems, contact your system administrator to establish the required connectivity through NFS or other network protocol.
If the client and data files reside on Windows operating systems, store the data files on any drive the client computer can access.
Create a flat file module that will contain the imported flat file definitions.
Create a module for each folder in your file system from which you want to import files. See "Example: Importing Metadata from Flat Files".
When you create a flat file module, the location corresponding to this module is a folder in the file system from which metadata is being imported. Use the Connection Information Page of the Create Module Wizard to specify the folder that contains the source metadata.
Note that a flat file location does not include subfolders of the specified folder.
Right-click the flat file module and select Import.
The Import Metadata Wizard is displayed.
On the Filter Information page, filter file names by selecting one of the following options:
All Data Files: This option returns all the data files available for the directory you specified for the flat file module.
Data files matching this pattern: Use this option to select only data files that match the pattern you type. For example, if you select this option and enter (*.dat), only files with .dat file extensions will be displayed on the next wizard page. If you type % as part of a filter string, it is interpreted as a wild card match for multiple characters. If you type '_' as part of a filter string, it is interpreted as a wild card match for a single character.
On the Object Selection page, move the names of the files to be imported from Available Objects on the left to the Selected Objects section on the right.
Because inbound synchronization for flat files is not permitted, the available objects will never appear in bold like other objects when they are reimported. When you reimport flat files, you always need to sample the flat file objects again.
On the Summary and Import page, ensure that metadata is available for the selected flat files is available in the workspace. You cannot complete the import if the metadata is not present.
If the Status field contains a red x, metadata is not available in the workspace. For all such files, either select a file with a matching format in the workspace or sample the file.
Use the Same As field to select a file with a matching format.
To sample a file, select the file and click Sample. The Flat File Sample Wizard is launches. The Flat File Sample Wizard enables you to view a sample of the flat file and define record organization and file properties. You can sample and define common flat file formats such as string and ascii.
For files with complex record structures, the Flat File Sample Wizard may not be suitable for sampling the data. In such cases, see "Adding Existing Binary Files to the Workspace" in the Warehouse Builder Online Help.
Once you provide metadata information for all files you want to import, click Finish.
The wizard creates definitions for files, stores the definitions in the flat file module, and inserts the file names under the flat file module in the Project Explorer.
Reimporting your source database definitions enables you to import changes made to your source metadata since your previous import. You do not have to remove the original definitions from the workspace. Warehouse Builder provides you with options that also enable you to preserve any changes you may have made to the definitions since the previous import. This includes any new objects, foreign keys, relationships, and descriptions you may have created in Warehouse Builder.
To reimport definitions:
Right-click a data source module name and select Import.
The Welcome page for the Import Metadata Wizard is displayed.
Click Next.
The Filter Information page is displayed.
Complete the Filter Information Page and Object Selection Page, selecting the same settings used in the original import to ensure that the same objects are reimported.
The Summary and Import page displays. For objects that already exist in the workspace or ones that you are reimporting, the Reimport action is displayed in the Action column.
If the source contains new objects related to the object you are reimporting, the wizard requires that you import the new objects at the same time. For these objects, the Create action displays in the Action column.
Click Advanced Import Options and make selections. (Optional)
Click Finish.
Warehouse Builder reconciles and creates objects. When this is complete, the Import Results dialog box displays.
The report lists the actions performed by Warehouse Builder for each object.
Click Save to save the report. You should use a naming convention that is specific to the reimport.
Click OK to proceed.
Click Undo to undo all changes to your workspace.
The Advanced Import Options dialog box displays the options that you can configure while importing objects. This dialog box enables you to preserve any edits and additions made to the object definitions in the Warehouse Builder workspace.
By default, all options on this dialog box are checked. Clear boxes to have these objects replaced and not preserved.
For example, after importing tables or views for the first time, you manually add descriptions to the table or view definitions. If you want to make sure that these descriptions are not overwritten while reimporting the table or view definitions, you must select the Preserve Existing Definitions option. This ensures that your descriptions are not overwritten.
The contents of this dialog box depend on the type of objects being imported. For more information about the advanced import options for each type of objects, refer to the following sections:
Select these options for reconciling views or external tables:
Import descriptions: The descriptions of the view or external table are imported. Existing descriptions are not preserved.
Preserve repository added columns: The columns you added to the object in the workspace are preserved.
Select these options for reconciling tables:
Preserve repository added columns: Select this option to retain any columns added to the table in the workspace.
Preserve repository added constraints: The constraints you added to the table in Warehouse Builder are preserved.
Import indexes: Select this option to specify additional details about how indexes should be imported. Importing indexes consist of the following options:
Preserve repository added indexes: Select this option to retain any indexes added to the workspace table.
Import physical properties of indexes: Select this option to indicate how indexes should be imported. Select the Preserve repository added physical properties of indexes option below this option to specify that any physical properties added to the indexes should be preserved.
Import index partitioning: Select this option to indicate how index partitions should be imported. Select the Preserve repository added index partitioning option to specify that any index partitions added to the workspace table must be preserved.
Import Partitioning: Select this option to specify additional details about how partitions should be imported. Importing partitions contains the following options:
Preserve repository added partitioning: Select this option to retain all partitions added to the workspace table.
Import physical properties of partitioning: Use this option to indicate how the physical properties of partitions should be imported. Select Preserve repository added physical properties of partitioning to indicate that all physical properties of the partitions in the workspace table should be retained.
Import physical properties: Select this option to indicate how the physical properties of the table should be imported. Select the Preserve repository added physical properties option to specify that all physical properties added to the workspace table must be preserved.
Import descriptions: Select this option to import the descriptions of the table.
Select these options for reconciling object types:
Import descriptions: Select this option to import the descriptions of the object type.
Preserve repository added attributes: Select this option to retain the attributes added to the object type in the workspace.
SQL collection includes nested tables or Varrays.
Import descriptions: Select this option to import the descriptions of the queue table, advanced queue, streams queue, nested table, or Varray.
The Edit Module dialog box enables you to edit the name, metadata location, and the data location of a source module.
To update the database definitions:
Double-click any Oracle module.
The Edit Module dialog box displays. You can edit the metadata location as well as the data location of the database.
To edit the metadata location, click the Metadata Locations tab and specify the following:
Source Type: The source type identifies the location of the data and the metadata. It can be either Oracle Data Dictionary or Oracle Designer Repository. Select Oracle Data Dictionary if the metadata is stored in the default workspace of the Oracle Database. Select Oracle Designer Repository if the metadata is stored in an Oracle Designer repository.
Location: Identifies the location of the module. You can select a location from the list.
To edit the data location, click the Data Locations tab. You can either select from the existing locations or create a new location. To create a new location, click New. The Edit Oracle Database Location dialog box displays. Specify the details of the data location here.
Warehouse Builder enables you to import metadata stored in an E-Business Suite database using the Import Metadata Wizard.
Before You Begin
Contact the database administrator for the E-Business Suite database and request a user name and password for accessing the APPS schema. The DBA may have previously created a user by running the script owbebs.sql as described in the Oracle Warehouse Builder Installation and Administration Guide. If not, you will need to provide the DBA with a list of the tables, views, sequences, and keys from which you want to extract data.
Depending on the preference of the DBA, there may be a single user who extracts both, the metadata as well as the data. Or, there may be two separate users to access the metadata and data respectively.
After creating the E-Business Suite source module, you can import metadata definitions from E-Business Suite objects using the Import Metadata Wizard. This wizard enables you to filter the E-Business Suite objects you want to import and verify those objects. You can import metadata for tables, views, and sequences.
To import E-Business Suite metadata:
From the Project Explorer, expand the Applications node.
If you have not already done so, create an E-Business Suite module and that will contain the imported metadata.
To create an E-Business Suite module, right-click ORACLE_EBUSINESS_SUITE under the Applications node and select New. The Create Module Wizard is displayed. Follow the prompts in the wizard. Click Help on a wizard page for more information about that page.
Ensure that the location associated with the E-Business Suite module contains information needed to connect to the E-Business Suite source. If you created a location earlier, associate that location with the module being created by selecting that location on the Connection Information page. Or create a new location by clicking Edit on the Connection Information page of the Create Module Wizard. For more information about the details to be entered on this page, click Help.
Right-click the E-Business Suite source module into which you want to import metadata and select Import.
Warehouse Builder displays the Welcome page for the Import Metadata Wizard.
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".
Select a filtering method and click Next to proceed with the importing of metadata.
Select Business Domain and click Browse to open the Business Component Hierarchy dialog box.
The Business Component Hierarchy dialog box 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 box to select the E-Business Suite business domains that contain the metadata objects you want to import.
Select a business domain and click Show Entities.
The Folder dialog box displays a list of objects available in the selected business domain.
Review this dialog box to ensure that you are selecting the required objects and click OK to go back to the Business Component Hierarchy dialog box.
Some business domains can contain more than 1000 objects. Importing such a large amount of metadata can take from one to three hours or more, depending on the network connection speed and the processing power of the source and target systems.
Click OK.
The wizard displays the Filter Information page with the E-Business Suite business domain displayed in the Business Domain field.
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 box is displayed.
Review this dialog box to ensure that you are selecting the required tables.
Click OK.
The selected objects appear in the right pane of the Object Selection page.
Click Next.
The 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 workspace.
The time it takes to import the E-Business Suite metadata to the workspace depends on the size and number of tables and the connection between the E-Business Suite application server and the workspace. Importing 500 or more objects could take one to three hours or more, especially if you are connecting servers in separate Local Area Networks (LANs).
When the Import completes, the Import Results dialog box displays. Click OK to finish importing.
PeopleSoft applications provide ERP solutions. A PeopleSoft application consists of numerous modules, each pertaining to a specific area in an enterprise, such as Human Resource Management System (HRMS), Financials, and Material Management. You can use the Import Metadata Wizard to import metadata from Peoplesoft applications into Warehouse Builder.
After creating the PeopleSoft source module, you can import metadata definitions from PeopleSoft objects using the Import Metadata Wizard. This wizard enables you to filter the PeopleSoft objects you want to import and verify those objects. You can import metadata for tables, views, and sequences.
To import PeopleSoft metadata:
From the Project Explorer, expand the Applications node.
If you have not already done so, create a Peoplesoft module that will contain the imported metadata.
Right-click PEOPLESOFT8_9 and select New. The Create Module wizard is displayed. Click Help on a wizard page for more information about the page.
Ensure that the location associated with the PeopleSoft module contains information needed to connect to the PeopleSoft source. If you created a location earlier, associate that location with the module being created by selecting the location on the Connection Information page. Or create a new location by clicking Edit on the Connection Information page of the Create Module Wizard. For more information about the details to be entered on this page, click Help.
Right-click the PeopleSoft source module into which you want to import metadata and select Import.
Warehouse Builder displays the Welcome page for the Import Metadata Wizard.
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".
Select a filtering method and click Next to proceed with the importing of metadata.
Select Business Domain and click Browse to open the Business Component Hierarchy dialog box.
The Import Metadata Wizard displays Loading Progress dialog box while it is retrieving the business domains.
The Business Component Hierarchy dialog box 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 box to select the PeopleSoft business domains that contain the metadata objects you want to import.
Select a folder and click Show Entities.
The Import Wizard displays a list of objects in the selected business domain in the Folder dialog box.
Review this dialog box to ensure that you are selecting the required objects.
Some business domains can contain more than 1000 objects. 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.
Select Text String, where object.
In the Object Type section, select the types of objects you wish to import. You can select Tables, Views, and Sequences.
If you wish to select specific objects, type the object name in the text field. Create a filter for object selection by using the wildcard characters (%) for zero or more matching characters, and (_) for a single matching character.
For example, if you want to search the business domain for tables whose names contain the word CURRENCY, then type %CURRENCY%. If you want to refine the search to include only tables named CURRENCY and followed by a single digit, then type %CURRENCY_.
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 box is displayed.
Review this dialog box to ensure that you are selecting an appropriate number of tables.
Click OK.
The selected objects appear in the Selected pane of the Object Selection page.
Click Next.
The wizard displays the Summary and Import page.
The wizard imports definitions for the selected tables from the PeopleSoft Application Server, stores them in the PeopleSoft source module, and then displays the Summary and Import page.
You can edit the descriptions for each object by selecting the description field and typing a new description.
Review the information on the Summary and Import page and click Finish.
The PeopleSoft Connector reads the table definitions from the PeopleSoft application server and creates the metadata objects in the workspace.
The time taken to import PeopleSoft metadata to the workspace depends on the size and number of tables and the connection between the PeopleSoft application server and the workspace. Importing 500 or more objects could take one to three hours or more, especially if you are connecting to servers in separate Local Area Networks (LANs).
When the import completes, the Import Results dialog box displays. Click OK to finish importing metadata.
Siebel applications provide Customer Relationship Management (CRM) solutions. Warehouse Builder provides a Connector for Siebel systems that enables you to extract both metadata and data from your Siebel systems.
The Siebel Connector enables you to connect to any Siebel application, read its metadata, import the metadata into Warehouse Builder, and extract data from the system.
Before you import metadata definitions from Siebel, you must create a Siebel module. You can then import metadata definitions from Siebel using the Import Metadata Wizard. This wizard enables you to filter the Siebel objects you want to import and verify those objects. You can import metadata for tables, views, and sequences.
To import metadata definitions from Siebel:
Create a Siebel source module, as described in "Creating a Siebel Source Module".
Import metadata from Siebel, as described in "Importing Siebel Metadata".
From the Project Explorer, expand the Applications node.
Right-click Siebel and select New.
The Create Module wizard is displayed.
Click Next to display the Name and Description page.
Specify a name and an optional description for the Siebel source module and click Next.
The Connection Information page is displayed.
Specify the connection information for the Siebel source module and click Next.
Ensure that the location associated with the Siebel module contains information needed to connect to the Siebel source. If you created a location earlier, associate that location with the module being created by selecting the location on the Connection Information page. Or create a new location by clicking Edit on the Connection Information page of the Create Module Wizard.
For more information about the details to be entered on this page, click Help.
On the Summary page, review the options entered on the previous wizard pages. Click Back to modify any selections. Click Finish to create the Siebel source module.
Right-click the Siebel source module into which you want to import metadata and select Import.
Warehouse Builder displays the Welcome page for the Import Metadata Wizard.
Click Next.
The Filter Information page is displayed.
Select the objects to be imported and click Next.
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 Siebel 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 Siebel Metadata by Text String".
On the Objects Selection page, select the objects to be imported into the Siebel module and click Next.
You can choose whether you want to import tables with foreign key relationships for each object that you choose to import using the following options on this page:
None: Import only the objects in the Selected list.
One Level: Import the objects in the Selected list and any tables linked to it directly through a foreign key relationship.
All Levels: Import the objects in the Selected list and all tables linked to it through foreign key relationships.
The foreign key level you select is the same for all tables selected for importing.
Note:
Selecting All Levels increases the time it takes to import the metadata because you are directing the wizard to import tables that are related to each other through foreign key constraints. Select this option only if it is necessary.Review the summary information and click Finish to complete the import. To modify any selections, click Back.
After you import metadata for tables, views, or sequences from Siebel applications, you can use these objects in mappings.
Filtering Siebel Metadata by Business Domain
Select Business Domain and click Browse to open the Business Component Hierarchy dialog box.
The Import Metadata Wizard displays Loading Progress dialog box while it is retrieving the business domains.
The Business Component Hierarchy dialog box 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 box to select the PeopleSoft business domains that contain the metadata objects you want to import.
Select a folder and click Show Entities.
The Import Wizard displays a list of objects in the selected business domain in the Folder dialog box.
Review this dialog box to ensure that you are selecting the required objects.
Some business domains can contain more than 1000 objects. 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 Siebel Metadata by Text String
Select Text String, where object.
In the Object Type section, select the objects you wish to import. You can select Tables, Views, and Sequences.
If you wish to select specific objects, type the object name in the text field. Create a filter for object selection by using the wildcard characters (%) for zero or more matching characters, and (_) for a single matching character.
For example, if you want to search 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 SAP Connector enables you to connect to SAP application source systems and import the SAP source definitions into a project in the workspace.
You can then generate ABAP or PL/SQL code to extract, transform, and load data from SAP systems to your target system.
The SAP Connector enables you to import metadata object definitions from SAP Application data sources into the workspace. 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 section also describes how to extract and load SAP data into your target.
This section contains the following topics:
SAP application systems logically group database and metadata objects 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 metadata objects from the SAP application server.
The SAP Connector enables you to import metadata for SAP Business Domains or any of their related ABAP Dictionary objects.
With the SAP Connector, 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.
Required Files for Windows
The SAP Connector requires a dynamic link library file named librfc32.dll to use remote function calls on the client computer. This file is available on the SAP Application Installation CD. You need to copy this file to the following directory on your client system:
OWB_ORACLE_HOME\bin\admin
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:
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.
Required Files for Unix
The SAP Connector requires a dynamic link library file named librfccm.so to use remote function calls on the client computer. This file is available on the SAP Application Installation CD. You need to copy this file to the following directory on your client system:
OWB_ORACLE_HOME\owb\bin\admin
You also need to add OWB_ORACLE_HOME\owb\bin\admin to the Unix environment variable path: LD_LIBRARY_PATH.
Use the Create Module Wizard to create an SAP source module that stores data from an SAP source. You can choose either SAP R/3 version 3.x or SAP R/3 version 4.x system type as your source. After you select the application version, you need to set the connection information between the workspace and the SAP application server. You can set the connection either by selecting from existing SAP locations or by creating a new SAP location.
Note:
To create a connection to an SAP source, 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.
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:
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:
You must load the
librfc32.dll file before you can set the connection details. 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: 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.
In addition, both the connection types require the following connection information:
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 SAP extraction file using FTP.
FTP Directory: The directory where the SAP extraction file is stored. 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.
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 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.
If you have not already done so, create an SAP module that will contain the imported metadata.
Right-click the SAP node and select New. The Create Module Wizard is displayed. Follow the prompts and create an SAP module. Click Help on a wizard page for details about the information you must provide on that page.
Ensure that the location associated with the E-Business Suite module contains information needed to connect to the E-Business Suite source. If you created a location earlier, associate that location with the module being created by selecting that location on the Connection Information page. Or create a new location by clicking Edit on the Connection Information page of the Create Module Wizard. For more information about the details to be entered on this page, click Help.
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:
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 SAP business domains to locate the metadata you want to import. You can view a list of tables contained in the business domain and the names of the tables in the SAP application. For more information, see "Filtering SAP Metadata by Business Domain".
This filter enables you to search for tables by typing text string information in fields provided in the Filter Information page. This is a more specific search method if you are familiar with the contents of your SAP application database. For more information, see "Filtering SAP Metadata by Text String".
Select a filtering method and click Next to proceed with the importing of metadata.
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 two to ten minutes to list the business domains depending on the network location of the SAP application server, the type of LAN used, or the size of the SAP application database.Use the Business Domain Hierarchy dialog box to select the SAP business domains that contain the metadata objects 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 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 SAP business domain displayed in the Business Domain field.
Select Text String, where object and 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.
The Name matches field is not case sensitive, while the Description matches field is case sensitive.
You must type a text string in the selected Text String entry field. It cannot be 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 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.
Note:
Description searches are case sensitive whereas name searches are not case sensitive.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 objects and enables you to select the objects you want to import into the SAP 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 box is displayed.
Review this dialog box to ensure that you are selecting the required tables.
Click OK.
The selected objects appear in the Selected list 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 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 on 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. Importing 500 or more objects could take one to three hours or more, especially if you are connecting servers in separate Local Area Networks (LANs).
When the import completes, the Import Results dialog box displays. Click OK to finish importing metadata.
To reimport SAP objects, 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. In 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.
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 reimport metadata.
You can update an SAP module by editing its properties using the Edit Module dialog box.
To update SAP object definition:
From the Project Explorer, expand the Applications node and then the SAP node.
Right-click the SAP source object and select Open Editor.
The Edit Module dialog box is displayed.
Select the appropriate tab to edit the SAP object properties.
Name: Use the Name tab to specify a name and an optional description for the table. Use the description field, for example, to note the purpose of the module and how it relates to the information required by the end-users of the project. In addition to the rules listed in "Naming Conventions for Data Objects", the name must be unique across the module.
If necessary, change the status of the SAP object. Select Development, Quality Assurance, or Production.
Data Source: Use this tab to modify the application type.
Metadata Location: Use this tab to change the location of the metadata.
Data Locations: Use this tab to change the data location. You can either select from an existing list of available locations or specify a new location.
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 SAP Connector 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 is one important difference, which is that only Table, Filter, Joiner, and Mapping Input Parameter mapping operators are available for SAP objects.
Adding SAP Objects to a Mapping
To add an SAP object to a mapping:
From the Mapping Editor Palette, drag and drop the Table operator onto the Mapping Editor canvas.
The Add Table Operator dialog box displays.
Choose Select from existing repository objects and bind.
The field at the bottom of the dialog box 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 Table operator on the mapping canvas to represent the SAP table.
You can define it as you would with any other type of mapping operator.
Configuring a mapping containing an SAP source is similar to configuring a mapping containing any other source:
Use the Operator properties panel of the Mapping Editor to set the loading properties.
Use the Configuration properties dialog box to define the code generation properties.
If you intend to generate ABAP code, set the directory and initialization file settings in the Configuration properties dialog box.
To set the loading type for an SAP operator:
On the Mapping Editor, select the SAP source operator. The Table Operator Properties panel displays the properties of the SAP table operator.
Select a loading type from the Loading Type list. If you specify ABAP code as the language for the mapping, the SQL*Loader code is generated as indicated in Table 4-2.
Setting the Language Parameter
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 language:
Right-click the mapping and select Configure.
The Configuration Properties dialog box is displayed.
From the list in the Language field, select the type of code you want to generate: ABAP, SQL*LOADER, or PL/SQL scripts (available for transparent tables only).
Click OK.
Setting the Runtime Parameters
If you set the language to ABAP, then you can expand the Runtime Parameters node in the Configuration Properties dialog box to display settings specific to ABAP code generation. These settings come with preset properties that optimize code generation and should not be changed. Altering these settings can result in a slowing down of the code generation process.
The following runtime parameters are available for SAP mappings:
SAP System Version: Specifies the SAP system version number to which you want to deploy the ABAP code. For MySAP ERP instances, select SAP R/3 4.7.
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.
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.
Primary Foreign Key for Join: Specify the primary key to be used for a join.
Nested Loop: Specifies a hint to generate nested loop code for a join, if possible.
Use Select Single: Indicates whether Select Single is generated, if possible.
SAP Location: The location of the SAP instance from where the data can be extracted.
Background Job: Select this option if you wish to run the ABAP report as a background job in the SAP system.
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. 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.
To generate the scripts for SAP mappings:
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 displays 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.
From the Generation Results window, click Save as File to save the ABAP code to your hard drive.
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 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 4-2 shows the SAP ABAP editor.
Click Change.
From the ABAP Editor menu, select Utilities, then Upload/Download, and then Upload.
The Import from a Local File dialog box is displayed.
In the File Name field, specify the location of the generated ABAP code.
Click Transfer.
Press F8 to execute the ABAP code. Or you can also select Program and then Check before selecting Program and then Execute to run the code.
The ABAP code is executed in the SAP application server.
Use FTP to fetch data from the SAP application server and send it to the 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
After you create an SAP mapping, you must deploy the mapping to create the logical objects in the target location. Deploying an SAP mapping is similar to deploying any other object. To deploy an SAP mapping, right-click the mapping and select Deploy. You can also deploy the mapping from Control Center Manager. For detailed information about deployment, see "Deploying to Target Schemas and Executing ETL Logic".
When an SAP mapping is deployed, an ABAP mapping is created and stored it in the workspace.
It 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 installation.
Executing an SAP mapping is similar to executing other objects. Before executing the mapping, make sure that the mapping has been deployed successfully.
To execute an SAP mapping, you need to perform the following steps:
From Control Center Manager, right-click the deployed SAP mapping and select Start.
The ABAP mapping is executed on the remote SAP instance and the resultant file is stored under the file system of the SAP instance.
Use FTP to transfer the file from the remote SAP system to the local system. Make sure that you provide the correct user name and password for the FTP connection.
Use SQL*Loader to upload the file into Warehouse Builder.
The auditing information is written onto the workspace and can be viewed from the Repository Browser. For more information about auditing, see "Auditing Deployments and Executions" in the Warehouse Builder Online Help.
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 more information about deployment, see "Deploying to Target Schemas and Executing ETL Logic".
Warehouse Builder provides an end-to-end business intelligence solution by enabling you to integrate metadata from different data sources, designing and deploying it to a data warehouse, and making that information available to analytical tools for decision making and business reporting.
Warehouse Builder introduces Business Intelligence (BI) objects that enable you to integrate with Oracle Business Intelligence tools such as Discoverer. You can define BI objects in Warehouse Builder that enable you to store definitions of business views. You can then deploy these definitions to the Oracle Business Intelligence tools and extend the life-cycle of your data warehouse. The method you use to deploy business definitions depends on the version of Discoverer to which you want to deploy and the Warehouse Builder licensing option you purchased. For more information, see "Deploying Business Definitions to Oracle Discoverer".
This section contains the following topics:
Warehouse Builder enables you to derive and define Business Intelligence (BI) objects that integrate with analytical business intelligence tools, such as Oracle Discoverer. By deploying these BI definitions to your analytical tools, you can perform ad hoc queries on top of the relational data warehouse or define a dashboard on top of multidimensional data marts.
The BI objects you derive or define in Warehouse Builder represent equivalent objects in Oracle Discoverer. These definitions are stored under the Business Intelligence node on the Warehouse Builder Project Explorer.
The Business Intelligence node contains an additional node called Business Definitions. You start by first creating a Business Definition module to store the definitions to be deployed to Discoverer. For details, see "About Business Definitions".
Business intelligence is the ability to analyze data to answer business questions and predict future trends. Oracle Discoverer is a BI tool that enables users to analyze data and retrieve information necessary to take business decisions. Discoverer also enables users to share the results of their data analysis in different formats, including charts and Excel spreadsheets.
Discoverer uses the End User Layer (EUL) metadata view to insulate its end users from the complexity and physical structure of the database. You can tailor the EUL to suit your analytical and business requirements and produce queries by generating SQL. The EUL provides a rich set of default settings to aid report building.
Through BI objects, Warehouse Builder enables you to design a data structure that facilitates this data analysis. Business Intelligence objects in Warehouse Builder provide the following benefits:
Complete and seamless integration with Oracle Discoverer.
Advanced deployment control of metadata objects using the Warehouse Builder Control Center.
Complete, end-to-end lineage and impact analysis of Discoverer objects based on information in the Warehouse Builder workspace.
Ability to utilize Warehouse Builder metadata management features such as snapshots, multilanguage support, and command-line interaction.
You can integrate with Discoverer by deriving business definitions directly from your warehouse design metadata. Alternatively, you can also create your own customized business definitions in Warehouse Builder.
The business definition objects in Warehouse Builder are equivalent to the Discoverer EUL objects. When you derive business definitions from your existing design metadata, Warehouse Builder organizes the definitions in Item Folders that correspond to Folders in Discoverer. You can define joins and conditions for the Items Folders and select the Items they contain using the Warehouse Builder wizards and editors. Additionally, you can define Drill Paths, Alternative Sort Orders, Drills to Detail, and Lists of Values for the Items within the Item Folders.
Warehouse Builder also enables you to define any functions registered with Discoverer. You can also sort your definitions by subject area by defining Business Areas that reference multiple Item Folders. You can then deploy these Business Areas along with the business definitions to a Discoverer EUL using the Control Center.
See Also:
"Defining Business Intelligence Objects" in the Warehouse Builder Online Help
