Thursday, July 18, 2013

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.





14 comments:


  1. I find the required information on Oracle ODI. Thanks for providing your information on
    Oracle ODI online training.

    ReplyDelete
  2. Yours guidence on Oracle ODI is helpful to learners. Thanks for providing the needed information on Oracle ODI online training

    ReplyDelete
  3. THANK YOU FOR THE INFORMATION .HI GUYS IF YOU SEARCHING FOR Website designing in Bangalore

    PLEASE VISIT US
    Website designing in Bangalore

    ReplyDelete
  4. Magnificent blog I visit this blog it's extremely wonderful. Interestingly, in this blog content composed plainly and reasonable. The substance of data is useful.
    Workday Online Training
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training
    Oracle Fusion Financials Online Training
    Big Data and Hadoop Training In Hyderabad

    ReplyDelete

  5. Hi, I really loved reading this article. By this article i have learnt many things about this topic, please keep me updating if there is any update

    oracle Hyperion Online Training
    Hyperion Training
    ODI Online Training
    ODI Training

    ReplyDelete
  6. Halo,I'm Helena Julio from Ecuador,I want to talk good about Le_Meridian Funding Service on this topic.Le_Meridian Funding Service gives me financial support when all bank in my city turned down my request to grant me a loan of 500,000.00 USD, I tried all i could to get a loan from my banks here in Ecuador but they all turned me down because my credit was low but with god grace I came to know about Le_Meridian so I decided to give a try to apply for the loan. with God willing they grant me  loan of 500,000.00 USD the loan request that my banks here in Ecuador has turned me down for, it was really awesome doing business with them and my business is going well now. Here is Le_Meridian Funding Investment Email/WhatsApp Contact if you wish to apply loan from them.Email:lfdsloans@lemeridianfds.com / lfdsloans@outlook.comWhatsApp Contact:+1-989-394-3740.

    ReplyDelete
  7. Extra Course is the best ODI training institute in Hyderabad. We offer 100% practical driven program through our unique experimental learning, focus on a comprehensive practical approach.
    https://extracourse.com/odi

    ReplyDelete

  8. Thanks for the nice blog here.I was searching this one for a long time.This blog is very helpful for my studies..I got another one site also,which is same as yours Oracle ODI .Check this one also Oracle Fusion Manufacturing .Sure it will be helpful for you too..Once more iam thanking you for your creative blog.

    ReplyDelete
  9. Thanks for sharing this blog. The content is beneficial and useful. Very informative post. Visit here to learn more about Data Warehousing companies and Data analytics Companies. I am impressed by the information that you have on this blog. Thanks once more for all the details.Visit here for Top Big Data Companies.

    ReplyDelete