Table to Table Mapping

1. ODI Component Designer:

  •  In this layer we defines rules for data transformation and data integrity.
  • Purpose of this layer is developing logic for data integration.
  • In this layer we create Project, mapping,variable and sequences.
  •  Designer layer contains
    — Project
    — Model
    — Load plan and scenario
    — Global Object



How to create project ?
step 1: Click on project and select new project

step 2: Give proper name of project

step 3: See Project window
In project we can create
— Package
— Mapping
— Procedure
— Sequence
— Variable
— User function
In next set of tutorial we will see detail description about this component


Mapping In ODI

  • Mapping is a new feature introduce in ODI 12c( Previously name was Interface in ODI 11g)
  •  Mappings are the logical and physical organization of your data sources, targets.
  • Mapping Defines How data flow from source to Target.
  •  Mapping Defines How the data are transformed from the Source format to the target format (the Mappings)
  •  Source and target are defined using Metadata imported from the databases and other systems.
  •  Flows are defined in Templates called Knowledge Modules (KMs)
  •  Basic requirement to run mapping.
    1 Target Data store
    2. Integration Knowledge module
    3. If source and target technology is different then LKM is required.



  • Model contain set of data store
  • A Model is the description of a set of data stores.
  • A model is based on a Logical Schema defined in the topology and we know that
    logical schema and physical schema connected through context.
  •  For every technology we are defining model.(suppose our source is file
    while creating model we define technology as a file.
  •  Generally we create source model and target model for every project.

How to create model ?

step 1 :

Go to model and select New model

step 2:
1. Give proper name of model
2. Select Technology
3. Select logical schema(This schema connected to physical schema
and physical schema define our source system)

Note: Here our source is Our oracle database so i selected technology as Oracle)
and in My physical schema I selected HR schema


step 3: Reverse engineering
Select object that you want to import from your source system

step 4: selective reverse engineering
Select Data store that you want in your source model and click on Reverse engineer

when you click on reverse engineer it will prompt window that your data will be saved so just click on yes


Wait until your all data stores are Reverse engineered


Now if You expand your source model then you can see all your data store

Now just right click on any data store and select view data to see data

now you can see location table data


Creating Simple table to table Mapping

  •  In topology We already define our source system(database) and we imported
    into our source model.
  •  Now lets define Target model and create Data store for target table.Creating Target modelstep 1: Go to model and select new modelcreating-model

    step 2:
    Give proper name
    select technology as oracle
    Select logical schema which you have createdtarget-model
    After that Save your target model.

    Creating Target data store

    step 1: Right click on target model and select new Data store

    step 2: give proper name and select data store type


    step 3: Click on attribute tab and add attribute that you want into your
    target table

    After that click on save.

Creating mapping
Give proper name to mapping
you will see Mapping window like belowdrag12step 3: Drag your source and target data store

You can map source data store attribute to target datastore attribute by
automatically by name or you can map manually also.

step 4: select integration Typeintegration-type
step 5: Go to physical architecture and Define Knowledge module and
make create target table true

Step 6: validating mapping

You can see successful validation after that you can run

step 7: click on run and see operator whether
step 8:

seeing operator log
How to view table data


Now you can see table data

you can also see this table in your database

Leave a Reply

Your email address will not be published. Required fields are marked *