Wednesday, December 15, 2010

ETL - Pentaho and Salesforce.com


I'm blown away every time that I use Kettle, especially with Salesforce.

The power and utility of this ETL tool is just brilliant, and the pain threshold to get running with this tool is actually really low.

I have been working on migrating my companies contract records off the Salesforce standard Contract record and over to the newer "Service Contract" object which supports the use of product line items.

The data I'm dealing with is not well structured. We have 30+ custom fields on the Contract object, typically service quantity and service price pairs, and some extra fields that all relate together in non-obvious ways. The idea is to do move to Service Contracts, and use product line items to quantify what services a client is entitled to, the price and the quantity.

The mapping of this is non-trivial, and one of those things that if you don't automate, you will have to scrap your work several times and start over.

The Kettle ETL tool let me create one bog ugly transformation, and re-peat the job again and again until I had incrementally built the whole thing out, and iron the bugs out.

I also use this same tool extract data from various source in the business, correlate and load (insert/update) those records into Salesforce.

Every time I mention ETL in the context of salesforce, people instantly say "Oh, so your using Informatica?". I have never used Informatica, and I know nothing about it, I have little interest in even looking because Kettle is one of the most solid items in my data toolkit, and it's free!

Unfortunately, Pentaho's other reporting tools do not seem to meet the same standard as Kettle has set.

If your doing ETL, and especially if it involves Salesforce, go check this out; http://kettle.pentaho.com/ Also known as PDI - Pentaho Data Integration

#Edit; fixed typos.

1 comment:

Anonymous said...

Awesome review! I don't use salesforce, but I can relate on many levels to what you're saying about Kettle. :)

Roland Bouman
Author of "Pentaho Solutions" ISBN 978-0-470-48432-6
Author of "Pentaho Kettle Solutions" ISBN 978-0-470-63517-9