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. It should define connections to your operational ERP (opentaps) database and to your opentaps analytics database. Usually, you can copy over the file config/jdbc.properties from opentaps analytics.

When you first start Spoon, you will need to set up your databases. Click on the [New] next to the "Repository" input, and then click on "New" to set up a database connection. You would need to set up to database connections, one for opentaps_analytics and one for the operational database, currently called opentaps-psql. Select the type of the database, set JNDI as the method of access, and then entered the database name as it is described in your jdbc.properties file above:

Then, to start Spoon, click on the [No Repository] button. You do not need a repository database because your transformations are stored in .ktr files in the opentaps analytics application, not in a kettle transformations database.

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

You can use templates/opentaps/DateDimensionSeedTemplate.ktr as a starting point. Import it into Spoon, change the database configuration if necessary, and 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

If you have your databases configured correctly, you can run your transformation in Spoon to debug the results. You can click on the [Run] button at the top of the menu, watch the results, which will show you how many rows of data were read and written. This will help you identify any steps that may have failed. If a particular step has encountered an error, it will be highlighted in red. You can click on this step and then click on show logs to see the log file of the errors.

You can also use the [Preview] button, which will let you select steps in the transformation and see the rows of data after a particular step has been executed.