Kettle turns up Xero!

by Zachary Zeus
September 29, 2014

Integrating to Xero.com from Pentaho Data Integration (PDI)

One of the things I love about Pentaho is its great data management tool PDI, or ‘Kettle’ as it’s known. Pentaho can load in and write out over 100 different data sources. It’s the ultimate tool for data munging – I’ve never seen anything in its league for diversity and capability.

Recently at BizCubed, we’ve had a lot of requests for integration to xero.com, that’s an effective software-as-a-service (SAAS) accounting system from New Zealand.

Screen Shot 2014-02-22 at 10.40.26 am

Xero has a nice webservice / REST based API set that exposes securely lots of data for reading and writing. This makes it a perfect target for PDI (did I mention it’s a great data munging tool?).

The obstacle to all of this is the need to play nicely with Oauth- an open standard for securing webservices. Oauth is used at some of the major Web assets like Google and Facebook.

Since there was no example we could call on currently in PDI, we went to the Java-defined class level as examples were provided on the xero developer site.

Atul, our newest engineer at BizCubed, developed the java class and tested the capability in fast order. We can now load Bank Transactions out of Xero for a given user / certification and get it into the PDI for further fun.

This is our simple example:

NewImage

What we are doing here is loading Bank Transactions from a specific Xero account out to a database. The practical application of this is to set a loop around this transformation and access all accounts for a specific bookkeeper, helping them to keep their various clients bank accounts reconciled on a daily basis.

We would do this by loading this data back to a dashboard or report viewable by the book keeper(s). Currently, to achieve this, the bookkeepers need to log into each and every Xero account and check the bank reconciliation page manually.

Since this is a User Defined Java class, our next port-of-call is to turn it into a true PDI plugin which makes the first two steps a lot simpler and provides translations to all of the various Xero web services, allowing Pentaho Data mungers to load and extract data from this neat accounting tool any time they want.

A deeper dive

The two steps that matter in this transform are Step 3 and Step 5.

NewImage

Step 3 uses Java from the examples at Xero to access the web service with the Oauth certificates and extract the relevant XML for the relevant account. We’ve got the code if you want it, just fill out the form on this page and we can send it to you.

Step 5 is neat, you can use the PDI XML Path reader to extract the pieces of the Xml data returned from Xero.

NewImage

I arrived at this screen by copy/pasting the Xero XML into the ‘content’ tab as an ‘example of content’. PDI then asked me at what ‘level’ of the Xpath to loop around. In this case, it was ‘/Response/BankTransactions’ Choosing that, returned the fields as above.

Putting this step together took about 1 minute in Pentaho. It would have been 2 hours at least for a skilled experienced Java Software Engineer believe me – I was one!

Pentaho Data Integration makes it really easy to extend the reach of your current world of data. In this case, we can now build dashboards and analytics, perhaps run some predictives over Xero. A great extension to a great product.

Now.. to talk to those bookkeepers…

Portrait of Maxx Silver
Zachary Zeus

Zachary Zeus is the Co-CEO & Founder of BizCubed. He provides the business with more than 20 years' engineering experience and a solid background in providing large financial services with data capability. He maintains a passion for providing engineering solutions to real world problems, lending his considerable experience to enabling people to make better data driven decisions.

More blog posts