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.