About
If you are interested in doing ETL with Oracle eBusiness Suite (EBS) using Oracle Warehouse Builder (OWB), please read on.
Articles Related
Setting up metadata extraction user
OWB employs a design-deploy-execute model as an ETL solution. As such, you need to import metadata from Oracle eBusiness Suite first before you can create mappings to perform ETL.
In EBS, the APPS schema holds key metadata of its tables, views and sequences.
However, in a production environment, it is assumed that you seldom get access to the APPS schema. Therefore, you need to set up a metadata extraction user so that OWB can extract metatdata out instead of from the APPS schema directly.
Here is how you do it:
- Ask your DBA to create a user on the database hosting EBS. This user will need at least CONNECT and RESOURCE roles.
- Ask your DBA to run the accompanying script (owb_home\owb\cmi\ebs\owbebs.sql). This script performs the following:
- These tables are owned by the APPS schema and contain metadata of tables, views, sequences and keys in EBS. Grant SELECT access to:
- 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.
- In the metadata extraction user's schema, create a synonym for each of the above objects.
Setting up data extraction user
The user you created in the last section only gets you the metadata. In the end, you want to extract data out by creating mappings in OWB to perform the necessary ETL. As a result, you need a user on the EBS database where you could extract data.
You could use the same user that you created for metadata extraction purpose, or you could use a different user.
You will need to provide your DBA with a list of objects that you need access in the EBS database. You DBA will then need to grant this data extraction user at least SELECT access to each object on your list.
Using OWB to do ETL with Oracle eBusiness Suite
- You first create an Oracle location to do metadata extraction from your EBS database. The user name should be the name of the metadata extraction user.
- If your data extraction user is different from the metadata extraction user, you need to create a second location for that purpose.
- Create a module under the “Oracle eBusiness Suite” node, assigning the location for metadata extraction purpose to the module.
- If you have a data extraction location, you need to edit the module and go to the “Data Location” tab, add the data extraction location to the list of locations, and choose it as the default.
- You then configure the module by doing right-click “Configure” in the console tree, and make sure that the “Location” property is set to the data extraction location.
- Please note that steps 4 & 5 are only needed if you extract metadata and data through 2 different users.
- Import the metadata into the module via the Import wizard.
- Design your ETL process with mapping and process flow editor.
- Deploy and execute your mappings and process flows.
Documentation / Reference
- owb_home\owb\cmi\ebs\readme.txt