1. Running ETL Processes for Business Intelligence Applications
  2. Additional Administration Tasks

3 Additional Administration Tasks

The Extract, Transform, and Load (ETL) administrators perform a variety of diagnostic, configuration, and deployment tasks.

Topics

Additional Administration Tasks

Here are some administration topics related to Oracle Business Intelligence Applications ETL processes.

Generating DDL Warehouse Scripts

The Business Analytics Warehouse tables are automatically deployed during the installation process when the Business Analytics Applications Suite Repository Creation Utility (RCU) executes a shipped Data Definition Language (DDL) script. The RCU does not prompt for which tablespace to assign to the individual tables and related indexes nor does it provide a mechanism for you to alter the shipped DDL. To introduce changes to the Business Analytics Warehouse data model, you use Oracle Data Integrator (ODI) to generate a new DDL script.

To generate DDL scripts and assign tablespaces to tables and indexes, see Generating DDL and Assigning Tablespaces to Tables and Indexes in Oracle Business Intelligence Applications Installation Guide.

Logging and Diagnostics

Oracle BI Applications Configuration Manager uses Oracle Diagnostic Logging. See Managing Log Files and Diagnostic Data in Oracle Fusion Middleware Administrator's Guide.

Creating ETL Tables for Siebel Sources

If your source system is Siebel and you are deploying one of the following offerings, you must create S_ETL tables in the Siebel database:

  • Oracle Marketing Analytics

  • Oracle Price Analytics

  • Oracle Sales Analytics

  • Oracle Service Analytics

To create the S_ETL tables, you generate a DDL script, which then must be provided to the Siebel database administrator, who must create the necessary tables in the Siebel database.

Note:

The database user used for the ETL connection requires all Data Manipulation Language (DML) privileges (SELECT, INSERT, DELETE, UPDATES) and DDL (TRUNCATE) privileges to S_ETL tables.

To create the S_ETL tables:

  1. Launch ODI Studio, and display the Designer navigator.
  2. In the Projects editor, expand the following folders: BIApps Project, Components, Sources, Siebel, Oracle, Generate Siebel DDL, Packages, Generate Siebel DDL, and Scenarios.
  3. Right-click GENERATE_SIEBEL_DDL Version 001, and then click Execute.
  4. Select the Context, ODI Agent, and Log Level, and then click OK.
  5. Indicate the information for each option.
    Option Description

    CREATE_SCRIPT_FILE

    Select Latest Value and enter Y in the Value field.

    REFRESH_MODE

    Enter FULL.

    CHAR_CLAUSE

    Provided for Unicode support. If set to Y, the CHAR clause is included in the DDL.

    RUN_DDL

    Enter N.

    SCRIPT_LOCATION

    Enter the path where you want the script to be created.

    The file name is similar to BIA_Siebel_Schema_DDL_<run ID>.sql.

    TABLE_MASK

    The default value % compares all tables.

Using a Database Link for ETL

Set up direct links to replace the default Open Database Connectivity (ODBC) links to improve ETL performance.

When the source database is Oracle, you can use Database Link Mode and create a compatible DB Link from the Oracle Warehouse to the source. The Loading Knowledge Module (LKM) assumes that a database link already exists with the following naming convention: <DATASERVER_NAME>.WORLD@DSN_<DSN_ID>. This database link must exist in the warehouse and be accessible using the warehouse credentials (user specified against the warehouse connection in ODI).

To use a database link for ETL:

  1. Have a database administrator create a private database link.
  2. In Configuration Manager, update the ETL_SRC_VIA_DBLINK parameter.
    1. Log in to Configuration Manager.
    2. On the Tasks bar, click the Manage Data Load Parameters link.
    3. In the Search pane, select the source system in the Source Instance list.
    4. Select Code in the Parameter list and enter ETL_SRC_VIA_DBLINK in the adjacent field.
    5. Click Search.
    6. In the Data Load Parameters list, click the default No value to open the Edit Parameter Value dialog.
    7. In the Parameter Value list, select Yes.
    8. Click Save and Close.