Warehouse Builder employs a design-deploy-run model as an ETL solution. To integrate with Oracle E-Business Suite (EBS), Warehouse Builder users must import metadata from EBS before designing mappings to move and transform data. Specifically, during the design phase, Warehouse Builder users require access to metadata in the APPS schema. Later, in the execution phase, Warehouse Builder users must access the data in that schema.
Because direct access to the APPS production schema is most likely limited and restricted, you may define a user on the EBS database through which Warehouse Builder users can access only the relevant metadata and data.
To enable access to EBS data and metadata:
Create a user on the database hosting EBS. This user needs at least CONNECT and RESOURCE roles.
Grant access to the relevant metadata by running the script OWB_ORACLE_HOME\owb\cmi\ebs\owbebs.sql.
This script grants access to the following tables in the APPS schema that contain metadata for EBS tables, views, sequences, and keys: FND_APPLICATION, FND_APPLICATION_VL, FND_TABLES,FND_VIEWS, FND_SEQUENCES, FND_COLUMNS, FND_PRIMARY_KEYS, FND_FOREIGN_KEYS,FND_PRIMARY_KEY_COLUMNS, FND_FOREIGN_KEY_COLUMNS.
The script also creates a synonym in the user schema for each of the preceding objects.
Enable a user to extract data from the EBS database.
You can create a new user or enable the same user you created in the previous steps. For each object to enable data extraction, grant this user at least SELECT access to each object.
Warehouse Builder users can now import the E-Business Suite metadata as described in the importing section of Oracle Warehouse Builder User's Guide.
The Repository Browser connects to Warehouse Builder repositories and enables you to view metadata, run Web reports, perform lineage and impact analysis on your metadata, and audit runtime executions.
When you install Warehouse Builder from the Oracle Universal Installer, the Repository Browser is also installed and available in the languages you selected in the for Product Languages in the Oracle Universal Installer.
To verify the installation, start the Repository Browser listener and then the Repository Browser. For information on how to use the Repository Browser, refer to Oracle Warehouse Builder User's Guide.
If end users need to view the Repository Browser in a language that you did not select when initially installing Warehouse Builder, then copy the additional language fonts from the Warehouse Builder CD. From the fonts directory, copy the following fonts to the JDK directory under the OWB_ORACLE_HOME:
By default, Repository Browser sessions time out after 180 minutes, that is, 3 hours of inactivity.
To change this setting, update the session-config tag in
web.xml located at
By default, the tag displays as follows:
Warehouse Builder gives you the option to perform name and address cleansing on your data with the Name and Address operator. The Name and Address operator identifies and corrects errors and inconsistencies in name and address source data. The operator identifies inconsistencies by comparing input data to data libraries supplied by the third-party name and address cleansing software vendors. Purchase the data libraries directly from these vendors.
To install data libraries, refer to the installation instructions of the name and address cleansing software vendor of your choice. For the list of certified name and address cleansing software providers, refer to Oracle Technology Network at
To integrate with third-pary name and address data libraries:
Install Warehouse Builder as instructed in this guide.
Purchase data libraries from one of the certified vendors listed on My Oracle Support (formerly OracleMetaLink) at
Install and access a certified vendor's data libraries and Name and Address adapter following the vendor's instructions.
If you are installing in a Real Application Cluster environment, then you may be able to install the name and address adapter on many nodes to benefit from the parallelism and failover enabled by the Oracle RAC architecture. Check with your vendor to see if your purchase license allows a multiple-node installation.
You do not need to install the data libraries on multiple nodes. However, if you install all data libraries on one node, then performance may suffer due to file access time latency. Follow the recommendations of your name and address cleansing software vendor.
Design a mapping using the Name and Address operator to cleanse name or address data. Refer to Oracle Warehouse Builder User's Guide for information on designing mappings using the Name and Address operator.
If you plan to use Warehouse Builder process flows, then use Oracle Workflow to enable deployment. You can also deploy Warehouse Builder schedules to Oracle Workflow. For more information, read about schedules in Oracle Warehouse Builder User's Guide.
To enable integration with Oracle Workflow:
Locate the Oracle Workflow installation program.
For a Warehouse Builder 11g repository on an Oracle Database 11g , the installation script is provided with the Warehouse Builder 11g software at OWB_ORACLE_HOME/owb/wf/install.
For a Warehouse Builder 11g repository on an Oracle 10g Release 2 Database, you must download the Oracle Workflow 2.6.4 software.
Start the Oracle Workflow installation program.
For Windows, type the following at the command prompt:
C:\> cd owb_home\owb\wf\install
For UNIX, type the following in a UNIX shell:
$ cd owb_home/owb/wf/install
Complete the Workflow Configuration Assistant as follows:
|Oracle Workflow Setting||Value|
|Install Option||Server Only|
|Workflow Password||Specify a password for the account.|
|SYS Password||Type the SYS password for the database where you are installing Oracle Workflow.|
|TNS Connect Descriptor||Type hostname:port:service_name, where the values of hostname, port and service_name correspond to your database.
Note: Do not use a net service name as the assistant does not reference the tnsnames.ora file.
|LDAP Parameters||Depending upon your situation, you may need to specify LDAP parameters. See the Oracle Workflow documentation for details.|
|Mailer Parameters||Depending upon your situation, you may need to specify mailer parameters. See the Oracle Workflow documentation for details.|
|Tablespace||You can optionally change the tablespace.|
Click Submit to start the Workflow configuration process.
The configuration process can take several minutes. Check owb_home/owb/wf/install/wf.log for messages to follow the progress of the configuration process.
When the process is complete, the Workflow Configuration Assistant displays a message of completion.
Install the Workflow Client. (Optional)
The installation of Oracle Workflow client is optional because the Process Flow Editor in Warehouse Builder replaces its functionality. However, install Oracle Workflow client if you want to view the deployed Oracle Warehouse Builder processes in Oracle Workflow.
On the computer where you installed Oracle Warehouse Builder client, install the Oracle Workflow client from the CD for Oracle Workflow client.
Create a Workflow Proxy User.
When the Workflow instance is remote from the database hosting the Warehouse Builder repository, you need to create a proxy-user.
Within the database hosting the repository, use SQL Plus to create a user and grant it the OWB_USER role as a default. This enables the remote OWF instance to connect to the services provided by the Control Center.