How to Update Newly Added columns in a Table using ODI: 11g and 12C

Here is a use Case:

There is one table "Table_1" with you, in another software release few columns Col1,Col2,Col3 got added to the table. Business have a third party system that will provide you with the value for these newly added columns along with the row identifier or the record identifier like the primary key or some key value for the record.So essentially the data that you are going to get for updating the Table_1 data is (PK,Col1,Col2,Col3) where PK is the record identifier like the empid in case of an Employee table. So we have to load the other columns now in one initial load.

Solution:
Alter the Target_1 table and add the extra columns to it. Col1,Col2,Col3.
The PK column would have been already there.

Create a Model for the source data : (PK,col1,col2,col3)
Reverse Engg Table_1, so that new columns are displayed in the datastore.

Create an Interface, drag the source data store into the source data store panel in ODI and the target data store in the Target Data panel.

Start the mapping, Map in Table_1 only PK,Col1,Col2,Col3 from the source datastore.
Select  IKM Oracle Incremental Update as the Integration knowledge module.
(The places to select the KM is different in 11g and 12c.) I hope you know those places.

Save the interface and run, that's it, on completing the interface run, check that the new columns that were added to the Table_1 are updated with the values provided by the 3rd party.

How to utilize Partitioning, Spliting and Sorting in ODI 12c

In this post we will look into some of the features in ODI12c.

Let me first mention the use case that we will look here.

Product versions: ODI 12.2.1

Database version: Oracle 12c

Database schema: 12c

In the Emp table, the Employees are present from various departments, lets assume that this was a very huge table in a typical datawarehousing environment and the table was partitioned based on the deptno. 

We have the requirement of processing only dept30 partition data, split this data into 2 part, based on JOB i.e SALEMAN goes to one table and other go to another table. If the branch is SALESMAN, the data needs to be loaded in SORTED Order.

To create this scenario, lets create the data first.

In the scott schema, run the following script:

CREATE TABLE "SCOTT"."EMP1" 

   ( "EMPNO" NUMBER(4,0), 

"ENAME" VARCHAR2(10 BYTE), 

"JOB" VARCHAR2(9 BYTE), 

"MGR" NUMBER(4,0), 

"HIREDATE" DATE, 

"SAL" NUMBER(7,2), 

"COMM" NUMBER(7,2), 

"DEPTNO" NUMBER(2,0), 

CONSTRAINT "PK_EMP1" PRIMARY KEY ("EMPNO"), 

CONSTRAINT "FK_DEPTNO1" FOREIGN KEY ("DEPTNO")

 REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE

   )  TABLESPACE "USERS"

   PARTITION BY HASH (DEPTNO)

    PARTITIONS 3;

Now insert the data into EMP1 table:

insert into EMP1

select * from EMP;

Create 2 more tables:

CREATE TABLE EMP_SALESMAN AS select * from emp1 where (1=2);

CREATE TABLE EMP_OTHERS AS select * from emp1 where (1=2);

Now Login to ODI and Create a Model "Oracle_Scott" with logical schema pointing to Scott schema. (Details about creating Physical Schema, Logical Schema, Model etc in ODI 12c could be found in various tutorials already present in internet.)

Reverse engineer the above three tables in the "Oracle_Scott" model.

Right Click on Mappings and select New Mapping, give some name  example :"INT_LOAD_PARTITION_SPLIT_AND_SORT"

Drag EMP1 from model and drop it in the mapping logical area and click on it and Navigate to the properties of EMP1 and navigate to the General tab and select the desired partition.

The screen shot shows all the steps required till now

Drag and Drop from Model the other 2 Data Stores also, EMP_SALESMAN and EMP_OTHERS.

Navigate to the Component Palette and Drag Split Component from there and drop into the mapping logical area.

Click on the Split component and navigate to properties of the split component, after navigating to the Output connector points, enter the values as shown in the screen shot.

Now Drag and drop the sort component from the Component Palette into the logical mapping area and perform the changes as mentioned in the screen shot.

The whole mapping should look some thing like this

Navigate to the physical tab in the Mapping set the integration knowledge module as IKM Oracle Insert- Global.

Save the mapping and Run.

The desired output will be Only the Department no: 30 Data is selected from the EMP1 table and that is split across 2 tables based on job, in table 1 Salesman job data is insert while in other the rest of the data is inserted.

Exports and Imports in ODI

Hello Friends!!

Lets try to explore, the import and exports in ODI. This is infact a very important part of you being able to implement a development project successfully.

We should be able to know how to export the development artefacts from one environment and put them into another environment.

The typical life cycle goes like this: Develop Projects in Development environment and then export the projects, models etc and check in to SVN.

Now for a different environment check out the same artefacts from the SVN and import them into the other repository.

Now lets try to dig into this topic in a simplistic manner.

To understand this concept, lets looks at a concept of Internal ID.

An internal ID is a unique id that is given by ODI to each and every object that is developed inside it.

How Internal id is generated?

While you create a work repository, a repository id is asked, this can an any 3 digit number. right from 0-999.

If the Internal id given is suppose 2, then ODI converts the internal id to 002, so it becomes a three digit number.

The internal ID of an object is calculated by appending the value of the RepositoryID to an auto incremented number <uniqueNo><RepoID>

So now that we understood that ODI identifies things with internal ids, if 2 different master/Work Rep have the same ids, and they contain different type of objects also, still they will be considered as containing the same data.

All the objects in the Work Repository are related to each other using the IDS. The tables in the repository are related to each other using the IDS.

However the objects of the master and the work Repository are related to each other using the code.

If objects from one repository are imported to other Repository then there are chances that the missing reference error may come, this is because,the object that is imported is referring to an object id that is not present in the new repository.

If you get an error like this, import the parent element first and then the child.

In a short while,let's explore how to import/export the project in a typical full development lifecycle projects for your customer.

Lets try to explore the various modes of import/export this will clear everything that is required to understand the Development flow in a typical ODI flow.

Smart Import and Smart Export: The Smart import and Smart export feature takes care of the parent child dependencies of the object that are being imported or exported.

Due to this facility of ODI to facilitate the dependencies, the problem of missing reference never comes.

Import in Duplication mode: All the objects that are being imported will be imported in the work repository with the NEWid which will be x<work rep number>

However in this mode if the object that is being imported has an object id that is referenced, then either the object with the object id should already be present in the repo or you will have to fix manually the missing reference errors.

The IDs generated by the Duplication mode/ or any import, works on giving the ids values based on the Object type.

So it might be possible that a Model folder may have 11101 as id and at the same time in the same repository 11101 is the id for a datastore.

Synonym Insert mode: 1. Same object id is preserved in the target.

2. Missing reference are not recalculated.

3. If an object with the same id already exist in the repo, then nothing is inserted into the rep.

4. If any of the incoming attributes violates any referential constraints, the import operation is aborted and an error message is thrown.

5. Note that sessions can only be imported in this mode.

Synonym mode update:

Tries to modify the same object (with the same internal ID) in the repository.

This import type updates the objects already existing in the target Repository with the content of the export file.

If the object does not exist, the object is not imported.

Note that this import type does NOT delete child objects that exist in the repository but are not in the export file. For example, if the target repository contains a project with some variables and you want to replace it with one that contains no variables, this mode will update for example the project name but will not delete the variables under this project. The Synonym Mode INSERT_UPDATE should be used for this purpose.

Synonym mode: Insert_update

If no ODI object exists in the target Repository with an identical ID, this import type will create a new object with the content of the export file. Already existing objects (with an identical ID) will be updated; the new ones, inserted.

Existing child objects will be updated, non-existing child objects will be inserted, and child objects existing in the repository but not in the export file will be deleted.

Dependencies between objects which are included into the export such as parent/child relationships are preserved. References to objects which are not included into the export are not recalculated.

This import type is not recommended when the export was done without the child components. This will delete all sub-components of the existing object.

import replace:

This import type replaces an already existing object in the target repository by one object of the same object type specified in the import file.

This import type is only supported for scenarios, Knowledge Modules, actions, and action groups and replaces all children objects with the children objects from the imported object.

Note the following when using the Import Replace mode:

If your object was currently used by another ODI component like for example a KM used by an integration interface, this relationship will not be impacted by the import, the interfaces will automatically use this new KM in the project.

Here's a word of caution

When replacing a Knowledge module by another one, Oracle Data Integrator sets the options in the new module using option name matching with the old module's options. New options are set to the default value. It is advised to check the values of these options in the interfaces.

Replacing a KM by another one may lead to issues if the KMs are radically different. It is advised to check the interface's design and execution with the new KM.

So Now if you are in a Development environment and you are asked to set up your ODI workspace by giving you the URL for SVN repository where the ODI artifiacts are kept.

Follow the following steps:

1. First ask your Infra team for the Work Repository details, try to connect to that schema.

2. Once you connect to the schema, check if the ODI objects are already imported by some other developer, if yes, then you are saved by this task.

3. If not, then you need to start with the task.

4. Check what sort of exports are formed for your Project, if they are smart Exports, nothing else is required, the dependencies will be taken care, you just need to smart import.

5. If the exports are not smart exports, then follow the steps, first topology->then model folders->models(if model folders and models are exported separately)->Projects.   ( In order to avoid conflicts, import things in Synonym Insert update mode.)

6. Once completed the import report will be generated, Bingo check your project set up is done.

Thanks for Reading this!! 

Peace!!