Wednesday, March 28, 2012

Basic Administration Steps of Oracle 11g Warehouse Builder

In Oracle Warehouse Builder (OWB) ETL consists of two main areas:

  1. Mapping (or Data Flow Diagrams)
  2. Processes (or process flow diagrams which contain multiple mappings)
So, Administration also consists of two parts.

Detail information will be found at http://docs.oracle.com/. Here is the summary -

Basics (This is for Mapping)

After installing OWB, you will get two users - OWBSYS, OWBSYS_AUDIT. You have to unlock these users.

  1. Create Repository (which is called workspace in Oracle 11g) and make a user as an owner using Ropository Assistant
  2. Administrate the OWB using the repository browser. For this type of administration, use the user who is also owner of the repository. To use repository browser, you have to start the listener -
    • start the OWB browser listener - owb_home\owb\bin\win32\startOwbbInst.bat
    • stop the OWB browser listener - owb_home\owb\bin\win32\stopOwbbInst.bat
    • start the Web Repository Browser - owb_home\owb\bin\win32\openDB.bat
  3. Register other users to this repository
  4. Start/Stop Control Center Service when necessary

Extended (This is for process flow)

  1. Enable Integration with Oracle Workflow i.e. Installation of Oracle Work Flow. In Oracle 11g, the installation s/w is provided. But for previous version you will need Oracle Companion CD. Location of Installer for Oracle 11g - ORACLE_HOME/owb/wf/install> wfinstall.bat
  2. After installation a user will be created - OWF_MGR. Register the Work flow user, OWF_MGR in workspace - OWB_USER & execute any procedure
  3. Control Center Agent (CCA) manages deployment and execution of the mappings that are based on Code Templates. CCA is not part of the Control Center service hosted within the Oracle Database. It is very much useful for Control Center Agent topology. In non-Oracle database host, this is the only part of OWB that can be used.
You can see also http://gerardnico.com/wiki/owb/owb.

If you want to create and maintain Analytical Workspace (AW) in your database, then you must have two more roles. They are - OLAP_USER and OLAP_DBA. OLAP_USER role allows the user to create AW in its own schema. But for other, you have to utilized OLAP_DBA role.