Application Integration using Pentaho Data Integration

Last week I was on a call where the person wanted to be able to easily access their data base and make that data available to other applications via both xml and Json.  Some of their other applications require XML but they have a few that also use Json and as an organization, they are moving toward consuming Json as well.  This will show an example on how you can easily use Pentaho Data Integration to gain access to any data source (for this example we will use the sample database that comes with Pentaho). 

Use Case Example - Customer Payment Information

Currently Acme Company has all of their customer payment information in a database and they want to expose that information to a new application financial forecasting system that will help them better forecast:

• How likely a particular customer is in making future payments
• Segment customer based on their Accounts Receivables Turnover
• What is the trend in Accounts Receivables Turnover

This new financial forecasting application can accept data via either XML or Json.  The first step is to build a simple transformation made up of three steps.

1. Table Input Step - This step will pull back data from the database
2. XML Output Step - This step will convert the data into a XML format
3. Json Output Step - This step will convert the data into a Json format

The answer to how Pentaho can do this is similar to the famous quote from Fried Green Tomatoes, "The secret is in the sauce."  In both the XML Output and the Json Output, Pentaho has a very cool feature that is enabled by a simple check box, it is the "Pass output to servlet" checkbox.  In other words, this exposes Pentaho Data Integration data over a web service.  Matt Casters has also documented this on his blog here.

In order to test the example, simply import the .ktr in the link below into Pentaho Data Integration and save it to your Pentaho Data Integration Enterprise Repository and access via a simple url call:

http://localhost:9080/pentaho-di/kettle/executeTrans/?rep=Enterprise%20Repository&user=joe&pass=password&trans=/home/joe/json1

The transformation gives you two options for XML or Json Output.  To switch between the two different output formats, open the transformation and enable/disable the appropriate hop.

NOTE: This url call is specific to a generic installation of Pentaho Enterprise Edition.  The default installation port number is 9080, yours may vary depending on your installation

Download Transformation Here