34 Using Oracle Data Integrator With Oracle BAM

This chapter provides information about the Oracle Data Integrator integration with Oracle Business Activity Monitoring. It contains the following topics:

Section 34.1, "Introduction to Using the Oracle Data Integrator With Oracle Business Activity Monitoring"
Section 34.2, "Installing the Oracle Data Integrator Integration Files"
Section 34.3, "Creating the Oracle BAM Target"
Section 34.4, "Using Oracle BAM Knowledge Modules"
Section 34.5, "Updating the Oracle Data Integrator External Data Source Definition"
Section 34.6, "Launching Oracle Data Integrator Scenarios From Oracle BAM Alerts"

Oracle Data Integrator documentation is located on the Oracle Technology Network web site at the following location:

http://www.oracle.com/technology/products/oracle-data-integrator/10.1.3/htdocs/1013_support.html

34.1 Introduction to Using the Oracle Data Integrator With Oracle Business Activity Monitoring

This document assumes the following:

The Oracle database is installed and you can connect to it.
Oracle BAM is installed and running.
Oracle Data Integrator installed and the basic configuration is done (the Oracle Data Integrator Master repository is created, repository connections are configured, Work repositories are created and connected, and any source topologies are configured).
If Oracle Data Integrator is installed on a separate host, Java 1.6 must be installed on the Oracle Data Integrator host before you can work with the Oracle BAM and Oracle Data Integrator integration.

When using Oracle Data Integrator with Oracle BAM, keep the following in mind:

Within the Oracle Data Integrator interface you must add quotation marks around field names that contain spaces.
Oracle Data Integrator cannot insert data into Oracle BAM read-only fields of type Lookup, Calculated, Auto-incrementing integer, and Timestamp. These fields are automatically populated.
Do not use Oracle BAM as a staging area (for example, if Oracle BAM is used as a source (as when using a loading knowledge module), do not use this source as staging area, and if Oracle BAM is being used as a target (as when using an integration knowledge module) do not use that target as staging area.

34.2 Installing the Oracle Data Integrator Integration Files

There are two ways to set up the Oracle BAM and Oracle Data Integrator integration.

The first method uses an installation script, typically when Oracle Data Integrator and Oracle BAM are deployed on the same system or the same network file system (Section 34.2.1, "How to Install Integration Files Using the Script").

The second method uses manual steps to configure the properties and copy the required files to the Oracle Data Integrator directories (Section 34.2.2, "How to Manually Install Integration Files"). This method is typically used if you are unable to map the ODI_HOME drive from the system where Oracle BAM is installed (usually when Oracle Data Integrator and Oracle BAM are installed in different network or file system).

34.2.1 How to Install Integration Files Using the Script

Use the installation script when you have Oracle Data Integrator and Oracle BAM installed on the same system or the same network file system.

To install the integration files:

On the Oracle BAM host, go to the ORACLE_HOME\bam\config directory and edit the bam_odi_configuration.properties file.
- ODI_HOME
  
  This property identifies the path to the Oracle Data Integrator home directory.
  
  The default value on Linux is /scratch/$user/ODI_HOME/oracledi.
  
  On Microsoft Windows systems, use the short 8-character name convention. Also, use double back-slashes (\\) to denote a directory separator. For example, C:\Program Files\ODI_HOME\oracledi would appear as:
```
ODI_HOME = C:\\Progra~1\\ODI_HOME\\oracledi
```
  Note:
  If Oracle BAM Server and Oracle Data Integrator are deployed on two different machines, then you must map the Oracle Data Integrator drive on the Oracle BAM system, and then set the ODI_HOME path using that mapped drive to successfully make use of the integration configuration scripts. If drive mapping is not possible see Section 34.2.2, "How to Manually Install Integration Files."
- WL_SERVER
  
  This property identifies the Oracle WebLogic Server folder name on the Oracle BAM system.
  
  The default value is wlserver_10.3.
Execute bam_odi_configuration.sh (or bam_odi_configuration.bat on a Microsoft Windows host) in ORACLE_HOME\bam\bin.

Figure 34-1 Integration Configuration Script User Input

Description of "Figure 34-1 Integration Configuration Script User Input"

Enter the values as prompted by the script, as shown in Figure 34-1 (using an Oracle XE database as an example). You must have the Oracle Data Integrator Master and Oracle Data Integrator Work repository account credentials to complete the script execution.

Note that the prompts displayed with [value] have default values in the brackets. Press Enter to choose the default. If there is no bracketed default value displayed, an input value is required, or the script stops.

The script creates the resources required in the Oracle BAM web applications, sets the Oracle BAM configuration properties in Oracle Data Integrator, generates a Oracle WebLogic Server client Java Archive (JAR) to deploy to the Oracle Data Integrator system, and copies all of the required files into the appropriate Oracle Data Integrator directories.

Note:
If you cannot use the script in your environment, use the instructions in Section 34.2.2, "How to Manually Install Integration Files."
After running the script, edit the ODI_HOME/oracledi/lib/config/BAMCommonConfig.xml file, and update the entries for ADCServerName and ADCServerPort to the hostname and port number values where Oracle BAM Server is running.
Shut down and restart the Oracle Data Integrator Topology Manager and Designer applications to load the changes you made to the BAMCommonConfig.xml file.

Now you can create an Oracle BAM target in the Oracle Data Integrator Topology Manager. See Section 34.3, "Creating the Oracle BAM Target" for instructions.

34.2.2 How to Manually Install Integration Files

Use these steps if Oracle Data Integrator and Oracle BAM Server are installed on machines in different networks, or for any reason you cannot use the script in your environment.

There are four major steps to this process:

Create External Data Sources for Oracle Data Integrator
Set Oracle Data Integrator Configuration Parameters
Copy files to Oracle Data Integrator Directories
Generate the Oracle WebLogic Server Client JAR
Edit the BAMCommonConfig.xml File

Create External Data Sources for Oracle Data Integrator

Create the external data sources in Oracle BAM Architect.

Open Oracle BAM Architect and select the External Data Sources page.

Description of the illustration bam_architect_menu_eds.gif

Click Create, and configure the two external data sources (ODI_Master and ODI_Work) with the values shown in Table 34-1 and Table 34-2.

Table 34-1 ODI_Master external data source values

Property	Value
External Data Source Name	ODI_Master
Driver	oracle.jdbc.driver.OracleDriver
Login	Oracle Data Integrator Master repository account user name
Password	Oracle Data Integrator Master repository account password
Connection String	jdbc:oracle:thin:ip_address:port_number:db_service_name

Table 34-2 ODI_Work external data source values

Property	Value
External Data Source Name	ODI_Work
Driver	oracle.jdbc.driver.OracleDriver
Login	Oracle Data Integrator Work repository account user name
Password	Oracle Data Integrator Work repository account password
Connection String	jdbc:oracle:thin:ip_address:port_number:db_service_name

Set Oracle Data Integrator Configuration Parameters

Modify the ODI_ADDITIONAL_JAVA_OPTIONS and ODI_ADDITIONAL_CLASSPATH values in the odiparams.sh(bat) file located in ODI_HOME/bin as shown in Example 34-1 and Example 34-2.

Example 34-1 ODI_ADDITIONAL_JAVA_OPTIONS Modification

ODI_ADDITIONAL_JAVA_OPTIONS="-Djava.util.logging.config.file=../lib/bam_odi.logging.properties"

Example 34-2 ODI_ADDITIONAL_CLASSPATH Modification

ODI_ADDITIONAL_CLASSPATH=../lib/weblogic/wlfullclient.jar

Copy files to Oracle Data Integrator Directories

This procedure copies several JAR files, logging properties, and knowledge modules into the Oracle Data Integrator directories.

Copy the following files from ORACLE_HOME/bam/modules/oracle.bam_11.1.1 to ODI_HOME/lib:
- oracle-bam-common.jar
- oracle-bam-etl.jar
- oracle-bam-adc-ejb.jar
Copy the following files from ORACLE_HOME/bam/modules/oracle.bam.thirdparty_11.1.1 to ODI_HOME/lib:
- commons-codec-1.3.jar
- xstream-1.1.3.jar
Copy the following file from ORACLE_HOME/modules/oracle.odl_11.1.1 to ODI_HOME/lib:
- ojdl.jar
Copy the following file from ORACLE_HOME/modules/oracle.jps_11.1.1 to ODI_HOME/lib:
- jps-api.jar
Copy the following file from ORACLE_HOME/modules/oracle.dms_11.1.1 to ODI_HOME/lib:
- dms.jar
Copy the following file from ORACLE_HOME/modules to ODI_HOME/lib:
- org.jaxen_1.1.1.jar
Copy the following file from ORACLE_HOME/bam/config to ODI_HOME/lib:
- bam.odi.logging.properties
Copy the following file from ORACLE_HOME/bam/ODI/config to ODI_HOME/lib/config:
- BAMCommonConfig.xml
Copy all of the XML files from ORACLE_HOME/bam/odi/knowledge_modules to ODI_HOME/impexp.

Generate the Oracle WebLogic Server Client JAR

Generate a wlfullclient.jar file using the Oracle WebLogic Server JarBuilder tool. See "Using the WebLogic JARBuilder tool" in Oracle Fusion Middleware Programming Stand-alone Clients for Oracle WebLogic Server for instructions.
Create a subdirectory called ODI_HOME/oracledi/lib/weblogic.
Copy wlfullclient.jar into ODI_HOME/oracledi/lib/weblogic.

Edit the BAMCommonConfig.xml File

Edit the ODI_HOME/oracledi/lib/config/BAMCommonConfig.xml file, and update the entries for ADCServerName and ADCServerPort to the hostname and port number values where Oracle BAM Server is running.
Shut down and restart the Oracle Data Integrator Topology Manager and Designer applications to load the changes you made to the BAMCommonConfig.xml file.

34.3 Creating the Oracle BAM Target

This section details the steps for creating an Oracle BAM target using the Oracle Data Integrator Topology Manager.

For more information about using Oracle Data Integrator, see the Oracle Data Integrator documentation located on the Oracle Technology Network web site at:

http://www.oracle.com/technology/products/oracle-data-integrator/10.1.3/htdocs/1013_support.html

34.3.1 How to Create the Oracle BAM Target

To create an Oracle BAM Target in Oracle Data Integrator:

Open the Oracle Data Integrator Topology Manager.
Go to Physical Architecture > Technologies > Oracle BAM.
Right-click and choose Insert Data Server.
Configure the following in the Data Server Definition tab:
- Name: Oracle BAM target name
- Server (Data Server): leave blank
- User: Oracle BAM Administrator user name
- Password: Oracle BAM Administrator password
Configure the following in the JDBC tab:
- JDBC Driver: any_text_will_do
- JDBC URL: instance1:host_name:port_number
  
  The host_name value must be the same as the ADCServerName property value in the BAMCommonConfig.xml file, and the port_number value must be the same as the ADCServerPort property value in the BAMCommonConfig.xml file.
- Do not use the Test button in this dialog box, because it is not functional for the integration between Oracle BAM and Oracle Data Integrator. After you successfully reverse engineer the data objects in the Oracle BAM model, then you can verify that the connection information is correct.
Click OK.
Configure the following in the Physical Data Server dialog box:
- In the Physical Schema Definition tab:
  - Modify the Local Object Mask to be %OBJECT.
- In the Context tab:
  - Create a new row which automatically introduces a row with the Context name Global.
    
    For that row, the Logical Schema value is initially <Undefined>. You must select the <Undefined> text and replace it with the display name for Oracle BAM.
  - Type in a display name for the Oracle BAM target such as BAM_TARGET as the name of a new Logical Schema. Oracle Data Integrator automatically creates the logical schema.
- Click OK.

34.4 Using Oracle BAM Knowledge Modules

Knowledge modules are generic code templates containing the sequence of commands necessary for a data integration pattern. A knowledge module contains the knowledge required by Oracle Data Integrator to perform a specific set of tasks against a specific storage technology. It defines methods related to a given storage technology and it enables processes generation for that technology.

There are different knowledge modules for loading (from the source data store), integration (to target data store), checking, reverse-engineering, journalizing and creating services. All knowledge modules work by generating code to be executed at runtime by knowledge module Interpreter.

There is a set of knowledge modules specific to Oracle BAM functionality within Oracle Data Integrator. These knowledge modules are installed in the ODI_HOME/oracledi/impexp directory when the integration files are installed. To use these Oracle BAM-specific knowledge modules, you must import them into the appropriate projects in the Oracle Data Integrator Designer application. Table 34-3 describes these Oracle BAM-specific knowledge modules.

Table 34-3 Oracle BAM Knowledge Modules

Knowledge Module	Description
CKM Get Oracle BAM Metadata	A check knowledge module that is used internally before integration knowledge module steps. This check knowledge module is the default knowledge module in Oracle BAM technology, and it is automatically acquired by Oracle Data Integrator. This check knowledge module creates two arrays which are later used by Oracle BAM-specific integration knowledge modules in the same Java session. This knowledge module has no options.
IKM SQL to Oracle BAM (delete)	An integration knowledge module that can be used to delete rows from Oracle BAM data objects by sending matching key column values. It has the following options: COMMIT_SIZE BATCH_SIZE DATETIME_PATTERN KEY_CONDITION LAST_BAM_TASK LOCALE_COUNTRY LOCALE_LANGUAGE LOCALE_VARIANT
IKM SQL to Oracle BAM (insert)	An integration knowledge module that can be used to insert rows to Oracle BAM data objects from heterogeneous data sources. It has the following options: BATCH_SIZE COMMIT_SIZE CREATE_TARG_TABLE DATETIME_PATTERN LAST_BAM_TASK LOCALE_COUNTRY LOCALE_LANGUAGE LOCALE_VARIANT
IKM SQL to Oracle BAM (looksert natural)	An integration knowledge module that can be used to insert rows into Oracle BAM data objects from heterogeneous data sources. It differs from IKM SQL to Oracle BAM (insert) by also inserting new entries in dimension tables (that is, the data object to which the lookup column refers) if it does not yet exist. Looksert integration knowledge modules do an insert into an Oracle BAM target based on a lookup field. Typically, this is used to load a fact table in a star schema. (A star schema is characterized by one or more very large fact tables that contain the primary information in the data warehouse, and some much smaller dimension tables (or lookup tables), each of which contains information about the entries for a particular attribute in the fact table.) This integration knowledge module is provided for better performance. It has the following options: BATCH_SIZE COMMIT_SIZE DATETIME_PATTERN LAST_BAM_TASK LOCALE_COUNTRY LOCALE_LANGUAGE LOCALE_VARIANT NON_KEY_MATCHING
IKM SQL to Oracle BAM (looksert surrogate)	An integration knowledge module that can be used to insert rows into Oracle BAM data objects from heterogeneous data sources. It is similar to IKM SQL to Oracle BAM (looksert natural) and differs in using a surrogate key instead of a natural key between a fact data object and dimension object. Looksert integration knowledge modules do an insert into an Oracle BAM data object based on a lookup field. Typically, this used to load a fact table in a star schema. (A star schema is characterized by one or more very large fact tables that contain the primary information in the data warehouse, and some much smaller dimension tables (or lookup tables), each of which contains information about the entries for a particular attribute in the fact table.) If the value for a lookup field does not exist in the relevant dimension table, the value is automatically inserted. This integration knowledge module must be used with LKM Get Source Metadata and CKM Get Oracle BAM Metadata. This knowledge module has the following options: BATCH_SIZE COMMIT_SIZE DATETIME_PATTERN LAST_BAM_TASK LOCALE_COUNTRY LOCALE_LANGUAGE LOCALE_VARIANT NON_KEY_MATCHING
IKM SQL to Oracle BAM (update)	An integration knowledge module that can be used to update rows in Oracle BAM data objects from heterogeneous data sources. It has the following options: BATCH_SIZE COMMIT_SIZE DATETIME_PATTERN LAST_BAM_TASK LOCALE_COUNTRY LOCALE_LANGUAGE LOCALE_VARIANT
IKM SQL to Oracle BAM (upsert)	An integration knowledge module that can be used to merge (upsert) rows (that is, update a data object if matching row exists or insert data object if a new row) to Oracle BAM data objects from heterogeneous data sources. It has the following options: BATCH_SIZE COMMIT_SIZE DATETIME_PATTERN LAST_BAM_TASK LOCALE_COUNTRY LOCALE_LANGUAGE LOCALE_VARIANT Note: During execution, the number of upsert operations are reported in the No. of Updates field, because the Oracle Data Integrator Operator user interface does not have a No. of Upserts field.
LKM Get Source Metadata	A loading knowledge module. This is not a traditional loading knowledge module because it does not load any data from the source to staging area. Instead it simply gathers the metadata that is required by the integration knowledge module IKM SQL to Oracle BAM (looksert surrogate). IKM ORACLE to BAM (looksert surrogate) performs the task of loading directly from a SQL source into the Oracle BAM target. In doing so, it uses the metadata provided by LKM Get Source Metadata. This knowledge module has no options.
LKM Oracle BAM to SQL	A loading knowledge module that allows client applications to load data from Oracle BAM. If using an Oracle BAM loading knowledge module as a source in an interface (for example LKM Oracle BAM to SQL), the user must change the default execute on button for each mapped field in the target to staging area. If left at the default source, erroneous results may occur. Technologies that do not allow for a staging area, such as Oracle BAM, should not have transformations performed on them. It has the following options: DELETE_TEMPORARY_OBJECTS DROP_PURGE LAST_BAM_TASK
RKM Oracle BAM	A customized reverse knowledge module for Oracle BAM. It has the following options: GET_COLUMNS GET_FOREIGN_KEYS GET_INDEXES GET_PRIMARY_KEYS LOG_FILE_NAME USE_LOG

Table 34-4 describes the parameters used in Oracle BAM knowledge modules.

Table 34-4 Oracle BAM Knowledge Module Parameters

Parameter	Description
BATCH_SIZE	The maximum number of records which are sent as a batch across from the client to the server. The batch size that is used to send batches from the client to the server. As larger machines are used with bigger Java Virtual Machine sizes, this parameter can be increased to improve performance. Default value: 1024
COMMIT_SIZE	The maximum number of records in a single transaction. The default, 0, means commit all input records in one transaction. A positive, nonzero, value denotes that the maximum number of records to be committed at a time.Negative values for this option are invalid. Default value: 0
CREATE_TARG_TABLE	Select this option to create the target data object on Oracle BAM Server.
DATETIME_PATTERN	This option and Locale specifications (for example, LOCALE_LANGUAGE, LOCALE_COUNTRY, and LOCALE_VARIANT) are used to construct a Java SimpleDateFormat object which is used in parsing the date and time data strings. See Section 33.2.2, "How to Configure DateTime Specification" for information about SimpleDateFormat.
DELETE_TEMPORARY_OBJECTS	Set this option to `NO` to retain temporary objects after integration. This option is useful for debugging.
DROP_PURGE	Set this option to `YES` to not only drop the work table, but purge it as well. When a table is dropped, it is recoverable in the database's recycle bin. When the table is dropped and purged, it is permanently deleted.
GET_COLUMNS	Set to `Yes` to reverse engineer the columns.
GET_FOREIGN_KEYS	Set to `Yes` to reverse engineer the foreign keys.
GET_INDEXES	Set to `Yes` to reverse engineer the indexes.
GET_PRIMARY_KEYS	Set to `Yes` to reverse engineer the primary keys.
KEY_CONDITION	Set this option to match one or more corresponding rows from source to target. Use the following operators: , =, !=, <, <=, >, >=. The match value (that is, the `where` clause value) should be supplied as the mapping value for the target data store's key field in the Diagram tab for the interface in Oracle Data Integrator Designer. Note that when the operator is chosen as the KEY_CONDITION option value, all rows are deleted from the target data store, regardless of its key field's mapping value.
LAST_BAM_TASK	Use this option to manage the life cycle of the Oracle BAM JDBC connection. If this task is the last Oracle BAM task in the work flow, it closes the JDBC connection; otherwise, it leaves the connection open.
LOCALE_COUNTRY	The country option is a valid ISO Country Code. These codes are the upper-case, two-letter codes as defined by ISO-3166. This option plus `LOCALE_LANGUAGE` and `LOCALE_VARIANT` are used to construct a Java Locale object.
LOCALE_LANGUAGE	The language option is a valid ISO Language Code. These codes are the lower-case, two-letter codes as defined by ISO-639. This option plus `LOCALE_COUNTRY` and `LOCALE_VARIANT` are used to construct a Java Locale object.
LOCALE_VARIANT	The variant option is a vendor or browser-specific code. For example, use `WIN` for Windows, `MAC` for Macintosh, and `POSIX` for POSIX. Where there are two variants, separate them with an underscore, and put the most important one first. For example, a Traditional Spanish collation might construct a locale with parameters for language, country and variant as: `es`, `ES`, `Traditional_WIN`. This option plus LOCALE_LANGUAGE and LOCALE_COUNTRY are used to construct a Java Locale object.
LOG_FILE_NAME	Specify when `USE_LOG` is set to `Yes`. Specify the path and file name of the log. Be sure to set this property value properly (that is, choose a location where user has write permissions) before running the reverse engineering.
NON_KEY_MATCHING	Determines if the incoming non-key column values are to be compared to the non-key column values in the dimension table. If `NON_KEY_MATCHING` is set to `true`, if the incoming non-key column values match those in the dimension table, the row is inserted into the fact table (which is the target data store). Otherwise, that row insert fails, which might even lead to the entire transaction being rolled back (in case `COMMIT_SIZE` was set to 0). A `COMMIT_SIZE` of 1 results in only this row being rolled back and ignored, and all other row inserts progress as usual. If `NON_KEY_MATCHING` is set to `false` and lookup succeeds, incoming non-key column values for the dimension table are ignored.
USE_LOG	Set to `Yes` if you want the reverse-engineering process log details in a log file. Specify the log file location using the `LOG_FILE_NAME` option.

34.5 Updating the Oracle Data Integrator External Data Source Definition

When you install the Oracle BAM integration files for Oracle Data Integrator with a correctly populated properties file, you are not required to do any other configuration in Oracle BAM. Two external data source (EDS) definitions are created during the installation process, and they are populated with the correct values to connect Oracle BAM Server with the ODI_Master and ODI_Work repositories in Oracle Data Integrator. These Oracle Data Integrator-specific EDS definitions must never be deleted.

There are cases in which you must update the Oracle Data Integrator EDS definitions:

If you change the Oracle Data Integrator login credentials, you must update the Oracle Data Integrator EDS definitions in Oracle BAM Architect.
If the ODI_Master or ODI_Work repositories are moved to different hosts after the initial installation, you must update the corresponding EDS definitions in Oracle BAM Architect.

34.5.1 How to Update the Oracle Data Integrator External Data Source Definitions

To update the Oracle Data Integrator external data source definitions:

Open Oracle BAM Architect, and go to the External Data Sources page.

Figure 34-2 Opening External Data Source Page in Oracle BAM Architect

Description of "Figure 34-2 Opening External Data Source Page in Oracle BAM Architect"
Select ODI_Master or ODI_Work, and click Edit.

Figure 34-3 Editing the ODI_Master External Data Source

Description of "Figure 34-3 Editing the ODI_Master External Data Source"
Update the Login, Password, or Connection String parameters as needed, and click Save.

34.6 Launching Oracle Data Integrator Scenarios From Oracle BAM Alerts

Alerts created in Oracle BAM can launch Oracle Data Integrator scenarios when specified conditions are met. See Section F.3.9, "Run an Oracle Data Integrator Scenario" for more information.