Transforming Data with Kettle

These are preliminary notes.

Setting up Kettle

Next we need to start Spoon and have it populate the kettle database with information. It is unclear how this will work in a real installation of opentaps analytics. When you start Spoon, create a new repository named Opentaps Analytics Kettle. We will have to add a Database Connection, call it Opentaps Analytics Kettle. Specify the connection information to localhost and add the following option under the Options tab,

 MySQL  characterEncoding  UTF8

You can use the Test button to make sure you can connect. Once done, use the Create or Update button to populate this database with kettle seed data. Now you can log in with user admin password admin.

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

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