It’s been a while since my last blog but I since there is always so much to tell you about Kettle, it’s high time to pick up the pace again.

This time I want to inform you that Kettle developer superstar Samatar informed me that he’s working on JSON steps as par of JIRA case PDI-3323 (input/output).

(click to see full size image)

Let’s hope the input step arrives in time to test Aaron’s (phytodata) REST/JSON web services on the BI server 3.7.

I’m also happy to inform you that Samatar will be joining us in Portugal for Pentaho’s yearly Community Meeting, September 25-26.  And, just to make sure you’ll recognize us there I’m posting a goofy picture of the both of us.

The picture was taken a few years ago when I visited his office in Paris.  I told him back then the picture would surface sooner of later

Until next time,

Matt

A few weeks ago, when I was stuck in the US after the MySQL User Conference, a new book was published by Packt Publishing.

That all by itself is something that is not too remarkable.  However, this time it’s a book about my brainchild Kettle. That makes this book very special to me. The full title is Pentaho 3.2 Data Integration : Beginner’s Guide (Amazon, Packt).  The title all by itself explains the purpose of this book: give the reader a quick-start when it comes to Pentaho Data Integration (Kettle).

The author María Carina Roldán (blog, twitter) is a seasoned BI consultant and a valued member of the Kettle community. Besides her frequent appearances on our forum, she is appreciated by many for the time she spent on the Kettle Tutorial.

I’m not going to go over the detailed table of content.  Since I wrote the foreword of the book, I’m sure you’ll agree I’m somewhat biased. However, in all objectivity, the book covers what it claims to cover: it does help the PDI/Kettle beginner tremendously.  It covers all you need to get started and then some: the installation of PDI, the typical “Hello World” setup of PDI, reading text files, calculating, scripting, databases, repositories, etc.  As the title indicates, this book covers the current 3.2 stable release of Kettle, not the upcoming 4.0 release. However, for as far as 99% of the topics covered are concerned, that shouldn’t make too much of a difference.

So obviously I can recommend this book very much. It’s a time-saver for those that are starting with PDI.  For those that have dabbled with Kettle before I must say that María packed the book with nice tips and tricks so I’m sure you’ll be able to learn a thing or two.

Until next time,

Matt

Pentaho Data Integration 4.0 and MySQL.pdf

I had a great time at the conference, met a lot of nice folks, friends, customers, partners and colleagues. After the conference I was unable to get back home like so many of you because of the Paul Simon singing Eyjafjallajökul volcano in Iceland.

So I ended up flying over to Orlando for a week of brutal PDI 4.0 RC1 hacking with the rest of the l33t super Pentaho development team.  However, after 2+ weeks from home, even a severe storm over Philadelphia couldn’t prevent me from getting home eventually.

Until next time,
Matt

Next week I’ll be strolling around the MySQL user conference in Santa Clara.  Even better, I’ll be presenting Tuesday afternoon (3:05pm).  The topic is Pentaho Data Integration 4.0 and MySQL.

The presentation will show you what the world’s most popular open source data integration tool can do for a MySQL user.  It will include practical examples and will showcase the latest improvements present in the brand new version 4.0.

Even more than the presentation itself, I’m looking forward to meeting you all over there.  The regular crowd, MySQL users, Pentaho partners, folks from Calpont, Continuent, SQLStream and many others but also the many new colleagues in San Francisco.

More than anything else I’m looking forward to hear about your Kettle successes and real-world data integration war stories.  If you want to chat about Kettle 4, see things first-hand or simply join the Pentaho gang for a beer, don’t hesitate to ask.  I’ll try to regularly tweet my whereabouts at the user conference so you’ll know where I’m at.

Let me finish with a note to everybody that promised me beer in return for features and bug fixes: it’s payback time!

See you soon at the conference!
Matt

As you know, Kettle 4.0 received a new logging framework not so long ago.  It allows us to know exactly where a log-line comes from, even in complex ETL situations.

So when codek asked to know the cause of errors in a job, it was quite easy to implement this.

Here is a single screen shot that should explain it all (click to open image):

Until next time!

Matt

Daniel & I had a lot of fun in Orlando last week. Among other things we worked on the User Defined Java Class (UDJC) step.  If you have a bit of Java Experience, this step allows you to quickly write your own plugin in a step. This step is available in recent builds of Pentaho Data Integration (Kettle) version 4.

Now, how does this work?  Well, let’s take Roland Bouman’s example : the calculation of the the date of Easter.  In this blog post, Roland explains how to calculate Easter in MySQL and Kettle using JavaScript.  OK, so what if you want this calculation to be really fast in Kettle?  Well, then you can turn to pure Java to do the job…

import java.util.*;

private int yearIndex;
private Calendar calendar;

public boolean processRow(StepMetaInterface smi, StepDataInterface sdi) throws KettleException
{
Object[] r=getRow();
if (r==null)
{
setOutputDone();
return false;
}

if (first) {
yearIndex = getInputRowMeta().indexOfValue(getParameter("YEAR"));
if (yearIndex<0) {
throw new KettleException("Year field not found in the input row, check parameter 'YEAR'!");
}

calendar = Calendar.getInstance();
calendar.clear();

first=false;
}

Object[] outputRowData = RowDataUtil.resizeArray(r, data.outputRowMeta.size());
int outputIndex = getInputRowMeta().size();

Long year = getInputRowMeta().getInteger(r, yearIndex);
outputRowData[outputIndex++] = easterDate(year.intValue());

putRow(data.outputRowMeta, outputRowData);

return true;
}

private Date easterDate(int year) {
int a = year % 19;
int b = (int)Math.floor(year / 100);
int c = year % 100;
int d = (int)Math.floor(b / 4);
int e = b % 4;
int f = (int)Math.floor(( 8 + b ) / 25);
int g = (int)Math.floor((b - f + 1) / 3);
int h = (19 * a + b - d - g + 15) % 30;
int i = (int)Math.floor(c / 4);
int k = c % 4;
int L = (32 + 2 * e + 2 * i - h - k) % 7;
int m = (int)Math.floor((a + 11 * h + 22 * L) / 451);
int n = h + L - 7 * m + 114;

calendar.set(year, (int)(Math.floor(n / 31) - 1), (int)((n % 31) + 1));
return calendar.getTime();
}

All you then need to do is specify a return field in the Fields tab called “Easter” (a Date) and a parameter YEAR (the field to contain the year).

The performance on my machine (Dual Core 2 Duo 2.33Ghz) is 134,000 rows/s for the JavaScript version and 450,000 rows/s for the UDJC version.  That’s over 3 times faster to do exactly the same thing.

Here is a link to the Kettle test transformation for those that want to give it a try.  As you can see, the deployment issue of having a plugin around is completely gone since now you can do anything you can do with a plugin from within the comfort of the UDJC step in Spoon.

The UDJC step uses the wonderful Janino library to compile the entered code to Java byte-code that gets executed at the same speed as everything else in Kettle.  This gives us pretty much optimal performance.

You can expect some tweaks to the UDJC step before 4.0 goes into feature freeze.  However, the bulk of the changes are in there and working great.  Thank you Daniel, for an outstanding job!

Until next time,

Matt

Besides refactoring and cleaning up code, I fortunately can write some new code once in a while as well.

Today, I’m happy to demo job drill down and step sniff testing for you.

The first feature, Job drill down, allows you to drill down into a running job entry, into sub-jobs, transformations and even mappings (sub-transformations). All the time, you’ll see the logging for that part of the root job as well as the usual metrics:

The second feature is also a lot of fun. It allows you to execute a transformation in Spoon and see the rows that are coming out of a step in real time. I called this feature a sniff test since that’s what it seems to be doing:

Hope you like these small usability features.  If you want to try it out yourself, get Hudson build 1598 or later.

Until next time,

Matt

I just wanted to share some good news with you regarding a new study that got published today.

Mark Madsen, an independent business intelligence analyst, together with the BeyeNETWORK took an in depth look at what is going in companies with respect to open source BI.**

You can find the complete report over at the BeyeNETWORK, but here is the graph that particularly interested me:

My congratulations to the Pentaho community for pulling this off.

Until next time,
Matt

** This study was sponsored by JasperSoft, KickFire, Talend and Pentaho

These are exciting times for Pentaho for sure.  These are also extremely busy times.  However, that doesn’t mean we can’t look around once in a while.  Today we’ll take a quick look at a new book that arrived on my doorstep a few weeks ago.  It’s titled

Pentaho Reporting 3.5 for Java Developers

I’m very pleased to be able to review this book as it is written by one of the smartest but more importantly also one of the nicest people at Pentaho: Will Gorman.  Not only that, he apparently had help from KC (Kurtis Cruzada) and Jem (Matzan) completing the dream team for this book.

And what a great book it turned out to be.  It covers pretty much everything from basic reporting, over mobile reporting, calculations and formula, sub-reporting, cross-tabs, charting down to the Java API.

Obviously, this book as been reviewed many times before by various people and websites. (Yes, it’s that popular)   To me that means that I can’t just do a quick review, I’m going to have to actually use and read the book.  And that’s what we’ll do today for this review.

We’re going to create a report in the form of a PDF.  The data for the report comes from a Kettle transformation.  We’re going to do it with my favorite programming language (Java) and a complete stack of Open Source Software…

I began by creating a new Eclipse project called KettleBook, download the source over here.
To make sure I didn’t miss any library dependencies, I used the complete “lib” folder of Pentaho Report Designer 3.5 as my class path. (not included in the download)

First, I went to Chapter 10 in the book and started reading the paragraph titled “Building a report using Pentaho Reporting’s API” as that seems to fit the bill. (page 266)

That part explains plain and simple how to create a new Master Report, how data sources work.  But wait, I don’t want a DefaultTableModel, I want to read from Kettle!  Well, a few page flips later we find ourselves on page 143 reading about the KettleDataFactory.  That got me quite far actually as the sample is quite descriptive.

So then I created a small transformation to read from a sample customer file using Pentaho Data Integration 3.2.  This is it:

It reads 100 rows of sample customer data, filters out the people from California, Florida and New York state.  That gives us 91 records.  We’re going to read from the RESULT step placeholder.

The part on page 147 I needed was this block:

KettleTransFromFileProducer producer = new KettleTransFromFileProducer("Customer data", transFile, stepName, "", "", new String[0], new ParameterMapping[0]);

KettleDataFactory factory = new KettleDataFactory();
factory.setQuery("default", producer);

This part describes a producer to the engine.

I then proceeded on page 269 and put a document header and footer on the report and an item band.  Then I put 4 columns on the page and the report was written.  This took me all of about 30 minutes. The nice folks at Pentaho Orlando will have to forgive me, reporting is not my specialty. Personally I was quite pleased that it was that easy to do.

So, with the report definition ready, I now wanted to create an actual PDF out of that.  More reading revealed that we needed a PDF Output processor (to generate the actual file) and a page-able report processor to paginate and process the report definition.  This is how it looks in my case:

  FileOutputStream fos = new FileOutputStream("files/output.pdf");
DefaultConfiguration configuration = new DefaultConfiguration();
PdfOutputProcessor processor = new PdfOutputProcessor(configuration, fos);
PageableReportProcessor reportProcessor = new PageableReportProcessor(report, processor);
reportProcessor.processReport();

5 lines of code to generate a PDF! Suffice it to say I was very happy.

In total I spent a little over an hour to produce this document:

It’s quite simple: if it weren’t for the book I would have a really hard time figuring out where to begin.  I probably would have had to talk to Thomas Morgner, the brain child of Pentaho Reporting.  A nice fellow as he is, communicating to him is not for the faint hearted. (Fortunately he recently moved to Ireland so things will get better soon)

All joking aside, if you are planning to create reports using the Java API, do yourself a favor and buy this book right away.  Even if you’re not going to use the API, Pentaho Reporting principles and concepts are explained in great detail.

Many thanks to Packt publishing for sending me the book to review and congratulations to Will Gorman and the reviewers for an excellent job.  Congratulations to Thomas and his community too for making Pentaho Reporting 3.5 a smash hit.

Until next time,
Matt

P.S. I’ll be obviously covering more of this Java API sample at the upcoming Devoxx conference in Antwerp.

Last weekend I saw an ad for a netbook in a Carrefour superstore leaflet that I guess was just too good to refuse.

Unlike other netbooks, this one was priced really low: €199,00 (including taxes which makes it cost my company €164.46 or about 200 $USD).  For me, that’s the price point where a netbook makes sense, not €400-500 what you see all over the place.

Now, for that low price, you get the following machine:

• 1.6Ghz VIA C7-M CPU
• 512MB RAM (DDR2 667, shared with video, 384 available)
• 120GB hard disk (2.5″, 7200rpm)
• 1024×600 LCD screen (pretty good quality actually)
• Webcam
• WIFI b/g
• 2xUSB 2.0
• VGA port
• a multi-format card reader (SD, SDHC, MMC)
• Microphone
• Sound in/out
• Mandriva Linux 2009.1

It was very interesting to see that “Windows 2007 Home Premium” was priced at exactly the same price.  Talk about a total waste of money on the Microsoft side.

OK, back to the netbook.  The memory issue is not a problem.  I already ordered a 2GB DDR2 RAM module for the machine at €39.

UPDATE 10/27 : the RAM arrived, was installed in 5 minutes and all works fine now.  With 1.9GB available the machine is a lot snappier too.

Performance is obviously not stellar but I didn’t expect this either.  I paid less for it then my current cell phone.  However, it plays full screen AVI without a glitch.

The only real problem the box has is that it comes with … Mandriva Linux.  Maybe I’m spoiled by years of Ubuntu use, but this distribution really sucks.  Can I please just install some software, customize the UI a bit?  Please?  I don’t recall the last time I couldn’t install a piece of software on Ubuntu because a package couldn’t be downloaded.  WTF?  And charge €28 just to get a couple of codecs to play audio/video? I can legally use these drivers in Europe without a problem.

Don’t get me wrong, all hardware is supported and works fine, including audio, the webcam, skype, flash, etc.

Anyway, I tried to put Ubuntu Netbook Remix 9.04 on it by booting from a USB stick.  Unfortunately, either the image or the stick has an issue since it freezes upon installer boot.  The live system boots but has a nasty video problem.  So I’m going to retry later next week.  Heck, maybe it’s better to just wait until Kubuntu 9.10 Netbook Remix comes out next week.

Feel free to leave advice on what distro to pick and how to best handle the install.  Also feel free to leave tips on how to explain the kids that this is not a toy.

Thanks in advance!

Cheers,

Matt