Changing dynamically Pentaho report's, transformation's and job's databases using Python

Fri 20 February 2015

Pentaho, the best BI (business intelligence) tool

Pentaho is a great open source drag and drop tool for automated process but include some repeated jobs when you're creating these automated processes.

It enables you to create tasks called transformations and group these tasks in jobs with a tool called PDI (Pentaho Data Integration). In addition, you can use a tool called Report Designer to make some reports based in data sources.

Pentaho is really beautiful and help too much some tasks and ETL (extract, transform and load) processes, but when you need to change database for these transformations, jobs and reports is really difficult! You must open each of them and change database by hand! As follows:

Pentaho Change Database

Now, lets imagine change by hand connections for each Pentaho Job like this:

Pentaho Job

With a lot of transformations like this:

Pentaho Transformations

Avoiding 'Click and Change' Pentaho repeated jobs

Programmers (and I think all people) hate repeated jobs (or should hate). These jobs are really boring and nobody should do these things. Automated processes improve company performance and enrich your employees skills.

Not only companies need automated process. Smart houses, cars and cities are here to make sure that people don't need to do some repeated tasks like turn off lights or detect cars crashes.

Smart things are getting up new innovations and for company process we have some tools as BonitaBPM for organize processes and Pentaho for automate them, but who helps programmers to automate processes with Pentaho?

To take care of this, Kappius Company Team did Pentaho DB Changer that can change dynamically database connections for transformations, jobs and reports from Pentaho.

With Pentaho DB Changer you can change XML files with extensions: .ktr (transformations), .kjb (jobs) and .xml (reports), but for reports you need to unzip .prpt file and change datasources\sql-ds.xml file. Here is sections that XML file should have to Pentaho DB Changer works:

KTR example file

To avoid Pentaho code changes and simplify future code compatibility with Pentaho DB Changer we designed Pentaho DB Changer with a decoupled approach that can be used as a Python module instead of just executed.