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" /]

Lately I’ve been looking into integration between open source BI solution, Pentaho, and Openbravo ERP. After installing and setting up the different components, I decided I would start with a very simple task-a basic ETL process, just to get a feeling for Pentaho.

To make things a bit more interesting, I decided I’d throw SugarCRM into the mix. SugarCRM is the world’s leading open source CRM solution. It is written in php and runs on an Apache web server using Mysql as the database engine. My ETL job will transfer some customer information from Openbravo to SugarCRM – that would be a scenario for anyone who is using Openbravo and would like to start working on Sugar as a CRM solution.

After installing the Pentaho BI suite, I decided to take a look at their ETL offering,Kettel. It’s a java based tool, but since the design-time of ETL is a client-side activity,I decided to run it on off my Windows XP PC. ETL tools are part of every major BI offering  because they are used to upload large amount of data from operational systems (e.g. ERP) to data warehouses.

ETL tools are also used in migration processes-if you replace your ERP vendor, upgrade to a new version or transfer data from ERP to CRM, for example,you will need an ETL tool. Although ETL tools allows you to upload as many or as few records as you like, the true strength of this tools lie in their ability to perform well when transferring very large amounts of data,sometimes tens of millions of records. In our simple test we will transfer a small number of customer records.

Openbravo->SugarCRM, Customer->Account ETL process using Pentaho Kettel

Notice in the screen capture the source (Openbravo business partner table), the fields mapping step in the middle and the destination – a CSV file to be imported into SugarCRM.

Read the rest of this entry »

Pentaho is a leading Open Source BI suite. It contains all components required from an enterprise-grade BI solution-reporting,dashboards,analytics engine,ETL and data mining. The reason I am covering Pentaho here is that I believe an open source BI can complement the open source ERP solutions I cover here.

The Pentaho offering is a classical, ‘modern’ open source model – you can download,install and use a ‘basic’ edition of the software for free, but Pentaho charges for additional ‘enterprise class’ functionality, support and some advance administration options.

The installation itself (I am installing Pentaho 1.7 GA) was not as straight forward as I expected and required some manual work. Because of that, I will write a complete installation guide, hoping it will save some time and effort to others who plan to install Pentaho. I will run the Pentaho server on my Linux Ubuntu Box.

Pentaho is written in Java, running on Jboss application server. That makes integrating it with Openbravo or Compiere somewhat easier, both written in Java as well. However, BI solutions are platform agnostic – they only rely on the underlinying data layer and are independent of the programming language used to write the ERP system.

Pentaho is a BI solution and it requires a database that will contain the data to be analyzed or mined. For that purpose, I will use a Mysql database, provided by Pentaho, with some sample data.

BTW – Pentaho delivers several out of the box default users and passwords. They can be found under the ‘Valid Users’ drop down menu on the upper-right corner of your Pentaho installation homepage.

Sample Pentaho Report

Read the rest of this entry »

A couple of days ago I wrote about my intentions to work on integrating Pentaho, an open source BI solution and Openbravo, one of the leading open source ERP products. Implementing a BI solution on top of your ERP system can provide a lot of added value for your ERP project.

After I wrote about my Pentaho-Openbravo integration plans,I got some positive feedback about the idea, as it seems a natural fit for existing Openbravo customers.

I also got an interesting comment from the Openbravo community director. I understand that Openbravo already got a request from some of its customers to add BI functionality to the Openbravo Suite, and are working on providing the integration themselves. From reading their functional specification document, it seems like they are still considering which open source BI to integrate with. I believe the decision will be to integrate Pentaho.

Obviously, this is great news. I have no plans of developing anything in parallel to Openbravo. I will try to get more details about the scope and timeline of the Openbravo-Pentaho connector. I am going to continue to work on my own connectivity,hoping I can contribute with my knowledge to Openbravo’s ‘official’ efforts, but my connector will not be released to the community as I thought I might do.

Commercial BI-ERP Connectivity Ownership

An interesting thing to note is that in commercial enterprise software, the BI vendor is always in charge of the connectivity to the ERP (or any other backend) system. The BI vendor writes an ETL connectors or adapters that can consume data from major ERP systems. No additional efforts are required on behalf of the ERP vendor. In our case, it seems like the ERP vendor (e.g. Openbravo) is going to be in charge of developing the integration-writing code, publishing documentation and supporting the solution. I think it must be a cooperation between the two project, and the ownership of the connector should be on the BI side (Pentaho). We’ll have to see how it goes.

Funny Pentaho Clip

Pentaho Installation

The next post will be an open source BI Pentaho installation walkthrough on an Ubunu 8.04 Linux Machine. Stay tuned.