Install ETL Component

This page will guide you in installing the Oracle Data Integrator-based ETL Component on the Oracle Utilities Analytics Warehouse (OUAW).

Note:

All the tasks below need to be followed in sequential order.

Prerequisites

Before installing the OUAW ETL component, verify the following:

  • Ensure OCI GoldenGate is provisioned.

  • Ensure Target (ADB) GoldenGate connection is created, tested and assigned to provisioned GoldenGate Deployment.

  • Ensure Oracle Data Integrator: Classic (14.1.2.0) is provisioned from OCI Marketplace.

See Perform Pre-Installation Steps for OUAW for more information.

Create Repositories

This section details the process to create the ODI MASTER and WORK repositories.

Create Master Repository

  1. Log in to ODI VNC and navigate to the /u01/oracle/mwh/odi/studio/bin directory.

    cd /u01/oracle/mwh/odi/studio/bin

  2. Open ODI Studio.

    ./odi

  3. Navigate to New > Create a New Master Repository and click OK.


    Create a master repository.

  4. In Database Connection, provide the ADB details.

    Field Name and Description Value

    Credential File

    Location of ADB wallet zip
    Connection Details
    ADB Service Name

    Note:

    Select _high
    User OUA_MASTER
    Password OUA_MASTER Password
    DBA User ADB ADMIN User
    DBA Password ADMIN Password


    Enter the ADB details.

  5. Click Test Connection and click OK then click Next.


    Test the ADB connection.

  6. Enter the Supervisor Password. and click Next.


    Enter the supervisor password.

  7. Select Internal Password Storage as the password storage and click Finish.


    Select the Internal Password Storage option.

  8. The application creates the Master Repository.


    Master repository is created.

Create Work Repository

Note:

Delete the existing connection which is created automatically during ODI Provisioning. New Master and Work connections will be created later.
  1. Open ODI Studio in VNC.

  2. Create a Master Repository connection by navigating to Connect to Repository > +.


    Create a master repository connection.

  3. Enter the Supervisor login details in Oracle Data Integrator Connection.

    Enter the OUA Master and ADB details in Database Connection (Master Repository).

    Select Master Repository Only from Work Repository.


    Enter the Repository Connection details.

  4. Click Test Connection then click OK.


    Test the repository connection.

  5. Log in to Master Repository using the created connection.


    Log in to the master repository using the connection.

  6. Navigate to Topology > Repositories and right click Work Repositories then click New Work Repository.


    Create a work repository.

  7. Enter the following ADB and OUA_WORK details.

    Field Name and Description Value

    Credential File

    Location of ADB wallet zip
    Connection Details
    ADB Service Name

    Note:

    Select _high
    User OUA_WORK
    Password OUA_WORK Password


    Enter the ADB and OUA Work details.

  8. Click Test Connection then OK.


    Test the work repository connection.

  9. Enter the following ODI Work Repository details.

    Field Name and Description Value

    Name

    OBIU
    Password

    Keep blank
    Work Repository Type Development


    Enter the ODI Work Repository details.

  10. Click Finish then No.


    Work repository is created.

    The application creates the Work Repository.


    Work repository is created.

Create Work Repository Connection

  1. Log in to ODI Studio in VNC.

  2. Create a Work Repository connection by navigating to Connect to Repository > +.

  3. Enter the Supervisor details in Oracle Data Integrator Connection.

    Field Name and Description Value

    Login Name

    SUPERVISOR Name
    User

    SUPERVISOR
    Password SUPERVISOR Password


    Enter the supervisor login details.

    Enter the OUA Master and ADB details in Database Connection (Master Repository).

    Field Name and Description Value

    User

    OUA_MASTER
    Password

    OUA_MASTER Password
    Driver List Oracle JDBC Driver
    Driver Name oracle.jdbc.OracleDriver
    Credential File Select ADB Wallet zip
    Connection Details Select ADB Service. For example, ADB_Service_high

    From Work Repository, select Work Repository (OBIU) and click OK.


    Enter the OUA Master and ADB detail.

  4. Click Test then click OK.

  5. Click OK.

Download OUAW ETL Component

  1. Download Oracle Utilities Analytics Warehouse v25.10.0.0 PaaS Multiplatform zip from edelivery, copy to the ODI Instance virtual machine, and unzip to a dedicated folder.

  2. After unzipping the package, you will get these folders:
    • AdminTool Component
    • Dashboard Component
    • ETL Component

    The ETL Component folder contains all the ETL Installation file and folders.


    ETL installation files and folders

    Note:

    Ensure to note the relative directory path of all folders in the ETL Component folder which will be used later.

Configure Physical and Logical Architecture for Target in ODI

Note:

All Physical Data Server and Physical/Logical schemas and ODI Studio activity should be performed by logging in to Work Repository.

Physical Data Servers (Oracle)

  1. Log in to Work Repository with the created Work Connection.

  2. Navigate to Topology > Physical Architecture > Technologies and right click Oracle then select New Data Server.


    Create a new data server.

  3. Enter the Data Server details and click Save then click OK.

    Field Name and Description Value

    Name

    B1
    User

    MDADM
    Password MDADM Password
    Credential File Location of ADB Wallet zip
    Connection Details ADB Service Name


    Enter data server details.

Once Physical Data Server (Oracle) - B1 creation is completed. You can find it under the Physical Architecture > Oracle tab.


Physical Data Server (Oracle) - B1

Physical Schema

This section provides the steps to create Physical Schemas for MDADM, DWADM, DWSTAGE, OUA_MASTER, OUA_WORK under physical data server (B1).

  1. Navigate to Topology > Physical Architecture > Technologies > Oracle and right click Oracle Physical Data Server (B1) then select New Physical Schema.


    Create physical schema for created physical server.

  2. Enter the Physical Schema details (for example, MDADM) and click Save then OK (information for an unspecified context for schema).

    Field Name and Description Value

    Schema (Schema)

    Select Schema as per Physical Schema Name for example, MDADM
    Schema (Schema Work)

    Select Schema as per Physical Schema Name for example, MDADM


    Enter the physical schema details.

  3. Repeat steps 1 - 2 to enter the Physical Schema for DWADM, DWSTAGE, OUA_MASTER, and OUA_WORK.

  4. After all the above physical schemas are created, you will find these schemas under Oracle Physical Data Server > B1.


    Physical Schemas Created

Physical Data Servers (Oracle GoldenGate)

  1. Navigate to Topology > Physical Architecture > Technologies and right click Oracle GoldenGate then select New Data Server.


    Create Oracle GoldenGate data server.

  2. Enter the JAgent details and click Save then OK (information to register at least one physical schema for the data server).

    Field Name and Description Value

    Name

    JAgent Name
    Host

    **** (provide asterisk, this a dummy value)
    JMX Port -1
    Manager Port -1
    JMX User **** (provide a dummy value)
    Password **** (provide a dummy value)
    Installation Path **** (provide a dummy value)


    Enter the physical data GoldenGate JAgent server details.

  3. After Physical Data Server (Oracle GoldenGate) > B1-GG is created, you will find this under Physical Architecture > Oracle GoldenGate.


    Created Physical Data Server (Oracle GoldenGate) – B1-GG

Physical Schema (File)

Note:

This section provides the steps to create a physical schema under Physical Data Server (File) – FILE-GENERIC.

  1. Navigate to Topology > Physical Architecture > Technologies > File and right click File Generic then select New Physical Schema.


    Create a physical schema for physical data server (File).

  2. Enter the Physical Schema (File) details and click Save then OK (information for an unspecified context for schema).

    Field Name and Description Value

    Directory (Schema)

    Update the Seeded-Metadata folder location for example, < /u01/OUAW_Deployment/ETL Component/BI_V2510000_39/Seeded-Metadata>
    Directory (Work Schema)

    Update the Seeded-Metadata folder location for example, < /u01/OUAW_Deployment/ETL Component/BI_V2510000_39/Seeded-Metadata>


    Enter the physical schema (File) details.

Logical Schema (Oracle)

This will help you to create individual logical schemas Journal, Master, Metadata, Replication, Repository, Source, Staging, Target under Oracle Logical Schema.

  1. Navigate to Topology > Logical Architecture > Technologies and right click Oracle then select New Logical Schema.


    Create an Oracle logical schema.

  2. Enter the Logical Schema (for example, Journal) details and click Save.

    Field Name and Description Value

    Name

    Logical Schema Name


    Logical Schema Details

  3. Repeat step 1- 2 to create the logical schema for Metadata, Replication, Repository, Source, Staging, and Target.


    Other created Oracle logical schemas.

Logical Schema (Oracle GoldenGate)

  1. Navigate to Topology > Logical Architecture > Technologies and right click Oracle GoldenGate then select New Logical Schema.


    Create a GoldenGate logical schema.

  2. Enter the Logical Schema details for OGG Source and click Save.

    Field Name and Description Value

    Name

    OGG Source
    Process Type Capture


    Logical Schema Oracle GoldenGate Details

  3. Right click Oracle GoldenGate and select New Logical Schema then enter the Logical Schema details for OGG Target and click Save.

    Field Name and Description Value

    Name

    OGG Target
    Process Type Delivery
    Target DB Logical Schema Replication


    Logical Schema Oracle GoldenGate Target Details

  4. The application creates the OGG Source and OGG Target logical schemas.


    Application created the OGG Source and Target logical schemas.

Logical Schema (File)

  1. Navigate to Topology > Logical Architecture > Technologies and right click File then select New Logical Schema.


    Create a logical schema (File).

  2. Enter the Logical Schema details and click Save.

    Field Name and Description Value

    Name

    Configuration


    Logical Schema File Details

Map Physical and Logical Schemas

This section will help you to map the Physical and Logical Schemas through the Contexts.

  1. Navigate to Topology > Contexts and right click Global then select Open.


    Map physical and logical schemas using Contexts.

  2. Enter the mapping details and click Save.

    Field Name and Description Value

    Configuration

    Select Seeded-Metadata Location from drop-down
    Master B1.OUA_MASTER
    Metadata B1.MDADM
    Repository B1.OUA_WORK
    Staging B1.DWSTAGE
    Target B1.DWADM


    Enter mapping details.

Flex Fields Configuration

This provides the steps to define the Flex Fields on the Scenario, Datastore, and Attribute objects.

  1. Navigate to Security > Objects and right click Scenario then select Open.


    Configure flex fields on Scenario objects.


    Enter Flex Fields context details.

  2. Enter the Scenario Flex Fields details and click Save.

    Field Name and Description Value

    Name

    B1_NMS_SCEN_NAME
    Code B1_NMS_SCEN_NAME
    Technology All
    Type String


    Scenario Flex Field Configuration Details

Flex Fields on the Datastore

This provides the steps to define the Flex Fields on the Datastore object.

  1. Navigate to Security > Objects and right click Datastore then select Open.


    Configure flex fields on Datastore objects.

  2. Enter the Datastore Flex Fields details and click Save.

    Field Name and Description Value

    Name

    B1_OBJECT_TYPE
    Code B1_OBJECT_TYPE
    Technology Oracle
    Type String
    Field Name and Description Value

    Name

    B1_TARGET_ENTITY_NAME
    Code B1_TARGET_ENTITY_NAME
    Technology Oracle
    Type String


    Flex Fields on Datastore

Flex Fields on the Attribute

This provides the steps to define the Flex Fields on the Attribute object.

  1. Navigate to Security > Objects and right click Attribute then select Open.


    Configure flex fields on Attribute objects.

  2. Enter the Attribute Flex Fields details and click Save.

    Field Name and Description Value

    Name

    B1_USE_FETCH_COLS
    Code B1_USE_FETCH_COLS
    Technology Oracle
    Type String
    Default N


    Flex Fields on Attribute

Import ODI Objects

Note:

Ensure all ODI import jobs should be executed by login Work Repository via ODI Studio.

  1. Ensure you have the ODI Import Objects folder on the ODI Instance which is extracted from Oracle Utilities Analytics Warehouse v25.10.0.0 PaaS Multiplatform zip. See Download OUAW ETL Component for more information.


    Make sure the ODI Import Object folder exists.

  2. Execute these additional SQL grants in ADB.

    1. Login to ADB with ADMIN user.

      sqlplus ADMIN/'<ADMIN Password>'@<ADB Service Name>

    2. Execute these grants.

      GRANT ALTER ANY TRIGGER TO MDADM;

      GRANT DROP ANY TRIGGER TO MDADM;

  3. Navigate to Designer and right click Load Plans then select Import Scenario.


    Import a scenario.

  4. Enter the Import Scenario details.

    Field Name and Description Value

    Import Type

    Synonym Mode INSERT_UPDATE
    File import directory

    Location of the ODI Import Objects folder. See Download OUAW ETL Component for more information.

    Select the following scenarios to import and click OK then Yes to confirm the import scenario details.
    • B1_IMPORT_OBJECTS_Version_001
    • B1_IMPORT_ODI_OBJECTS_Version_001
    • B1_IMPORT_ODI_VAR_OBJECTS_Version_001


    Select the scenarios to import.

  5. The application displays the imported scenarios under Designer > Load Plans and Scenarios.


    List of imported scenarios.

  6. Execute these SQLs to insert data into the B1_PATCH, B1_PATCH_OBJECT, and B1_PATCH_OBJECT_TYPE tables.

    1. Log in to ADB with MDADM user.

      sqlplus MDADM/'<MDADM Password>'@<ADB Service Name>

    2. Run the SQL B1_PATCH.sql (SQL script is inside the ETL Component folder from Oracle Utilities Analytics Warehouse v25.10.0.0 PaaS Multiplatform Package).

      @B1_PATCH.sql

  7. Log in to ODI Studio and run each imported scenario by performing the following steps.

    Note:

    Run below scenarios in sequential order.
    B1_IMPORT_ODI_VAR_OBJECTS Version 001

    1. Navigate to Designer > Load Plans and Scenarios and right click B1_IMPORT_ODI_VAR_OBJECTS Version 001 then select Run.

      Enter the Run details and click OK.

      Field Name and Description Value

      Context

      		 Global
      Logical Agent

      Local


      Configure B1_IMPORT_ODI_VAR_OBJECTS Version 001 Run Details

    2. Set the Session Variables (Variable values) and click OK.

      Field Name and Description Value

      Name

      		 GLOBAL.B1_OBJECTS_DIR
      Latest Value

      Uncheck the box
      Value

      Location of BI_V<OUAW Version>_<Build Number> Directory.

      Example: < /u01/OUAW_Deployment/ETL Component/BI_V2510000_39/ >

      Note:

      Ensure to put “/” at the end of directory.

      Note:

      Select the other three Latest Value checkboxes as per the image below.


      Set the variable values of the scenario.

    3. In the Operator tab, check the status of the job for its completion.

      Example:


      Verify completion of the scenario run.

    B1_IMPORT_OBJECTS Version 001

    1. Navigate to Designer > Load Plans and Scenarios and right click B1_IMPORT_OBJECTS Version 001 then select Run.


      B1_IMPORT_OBJECTS Version 001 Run Configuration

      Enter the Run details and click OK.


      B1_IMPORT_OBJECTS Version 001 Run Details

      Field Name and Description Value

      Context

      		 Global
      Logical Agent

      Local

    2. Set the Session Variables (Variable values) and click OK.

      Field Name and Description Value

      Name

      		 GLOBAL.B1_OBJECTS_DIR
      Latest Value

      Uncheck the box
      Value

      Location of BI_V<OUAW Version>_<Build Number> Directory.

      Example: < /u01/OUAW_Deployment/ETL Component/BI_V2510000_39/ >

      Note:

      Ensure to put “/” at the end of directory.

    3. In the Operator tab, check the status of the job for its completion. Ensure none of the jobs are in failed status.


      B1_IMPORT_OBJECTS Version 001 Completion Status

    B1_IMPORT_ODI_VAR_OBJECTS Version 001

    Note:

    B1_IMPORT_ODI_VAR_OBJECTS Version 001 needs be executed again.

    1. Navigate to Designer > Load Plans and Scenarios and right click B1_IMPORT_ODI_VAR_OBJECTS Version 001 then select Run and click OK.

    2. Set the Session Variables (Variable values) and click OK.

      Field Name and Description Value

      Name

      		 GLOBAL.B1_OBJECTS_DIR
      Latest Value

      Uncheck the box
      Value

      Location of BI_V<OUAW Version>_<Build Number> Directory.

      Example: < /u01/OUAW_Deployment/ETL Component/BI_V2510000_39/ >

      Note:

      Ensure to put “/” at the end of directory.
      Field Name and Description Value

      Name

      		 FM_B1_WORK_REP_DETL
      Latest Value

      Select the checkbox
      Value

      OBIU (This will be auto reflected.)

      Note:

      Select the other two Latest Value (FW.B1_RETRY_INITIAL and FW.B1_SET_RETRY_INITIAL) checkboxes.

Apply ETL Single Fix

  1. Download OUAW 25.10.0.0.0 PaaS Single Fix (Patch 38976085) from My Oracle Support and unzip it. Copy the ETL Component folder to ODI Instance virtual machine.


    ETL Component Folder

    Note:

    Make sure to remember the path of SCEN_B1_SYNC_MODEL_PAAS_Version_001.xml file.
  2. Log in to ODI Studio to Work Repository.

  3. Navigate to Designer > Load Plans and Scenarios > Framework > Scheduler and right click B1_SYNC_MODEL_PAAS then select Import > Import Scenario.


    Import a scenario.

  4. Enter the Import Scenario details, select the SCEN_B1_SYNC_MODEL_PAAS_Version_001 checkbox and click OK then click Yes to confirm the import scenario details.

    Field Name and Description Value

    Import Type

    		 Synonym Mode INSERT_UPDATE
    File import directory
    Path of SCEN_B1_SYNC_MODEL_PAAS_Version_001.xml file for example, < /u01/OUAW_Deployment_SF/25.10.0.0.0_SF/>.

    Note:

    Make sure to put “/” at the end of directory as per above screenshot.


    Enter details of imported scenario.

Perform Initial Setup for Star Schema

  1. Log in to the ODI Studio Work Repository.

  2. Navigate to Designer > Load Plans and Scenarios > Framework > Scheduler and right click B1_INITIAL_SETUP_PKG Version 001 then select Run.


    Set up the Star schema.

  3. Enter the Run details and click OK.

    Field Name and Description Value

    Context

    		 Global
    Logical Agent

    Local (No Agent)
    Log Level For example, 6

  4. Navigate to Designer > Load Plans and Scenarios and right click B1_INITIAL_SETUP_PKG Version 001 then select Variable Values.

  5. Enter the Variable Values details, select the Latest Value checkboxes then click OK.


    Enter the variable values for the scenario run.

  6. In the Operator tab, check the status of job for its completion.


    Initial Setup for Star Schema

Increase b1_text_array Type Length from 4000 to 8000 Characters

  1. Login to ADB with MDADM User.

    sqlplus MDADM/<MDADM Password>@<ADB Service Name>

  2. Run this SQL to increases the b1_text_array type length from 4000 to 8000 characters.

    create or replace TYPE MDADM.b1_text_array IS TABLE OF VARCHAR2(8000);/

Update MDADM.B1_PATCH Table

Update the APPLIED_DTTM value to the current SYSDATE in the B1_PATCH table after the successful deployment of the ETL Component.

  1. Login to ADB with MDADM User.

    sqlplus MDADM/<MDADM Password>@<ADB Service>

  2. Execute this SQL.

    UPDATE MDADM.B1_PATCH SET APPLIED_DTTM = SYSDATE WHERE PROD_VERSION = '25.10.0.0.0';

  3. Commit the changes.

    Commit;

Create the WebLogic Domain for Oracle Data Integrator Agent

Refer to Configuring the Domain for Collocated/Java EE Agent on Oracle Cloud Marketplace for the steps to create and configure an ODI Domain.

Create Physical Agent (OracleDIAgent)

  1. Log in to the ODI Studio Work Repository.

  2. Navigate to Topology > Physical Architecture and right click Agents then select New Agent.


    Create the physical agent for Oracle DI Agent.

  3. Enter the Agent details and click Save.

    Note:

    Agent values are case sensitive.
    • Name: OracleDIAgent
    • Host: Your ODI Host
    • Port: ODI Managed Port
    • Defaults are applied to the rest of the Agent parameters.
  4. Restart the ODI Admin and Managed servers.

  5. Test the Oracle DI Agent connection.


    Test the Oracle DI Agent connection.

Create Logical Agent (WLS_AGENT)

  1. Log in to the ODI Studio Work Repository.

  2. Navigate to Topology > Logical Architecture and right click Agents then select New Agent.


    Create a logical agent.

  3. Enter the Agent Name (WLS_Agent) and map Physical Agents (OracleDIAgent) with Contexts then click Save.
    • Name: WLS_AGENT
    • Development: OracleDIAgent
    • Global: OracleDIAgent
    • Production: OracleDIAgent
    • Test: OracleDIAgent


    Enter the logical agent details and map it to physical agents using Contexts.

Enable ODI Scheduler

  1. In Oracle Data Integrator Studio, navigate to Designer > Load Plans and Scenarios > Framework > B1_RUN_ALL Version 001 and right click Scheduling then select New Scheduling .

  2. Enter the Scheduling details.

    Field Name and Description Value

    Context

    		  
    Logical Agent WLS_AGENT
    Log Level For example, 5

  3. To specify how often the scheduler should run, navigate to the Execution Cycle tab of the Scheduler and select the Many Times option. Set the interval between repetitions.

  4. Navigate to Topology > Agents and right click OracleDIAgent then select Update Schedule.


    Update the Oracle DI Agent's schedule.

  5. Select OBIU in the Select Repositories dialog and click OK.


    Select the repositories from which to refresh the schedules.

Modify Maximum Table Name Length Parameter

Modify the value of the Maximum Table Name Length parameter to 128 in ODI.

  1. In Oracle Data Integrator Studio, navigate to Topology > Physical Architecture > Technologies and right click Oracle then select Open.

  2. Navigate to the Advanced tab and set the value of Maximum Table Name Length to 128.

  3. Click Save.