Home > Configuration, ETL, OpenERP v6.0 > OpenERP, Kettle and TerminatOOOR

OpenERP, Kettle and TerminatOOOR

The blue/white elephant logo of the PostgreSQL...

Image via Wikipedia

Yesterday I have been testing Kettle and OpenERP. Other OpenERP consultants recommended me to use TerminatOOR in order to update the OpenERP data model. I was intrigued by it and yesterday I gave it a try. So this is a summary of my yesterday’s experiences with Kettle and TerminatOOR.

So when it comes to dealing with OpenERP data model, pretty much you do two things. You either extract data or load data. Importing is pretty straightforward, I was able to connect to the OpenERP server with the Table Input step and extract data with JDBC (don’t forget to install the PostgreSQL JDBC driver). This might not be the  best idea for pulling data from the system but it is a quite intuitive and efficient one. Just be sure you are pulling the right data, double check against the system. And don’t forget to tell your DBA to set the database permissions so you don’t experience any undesired visit to your database.

The real challenge lies in updating the data. You can update the data with the Table Output step, but this approach has several drawbacks. The first one is security, OpenERP can not enforce the security rules with this approach since it bypasses the application security layer. Second one is you might end up with inconsistencies in the database. Don’t forget that objects in OpenERP might update more than one table everytime they are updated/created. So if you update the OpenERP tables with a database tool, chances are you will run into consistency problems sooner or later (actually sooner than you think).

This is where TerminatOOOR comes into the scene. TerminatOOOR is (other than the movie where Arnold Schwarzenegger was smartly cast as a robot) a Kettle Step that provides a connector between OpenERP and Kettle. TerminatOOOR is a project of the founder of the brazilian society Akretion. I will not go into the details on how to install it and configure it, you can find it in the project wiki. The wiki is quite complete and helps you figure out how to use the connector and test it. And there is more documentation on the way.

Before finishing this post, I would like to make a couple of suggestions if you are trying TerminatOOOR for the first time. First, learn to use Kettle before trying the connector. Learning ETL tools and how they are used and their potential takes more than one day, so devote at least one day to learn the ropes of developing Transformation in the Pentahoo tool. Don’t forget that you will update OpenERP with Kettle, not with  TerminatOOOR. The latter will connect Kettle and OpenERP. There are tutorials online for learning Kettle and it is quite an intuitive tool to learn. But if you want to learn the ropes of developing the scripts used by TerminatOOOR, you can check the OOOR page.

Second, study the examples that come with the connector (you can find them in the samples directory). Open them and try them. They will show you how TerminatOOOR works and how it updates OpenERP. Third, don’t forget that openerp-server has to be running! It took me a while to figure out what was wrong with my transformations and the main problem is, I did not start the openerp-server daemon, therefore Kettle was not able to call the webservices in the system.

And last but not least, TerminatOOOR comes with a very handy Ruby Test Console. Try it and debug your Ruby scripts in the console before running them with Kettle. It is very handy for debugging connection problems with OpenERP (it saved me lots of hours).

Cheers!

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: