Loading multiple target tables in single interface (mapping) in Oracle Data Integrator

Loading multiple target tables in single interface (mapping) in Oracle Data Integrator

ODI 12c is released and it has the feature of loading mutiple target tables as part of a single interface. Actually interface is termed as mapping in ODI 12c. So i will refer as mapping going forward in this post.

In this post, I will show how to load all rows of source into multiple target tables. And also based on some condition, how to load few rows of source to one target and other rows into other target. It is only the split condition which decides which rows goes to which target table. 

Lets get started.

1. Load Multilple target tables in a mapping

I am going to use the HR.EMPLOYEES table as source and create two new tables to capture sub sets of columns from the employees table. I call emp_basic and emp_advanced. Here are the DDLs for both source and target tables.

Source:

CREATE TABLE "HR"."EMPLOYEES"

  (

    "EMPLOYEE_ID"  NUMBER(6,0),

    "FIRST_NAME"   VARCHAR2(20 BYTE),

    "LAST_NAME"    VARCHAR2(25 BYTE) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,

    "EMAIL"        VARCHAR2(25 BYTE) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,

    "PHONE_NUMBER" VARCHAR2(20 BYTE),

    "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,

    "JOB_ID"         VARCHAR2(10 BYTE) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,

    "SALARY"         NUMBER(8,2),

    "COMMISSION_PCT" NUMBER(2,2),

    "MANAGER_ID"     NUMBER(6,0),

    "DEPARTMENT_ID"  NUMBER(4,0),

    CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,

    CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL"),

    CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID"),

    CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID") REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE,

    CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE,

    CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE

  )

Targets:

CREATE TABLE "DATA_TARGET"."EMP_BASIC"

  (

    "EMP_ID"     NUMBER(16,0) NOT NULL ENABLE,

    "FIRST_NAME" VARCHAR2(50 BYTE),

    "LAST_NAME"  VARCHAR2(50 BYTE)

  )

CREATE TABLE "DATA_TARGET"."EMP_ADV"

  (

    "EMP_ID"     NUMBER(16,0) NOT NULL ENABLE,

    "JOB_ID"     VARCHAR2(20 BYTE),

    "SALARY"     NUMBER(16,0),

    "COMMISSION" NUMBER(16,0)

  )

Lets create the mapping in ODI 12c client.

Go to logical tab and drop the source table hr.employees. You can see two employees table instances with name employees and employees1 dropped there. It is because of the manager_id foreign constraint referring to the self table. ODI 12c client represents this constraint as self join here automatically.

Lets drop the two targets data_target.emp_basic and data_target.emp_adv.

Drag and drop the column names from source to target tables to do the mapping.

Lets review the physical tab for KM details. Select each target table in the physical tab to review the IKM and CKM used. As we can see, ODI uses oracle parallel hint to load the target tables which improves performance for appending the data.

You can also see that IKM and CKM are from Global Knowledge modules. By default ODI 12c client has few KM in global KMs.

Lets run the mapping and see the results.

Lets review the target tables data. we see that all rows from source is populated into both tables for different columns.

2. Splitting records into multiple target tables based on condition

Lets try to split rows from source into multiple targets based on some condition. rows which are matching the condition are going into the respective targets. 

Go to the logical tab and delete the lines from source to target and add split components from components.

connect source to split and split to multiple targets. while deleting the existing lines from source to target, odi will still keep the existing mappings.

Click on split component and in the split component properties, define condition in expression section for each target output1 an output2 so that only rows matching this condition from source are loaded to respective target.

The condition I have defined here are 

OUTPUT1 - Default.EMPLOYEES.EMPLOYEE_ID <= 109

OUTPUT2 - Default.EMPLOYEES.EMPLOYEE_ID >   109

so employee_id less than 109 goes to emp_basic table.

employee_id greater than 109 goes to emp_adv table.

Lets run the interface and check the results. Run is successful, lets verify the results in target tables.

emp_basic table has records till employee id 109.

emp_adv table has records from employee_id 110 as expected.

ODI 12c Installation and ODI 12c Repository upgrade from 11g

ODI 12c Installation and ODI 12c Repository upgrade

ODI 12c version is releases and it has come with lot of exciting features. You can find the new features that has been shipped with 12c version in the link ODI 12c new features of oracle documentation.

1. Multiple Target support - Now mappings can load multiple targets as part of the new flow.

2. Re-usable mappings - We can create mapping once and can be re-used across objects which is very good for huge mapping which involves xml, table, complex files.

3. Step by Step debugger - mapping, package, procedure and scenarios can be debugged step by step.

4. Standalone Agent with Weblogic - Standalone agents can be managed through weblogic management framework

5. XML Improvements - lis, union, substition group, mixed context and annotation are supported in 12c version for XML technology.

Oracle's go-forward strategy is to have data warehousing/data integration to be done through Oracle data integrator and phase-out Oracle Warehouse builder to ODI by providing migration tools. Based on this strategy lot of integration from OWB and ODI features introduced as well as OWB migration to ODI is also made easy.

In this post, I would like to show my experience on 12c ODI installation and upgrade of the repository from 11g to 12c. 

Download the 12c ODI generic version from oracle downloads. I have downloaded 12.1.2 generic version which is the current version when this post is written.

ofm_odi_generic_12.1.2.0.0_disk1_1of1.zip

Extract this zip file. we can see the main jar for installation and patches zip file to apply the patches. Extract the patch zip file. ODI 12c installation has 2 steps.

1. Install ODI 12c jar

2. Apply the patches

Installing ODI 12c

Run the jar file in command prompt for installation. we need to run this jar with admin rights in windows (run as administrator option). Also ODI 12c is certified above 1.7.0_15 or above. Be sure to use latest JDK 1.7.

In windows, to run the command with admin rights, type cmd in run and use cntrl-shift-enter to launch it. select yes in the confirmation for running the commands with admin privilege in the cmd window.

 type the command java -jar odi_121200.jar (make sure java executable is from jdk 7 latest version)

Provide oracle home for the installation

Select enterprise installation option. Complete the wizard to complete the installation.

Apply ODI 12c patches

Read the readme file present in the patch directory for pre-requisites which may be different for your configuration before running opatch.

Verify the OUI inventory before running opatch. It needs to run without errors before applying the patches. Run the command "opatch lsinventory" to check this.

Go to the unzipped directory of odi patches and run the command "opatch napply odi_1212_opatch" to apply the patches. Ignore the error "There is an error with library regeneration" which is coming in the command prompt. Patch readme file says it is as expected and we can ignore it.

Lets check the OUI inventory again to check the applied patches.

Upgrading ODI repository from 11g to 12c

Before upgrading the repository, please make sure of the following so that you dont lose your repository if there are any errrors during upgrade.

1. All affected data is backed up (middleware home, oracle home, master and work repository schemas and data)

2. Datbase version is certified to work with ODI 12c

3. Certification and system requirements are met.

To run the upgrade, Go to <Oracle_Home>/oracle_common/upgrade/bin directory and run the ua command.

Select schemas to upgrade repository.

Select ODI for schema for repository upgrade.

Check the prerequiresites and check them to agree that you have completed them.

Provide sysdba user and master repository schema details.

Select all options for upgrading.

Provide supervisor user details

Please note down the upgrade key detail for future reference and work with support for any issues.

Complete the wizard to complete the upgrade. It will take some time to do the upgrade and confirm the upgrade is successful.

Lets connect to the repository using the new 12c ODI client.

ODI client  odi.exe  or odi64.exe can be found in <Oracle_Home>/odi/studio. Lauch the exe for odi studio client.

Oracle has made the look and feel same for Jdeveloper 12c and ODI 12c client. It is evident that OFM is standardized across tools and infrastructure in 12c.

If asked for import earlier version configs, select yes to carry them to 12c client. You will asked to create wallet for storing passwords. Provide a password of your choice for wallet password. It will be prompted for this wallet password while connecting to repository.

Lets connect to our upgraded repository. Select connect to repository and provide wallet password.

It automatically shows all existing repository connections for you to select and provide password. select the work repository and provide password.

Here is how upgraded repository objects looks. we can see that 11g interfaces are renamed to mapping in 12c (not suprisingly, interface is confusing and mapping term looks straightforward).

we can also see that UI and layout has been revamped in the 12c version. 

I will talk about 12c ODI new features with hands on in my next  posts.

Thank You and share your comments if any.