Automatically uploading Exchange Rates from a Web Service with Pentaho Data Integration

The Pentaho Data Integration tool is an extremely powerful open source tool for moving data from application to application.  I use it for automating data transfer between various applications and databases.  Why do things manually when you can automate 

One of my current projects involved a requirement to upload exchange rates into xTuple's PostBooks application.   This is a mundane task that required someone to remember to do this on a regular basis and a prime candidate for automation.  As we are using the Pentaho suite of tools for data warehousing, reporting, and application integration, automating the exchange rate upload into PostBooks was easy. 

This has proved a very useful solution and so I am offering it back to the community.

I decided on geoPlugin's free web service (http://www.geoplugin.com/webservices/currency) as this provided a powerful service that returns a nicely formatted xml file.  geoPlugin uses your IP address to determine your country and returns the conversion to the desired currency as an xml field (along with a bunch of other information) which we then load directly into the PostBooks database.

DISCLAIMER:  I am in no way affiliated with either Pentaho or geoPlugin and can offer no guarantees that this solution will work for you or that the products and services will continue to be offered in the future.  The files provided are done so with no guarantees they will work for you.  You will need to make modifications to suit your local installation.

Some Pre-requisites:

• I am using Pentaho Data Integration 3.2 and have not tested this for other versions
• I am updating a PostBooks database but I assume it will work similarly with other xTuple appllications
• I am assuming you have an understanding of Pentaho Data Integration as you will need to make adjustments to the supplied files to suit your business.  This article does not cover the installation, operation, or configuration of PDI.
• geoPlugin do not guarantee that they can determine your base currency based on IP Address alone.  You might need to register with the service to assist this process

Install the Transformations

The attached zip file contains two transformations:  the main transformation and a sub-transformation which does most of the actual work.  A Pentaho transformation is an xml file and you can open it in a text editor and view the contents although it is easier to do from within the Data Integration product.  Unzip the two files and open them in the PDI application.

Update the Database Connection

The transformations use a database connection to update the PostBooks database.   The database connection is named "PostBooks" and you will need to maintain the connection information with your server hostname or IP address, and the username and password with authority to insert and update your database.  If you change the connection name, you will need to make changes to the main transformation as described below.

Update the Main Transformation

• Open the Currencies_WebService_001 transformation.  If you have maintained the database connection, you will need to open the Insert/Update step and ensure the correct connection is selected.
• I have saved my transformations in a sub-directory called WebServices.  If you save your sub-transformation in another directory you need to open each sub-transformation step and modify the directory setting in that step.
• You cannot have overlapping exchange rates in the system.  In my scenario I am loading the exchange rates weekly.  I have scheduled the job to run on Sunday and therefore the exchange rates expire on the Saturday following.  If you want to change this, open the Dates javascript step and modify the expiry date accordingly in the line var mydate = dateAdd(currentDate, "d", 6);  where 6 is the number of days from today's date to set the expiry.
• In this transformation you can clearly see which exchange rates are being updated.  This will obviously change according to your requirements.  Copy or remove the steps according to your requirements.  In the constants step enter the currency you wish to retrieve.  This must use the international standard 3 letter abbreviation.  I suggest you also rename the constant step accordingly for ease of future maintenance.
• If you change the number of currencies being updated you will also need to change the first step Generate Rows to the correct number of currencies.

Schedule a job

Save the transformations and you can now add the main transformation to a PDI job.  It is very important that the job schedule matches your exchange rate frequency otherwise you will get errors returned from your xTuple database.

Enjoy!

Dave