STEPS FOR CREATING A MAPPING /INTERFACE IN ODI

STEPS FOR CREATING A DATABASE TO DATABASE MAPPING /INTERFACE IN ODI

Create a master schema and work schema in database
Create a master repository and work repository along with login
Create Models and data stores for source and target
Create a Project
Import Knowledge modules
Create a mapping

PRE-REQUISITES:

· A Database for configuring ODI repositories should be installed and running ,which may be one of the following

Oracle
IBM DB2 for iSeries (AS/400)
IBM DB2 UDB
PostgreSQL
Sybase
Microsoft SQL Server

· ODI software (Downloads available in http://www.oracle.com/technetwork/middleware/data-integrator/downloads/index.html) should be installed and running .

t The below mentioned steps for creating an interface in ODI is for Database to Database mapping.

CREATING MASTER SCHEMA AND WORK SCHEMA IN ODI:

· The first step is to create master and work schemas in the database (databases which are mentioned above)

· Use the following script or query to create schemas and for providing privileges

Create user <master schema> identified by <master schema password>;

Eg: create user odimaster identified by welcome123;

Grant connect,resource to <master schema>;

Eg: Grant connect,resource to odimaster;

Similarly we need to create work schema and provide privileges to it.

Create user <work schema> identified by <work schema password>;

Eg: create user odiwork identified by welcome123;

Grant connect,resource to <work schema>;

Eg: Grant connect,resource to odiwork;

CREATING MASTER REPOSITORY IN ODI:

· Click File -> New from the menu after starting ODI client (Location of executable: ~\oracledi\client\odi)

· Now click Master Repository Creation Wizard from the pop up window

· Provide details for the following

Technology : (By Default Oracle will be chosen)
JDBC Driver
JDBC Url
User : (Master schema)
Password: (Master Schema’s password)
DBA User: (User with DBA privileges like SYSTEM/SYSDBA/SYSOPER)
DBA Password
Provide ID for new master repository (Note: This is an unique id )

Now click Test connection

An error message will be shown in the popup if there is any problem with the details provided above.

· Now select ODI authentication mode for managing users

Select Use ODI Authentication for managing users through ODI
Select Use External Authentication for managing users via external tools like Identity management, Oracle Internet Directory etc.

· Now select the password storage mechanism

Internal storage (ODI internal password storage mechanism)
External storage (Using Weblogic server)

· Click Finish

· Now the master repository is successfully created.

· Now create a new login for the master repository

Click File -> New from the menu after starting ODI client

· Select Create a New ODI Repository Login for creating a new ODI master repository login

· Provide the details as required

Oracle Data Integrator Connection

User : <Username provided for ODI authentication mode during master repository creation>

Password : <Password provided for ODI authentication mode during master repository creation>

Database Connection (Master Repository)

User :< Master repository/schema username>

Password :< Master repository/schema password>

Driver List :< Select driver according to the database in which master repository is configured>

Driver Name : <Automatically selected based on the driver list>

Url : <Enter the jdbc url>

Work Repository

Click Master Repository only as we are creating login for Master repository .

Check the Default connection option if this master repository might be selected as default one while connecting .

Now click Test

CREATING WORK REPOSITORY IN ODI:

· Click Connect To Repository to connect to master repository which we have created in our previous steps.

· Select the login name from the drop down from the drop down. Since we have only one login for master repository it will be displayed. Click OK to proceed.

· Now we will be connected to master repository

· Under Topology expand Repositories

· Now right click on Work Repositories and click on New Work Repository to create new work repository

· Provide the connection details to work repository

In User and Password provide the work schema username and password

· Click on Test Connection for checking whether the provided details are correct

· Now provide the details for the following

Id - Unique id for work repository
Name - Any logical name to work repository
Password- Password for work repository
Work Repository Type- Development or Execution

Development – For Development mode

Execution –For Production mode

· Now click Finish to create work repository

· Now create a login for work repository also

Click Yes to create or No so that we can create manually later. Here the option is Yes.

· Double click on the newly created work repository under Repositories -> Work Repositories to check the provided details are correct

· Now disconnect from master repository login from the menu ODI -> Disconnect “<Master repository login >”

· Now login to work repository

Click Connect To Repository and select the work repository login

· Since password was not provided during work repository login creation , it will be blank

· To change that click on edit button.

· Now provide the password in the details

Check whether the work repository is selected or not . If needed check the Default connection also.

· Now click on Test

· Now click Ok to see the login popup

CONFIGURING DATA SERVERS (SOURCE / TARGET) IN ODI:

· Under Topology expand Physical Architecture and right click on the architecture or data server which needed to be configured.

Click New Data Server

· In Definition tab, provide a name to data server and provide the credentials for the schema from/to which data is read or inserted.

· In JDBC tab, provide the jdbc driver details

· Click the search icon for JDBC Driver to select available jdbc drivers

· Click the search icon for JDBC Url to get the sample URL format for the selected JDBC driver

· Provide the host, port, sid for the selected url

· Click Test Connection which will open a popup for saving the server details. Click Yes for save and test else Click No to save manually later.

· There will be a warning popup for creating physical schema .Click Ok.

· Click Test

We have not configured any new physical agent yet, so use the default local agent.

CREATING PHYSICAL SCHEMA IN ODI:

· Now create a new physical schema for the created data server

· Right click on the newly created data server and click New Physical Schema

· Select the Schema and Work Schema details from the drop down

Selecting Schema will provide access to all the objects under the selected schema

Selecting Work Schema will provide access for ODI to create its temporary objects during execution.

· Check default option if the created physical schema is default for the data server

· Now click Save

· A warning will be thrown for selecting context. Click OK.(This will be covered in next section Creating logical schema)

· Now the created physical schema will be available under the data server

CREATING LOGICAL SCHEMA IN ODI:

· Now a logical schema should be created for an existing physical schema

Note: Any number of logical schemas can be created for a physical schema but the reverse won’t work.

Under Topology expand Logical Architecture

· Right click on the architecture or technology for which logical schema has to be created and click New Logical Schema.

· Now provide the name and select the context or the physical schema to which it should be mapped.

· Click Save

· After saving the logical architecture , expand Physical architecture and expand the corresponding physical schema .

· Now double click the physical schema and click context tab. We can see the logical schema already mapped to physical schema .

· Above are the steps for creating physical and logical schemas.

· We need to create the above mentioned steps for both source and target data servers.

Above is the example for creating data server and schemas both physical and logical for source server. Use similar method for target also. Below screenshots will show the above process for creating target.

CREATING A PROJECT IN ODI:

· Now we need to create a project which contains the interfaces / mappings and various other objects.

· Click Designer tab and expand projects

· Click the drop down menu for creating new project

· Select New Project from the options displayed

· Provide a name for the project

· Click Save

· Expand the saved project and you can see the objects related to ODI mapping

CREATING A MODEL IN ODI:

· Before creating a mapping we need the replica of tables and related structures / objects which are to be used by mapping.

· Now create a model under Designer expand Models and click New Model under Drop down menu

· In Definition tab, provide the name and technology

· Now select the Logical schema from the drop down menu in the Logical Schema parameter

· In Reverse Engineer tab, choose the Standard option if the tables are reverse engineered without any modules and select the context, types of objects to reverse engineer.

· In Selective Reverse Engineer tab, check or choose the following options to reverse engineer the objects

Selective Reverse-Engineering
New Datastores
Existing Datastores
Objects to Reverse Engineer

For Select all or de-select all we have options at right most corner. Choose the options accordingly.

The picture below shows how to select all objects displayed.

The picture below shows how to de-select all objects displayed.

· Now save the model and then click the reverse engineer button

· Else click reverse engineer button directly to save on the fly

· Click Yes to save and proceed

· After reverse engineer expand the model that was created above and the list of objects reverse engineered will be displayed.

· We can see the details of the object that was reverse engineered like columns, constraints to columns etc.

· For more details double click on the object eg. Table name countries

· A popup for object locking will be shown for locking the object, which will be helpful in multiuser development environment so that the modification to an object by multiple users will be prevented.

The above mentioned steps we have seen the snapshots for creating MODELS and DATASTORE for source. In similar way , target MODEL should also be created.

After Reverse Engineering TARGET model, double click on the data store EMPLOYEE to see the details .

IMPORTING KNOWLEDGE MODULES IN ODI:

· Next step is to import knowledge modules which are required for the interface mapping. We have to choose the knowledge modules based on the source and target technologies used.

· Under Designer expand Projects -> expand the project in which the mapping is to be created and right click on Knowledge modules and click Import Knowledge Modules

· Now select the path from which we need to import the knowledge modules by clicking Browse button.

· By default the knowledge modules for 11g will be under the ODI installation directory named xml reference

~ODI_HOME\oracledi\xml-reference

· Click Open to list the files for knowledge modules

· Now select the required KM’s according to the source and target technologies

Multiple selections are possible by using Ctrl + Mouse left click combination.

· Click OK to import the selected KMs.

· A summary of imported KMs will be shown if needed this page can be saved for future use.

· Now expand the Knowledge Modules section to see the imported KMs.

CREATING NEW MAPPING OR INTERFACE:

· In Designer tab under Projects expand the project under which the interface should be created .

· Right click on the interface and click New Interface.

· In the Definition section, provide the interface name, context and staging area for the interface .

Note : Staging area is used whenever the source is not an actual database like text files, csv files, xml files.

· Now click the Mapping tab (which will be shown in the bottom of the Interface page)

· The mapping section has four parts

Top Left-Source mapping- All the source related mappings, joints , filters will be shown
Top Right-Target - Only one target table will be mapped
Bottom Left-Property Inspector- Properties related to the objects in both source and target panes.
Bottom Right-Thumbnail – show overall view of mapping in thumbnail view

· Now drag and drop the table to be mapped as the target in the target panel.

· Now drag and drop the Source tables which are to be mapped against the target columns into the top left pane (where you can see a note Drag Datastores from the Designer Navigator Models tree view here to use them as sources for this dataset).

A popup will be shown for automatic mapping. Click Yes to map automatically or No to map manually later.

(Note: By default the mapping from the source to target will be based on the Column name and data type. If both source column and target column match in column name and data type, the target column will be mapped with the respective source column)

· After automatic mapping

· Drag and drop all the tables which from source which should be mapped to target .

(Note: By default the parent child relationship will be maintained by automatic join conditions based on table column keys/constraints and their relationships)

Eg:- In the above picture

employees and departments tables are linked based on department_id and managar_id and locations and departments are linked by location_id.

So joins are made between these three tables based on the above links.

· Click on a link in the join and the join condition will be displayed in the properties window .

· Change the nature of join conditions in the property inspector like inner join, cross join, natural join , left outer join, right outer join etc.

· Once the source tables are linked , map the unmapped columns in the target by drag and drop the source columns to the target columns property .

Click on the unmapped column in the target

Drag and drop the related source column to the target columns property Inspector.

· Change the execution location and insert or update in the property inspector for each target column mapping according to the requirement.

· Click on Quick-Edit tab to change the above mentioned step (insert/update)

· Click on Flow tab to select LKM’s and IKM’s depending on the source and target.

Since the target is Oracle database, we have selected LKM SQL to Oracle, IKM Oracle Incremental Update in our example.

(Note: Please go through the description and steps that will be executed by the selected LKM’s / IKM’s in the description section of Target Properties)

Select Distinct Rows option for inserting only distinct values to the target.

· In the Control tab, select the CKM appropriate to the target.

· Now save the mapping

· Lock the interface if required for avoiding conflicts in multi user development environment.

· Click Execute button

· Select Context, Logical Agent(if configured already else use default), Log Level ( use 5 to log all details to 0 which will record only the start and end of the execution).