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

designer

Project

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

new-project
step 2: Give proper name of project

new-project1
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

odi-project

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

 

  • 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

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

new-model1

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

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

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

yes

Wait until your all data stores are Reverse engineered

reverse-engineer12

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

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

view-data
now you can see location table data

table

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

    new-data-store
    step 2: give proper name and select data store type

    datastore

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

    attribute
    After that click on save.

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

drag-source
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

final
Step 6: validating mapping

validate-mapping
You can see successful validation after that you can run

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

mapping-run
seeing operator log
operator-session-mapping-successfull
How to view table data

view-data

Now you can see table data

target_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 *