Open Source ERP Guru

The next few posts are going to show several possible methods of integration between Openbravo E.R.P. , Sugar C.R.M. and Pentaho BI. I think this type of integration might be very interesting for anyone looking for a complete business solution. C.R.M. is becoming increasingly popular, especially among SMB’s, as well as BI. I have chosen Openbravo as the ‘backend’ E.R.P. system, but whatever is demonstrated here can be implemented using other open source (or proprietary) E.R.P.’s as well.

This post will demonstrate a simple ETL process where new Openbravo customers records are transferred, through Pentaho Data Integration (Kettle), to SugarCRM and created as accounts. Obviously, in a real-life scenario this would not be a manual job as demonstrated here.

The recorded demo will be added, just like all other recorded demos, to our Flash Comparator page.

Data Transfer From E.R.P. To C.R.M.

There are several ways to go about this, and it really depends on your business scenario. You might want to decide that the moment a new customer record is created in Openbravo, the record is transferred to Sugar. To do that, you would have to customize Openbravo so that when the save button in Openbravo is clicked, the Pentaho Data Integration job is triggered, a file is created, followed by a trigger to an automatic upload of this file into SugarCRM. That is possible but not so simple and relatively error prone.

The second way would be to use web-services, or implement SOA (service oriented architecture). Assuming your C.R.M. (I still need to check that for Sugar) supports a ‘create customer’ web-service, a call would have to made where all the fields in the source customer record are mapped to the web-service input fields. To do that, you would need a web service client that is able to read the data from Openbravo,map it to the web-service input parameters and then call the SugarCRM web service. One of my next posts will present this option in details.

As for the process I have actually implemented – it is for demo purposes only. I have created a job that reads 3 Openbravo tables, containing some of the customer information I need to pass to Sugar (customer name,address information etc.). I have decided to join the 3 tables using an SQL query as this is  best practice provided  by Pentaho. A file is created by the job from the Openbravo data and then imported manually into Sugar.

The job I created would be better off used in a batch process, transferring a large amount of customers and triggered by a scheduler. So you could set up a scheduler to start this Pentaho Data Integration job every night and transfer all the customer records that where create in the past 24 hours.

Openbravo Customer to SugarCRM Account – Recorded flash demo

(Right-Click ‘Zoom In’ on the flash movie for better quality)
[kml_flashembed movie="http://opensourceerpguru.com/flash/openbravo2sugar.swf" height="480" width="700" /]