TerminatOOOR

TerminatOOOR - connect your OpenERP to everything with Akretion

 

Overview

TerminatOOOR enables to easily connect OpenERP to almost any other system, using the connectivity power of one of the leading ETL solutions: Kettle from Pentaho.

 

The purpose of an ERP is to have a unique database and hence avoid the cost of duplicating data and the cost of the errors related to the duplication of data. But of course, this would mean that suddenly only one tool - OpenERP - should do everything perfectly. In practise, there is always a transition phase, at least a first migration step, during which systems should be connected or at least data should be exported to OpenERP. Also, in some relatively rare situations, it’s better to connect OpenERP to specialized third party systems than try to copy the functionality of those robust applications into OpenERP. For instance OpenERP will hardly be a good e-commerce system (security, scalability...). Or you might start doing you accounting in your existing accounting software at least for some time. You might also want to use dedicated Business Intelligence tools (like from Pentaho) for professional reporting or even OLAP reporting...

 

TerminatOOOR is here to fit that need to connect OpenERP to other systems.

The purpose of TerminatOOOR is to be a relatively easy solution - but not the easiest! - offering maximum power at a reasonable performance.

 

 

The name “TerminatOOOR” is an humorous reference to brute force: meaning the solution is not the most elegant and fast performing in the world, but ultimately it’s reasonably simple and it just get the job done. The suffix OOOR also comes from the OOOR (OpenObject on Ruby) connector by Akretion.

 

Here is how TerminatOOOR compares to other solutions:

 

 

 

pros

cons

client-side CSV OpenERP import/export

easier

TerminatOOOR is really more powerful when it comes to relating data, calling business logic such as buttons and workflows transitions...

server side CSV/XML OpenERP import/export

might be easier if you don’t have a lot of data

TerminatOOOR is much more powerful with the business logic, it doesn’t require file access to OpenERP and it’s more powerful when importation/exportation should be repeated

python code as OpenERP modules

if done right, will offer better performance

TerminatOOOR has an easier ActiveRecord like API and also it will be safer to use as the TerminatOOOR user will have no more access right than it has using the OpenERP GTK or web clients. a module developer could do any harm they want to your server instead. TerminatOOOR will have a better interoprerability also.

 

 

So as you can see, if you only have a very simple CSV file to import, you might still prefer the easy learning of the native OpenERP client-side importation/exportation.

 

Now, if instead you have complex related data to import or export or that you should interact with other things than CSV/Excel files or that you need to repeat the import/export at determined times, then TerminatOOOR is your friend.

 

How?

 

Pre-requisistes

As you will see, mastering TerminatOOOR is not the easiest thing in the world, is more oriented to be the most powerful instead. So here are the pre-requisites before you can play with TerminatOOOR:

 

  1. Good knowledge of OpenERP:
  2. basic functional usage
  3. you should know how to introspect the database/object UML structure
  4. you should be able to introspect the RPC methods called by the clients, for instance by activating the RPC logs of the GTK client with the --log-level=debug_rpc option
  5. you should be able to navigate in the source code of OpenERP to look at the signature of some RPC methods, like an on_change.
  6. Basic knowledge of Ruby, learn it here: http://tryruby.org/
  7. Medium knowledge of OOOR https://github.com/rvalyi/ooor
  8. Medium knowledge of Kettle (TODO doc pointers)
  9. Medium knowledge of the “Ruby Kettle Step” plugin github.com/type-exit/Ruby-Scripting-for-Kettle   you should specially play with the provided samples available from the sample tab of the plugin.

 

 

Installation

 

  1. Download the Kettle ETL from here: http://sourceforge.net/projects/pentaho/files%2FData%20Integration/ take the latest version, the zip or the tar.gz as you want.
  2. Extract it somewhere
  3. Download the latest TerminatOOOR plugin from here: https://github.com/rvalyi/terminatooor/downloads
  4. unizp it and put the Ruby directory inside your data-integration/plugin/steps directory, so you have: data-integration/plugin/steps/Ruby with files such as ruby-step.jar inside.
  5. Start the Kettle ETL, for instance launching sh spoon.sh on Linux or Spoon.bat on Windows in your data-integration directory. Enjoy the new Scripting>Ruby Scripting step where you can use OOOR and plenty of other killer gems!

 

Basic installation for occasional local PC usage

Case 1 extracting data from OpenERP

 

In this case, we will see a simple transformation for extracting data from OpenERP using TerminatOOOR. The goal is to show how choose the data we want to export in a CSV file.

 

Here is a list of the 32 partners we have in our openERP :

For this, we first will need to open a new kettle transformation and add the RubyScript

 

 

 

 

 

                                   

Next, We need to indicate the path to the ruby library. For that, you have to click on the tab “Ruby Runtime” and fill the Gem Home Directory

                                   

 

 

TerminatOOOR can have several tab for one step. Each tab can be :

  1. A transformation script, which will be executed for each line of the Kettle data flow.        
  2. A start script, which will be executed only once, before the first line of the Kettle data flow.
  3. An end script, which will be executed only once, after the         last line of the Kettle data flow.

 

Here, we will define a start script (right click on the script tab) which will initiate OOOR and a transformation script (right click beside the start script tab => add new script => set as row script) which will export the Partners data from OpenERP

                                   

So we now have two tabs in our ruby step. In the start script, we begin by declare the requirements we will need for the code.

 

require 'rubygems'

require 'ooor'

 

 

 

 

Then, we can make the connection with OpenERP.

To start a connection with OOOR, you can enter the parameter directly in the ruby script => Ooor.new({:url => "http://localhost:8069/xmlrpc", :database => "kettle_demo", :username => "admin", :password => "admin"})

 

Or, as in our case, you can enter those variables :

Ooor.new({:url => "http://#{$step.get_variable('oerp_host')}:#{$step.get_variable('oerp_port')}/xmlrpc", :database => $step.get_variable('oerp_db'), :username => $step.get_variable('oerp_user'), :password => $step.get_variable('oerp_pwd')})

 

 

The list of available variables that will be passed to Kettle by default is :

  1. oerp_db
  2. oerp_user
  3. oerp_pwd
  4. oerp_host
  5. oerp_port
  6. kettle_task_id
  7. task_attachment_id
  8. last_date (last date of execution)

 

 

Putting variables makes the transformation more portable, and as we will see later, these variable can be set automatically.

oerp_db represents the host name of your server, oerp_port, the xml rpc port of your openERP server, oerp_db the name of your database, oerp_user and oerp_pwd, the login and password of the user.

These variables can be defined in the parameters (edit => settings then, parameters tab) as follow :

                                   

 

We begin by creating a hash which will contain all the data we want

 

data = {

}

For better understanding of the following, I recommend to first read the OOOR documentation :

https://github.com/rvalyi/ooor

 

As we are interested by exporting information of all partner present in our OpenERP we search for them in our res.partner object :

 

res_partners = ResPartner.find(:all)

 

 

We now can make a loop with all the partners in order to choose which information we want and to write in a file

 

res_partners.each do |res_partner|

 

A particularity of the ruby script step is it can work as a normal step and execute once for each line of the transformation.  But it can also be executed just one time using this structure : $output << data

It will add a table in the $output variable for each line. and the ruby step will be executed just once.

 

Next we simply get the fields we want from the res.partner object.

 

data['Name'] = res_partner.name

data['City'] = res_partner.city

data['Phone'] = res_partner.phone

data['Website'] = res_partner.website

data['E-mail'] = res_partner.email

 

We can also get fields from an other object which is related to our object.

Adding a condition for these lines to ensure that this object is not empty.

Many adress can exist for a same partner, you have to precise which one you want (here “adress[0]”)

 

data['Address'] = res_partner.address[0].street if res_partner.address[0]

data['Country'] = res_partner.country.name if res_partner.country

 

 

Once you have all information you need, you just have to add your hash to the output

and end your loop

 

$output << data

end

                                   

The final step in this ruby script is to declare the variable you want to pass in the CSV file in the “Output Fields” tab as follow :

                                   

We are now done with the ruby script, you can leave this step clicking on the “ok” button

And add a new step in your transformation, the text file output.

Create a link from the ruby forward this new step.

                                   

 

Then, you just have to configure this last step, double clicking on it,in the “File” tab, you enter the path and the name of your file in the “Filename” field, You can choose the extension in the “extension” field (CSV for this case)

 

                                   

In the second tab, “content”, you can set the separator and enclosure fields and finally, match the header square

                                   

And it is done, you can run the transformation, open your file and see the result.

Well done!

 

 

Case 2 writing data in OpenERP

In this second case, I will show how import partners information from a CSV file to OpenERP.

First I created a CSV file with the same fields we had in the first case.

 

We can now create a new transformation. This time will extract information from a file, so we put the CSV file input as a first step. Double clicking on the step, you can enter the path of the file and its name in the “Filename” field. Choose the Delimiter and Enclosure depending of your CSV file.
Uncheck the lazy conversion and check the “Header row present square”

Then, you have to click on the “get fields” button, then ok. It will get the fields from you CSV file and will transmit it to the next step.

We can now put a ruby scripting script step, as before, we put the path of our gem directory (as explain in the previous case).

We declare the requirements and start the connection with OpenERP in a start script again :

                                   

require 'rubygems'

require 'ooor'

 

Ooor.new({:url => "http://#{$step.get_variable('oerp_host')}:#{$step.get_variable('oerp_port')}/xmlrpc", :database => $step.get_variable('oerp_db'), :username => $step.get_variable('oerp_user'), :password => $step.get_variable('oerp_pwd')})

 

Important : do not forget to declare the variable in the parameters of the transformations (see previous case)

 

After creating a link from the CSV input file step to the ruby script step, we can create a a row script.

 

This time, each steps of the transformation will be executed for each line of our file.

 

First we verify that the partner does not exist already in OpenERP.
The following code compare the name of the partners in OpenERP with the name column of our file. The result is a table, I add “[0]” in order to get a name, or nil if it does not exist.

 

unless ResPartner.search(['name', '=', $row["Name"]])[0]

 

We can now create the new partner in OpenERP and save his id in a variable.

                                   

new_partner_id = ResPartner.create(:name => $row["Name"], :website => $row["Website"]).id

 

We can’t enter all the information in the same time because these information do not belong to the same object.

The new partner is now created, we will search for the OpenERP id of his country

                                   

country_id = ResCountry.search(['name', '=', $row["Country"]])[0]

 

Once again, it return the result in a table, that is why I add “[0]”.

The id of our partner country is now stocked in the variable country_id, we will be able to enter the others information we have on our partner.

                                   

ResPartnerAddress.create(:street => $row["Address"], :email => $row["E-mail"], :phone => $row["Phone"], :city => $row["City"], :country_id => country_id, :partner_id => new_partner_id)

 

Here we creating a new partner address with the informations of our CSV file : the address, e-mail, phone and city.
We were also able to add the country because of the id we found before.

And finally, we use the partner_id to link this address with our new partner.

 

Then we can finish the ruby code

                                   

end

 

 

 

The transformation if finished, you can now save it and run it, which will give the following result in OpenERP : 

Case 3 workflows and on_change: importing orders into OpenERP

TODO

 

 

Installation of kettle_connector: wire OpenERP and Kettle together!

Transformations - howto

Once the kettle_connector is welle installed in OpenERP, we need to configure different things before being able to launch transformations.
First, we need to add a server.

After clicking on the server tab, we are able to configure our server, or we just need to indicate the name of the server, the path where kettle is installed and also you have the possibility to indicate the url of a kettle server (which is optional).

After, going in the “transformation/job” tab, we can create a new transformation, indicating the  name of the transformation, the server we will use (the one we created above), and also the path of our transformation kettle.

The ultimate step consist in create a task. In the same way, in the task step, we enter the name of the task, we choose the server we configured before, we choose the transformation we want to execute.

We can also fill in different case if the transformation need an incoming file (“Upload file box”), if we want to execute python code.....

And finally, we can add parameters to the transformation, adding them directly in OpenERP.
We already saw in the case 1 that some variables were passed to kettle By default. (oerp_db, oerp_user...)
With this feature, we can pass any other variable we want and use it in the transformation with the same structure ($step.get_variable('variable’))

Then, we just need to click on the button “start task” (on the right) or to create ta scheduler to launch the transformation automatically!

Jobs - howto

Now, we will see how to make a job using a transformation work in OpenERP. We will take the transformation we realised in the first case “ extracting data from OpenERP” and we will use a job to send the file containing the partner addresses by mail. So, we make sure you have the transformation “export_partner” working well (see case 1), then, we create a new job in kettle and save it in the same repertory than the transformation.

We can start the job with a start step.

We can now add the step which will launch the transformation. Double clicking on this new step, we can configure it. In the “Transformation filename” field, we choose the transformation we want to execute in our job.

After, in order to make the transformation work properly, we have to passe the variables from the transformation to the job (erp_host, oerp_port, ect.. ). We have to put a default value, but the job will use it only if the variable is not present in the transformation. In the menu “edit” then “Settings”, we can digit the variables.

Finally, we can add the mail step, and also configure it. In the addresses tab, we can fill the destination address, the sender, ect.. and in the server tab, we enter a smtp server. gmail offer this service free of charge for example.

Next we configure the “Email Message” tab we a subject, a text for the mail...
To add the file, we just need to fill the box attach file(s) to message and select Geral.

The file created will be automatically detected and attached to the mail.

Now, in order to be able to run this job in OpenERP, we need to zip the transformation file and the job file together. Then, in OpenERP, we create a new transformation (see the transformation chapter) choosing job instead of transformation, and indicating the zip file path.

Now we create a task using the Transformation/job we just created. And we can run it. It will create a file with the partners presents in OpenERP, save it in the path indicated in the transformation, and send a mail with this file. A lot of different steps exist to manage the file with the job. For example, we can easily delete it, move it...

logging and error management

 

An other interesting feature of the kettle connector is the easy way to access and sore the logs in OpenERP. On the task tab, on the bottom right, we can see a red line when the transformation contains at least one error, and a black line when the transformation ended without any errors.

Clicking on these line, we will be able to access the log and eventually to see which errors occurred during the transformation. 

 

On the right, we can see the log of the execution of the transformation. On the left, you can see what happened in each steps of the transformation (here, nothing happened because there was an error at the start.). It can be very useful, for the error handling for example.

Indeed, Kettle allow us to make a good error handling, in a transformation which import orders from a csv file to OpenERP, we can separate the flow in Kettle to redirect them in different steps.

Above, in the table, we can see different useful information : 344 lines read front the CSV file.
Among these 344 lines, we can observe that 217 lines contained a product not found (not present in OpenERP yet), 13 order not ready, and, finally, 114 orders without any problems.
Once the flow has been separated this way, it is very easy to write the lines containing a product_not_found error in a file for example.

Tips and tricks

Logging to the Kettle standard error or output streams

You can always use the Ruby puts(string_message) or p(string_message) but you’ll only see the result in the console where you launched Kettle. To log a specific message into the standard Kettle log, according to the log level, you should use instead:

$step.logDebug(string_message)

$step.logDetailed(string_message)

$step.logError(string_message)

$step.logMinimal(string_message)

$step.logRowlevel(string_message)

 

Faster transformation startup

When starting the connection to OpenERP with OOOR, you can add a field “:models” in order to indicate which objects you will use in the ruby script. The point is to make the script quicker. For example :                                    

Ooor.new({:models =>['res.partner'], :url => "http://#{$step.get_variable('oerp_host')}:#{$step.get_variable('oerp_port')}/xmlrpc", :database => $step.get_variable('oerp_db'), :username => $step.get_variable('oerp_user'), :password => $step.get_variable('oerp_pwd')})

 

 

OOOR tricks to make faster requests to OpenERP

TODO


XML/RPC Secure

TODO