Skip Headers
Oracle® Fusion Middleware User's Guide for Oracle Business Intelligence Data Warehouse Administration Console
11g Release 1 (11.1.1)

Part Number E14849-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

13 Integrating DAC With Other ETL Tools

This chapter provides information about how to integrate DAC with ETL tools other than Informatica.

The external executor framework enables you to integrate DAC with ETL engines other than Informatica. The general process for integrating DAC and an ETL engine is as follows:

The required interfaces are defined in dac-external-executors.jar, which is stored in the Oracle_Home\dac\lib directory. Javadocs provide information about the interfaces and methods, and are located in the Oracle_Home\dac\documentation\External_Executors\Javadocs directory.

In addition, a code sample is provided that demonstrates how to integrate DAC with an ETL engine. The sample code files are stored in Oracle_Home\dac\documentation\External_Executors\SampleSQLExecutor.

This chapter contains the following topics:

Interfaces That Need to Be Implemented for the External Executor Framework

To integrate DAC with an ETL engine, you need to create classes to implement the following interfaces. Information about the methods used in these interfaces is included in the Javadocs, which are located in Oracle_Home\dac\documentation\External_Executors\Javadocs directory.

In addition, the interface DACExecutorProperty is provided, which defines the properties of the DACExecutor interface. You can use the default class for this interface or create your own. The default package for DACExecutorProperty is com.oracle.dac.thirdparty.executor.DACExecutorProperty.

DACExecutorDescriptor

The DACExecutorDescriptor interface is a starting point for integrating an ETL engine with DAC. It describes what the ETL engine does and the properties it requires. An instance of DACExecutorDescriptor is used by the DAC code to generate the required executors during an ETL process.

The properties for this interface need to implement com.oracle.dac.thirdparty.executor.DACExecutorProperty. You can customize the implementation by using com.oracle.dac.thirdparty.executor.DACExecutorPropertyImpl.

In the sample SQL Executor code (located in Oracle_Home\dac\documentation\External_Executors\SampleSQLExecutor), DACExecutorDescriptor is implemented by com.oracle.dac.thirdparty.executor.samplesqlexecutor.SampleSQLExecutorDescriptor. This interface produces the corresponding executor DACExecutor and requires the following properties:

  • JDBC driver name

  • JDBC URL

  • Database user

  • Database user password

  • Number of connections to be created.

    Note: This property also controls the number of tasks that can run in parallel.

DACExecutor

The DACExecutor interface is an instance of an external executor. The DACExecutor object is instantiated during the ETL process and is used to communicate with the ETL engine. Multiple instances of DACExecutor can be created during an ETL process to communicate with multiple ETL engines of a specific type. DACExecutor is initialized with a set of properties (as defined in the DAC metadata) and is used to create DACExecutorJobs, one for each DAC task. A map of DAC parameters relevant for the task and DAC task definitions is provided. Task definition information includes the following:

  • Task name

  • Command name (For Informatica, this is the workflow name.)

  • Source table names

  • Target table names

For more information, see com.oracle.dac.thirdparty.executor.arguments.DACTaskDefinition in the Javadocs (located in Oracle_Home\dac\documentation\External_Executors\Javadocs).

In the sample SQL Executor code, DACExecutor is implemented by com.oracle.dac.thirdparty.executor.samplesqlexecutor.SampleSQLExecutor. At the start of an ETL process, each instance of SampleSQLExecutor used in the ETL will create a connection pool to the specified database and will create the table SAMPLE_SQL_TBL if it has not already been created. During the ETL process, SampleSQLExecutor instances create the corresponding jobs (see "DACExecutorJob").

DACExecutorJob

DACExecutorJob runs actual tasks on the external ETL engine. Jobs are created by executors during the ETL process. One job is responsible to run one task. Jobs are also initialized in the executor using task parameters. DACExecutorJob should implement a synchronous call to the ETL engine and report back the execution status.

In the sample SQL Executor code, DACExecutorJob is implemented by com.oracle.dac.thirdparty.executor.samplesqlexecutor.SampleSQLExecutorJob. For each task of the execution type that corresponds to SampleSQLExecutorDescriptor, one job is run and makes an entry with the task command (for full or incremental mode, depending what load type is appropriate) and the current timestamp in the table SAMPLE_SQL_TBL.

External Executor Utilities

You can use the external executor utility classes to simplify database operations and logging. The utility classes are included in the package com.oracle.dac.thirdparty.executor.utils. This package is not included in the dac-external-executors.jar library. It is part of DAWSystem.jar. If you want to use any class from this package, you must add DAWSystem.jar to your build path.

The following classes are provided to assist with implementing an external executor:

DACExecutorConnectionHelper

You can use the DACExecutorConnectionHelper utility to create and manage database connection pools and to execute SQL statements with or without bind parameters.

When using this utility, make sure to call the cleanUp() method at the end of the ETL process (when the method is called on the executor that owns an instance of DACExcecutorConnectionHelper).

DACExecutorLoggingUtils

You can use the DACExecutorLoggingUtils class to have messages and exceptions added to DAC Server and ETL log files. The ETL logs are saved in the <Domain_Home>\dac\log\<subdirectory specific to ETL run> directory.

Registering an External Executor in DAC

Follow this procedure to register an external executor in DAC.

  1. Create the classes to implement the required interfaces.

    For information about the interfaces that need to be implemented, see "Interfaces That Need to Be Implemented for the External Executor Framework".

  2. Create a library with the classes you created.

  3. On the DAC client machine, do the following:

    1. Add the library to the Oracle_Home\dac\lib directory.

    2. Append the library file name and directory path to the end of the DACLIB entry in the config.bat or config.sh file:

      For example, in the config.bat file, enter %BI_ORACLE_HOME%\dac\lib\<file name>. The DACLIB entry should look similar to:

      set DACLIB=%BI_ORACLE_HOME%\dac\DAWSystem.jar;%BI_ORACLE_HOME%\biacm\applications\biacm.paramproducer.jar;%BI_ORACLE_HOME%\dac\lib\dac-external-parameters.jar;%BI_ORACLE_HOME%\dac\lib\<file name>;
      

      In the config.sh file, the DACLIB entry should look similar to:

      export DACLIB=${BI_ORACLE_HOME}/dac/DAWSystem.jar:${BI_ORACLE_HOME}/biacm/applications/biacm.paramproducer.jar:${BI_ORACLE_HOME}/dac/lib/dac-external-parameters.jar:{BI_ORACLE_HOME}/dac/lib/<fila name>
      
  4. On the DAC Server machine, add the library to the WebLogic domain libraries folder.

  5. Define an execution type for the tasks that will be executed by the external executor.

    1. On the Tools menu, select Seed Data, and then select Execution Types.

      The Execution Types dialog is displayed.

    2. Click New in the toolbar.

    3. Enter a descriptive name for the execution type.

    4. Enter the appropriate DACExecutorDescriptor implementation class, including the package.

      For example: com.oracle.dac.thirdparty.executor.samplesqlexecutor.SampleSQLExecutorDescriptor

    5. Click Save.

    6. Click OK to close the Execution Types dialog.

  6. Declare the external executor.

    1. Go to the External Executors tab in the Setup view.

    2. Click New in the toolbar.

      A new, blank record is displayed

    3. Enter a descriptive name for the external executor.

    4. From the Type drop-down list, select the execution type you defined in the previous step.

    5. Click Save.

    6. Click Generate.

    7. Go to the Properties subtab, and click Refresh in the bottom pane toolbar.

      The list of properties defined in DACExecutorDescriptor is displayed in the Properties subtab.

    8. For each property, enter the appropriate value.

    9. Click Save.

  7. Create the DAC tasks that will need to run on the external executor.

    1. Go to the Tasks tab in the Design view.

    2. Click New in the toolbar.

      A new, blank record is displayed.

    3. Enter the appropriate task definitions. For a description of each field, see "Tasks Tab". For the Execution Type, make sure you select the execution type you defined in step 6.

    4. Click Save.