DEV EPM

/* This is a guest post written by Eduardo Zancanella, one of our friends at DEVEPM. Enjoy and thanks Eduardo for the content!*/

Hello folks,

Hope you are having a great day today.

We would like to share a quick tricky when it comes to transport data between different technologies.

Let’s suppose we were requested to create an ETL process to migrate data from PostgreSQL to Oracle.

Our source has some columns set as TEXT, which does not exist in Oracle. To be able to perform the ETL we would need to translate it to CLOB or VARCHAR2 for instance. But how would ODI knows it to create the temporary tables accurately?

Easy peasy, go to your Physical Architecture, select PostgreSQL and check if the data type is in there. If not, create it following the steps below (if it already exists, go straight to the step 3!)

1) Right click on Data Types, New Datatype

2) Fill it up the information as below, special attention to what is highlighted:

3) Click on Converted To and set to which datatype you want it to match in your target, in this case we have chose VARCHAR2.

After getting all this setup done, let’s run through our example really quick.

Firstly, let’s reverse our source and check if the TEXT fields are in there, keep in mind that we are trying to simplify, so don’t expect to see a full picture of the tool.

Secondly, let’s create our target table, be aware that here you must use the datatype you chose on the step 3 above for any fields that will be converted. After reversing it you will see as below:

At this time, our mapping is created, LKM SQL to Oracle and IKM Oracle Incremental Update have been chosen. A quick check on how the CUSTOM_2716 field looks like:

The hint SOURCE is an extra tip, the transformation for this case has to happen before the data is inserted into the temporary tables, always keep that in mind.

Time to run!

As a first step, ODI will create the C$ and here is where the magic happens:

C$ was successfully created!

After, ODI will follow its flow and everything should be fine.

That is how we can automatically convert different datatypes among different technologies.

Thank you everyone.

Cheers!

This entry was posted on January 14, 2019 at 12:05 pm and is filed under Guest Post, ODI, Tips and Tricks with tags Guest Post, ODI, Tips and Tricks. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.