Revision as of 01:47, 28 May 2012

Note: DISCLAIMER - This is an implementation guide written by Kittiu, from ecosoft Bangkok, Thailand. It is not an official guide nor meant to be comprehensive yet. Other contributors are welcome to discuss on improving it.

Status: Draft

Overview

In ERP Implementation, one of the major activity is Data Migration. This task is no doubt, tedious and time consuming, and no one want to work on it. I have been looking for tools to help in this task since I start working with ADempiere, I.e., Excel, ADempiere's Data Import Tools, or even 2Pack. And yes, we provide templates and hoping the customers will fill them in correctly, so that we can load them using ADempiere's Data Import Tools. But in reality, most data from customers are far from usable. And most of the time, we need to modify or rewrite the whole import process by ourselves.

I have been thinking about tools like ETL (i.e., Pentaho Kettle, etc.) to ease the process for a while. While most use case, are direct import to tables. Without ability to call ADempiere API to importing data is not very useful.

Until very recently, I just found that Talend has been used for ADempiere with ability to call ADempiere API some time back at Talend Open Studio. Later is better than never. So far, it is the best tools to work with ADempiere I have found. And in this article, I will try to help explaining how to use Talend with Adempiere in a bit more detail.

Start with Talend and ADempiere

The good thing about Talend that make it useful for our cases (ADempiere), is ability to write ADempiere Connector and Import Components. Following picture depict the overview of how it will work for you,

Following will briefly discuss how it works

1. Initialize database connection
2. Login to Adempiere using tAdempiereConnection component to receive ADempiere Context (i.e., Env.getCtx()). Now, we considered Logged In. This will also trigger the main import subjob.
3. This component will will initialize Delimited File (point to local csv file), read the file and send columns and data (row by row) to to tMap component
4. tMap component (the following picture), will map the input schema to the output schema. We can use expression to modify / filter / lookup data before sending to output.
5. Provide lookup to other table, in this case, we have Org Name and we want to lookup for AD_Org_ID.
6. Output data will be mapped to Adempiere's Model using tAdempiereOutput component. As you can guess, data will be created using Adempiere's API (i.e., object.save()).
7. Just for information, the tLogRow_1 will display the raw output data.

Now, if you are convinced that Talend can help your project, we can move on (if not, and you have better solution, please let me know too. :).

Before continue reading, I recommend you to read the following list of articles. In this tutorial, I will not go into details of why I am doing the way I am doing, but rather provide you with useful examples.

• Download and install Talend Open Studio for Data Integration -- http://www.talend.com/products/open-studio-di.php
• Quick study about Talend -- http://www.talendforge.org/tutorials/menu.php
  • You can lean as much here, but at least please finish the 5mn Demo - Talend Open Studio for Data Integration Overview
• Understand the concept of new component creation -- http://powerupbi.com/talend/componentCreation_index.html
  • Per say, the advantage of using Talend is about integration with Adempiere, as such, this article is critical for you to be able to know how it works and how to debug if face problem.

Setup Talend for ADempiere

Note: You can also refer to Talend Open Studio as the original document.

• Provide Adempiere core libraries
  • Copy ADEMPIERE_HOME/lib/Adempiere.jar and ADEMPIERE_HOME/lib/CCTools.jar to TOS_HOME/lib/java
  • Note: For customization.jar or other additional library, you can put it here as well. But be remembered that we will have to tell about it in the next Optional step.
• Install Adempiere Talend's components
  • Download the Adempiere components Media:Adempiere_talend_0.2.zip
  • Extract components (tAdempiereXXX) and put it in any directory, i.e., TOS_HOME/custom_components
  • Point TOS to the directory containing the components.
    • Open TOS, > Window > Preferences, in tree, > Talend > Components
    • Update field "User component folder:" to your custom_component folder.
    • Apply the change. Now, if you create new job, you should now see Adempiere components in the Palette area under Business > Adempiere
• (Optional), if you also installed customization.jar, make sure your Adempiere components know about it.
  • With TOS, > Window > Perspectives > Component's Designer
  • You should see 3 Adempiere components here, tAdempiereConnection, tAdempiereProcess, tAdempiereOutput. Each component will have a config file (tAdempiereXXX_java.xml).
  • Add new line for your customiizatoin.jar in <CODEGENERATION> part.

	<CODEGENERATION>
		<IMPORTS>
			<IMPORT NAME="Adempiere" MODULE="Adempiere.jar" REQUIRED="true" />
			<IMPORT NAME="Adempiere Libraries" MODULE="CCTools.jar" REQUIRED="true" />
			<IMPORT NAME="Customization" MODULE="customization.jar" REQUIRED="true" />
		</IMPORTS>
	</CODEGENERATION>

• Install Samples, for samples job discussed in this tutorial.
  • Download the sample jobs Media:Adempiere_talend_example.zip
  • Switch back to perspective "Integration", on "Job Design" not, right click and "Import Items"
  • Select archive file to import.

Data Migration Use Cases

Case 1: Simple Data Import - Unit of Measure

Case 2: Complex Data Import - Business Partners

Case 3: Document Import with DocAction - Import and Complete Sales Order

Case 4: Import XML Data generated from 2Pack

Case 5: Logging Unsuccessful Imports