Transforming Data with Kettle

These are preliminary notes.

Setting up Kettle

If you want to design transformation with Kettle, you have first to setup your databases connections. To do this you have to edit the file simple-jndi/jdbc.properties in the Kettle installation directory.

Here is an example on how to setup this file

opentaps/type=javax.sql.DataSource
opentaps/driver=org.postgresql.Driver
opentaps/url=jdbc:postgresql://127.0.0.1/ofbiz
opentaps/user=ofbiz
opentaps/password=ofbiz

opentaps_analytics/type=javax.sql.DataSource
opentaps_analytics/driver=com.mysql.jdbc.Driver
opentaps_analytics/url=jdbc:mysql://localhost/opentaps_analytics
opentaps_analytics/user=opentaps
opentaps_analytics/password=opentaps

opentaps_analytics_testing/type=javax.sql.DataSource
opentaps_analytics_testing/driver=com.mysql.jdbc.Driver
opentaps_analytics_testing/url=jdbc:mysql://localhost/opentaps_analytics_testing
opentaps_analytics_testing/user=opentaps
opentaps_analytics_testing/password=opentaps

Designing Transformation and Job Templates

To create our date dimension seed data using Spoon, the steps are,

1. Create a New Transformation using New -> New Transformation
2. On the left, you'll see three collapsed sections, Main Tree, Core Objects and Favorite Steps. Expand the Main Tree section.
   1. Under Transformations right click Database Connections and select New
      1. Specify the connection information to opentaps_analytics, our data warehouse. Also ensure characterEncoding of UTF8 in options as above.
      2. Test the connection and ensure it works

On the right is a screenshot of the way to configure a database.

I have created a kettle template for generating the seed data (templates/opentaps/DateDimensionSeedTemplate.ktr). You can import this in Spoon and change the database configuration if necessary. That is only if you need to edit the template.

Editing a Transformation

To edit an existing transformation in Spoon, use File > Import from an XML file, and import the file (for example, StoreDimensionTemplate.ktr from analytics/templates/ directory.) When you are done, use File > Export to an XML file to save the transformation as an XML file again, instead of storing it in the database.

Debuging a Transformation

After clicking on the run button in the menubar, there is an option to activate logs with more or less verbosity.

You can use the preview button in the menubar, which let watch the whole result of one step