As you might know, we are using Pentaho Data Integration (PDI), aka Kettle, as ETL tool in two of our DWH projects. In these projects, we also use Firebird as a relational backend for both OLTP and OLAP. In the PDI execution environment, an implemented ETL job can be triggered manually or scheduled at a given time or periodically with a given time interval. In all cases, the ETL job queries the OLTP database, checks if there are new records to move to the OLAP database and does its job, even if there were no new records to transfer. The actuality of the OLAP data depends on the chosen ETL job triggering approach.

Using Firebird, it's pretty obvious to change the whole triggering topic to an event-based approach. That said, the ETL job should kick in immediately, after a new record has been added in a particular table in the OLTP database which is relevant for the ETL job. Sadly, PDI does not have an Firebird event listener transformation step or job entry, but one can write custom plugins which are visible in the PDI IDE called "Spoon" and can be used when implementing an ETL job in a visual way. This is the route I went ...

Without going into detail how to write a custom plugin, the result looks like that:

* A new job entry "Firebird Event Listener" is shown in Spoon



* The job entry can be configured with the necessary data



* My entire ETL job looks like that now



Thanks to the excellent Firebird JDBC driver "Jaybird" and the ability to write custom plugins in PDI allows me to run the ETL job event-based now. No polling of the OLTP database and nearly accurate data in the OLAP database are the results of this move.

Great!

Thomas