Oracle Data Integrator (ODI) Setup

Oracle Data Integrator (ODI) is an Extract, load and transform (ELT) tool that offers a GUI environment to build, manage and maintain data integration processes in business intelligence systems. Oracle Data Integrator is a comprehensive data integration platform that covers all data integration requirements: from high-volume, high-performance batch loads, to event-driven, trickle-feed integration processes, to SOA-enabled data services. Oracle Data Integrator (ODI) 12c, the latest version of Oracle’s strategic Data Integration offering, provides superior developer productivity and improved user experience with a redesigned flow-based declarative user interface and deeper integration with Oracle GoldenGate. ODI12c further builds on its flexible and high-performance architecture with comprehensive big data support and added parallelism when executing data integration processes. It includes interoperability with Oracle Warehouse Builder (OWB) for a quick and simple migration for OWB customers to ODI12c. Additionally, ODI can be monitored from a single solution along with other Oracle technologies and applications through the integration with Oracle Enterprise Manager 12c.

1. Oracle Data Integrator

• ODI’s ELT Architecture – which provides high performance – ELT faster than ETL
• ODI combines 3 styles of data integration

Data based - by transforming large volumes of data in batch

Event based – by processing events in real time through its advanced CDC

Service based – by providing the data service to the Oracle SOA suite

• ODI’s Declarative Design – ODI generates the flow and code based on the Business user’s requirement.
• ODI Knowledge Modules - Reusable code templates to all major databases.

ETL Process

Source Target

       File/XML/ Oracle                    TRANSFORM                                ORACLE

Extract ->Transform->Load

2. ODI Master and Work Repositories

Master REPO - 1. Development Work Repo   2. Production Work Repo
The first steps to setting up Oracle Data Integrator are

To create the Master repository,

Connect to the Master repository,

Create the Work repository,

And then connect to the Work repository

Step: 1) Connecting to the RDBMS (Oracle  XE)

Step: 2) Creating the RDBMS Schema/User (Oracle 10g XE) for the Master Repository and Work Repository

Create the schemas by executing the following SQL commands:
create user identified by ; grant dba, connect, create session to ;

1. Create connection to system user in SQL Developer and login to system user.
2. Creat needed DB schemas
        Master:
      SQL> CREATE USER DEMO_MASTER IDENTIFIED BY E1APS;
      SQL> GRANT RESOURCE, CONNECT, DBA TO DEMO_MASTER;
      SQL> GRANT CREATE SESSION TO DEMO_MASTER;
   
        Work:
       SQL>CREATE USER DEMO_WORK IDENTIFIED BY E1APS;
       SQL>GRANT RESOURCE, CONNECT, DBA TO DEMO_WORK;
       SQL>GRANT CREATE SESSION TO DEMO_WORK;
3. To drop schema (example):  SQL>drop user DEMO_WORK cascade;

Step: 3) Creating the ODI Master Repository

In the URL field, enter the following URL: jdbc:oracle:thin:@localhost:1521:xe, and then enter the User as DEMO_MASTER and Password as E1APS(This is in our condition). In the Technology field, select Oracle. Click the Test Connection button and verify successful connection. Click Next. Click OK on Master Repository Creation Wizard screen.

Here I have entered the password as SUNOPSIS.

The SQL command is executed in the command window. If the Master repository creation is successful, you will see the following message. Click OK. The ODI Master repository is now created.

Step: 4) Connecting to the ODI Master Repository

Configure Repository Connections with the parameters provided in the following screen. Click the Test button. Verify successful connection and click OK. Click OK to save the connection.

Select the newly created repository connection (Master Repository) from the drop-down list. Click OK. The ODI Topology Manager starts.

A relational schema must be created for each repository. The following steps walk you through creating the relational schema for the ODI Work repository.

Step: 1) Creating the ODI Work Repository

In ODI, click the Topology Navigator tab and then click to the Repositories panel. Right-click the Work Repositories node and select New Work Repository. The Create Work Repository Wizard opens. Click the Test button. Verify successful connection and click OK. Click Next.

In the Create Work Repository Login window, click Yes. Enter the Login name: WORK_REPO as shown on the screenshot. Click OK. Verify that the newly created Work repository is now in the work repositories tree view

Now you disconnect from the Master repository and connect to the Work repository. Click ODI menu and select Disconnect “Master Repository”.

Click Connect to Repository. Select “WORK_REPO” from the Login Name drop-down list. Enter Password: SUNOPSIS. Click OK. Click the Designer tab. The following ODI Designer screen appears.

We can also create master and work repository using RCU.

ODI Studio Components

• Designer – Develop projects
• Topology – Define the infrastructure for development (data source creation for             difference sources and targets)
• Operator – Monitor the session
• Security – Manage user privileges

3.  ODI Topology concepts

ODI Topology

- Topology is the representation of the IS (different source systems) in ODI.

-To connect resources in the IS for integrations process

-All available technologies are defined in the topology.

Physical Architecture

1) Data Server

-System that is capable of storing data and making the data available in the form of tables in ODI.

-Data server is always attached to a specific technology Oracle, File or XML

2) Physical Schema

- Subdivision of Data Server

-Indicates the physical location of the data stores such as files,xmls and tables inside a data server.

Example

Technology	Data Server	Physical Schema
Oracle	Instance	Schema
File	File Server	Directory

Logical Schema

-Single alias for different physical schemas that have similar data structure based on the same technology but in different contexts.

Logical Schema(Payroll)

HYD(Salary) BLR(SAL)

In the above example of logical schema, In HYD, the payroll database is stored in the schema called SALARY. In BLR, the schema is called SAL. Both of them have the same data structure and they contain in all tables for a payroll application. In the ODI topology, you consider them as one logical schema i.e., Payroll.

Logical Versus Physical Architecture

The physical architecture describes the locations of servers that exists in your IS and the schemas that you want to use on them

You may logically have one data schema representing you payroll applications. But, if this schema exists in different places, you should declare these as separate physical schemas.

The logical architecture gives more abstract view of these resources. In the above example, your payroll application schema is defined only once is the logical architecture. It is a single schema implemented in several different contexts.

Design Time Versus Run Time

In ODI, you always work at design time using objects defined in the logical architecture.

In the given example, the data stores defined in the logical schema PAYROLL without specifying whether you meant the server in HYD or BLR.

At run time, physical resources are required to carry out the integration process. For this we need context to execute within. ODI is then able to provide access to the physical resources of that context.

Context

A Context represents a “situation” where you see the same group of resources. From a technical perspective, a context maps individual logical resources onto individual physical resources.

In terms of topology, the logical architecture is mapped onto the physical architecture through a context.

HYD and BLR are context representing the different sites. These are for different purposes such as development, testing and productions sites.

ODI Agent

ODI Agent is a lightweight Java process that orchestrates the execution of ODI Scenarios.