kJube - fair and square

Pentaho Report Designer with dynamic queries

2012-02-07T11:42:00.001+01:00

Already a year old, but still a cool tutorial.

Pentaho Community Gathering (Live)

2011-09-24T10:52:00.001+02:00

This year PCG11 is happening in Frascati, just outside Rome. No sea-side this year, but the famous hills of Rome. As last year, I'll try to do a live write-out, as things happen. (The first two presentations will be posted with some delay as the hotel wireless was down in the morning.)

As during the prevous years, we have a full house of Pentaho enthusiasts ...

9h45 Doug Moran
As usual, Doug kicked-off, with a short introduction of the event. No pictures of that as I was still complaining to the hotel staff about the failing wireless at that time.

10h Matt Casters
Matt gives an update of some of the new kettle stuff:
- DataCleaner profiling integration
- kettle JDBC driver ported over to Kettle trunk, in use in the DataCleaner integration
- KFF into PDI 4.3: first overview of the plans to move technical parts of KFF into Kettle trunk
- An overview of what happened with dynamic ETL Metadata injection

He does remind the audience and speakers that there is an unwritten PCG rule, that all presentations should be done without the use of Powerpoint, something that last year wasn't really respected. However, this will not stop me from putting up any Power Points people should have made!

10h 15 Pedro Alves
Pedro gives a live demo of how CDF dashboard parts can easily be added on a Pentaho report. This is done through custom plugins for PRD/BI server that allow directly accessing CDA and CDF components.

10h40 Alain Debecker
Alain explains how he joined linalis (Geneva) to come back to Pentaho and why he is talking about security. He started the development of a UI to manage Mondrian role grants of a complex customer. He also explained how Kettle can be used to load a set of users from an employee database. Finally, he promises a white paper soon to be published on this subject.

11h00 Coffee break
First presentations are over and the crowd (mostly with serious hangovers from the day before) are craving for coffee. And while the Italian organization hasn't proved the best a setting up the wireless, the coffee table looks quite good.

11h15 Roland Bouman (presentation)
Roland walks us through the concepts of XMLA4JS his the wrapper around XMLA which allows accessing XMLA through Javascript. Since PCG11 he's been working with people from Palo to extend this. He's has also collaborated with Andy Grohe from Pentaho to put an object model on top of XMLA4JS that would make usage of XMLA4JS easier.

Roland explains the progress that has been made on kettle Cookbook since last PCG (and demo's it):

Nicer diagrams (thanks to Slawo),
syntax highlighting,
TOC,
Incremental generation,
Saxon configurable,
xaction documentation and M
Mondrian schema documentation.

Especially mondrian schema documentation with an overview of (shared) dimensions and facts with a visualization of the star schema is looking very nice.

Ongoing work on Cookbook:

kettle repository support
KREX (Kettle repository export)

Ideas for the future:

Using the kettle auto documentation step?
Data lineage?
xaction flowcharts
prpt report documentation
diagrams for SQL queries

Pendular is Roland's latest hobby project. The idea behind Pendular is to provide a Pentaho interfaces that is better suited for mobile devices. Also here, we get a short demo. For those who are curious, here's a link to a YouTube video Matt made of Pendular when Roland release it.

And to end, Roland announces the crowd that he'll be joining the dark side soon. Another Pentaho rock-star joining Pentaho. But don't worry, not every one in the community will join Pentaho :-)

11h30 Gretchen Moran (presentation)
And while Roland is joining Pentaho, Gretchen announces PCG that she' is leaving Pentaho. She'll be working with OpenMRS in the future and she'll

OpenMRS was created as a response to HIV. There was limited infrastructure and what was in place was totally overloaded. While HIV can be treated, tracking treatments for millions of patient requires however a serious information management system. That's what OpenMRS (Open Medical Record System) was created for.

There's no "commercial side" to OpenMRS. OpenMRS have a great and large community. Gretchen has dived into that community in order to help them set up Pentaho BI with OpenMRS since they currently have no or very limited insight in their data. She has done a quick proof of concept for OpenMRS management who were extremely enthusiast.

So this presentation really is a call to the community to help set up a 100% open source BI implementation for OpenMRS, using Saiku, C*tools, and all other funky community resources.

11h45 Jos van Dongen & Aly Hollander (presentation)
And to continue in the medical field Jos and Aly present the Pentaho BI implementation at St. Antonius hospitals. St. Antonius have their home built system for the administration of the hospital. About 2 years back St. Antonius started deploying Pentaho BI on top of that system. Aly gives an overview of how their systems were developed and how the Pentaho BI project was run. Next Jos shows a great set of C*tools dashboards from the St Antonius implementation.

12h15 Luc and Julian (presentation)
Julian quickly explains how roles for Mondrian development are divided between himself and Luc. After that Luc gives an overview of all the Mondrian 3.3 features. All the new features are well documented in the presentation, so no need to fully replicate that here.

Julian quickly updates us on the following topics:

Hyde Son 2.0, some time off for the kids which allows Mondrian coding time
His new MacBook air and when he'll start wearing designer clothes
OLAP4J 1.0
Mondrian Enterprise Cache
Mondrian 4.0

Until now we've managed almost to stick to the talk schedule. Tom's presentation is the only one that will be shifted to the afternoon.

13h00 Lunch

The general idea behind having the lunch in the hotel of the venue was to save time. Julian finished around 13h30 and we still managed to squeeze in time for starters, primi, desert and coffee in slightly under an hour leaving more time for presentations.

14h30 Tom and Paul (presentation)
Paul shows off the Pentaho hosting environment that Meteorite is setting up. The original idea was to automate the set-up of a new Pentaho environment, but it grew into a full hosting offer. Through a simple web interface you can now generate your own Pentaho environment, choose what database you want underneath it, the hosting provider you want to run it with, etc. The slides tell the story.

14:45 Pedro Pinhero
Pedro demo's CDF for mobile platforms. No need for words, as you can access the demo directly here: http://goo.gl/pcbWv

15h10 Pedro Alves
Pedro gives an overview of the Firefox Telemetry project.

15h30 Matt Casters
Pentaho's Chief Data Integration is back onstage with an explanation on the Single Threader step.

15h40 Cees Vkemenade
Cees has worked with Jos on the St. Antonius Ziekenhuis project(s). When using CDF, they realized they needed a functionality to deploy changes to different environments.

15h50 Thomas Morgner (blog post)
Thomas shows how you can set up a light-weight report viewer (no GWT) as a follow up of a blog post he did a short time back. Given the enthusiast feedback he got on this post, he also goes for some discussion on how these ideas could evolve.

16h20 Bart Maertens
Bart gives us an update of his BIRT plugin for the Pentaho BI server. Even though obviously BIRT is similar to PRD, there are a few functionalities (as cross tab reports) which aren't available in PRD, that might make it interesting to use BIRT for reporting.

Bart quickly shows how the plugin should be installed/configured and then shows how that works and looks like in the BI server. For those interested, here's some blogposts bart did on the subject.

A BIRT reporting output step has been developed to. Bart based himself on the PRD Output Step in PDI. So it is now possible to set up a scheduling of BIRT reports through Pentaho Data Integration. The code for the step is already part of the kettle trunk.

16h30 Coffee break and group picture

Only coffee though:

17h00 Jens Bleuel (presentation)

This year Jens moved from Pentaho's professional services division to product management. Jens has been working over the last year on connecting PDI to his son's electric train. kettle controls the stops of the trains through a relay.

(also in high quality)

He has made some of the information from this project available on the kettle exchange site.

He also shared some ideas he is working on in PDI product management as:

lifecycle management
the heat maps
implement ID's for PDI steps
best practices, concepts and solutions on Wiki

17h25 Paul (presentation)
Paul is back with an overview of Saiku. Since the PAT presentation of last year the team was extended. Paul and Tom are now assisted by 2 other guys. Paul demo's all the cool features of Saiku and gives an overview of the road map. All very well documented in the slides.

Some really exciting stuff is almost ready to put int Saiku 2.2

Graphs (through CCC)
Write-back (with different allocation policies)

No kidding! This one is absolutely mega!!!

... and what else will follow for 2.2 are security (working with roles) and using Saiku on mobile platforms.

Some stuff that's in the backlog:

C*tools integration
PRD export
Drill-down
Parameters

17h50 Marioz
Marioz is showing a light weight ad-hoc reporting tool which uses the Saiku interface to report on top of a meta-data layer in order to generate and execute queries. He also wants to merge PRPT functionality into this in order to leverage PRD's lay-outing possibilities.

18h00 Ricardo Pires (presentation)
Ricardo gives an overview of the Pentaho Fusion Charts Plugin XpandIT have developed (see also WebEx on this topic earlier this year). There have been a lot of downloads since it was released so it would seem there is a lot of interest for this type of charting in Pentaho.

And there is a small tutorial on YouTube.

18h10 Slawo Chodnicki
Slawo has been working on a Ruby scripting step for PDI that used JRuby. More information and the code are as always well clearly laid out on his blog.

18h40 Roland Bouman (presentation)
And to wrap up, Roland is giving a talk about data vault and anchor modeling.

And #PCM11 / #PCG11 is over
... thanks for joining us!

Once twitten, twice shy

2011-09-20T15:34:00.002+02:00

In the preparation for the Pentaho Community Meeting 2011 in Frascati, I suddenly recalled some events that occurred during the voting for the location.

One week before the closing of the votes, @magicaltrout fueled the discussion.

Luckily Twitter allowed me to go back in time to dig up some tweets from prominent members of the Pentaho Community. There seems to be a clear indication that members have been trying to "buy votes" in order to influence the location of the event. Below tweets clearly illustrate this.

Now, I don't believe there is any rule against such bribery as long as the bribes are being paid.

So, if I've been reading right, we should figure out when the community can cash in on the following:

beers from @josvandongen
wine from @mattcasters
grappa from @rolandbouwman
home brew liquor from @jan_aertsen

Being on the list, I suggest I'll bring my bottles on friday evening. I guess the Community will figure out a right place and time to consume!

As for the others: your words have not been forgotten. Watch out what you tweet next time.

PCG11 Prologue

2011-08-30T23:11:00.001+02:00

Pentaho usage is growing rapidly, and thus those who have Pentaho skills are very busy. As a consequence, the organization of the annual Pentaho Community Gathering has been 'a bit slow' this year. And while Pentaho employees normally aren't allowed to stick their nose in community business, somehow the organization of this years venue ended up on my task list. (Thanks Doug!)

So there I was, mid August, 40 days to go, nothing set. I asked my wife to call some hotels and by August 22nd answers arrived. After some comparison of offers a decision was made on order of preference. To finalize the selection process and in order to make sure the community would get the very best location on this side of the planet, I decided to go and have a look on the spot.

I jumped in my car and started driving, whilst punching the address of the first hotel into the GPS. Only 1576km and I could be there by the next morning 7AM. I've done BI projects that lasted a lot longer, so piece of cake. Here's the dashboard (sorry Pedro, not done with Ctools):

Unfortunately, weather conditions weren't that good. While driving through France heavy rain showers hit me. It slowed me down seriously and even obliged me to stop over in Switserland, waiting for things to clear up. And if that wasn't enough, when I started the journey again, the next day, a sudden temperature drop made things even worse.

Here's the dashboard (because we love them so much) showing the situation (again Pedro, sorry no Ctools):

Yes, if you look carefully the dashboard says zero degrees. And for those who think you can prove anything with numbers and dashboards, here's the pictures of the road situation at that time.

But a Community Gathering had to be booked. And I needed to move on. Once I passed the alps weather cleared up seriously, and I started trying to catch up the time I lost. Just after lunch temperatures started to be more acceptable (33 degrees) and I had found a nice cruise speed to make it to Frascati in a reasonable hour.

And around 15h30, I finally reached Frascati. I visited some of the locations, I finally opted for Hotel Flora, as you can read by now on the PCM11 wiki pages.

Why do I want to share this silly story? Basically, I'd like to express my great appreciation for all the people who volunteered to set up the Pentaho Community Gatherings in the past. Organizing such an event does take time, and it is amazing that Pentaho Community members have always picked this up, notwithstanding busy agenda's and thousands of more interesting things to do :-). So, to who picked this up in the past, and to who'll pick this up in the future: a great thanks.

Proprietary BI vendors feel the heat

2011-04-21T10:46:00.001+02:00

Commercial open source software for BI has been coming up strong for the last years and keeps on marching forward at a rapid pace. For several years now, we have experienced in several projects that commercial open source BI is a viable alternative for the traditional vendors. Gartner has been slow to confirm that (as they usually are when it comes to open source), but they their recent market studies now include open source consistently. And today I noticed that also the proprietary vendors have to publicly admit that they feel the competition from the open source vendors. Look at this page on the MicroStrategy website. They clearly feel they need to compare themselves to Pentaho, and since they are comparing to version 3.6 which has been out for about a year, I'd guess they have been doing a lot of explaining to their customers since some time already.

I feel rejected!

2011-03-09T16:54:00.000+01:00

On september 21st 2010, I wrote in this blog post the following lines.

Rejects: A generic component for error handling. It will converge all error records into one common format so all your rejects fit in one and the same output file or table. We'll elaborate on this one soon in an extra blogpost. Documentation will be added to the KFF pages.

Today, while on the phone with "a fan of the art of data integration through the means of Pentaho Data Integration", I realised that I never wrote that extra blogpost, and neither did anyone update the KFF pages with regard to the "Rejects Step". So it is time I amend.

Error handling in kettle

The standard error handling in kettle is probably well know to all of you. For those who aren't up to speed, a quick intro follows here.

A common scenario is shown below.

You have stored information in the staging area of your data warehouse, which you need to load into an ODS. The ODS area of your data warehouse however needs to contain validated or clean data. Therefore your ODS table(s) may have foreign key or other constraints to ensure that your data is correct. Consequently any record you try to write to the ODSTable, but that isn't allowed by the data model for whatever reason (value larger than the field, precision not correct, foreign key not found, ...) will throw an error and your transformation will end with errors.

This will make you, as an professional data integration designer/developer, very unhappy. So you will adjust your transformation and put a lot of logic between the data in and data out steps, to ensure that everything can be loaded correctly to the target model.

But nevermind how precise and clearvoyant you are, at some point in time you will have some nasty record passing by for which you didn't write the right code, and bang, your transformation goes in error. Then you discover kettle has error handling functionality for these nasty records. Yes, kettle can divert records, which the data output steps cannot process, to another step. That looks as follows.
Basically, what is done very often, is just divert the records that cannot be inserted into the database to a flat file, and leave it there so some one can have a look at why this record wasn't inserted into the ODS or data warehouse. At the same time this permits your data flow to end without throwing errors and aborting in the mid of the night so you need to get up and restart that data warehouse load or else you'll find 10 angry managers at your desk in the morning.

In the data output step where you enable the error handling (in this case the ODSTable step), you can decide some extra settings for the error handling.

For those who want to read up bit on this feature, go and surf the net. Try these pages:

original blog post from 2007 when Matt first released error handling
Pentaho wiki: the data validator and error handling
Pentaho wiki: error handling codes
Slawo in 2010 on Javascript step and error handling

The documentation isn't abundant, but the information is out there. My little scenario ends here, because I really want to talk about the 'Rejects step' and not do a write up on Error handling in PDI. So here I go.

Why a rejects step, if kettle already does it all?

As you might guess from the above scenario, one of the problems you often end up with if you push all the rejects to flat files, is that you end up with a large amount of files, with a lot of records to analyze for data quality. If you are lucky, you are working with to shelf data, and you'll have few rejected records and few files to look at, but most people will not be that lucky.
So as your data warehouse grows (not only in size, but also in data integration logic and code), the amount of rejects files will grow and you'll loose the overview of what is happening in terms of data quality.

To avoid this problem, we came up with the idea to merge all rejects into one single database table. This has some advantages:

Having all your rejects in one single place, makes it easy to make some statistics on your rejected records.
If they are in a database table, they really are just part of your data integration logging, and therefore you can easily include them in the logging reports you already make every day to see/show how the ETL runs have been going.

So what does it look like?

Basically nothing has been modified to the standard error handling mechanism of PDI. The only thing we added was the output step for the rejects. In this step you can define the following things:

Rejected records connection: database connection to which you want to write the rejects (this can be a parameter)
Rejected records schema: database schema o which you want to write the rejects (this can be a parameter)
Rejected records table: database table to which you want to write the rejects (this can be a parameter)
Error count field: name that you want to give the column containing the error count (this can be a parameter)
Error descriptions field: name that you want to give the column containing the error description (this can be a parameter)
Error fields field: name that you want to give the column containing the error fields (this can be a parameter)
Error codes field: name that you want to give the column containing the error code (this can be a parameter)

Below those input fields you can specify which list of incoming fields make up the key of your record.

The rejects table will have the following lay-out.

id: This column contains all the key fields of your rejected record in comma separated format (provided you specified them in the rejects step)
value: This column contains all the remaining (non-key) fields of your rejected record in comma separated format
batch_id: Your batch ID (see KFF)
transname: name of the transformation that generated the reject
jobname: name of the job that generated the reject
stepname: name of the step that generated the reject
${error_count_field}: nbr of errors
${error_descriptions_field}: error description
${error_fields}: field that generated the error
${error_codes}: error code
logdate: date/time of the reject

Simple enough to write some statistics on. Should I want to know which steps generate most errors, a simple query of the type:

select stepname, count(*)
from ${rejected_records_table}
group by stepname

would tell me enough.

Now if you add the element time to this, storing your rejects for each nightly batch run, you can plot a nice evolution of how you have been handling data quality problems over time. Any data warehouse project will sooner or later require this kind of statistics, because data quality problems are there. The question is only when they will surface.

Good luck with it!

Another kettle book on its way ...

2011-03-08T12:21:00.000+01:00

For the kettle afinados out there, two excellent PDI books are already available namely:

Pentaho 3.2 Data Integration - Beginners Guide, by María Carina Roldán was released in april 2010.
Pentaho Kettle Solutions - Building Open Source ETL solutions with Pentaho Data Integration, by Matt Casters, Roland Bouman and Jos van Dongen (extensively discussed in a previous post) is available since september 2010.

And if that is not enough, another book is in the making. María Carina Roldán has teamed up with Adrián Pulvirenti to deliver the "Pentaho Data Integration 4: Cookbook" (again with publishing house PACKT).

The book will deliver a series of 'recipes' to solve typical (and less typical) kettle riddles, divided over 9 chapters with the following titles.

Working with Databases
Reading and Writing Files
Manipulating XML Structures
File Management
Looking up for Data
Understanding How Data Flows
Executing and Reusing Jobs and Transformations
Integration with Pentaho Suite
Some More Useful Recipes

I have had the pleasure revising chapters 1 to 7 up to now, and will soon read chapters 8 and 9 which are in the writing.

My impression so far is that the analogy with a cookbook is actually well choosen.The recipes are organised in logical groups like you would have starters, rice dishes, deserts etc in a cookbook, but still the recipes within a same category, though logically related can be very different, like vanilla pudding and chocolate mouse.

Maybe not all of them may be to your liking, as each recipe tackles a different problem. And some people just prefer chocolate over strawberry. But having said that I'm not saying you shouldn't get the book because there might be some recipes you don't like or don't want to use. Actually, while reviewing the book, I found all the recipes interesting. And even if there are a few I won't use to literally cook the dish Maria and Adrian sugges, I will use the ideas I picked up in them to flavor my own dishes.

You can already get the book in RAW format from PACKT right now, or way till the end of april until all chapters are complete and the book becomes available.

Blog for mobile devices

2011-03-08T10:30:00.003+01:00

We noticed that about 5% of our blog visits are from mobile devices. This percentage has been increasing steadily over the last year.

Since november or december 2010, Google Blogger (finally) supports adapted lay-outs for visualization of blog pages on mobile devices. Given the fact that you are using mobile devices to look at our site, we have enabled this feature on our blog. We hope it will improve the readability of our blog while you are on the move.

What you should see when you go to our blog on your mobile device is the following:

Enjoy the reading. And don't hesitate to leave your comments.

Connect as sysdba

2011-03-08T10:01:00.001+01:00

Sometimes, when working with Oracle database, you need to connect as sysdba or sysoper.

A user must connect AS SYSDBA or AS SYSOPER if he wants to perform one of the tasks that require sysdba or sysoper privileges(such as to shutdown or to startup an instance. If he connects as SYSDBA, he becomes SYS, if he connects as SYSOPER, he becomes PUBLIC.

On the command line, using sql-plus this is pretty simple. Just add "as sysdba" to your connect statement.

connect sys/my_secret_password as sysdba

However when you are working with a graphical user interface logging in as sysdba isn't that evident. Basically because the login box that you get presented just allows you to insert a username and a password. So where goes the "as sysdba" part?

Using SQL Developer

Just add the "as sysdba" statement after your username. I needed this not so long ago, I luckily found it after some Googl-ing. My sql-plus isn't up to par, so really being able to use a GUI is really a time saver.

Using Quest Toad
If you are using Toad, the answer is fairly simple. They have provided a little drop down list called "connect as" which allows you to select between "normal", "sysdba" and "sysoper".

AD/AC

2011-03-08T07:47:00.003+01:00

Mobile blogging

2011-03-08T07:42:00.064+01:00

This blog post wasn't written entirely on the Mobile Blogger app by Google. Some editing has been done afterwards using the standard Blogger webinterface. All editing done afterwards is listed out below.

Google finally released a mobile app for Blogger. Since I happen to travel a lot the following days, I thought to give the app a try, just to kill time on the road and getting ready for the next PCG ;-)

First of all let me say I'm a fan of the available Google mobile apps. Gmail on Android is excellent. Mobile Maps is beyond excellent. Skymaps is ultra cool and though I hardly use YouTube on my computer, I started using it on Android. Needless to say I have expectations for this Mobile Blogger app.

From the moment you fire up the app, you are connected to your Google account. Mobile single sign-on, nice. And the app will present you with your list of blogs. So you simple pick the right blog from the list and ... action.

Creating a new mobile post and edit it with Mobile Blogger is pretty intuitive. Just hit the pencil button on the main screen and you are ready to go. There is a first text box for the title and a second for your post. Easy enough to start a new post, for instance while you are standing in a cue to check in your luggage in Ciampino airport.

At the bottom of the screen there is a publish/save button, which allows you to publish your post or save a draft, same as in the web version of Blogger. I believe saving isn't done automatically (which is the case in the web interface). A bit annoying, as you will have to remember to regularly save your work. Seems like MS Office 3 dot something all over.

Next to the pencil button, there is a button to see you list of mobile posts. It is important to know that that list will only show mobile posts (published and draft versions). Any drafts or posts you may have made on your pc, you will not see in this list. So forget about quickly adding a few lines to a post on which you were working from home. You just don't have access to those. And vice versa, you don't see your mobile posts in the web interface. So there is zero interoperability. A missed opportunity if you asks me.

Under the main text box, you find two buttons to add images. By now I can already tell you that you will probably use these more then actually writing text. An Android phone just isn't theft right device for massive text input. Also since you cannot lay-out (as in using bold, italics, underline, different fonts, ...) your text in Mobile Blogger app, creating a lot of text doesn't make sense. You have to few means to control the readability. So adding images will be what you'll do most using this app.

The first button allows you to take a snap and insert that right away in the text. Here's the boring view from my train seat.

After you'be taken the picture the screen goes black for about a second or two. What the app does in that moment isn't clear. I would guess uploading the image. The second image button allows you to pick a picture from you Android's media gallery. Again, after selecting the image the app gets sluggish for a second or so.

The pictures you add will appear next to the buttons, and not in the text. This way you don't know where your pictures will appear in your text. And since there is no preview feature I have no clue while writing this where the picture of my metro ride will appear. Not really very user friendly.

Just below the picture bar there is a line to type your tags. Unfortunately you will have to remember your usual tags as the app doesn't seem to know my tag list. Again, a bit annoying as I'd rather keep my tag list nice and clean.

And finally just above the publish/save buttons, there is a line which allows you to geotag your post. Since you can only add one single location, this seems to indicate you aren't supposed to write long posts, meaning if you are writing on this Blogger app for over two hours on a high speed train, your location changes regularly, which isn't supported.

All in all I must say I'm not overly impressed with this app. It is hardly a step up from the SMS/MMS and email posting that has been available for years. Sure I can tag and geo-tag my posts now, but what extra functionaries are there. In general it stems Blogger keeps lagging behind on other blog software and services, also in the mobile field. Hopefully getting a first version on the market, will be the beginning of some rapid improvements. The feedback form included in the app would seems to indicate Google wants listen to the users. I really hope they make something of this app.

PS: While walking past the tram stop close to my place, I noticed the light boards indicating the position and ETA of the trams finally became operational. Those board have been hanging there for over a year without lighting up (except for a short period where they showed the hour). Let us pray there is no analogy with Mobile Blogger.

Editing done afterwards:

The following editing of the text has been done using the standard Blogger interface.

All pictures which are added using Mobile Blogger appear at the bottom of your post. I move all images to the right place in the text, resized them and centered them.
I changed the appearance of all the text to 'justify' and added a blank line between all paragraphs as the text wasn't really readable otherwise.
I put in the wrong label, so I updated that.
I've added some links to the text. I didn't figure out whether that could be done using the app.

Conclusion:

As the app is today, I don't suggest using it for any serious mobile blogging. It is good to quickly upload a picture with some text and geo-tag it. Beyond that, the app will just let you down.

Kill or protect the Toad?

2011-02-24T11:00:00.001+01:00

A short story about closed source killing real open source and commercial open source killing closed source.

If you have every come close to an Oracle datapond in - let's say - the last 15 years or so, then you must have seen the Toad sitting somewhere close to the pond. If you don't recall seeing any of these little animals, the picture provided here shows what they look like. They are extremely attracted to Oracle ponds and the fisherman surrounding it. So really, you must have seen them. And if you haven't seen them you most definitely must have heard them, because they make a specific noise when waking up.

The Toad stams from the family of the Questus Questionalibus. Now a very interesting fact about this Questus family is that they are known to be a very dominant species who are very protective of their extremely closed ecosphere (sometimes called the orasphere). Many assume that their dominance just stems from the natural 'survival of the fittest' paradigm, but there is a large base of scientists and biologists that believe the Toad dominance in the orasphere is unnatural.

The Toad have an very clever way of protecting their environment. One way they cope with genetic competition is, if a related species shows up, they will first invite it into their family and mate with it. After this first phase of hospitality however they will slowly take over some of the other animal's genes. Also once the animal is invited into the Quest ecosphere and the initial gene exchange has been accomplished, the poor newcomer will be excluded from any sexual activity, thus condemning it to extinction. This happened about 6 or 7 years ago some years ago with an small species call the Tora. Shortly after the Tora was invited into the orasphere somehere during 2004, it's sexual activity dropped to zero.

Interventions from animal protection groups didn't help much. Up to today, the poor Tora species is barely alive. And it's genetic development has come to a near stop while the Toad species has flourished.

Over the last few years however a new evolution has taken place. The owners of the Oracle dataponds have been watching the rapid growth rate of the Quest frog with great attention. Scared by the effects that a too dominant species could have on the pond's ecosphere, they decided to introduce their own species, with the scientific name of Oracle SQLD.

This genetically engineered animal has genes which are specifically adapted to the Oracle pond environment, thus making it a competitor that has sufficient gene strenght to survive against the Toad. Additionally the also made de gene structure of their frog so open that it could adapt whatever gene code from the outside in order to allow it rapid evolution. An amazing point of strenght for this little frogger.

Additonally the pond owners clearly supervise the population carefully avoiding any mating between the species as this would not be in the interest of the pond owner. The effects were and are amazing. The Toad population gradually dropped over the last few years, while the SQLD's took over the house, as shown by the graph below.

Clearly this case learns us a few things

First of all, sometimes a species needs some support from us humans to actually break a natural dominance of a specific species that tries to close and dominate the ecosphere. I believe everyone agrees that opening up the orasphere is a good thing for natural evolution
A second of all is, how far do we want to go with this human intervention. Clearly some diversity in the orasphere was a good thing, but where we seem to be heading now, is to wiping out the Toad species and letting the pond owner decide how the ecosphere should look like. Is that a good way to go?

To end this article, I want to underline that I'm not a biologist. I just have an interest in the Oracle ecosphere as I sometimes go fishing there. I though it would be interesting to write down my observations and see what other people think of this evolution of species. So I'm looking forward to the comments of all the frog lovers out there!

Slowly changing dimensions slowly appearing

2011-02-14T14:32:00.000+01:00

A short time ago, I came across this blogpost written by Todd McDermid regarding his Kimball Method Slowly Changing Dimension Component for Microsoft Sequal Server Integration Services (MSSSIS). The first version of this component has been release somewhere during 2008, as an open source contribution to MSSSIS and has grown tremendously popular since then.

Now, anyone who has ever built a data warehouse using the Ralph Kimball approach , will not be amazed by the popularity of such a component, as slowly changing dimensions are an quintessential element of dimensional modeling, explained by Kimball in his very first writings on dimensional modeling. (See: "A dimensional modeling manifesto", DBMS magazine, 1997)

Those among you who know MSSSIS a bit,might know that Todd's component isn't the only way to achieve slowly changing dimensions using this Microsoft tool. Some alternatives are available.

First of all there is a slowly changing dimension wizard available in MSSSIS, which will generate a slowly changing dimension for you. Unfortunately this wizard has some downsides:

Obviously a code generation wizard has the downside that, if you do any tweaking on the generated code, all your edits will be overwritten in case you should rerun the wizard. Since the coding required to do slowly changing dimensions can be relatively complex - otherwise why would you need the wizard - that is a true downside.
Another downside of the wizard seems to be the performance. The generated code just isn't performing very well. And that is again a real shame, because data warehousing usually deals with large volumes of data. Actually, if you wouldn't have large volumes, you probably wouldn't even need a data warehouse, let alone a slowly changing dimension.

Secondly, if you don't want to use the wizard, you can always revert to writing the whole logic for implementing a SCD by yourself, using the existing MSSSIS components or some smart scripting, an the T-SQL merge (oh, only available since SQL Server 2008) I don't even need to argue for this case. If the logic to implement a SCD is so complex that you would want a wizard to generate it, why would you even want to write it manually.

So, in the end, maybe Todd's way seems to be the only right way to handle the implementation of slowly changing dimensions. One single component that handles it all, without any need for you to worry. And that conclusion brings me to the real question of this blog post?

Why would Microsoft want to release a data integration tool without any decent support for SCD? And why is it up to an open source initiative to actually fill that gap? MSSSIS was introduced with the release of SQL Server 2005, yet users had to live with a (buggy) wizard until Todd released a SCD component in september 2008. That is three years before a decent and working alternative became available. With the popularity that data warehousing has enjoyed in the last decenium, it is amazing that MSSSIS developers haven't marched down to Redmond to slice up the SQL Server development team using the genuine installation disks.

Having experience with ETL and data integration tools from likes of Informatica, IBM, Oracle, ... I cannot help by notice that Microsoft isn't a stand alone case. Most of the data integration vendors have been ignoring proper support for slowly changing dimensions, a concept that has been around for about 15 years now. Informatica Powercenter offers up to today only a wizard to implement SCD. IBM Datastage has included support for SCD since the release of Infosphere Datastage in 2009. How different from an open source product like kettle (aka Pentaho Data Integration) that included already in its very first release a SCD step.

Large data integration vendors, please hear me. Why o why is it, that we can expect support for SCD from open source initiatives but not from you? Slowly changing dimensions are as elementary to data warehousing as the 'CREATE' statement to a relational database. Wake up! And start delivering!

The box maybe virtual but I'm still stuck with Windows

2011-02-10T23:12:00.000+01:00

Running a Windows Machine in Virtual Box Seamless mode on top of a Kubuntu, can look pretty confusing. I mean, Windows security center right next to KPackagekit and Dolphin?

Anyhow, one of the few reasons I sometimes need to revert to Windows is to run either CA Erwin of Sybase PowerDesigner. It just seems to be the case that there aren't any fully featured data modelling tools for Linux available.

And of course. KFF needs to be tested on Windows. It seems there are people using kettle on windows.

Customer service from my mobile provider

2011-02-04T00:06:00.000+01:00

This blog post contains total non information. So please, move on if you have better things to do. I warned you.

On the other hand, if you are bothered with the service level offered by the help desk of your mobile provider, have a look at this picture from my Android phone and know that you are not alone.

Seeing is believing! Yes indeed! After I had - for the fourth time in four months - a problem with my invoice, I called my mobile provider, know as Proximus in order to have the error corrected. Alas, I never got through to them. I gave up after 1 hours and 5 minutes of listening to their promo music.

If you think you can do better, below you'll find the music that Proximus (ab)uses - for the record, I like(d) Wim Mertens - for the people stupid enough to remain on hold. Please try to listen to this tune for over an hour. Good luck.

Magical indeed

2011-01-31T17:02:00.000+01:00

Gartner have just released the magic BI quadrant for Januari 2011. Magic indeed, as some things that are going on must be based on magic rather than on objective criteria.

Januari 2009

In Januari 2009, Gartner released a magic BI quadrant. Pentaho doesn't make it to this magical quadrant due to revenue requirements. Gartner added the following comment to their magical quadrant text:

“However, while they don’t meet the revenue requirement, Jaspersoft and Pentaho have emerged as viable players in the BI platform market and as such we invited these firms to take part in the Magic Quadrant user survey. Both open source vendors provide comprehensive BI platform capabilities that are comparable to traditional BI platform vendors. A key part of both vendors’ strategy is to forge OEM relationships with commercial independent software vendors (ISVs) looking to easily embed BI functionality at a low price point. Jaspersoft and Pentaho enable ISVs to OEM open-source BI components without being bound by the GNU General Public License (GPL) terms and conditions. Given their subscription-based model, both vendors need to provide exemplary support. This was in evidence in the MQ reference survey, as both Jaspersoft and particularly Pentaho scored strongly on the customer support question — higher than any of the megavendors.” Source: Sherlockinformatics

Januari 2010
In Januari 2010, Pentaho doesn't make it to the magic quadrant. Gartner explains why:

"Beyond the emerging vendors, Gartner gave serious consideration, as it did last year, to including open-source BI suppliers in the Magic Quadrant. While this year, both major open-source BI platform suppliers generated enough revenue to be included in the Magic Quadrant, they did not garner enough customer survey responses. Although they did not meet the references requirement, Jaspersoft and Pentaho have emerged as viable players in the BI platform market. Both open-source vendors provide comprehensive BI platform capabilities that are comparable in many functional areas with those of traditional BI platform vendors. A key part of both vendors' strategy is to forge OEM relationships with commercial independent software vendors (ISVs) looking to easily embed BI functionality at a low price point. Jaspersoft and Pentaho enable ISVs to embed their open-source BI components without being bound by the GNU General Public License terms and conditions. Given their subscription-based model, both vendors need to provide exceptional support. This was reflected in the Magic Quadrant customer survey, as both Jaspersoft and Pentaho scored strongly on the customer support question — higher than any of the megavendors for the second year in a row. Source: Gartner

Januari 2011
In Januari 2011 Pentaho again doesn't make the magic quadrant. Gartner comments.

Pentaho garnered enough survey customer responses for inclusion on the Magic Quadrant, and it indicated to Gartner early in October 2010 that its 2010 BI platform revenue would meet or exceed $15 million. However, Pentaho recently informed Gartner that its growth in BI platform revenue (separate from its stand-alone extraction, transformation and loading [ETL] revenue) was slower than expected and thus represented a much smaller percentage of its overall revenue. This resulted in Pentaho falling well below the survey revenue inclusion requirement of $15 million (this will be reflected in the upcoming 2Q11 report, "Market Share: Business Intelligence, Analytics and Performance Management Software, Worldwide, 2010"). Subsequently, Pentaho was excluded from the Magic Quadrant this year. However, it did provide enough customer references to be included in the Magic Quadrant Customer Survey research notes that will publish in 1Q11. Source: Gartner

Remark: Gartner defines total software revenue as revenue that is generated from appliances, new licenses, updates, subscriptions and hosting, technical support, and maintenance. Professional services revenue and hardware revenue are not included in total software revenue.

Enough sales in 2009 but not in 2010?

So if I read this right, according to Gartner, Pentaho realized enough revenue in 2009 (inclusion criteria: $15 million") but didn't realise enough revenue in 2010 (inclusion criteria: $15 million). That would seem strange since Pentaho announced a 120% growth of bookings during the year 2010. How is it possible they suddenly end up "well below the survey inclusion requirement of $15 million"? The whole thing makes me wonder about the decision criteria Gartner uses to compose their magical quadrant. How much do these analysts really play with the criteria to get a wanted output.

Can some one enlighten me? Please?

50 ways to make your report

2011-01-31T14:06:00.000+01:00

Pentaho Business Intelligence offers a variety of ways to make reports. A large variety. Or should I say a really very large variety. In this post I'm trying to list out some of the options as well as how to manage that diversity. Given the title of this post, I couldn't help but include the appropriate background music. So please, hit play, and read away.

8 tools to get the job done

As a starter, I've tried to make a little drawing of the different tools included (or includable) in the Pentaho BI server. As you can see, to make a simple report, you can already choose between 6 different reporting tools, namely:

Pentaho Reports (made with Pentaho Report Designer)
WAQR, the Web Ad-hoc Query Reporting tool, an online wizard to generate PRD reports.
BIRT reports (made with Eclipse based reporting system: BIRT)
Pentaho Analyzer (LucidEra's ClearView product, acquired by Pentaho)
JFree Report (the Ad-hoc reporting engine Pentaho offered before Analyzer)
Saiku aka PAT.

And if that wasn't enough, I'm leaving out of the picture (literally) 2 tools for dashboarding, which also could easily by (ab)used to create a simple report.

Pentaho Dashboards and
the Community Dashboard Framework and the Community Dashboard Editor by WebDetails

OK. So if we count also the dashboarding tools, we have 8 different tools to make a "report", that is by any Business Intelligence standard, a large choice. But there are more choices to make.

Endless ways to get to the data

If you decide to go for Pentaho Reporting, you will have to decide how to fetch your data. Again, there seems hardly any reason to lament about the number of choices at your disposal. Here is what you get.

JDBC: This allows you to define your own JDBC connection (or use an existing one) and manually write a SQL query that will be executed against that connection.
Metadata: This method will use the Pentaho Metadata Layer to access the data. You don't get to see the database, but you'll have to use the Metadata Query Builder to generate an MQL (Metadata Query Language) query. (Quick start guide here)
PDI: You can use a kettle transformation as a "data source" for your report. This of course opens up a again an endless series of options as PDI can use even your grandmother as a data source provided there is a JDBC driver available. That opens up data sources as: MS Access, MS Excel, flat files (fixed width and "something" separated), directory structures with file names, LDAP, Mondrian & OLAP, Salesforce data, SAP R/3 data, any SQL database with a JDBC driver, ... I guess we've made the point.
OLAP: This option allows you use an MDX query as a basis for your report.
XML: How about using an XML file as a basis for your report and defining your query against it here?
Advanced: Seemingly the people at Pentaho don't consider any of the above options 'advanced' enough, because under the advanced menu you'll find some more options to toy around with.

custom JDBC connection
scriptable data access: use beanshell, groovy, netrexx, javascript, xlst, jacl, jython
(named) java method invocation
external

Personally, I haven't gotten round to using all of these methods, and though they intrigue me, I also hope I'll never have to use all of them. That is just too much to get my head around.

If you decide to go for Analyzer, JFreeReport or Saiku, your options are much more limited. Basically they all live on top of Pentaho Analysis Services aka Mondrian. So your choices here would be simply to create an MDX query. The difference between creating an MDX query with the 3 fore-mentioned tools and Pentaho Report Designer, is that these tools have a nice GUI to create the MDX for you (drag and drop or point and click).

When using BIRT reporting, you get a series of options that are closer to PRD again. I haven't listed all the features out, but they described here. The BIRT online demo also shows clearly how BIRT works.

Intermezzo

Depending on your reading speed, I believe your song must be finished by now, so maybe give this version a try.

The very best of

So why am I writing all of this out? Well, first of all, many customers don't understand the flexibility they have at their disposal when working with Pentaho. Often they have seen a demo or read some documentation, and they believe that what they have seen is: THE way "it works" with Pentaho. Consequently they ignore the other 49 ways to make a report. So when the consultant comes in and shows the options, they usually say "ah, I didn't know that was possible" or "why didn't any one tell me this could be done".

Once customers understand that 'THE way' doesn't exist, but that there are "50 ways to make your report", they automatically get to the next question, being: "what is the best way to make my report?". (Let's face it, people want to simplify things). And here the consulting work gets tricky, as it is impossible to make the answer fully customer independent. One thing however is sure, using all the "50 ways" in the same environment is not recommended. Using all the different possibilities, will require a large set of skills from your IT personnel and will hamper the maintenance work on those reports.

So, imho, a key element of implementing Pentaho Reporting at a customer, includes a clear study of which different reporting tools and data access methods fit best in the customer's IT architecture, and making a clear selection of which methods they should adopt as standards and which ones they should only use if the standard options don't work. Obviously this "customer strategy" should be aligned with the official Pentaho road map.

Good, bad or ugly?

Now the 64.000$ question is whether all this richness, actually makes a good "Reporting strategy" from a customer point of view? You could say that the picture I made looks pretty ugly or at the least very confusing. And in my experience, that is often how customers perceive it. Once they understand how many possibilities there are, the usually are profoundly confused.

What they ignore when making this assessment is that Pentaho is an open source initiative, which means that any one can extend the capabilities of the BI server with new reporting possibilities. This happens and will continue to happen, because it is an immediate consequence of an open source environment, So customer must first of all understand that Pentaho solutions will allow to do the same thing in more than one way.

Now again is that good or bad? I believe I have given the answer to that question already. If a customer doesn't overcomplicate his usage of Pentaho technology, and adapts clear standards, then Pentaho offers reporting strategies that are simple to learn and implement, as well as easy to maintain. It is up to the customer to make the right choices.

And where does Pentaho stand in all this? As far as I can see, I believe Pentaho should somehow monitor that all the richness of possibilities is explained to their customers and that they are guided in using the right set of possibilities. Over my career as a BI consultant I have seen many BI implementations. Other BI suites that allow for a high "diversity" of possible solutions as SAS BI or Microsoft BI, often resulted into Business Intelligence environments that became technologically hard to understand and impossible to maintain solely because 50 different programmers with a different opinion have come by. Are the vendors to blame for that? Not really. But still some guidelines from the vendor would have helped those poor customers. My experience is that Pentaho deliver this kind of service to its customers. Pentaho's Support, which is extremely well appreciated by customers, typically includes advice that is crucial in a start up phase, and that is a service that few BI vendors offer.

What I left out

While writing this post, I realized I left out some reporting options. I quickly throw in what I remember now, but there might be some more stuff. Any one reading this post and want to add something, please add it to the comments section, I would love to see this grow out to a completely complete overview :-)

You can create Excel based reports, using only Pentaho Data Integration and Excel Writer, see also my previous blog post.
Similarly you can create PRD reports using Pentaho Data Integration and PRD step, as demonstrated here.
I didn't mention anything on embedding Pentaho Reports into other applications, as e.g. the Confluence Pentaho reports

Outtro

To end this post, I wanted to include a little tribute to Mr. Steve Gadd, the man who wrote the incredible drum riff that kicks off Paul Simon's "50 ways to leave your lover". An extremely unusual drum riff but some times the unusual methods deliver the best result. I guess Paul Simon was just lucky to have the right musician available that could deliver him the best groove to fit his song, even if that was a very unconventional one. Which shows in the end that diversity is good.

Excel writer fun

2011-01-26T15:41:00.000+01:00

This post is about making some complex Excel reports using Pentaho data integration and the Excel Writer step.

The Excel Writer step
Some time ago Slawomir Chodnicki (aka Slawo) released the Excel Writer Step version 1.2 for Pentaho Data Integration. Not only did he deliver a wonderful piece of extra PDI functionality, but he also accompanied its release with a great blogpost as well as a sheer endless list of samples.

Since the Excel Writer is donation ware, and Slawo is a big kettle contributor, it should come a no surprise that the Excel Writer will be standard included in kettle 4.2. A great contribution, which have been eager to use in a real life situation.

The use case
Last week, I used the Excel Writer for an assignment. The reason why customer wanted the results in Excel were simple. They needed to manipulate both the data as well as the look and feel of the graphs, before using them in Word documents and or Powerpoint documents. Some questions weren't even needed in the final report. So a lot of editing was needed after the creation of the initial report.

The basic requirements were the following:

Generate Excel reports from the responses to (multiple choice) questionnaires.
Create one Excel sheet with the questionaire, and one Sheet per question showing the answers
Each Excel report with answers should contain both the figures in a table as well as in a graph.
Each questionnaire can consist of somewhere between 20 to a 100 questions.
Each question can have somewhere between 2 (yes/no) to 10 answers.

The code
The basic code was pretty simple.

One transformation to fetch all the questions of the questionnaire and write them to an Excel Sheet.
One transformation to write all the answers to a sheet, to be executed per question
+ some logic to clean up the previous version and zip the new version

The output - one Excel file?
My first idea was to generate one Excel book, containing 1 sheet with an overview of all the questions in the questionnaire, and next per question 1 sheet with the graphs/tables on the answer. It was based upon two sheet templates and the result was supposed to look like this.

However when I tried this I ran into the strange issue that the graphs on my Excel template disappeared. I took me some time to figure out that this was a limitation of the Excel writer. A limitation that was even documented, but unfortunately, I don't always read tool tips.

Anyhow, Slawo confirmed me that the POI library that is used, has some limitations:
> You may have hit a limitation here. In order to use a sheet as template
> it needs to be copied (only in memory, but a copy non the less). If POI
> does not understand things (like charts) it ignores them as best it can.
> But if you ask POI to copy stuff, it will ignore things it does not understand too.
Basically it seems that when your sheet template gets copied, our friend the poi library will just randomly leave out stuff he (or she?) doesn't like.

The output - many Excel files
So I started looking for a work around that was still elegant enough. The answer was pretty simple. Don't work with separate sheets, but with separate files. As soon as I disabled the use of the SheetTemplate, and I wrote answers to each question each time to a new Excel sheet, all graphs and lay-out stayed intact.
To make it a bit easier for the users, to navigate the large number of output files, I used the hyperlink features of the Excel Writer step. Actually putting a hyperlink from the questionnaire to the right file with answers made the whole set of reports easily browsable. And yes, on every sheet with answers, a link back to the overview was inserted.
I also added a zip step at the end to bind all the reports together into 1 zip file, making it easier for the user to receive the file. (More on that in my next post by the way).

Some Excel tricks

Using Excel allowed me (or obliged me) to use some Excel report tricks as dynamically changing the range of values shown in your graph (based on the actual values that are filled in in your table). Or allowing the user to quickly change the numbers in the graph from absolute figures to percentages.

Even though I'm not such an Excel reporting fan, it was amazing to see that all of your "programming" in Excel actually continues to work nicely (as long as you don't use SheetTemplates that is :-) ).

Conclusion

Notwithstanding the issue with the 'disappearing' charts, I must say that I'm pretty pleased with the end result and the actual time to put the whole thing together. Development time was really minimal and given the nice set of samples that Slawo has put out there, using the Excel Writer writer in the correct way was really a piece of cake. And to round it up, I must agree with Trout, that Slawo is encredibly quick with problem analysis and answers. And I'm not expressing any opinion about Pentaho support with that :-)

Mailing New Years cards

2011-01-03T19:02:00.001+01:00

It is no secret that at kJube we like Pentaho Data Integration. This year, in order to send all our best wishes for the new year to customers and partners, we used our kettle mailer engine again (aka Normal Mailer). Since it has been something we wanted to put out there, I thought it was a good idea to share a bit of code. Even though this isn't the whole solution, I still thought, that there might be one or two people that still need to send cards. So serve yourself.

The basics
How does it work? If you unzip the code, you'll see that there is just one transformation, named "mailer.ktr".

This transformation uses two input files: "mailing_list.xls" and "test.html" as inputs.

"Mailing_list.xls" contains the list of persons to who you want to send mails.
"test.html" is the standard mail that will be sent when running the transformation. Obviously you can create whatever mail template you want. You can provide it as input parameter when running the transformation, as the name of the mail template is a named parameter. In the example you'll find our new year's card "2011.html"

The transformation also has an output file, which is the same as the input file. It will add a tab in your "mailing_list.xls" with the name of your mail template and the result of the mailing. By the way, we've used the Excel Writer step here. So you might need to add that to your kettle deployment, or you could just disable the tracking of results if you don't care about that part.

In order for the transformation to work you also need to add some variables to your kettle.properties which are more or less self-explanatory.

KFF_MAIL_SMTP_SERVER
KFF_MAIL_SMTP_PORT
NORMAN_MAILER_SENDER_NAME
NORMAN_MAILER_SENDER_ADDRESS

Ideas for extending

Obviously this idea can be extended. Some things that have crossed my mind are the following.

It would be nice to be able to insert variables into the mail template, which can then be replace at run time. E.g. add somewhere in your mail template "Dear ${SALUTATION} ${LASTNAME}" and replace this with actual values from the mailing list. I guess Roland's latest blog post should offer some possibilities there.
Obviously when you send these mails, the sender mail address that you use, will probably get some "undeliverable" replies. Most of the time your mailing list isn't correct. Reading out the mailbox and automatically figuring out which email addresses are invalid would be a great feature. With the POP3 step and some Regex magic that shouldn't be to hard either.
Another cute feature would be to offer people to "unsubscribe" from your mailing list. Again, providing a mail address and a standard subject, like "Unsuscribe" in combination with the POP3 step and some Regex should take care of that.

If any one wants feels like extending this a bit, feel free to contact us.

Oh, and a happy new year to all of you !

Table compare - test automation

2010-10-19T09:32:00.000+02:00

Background

Some time ago we released a series of kettle plugins as part of the Kettle Francising Factory. The plugins (kff-plugins3.jar for PDI version 3.2.3 and above and kff-plugins4.jar for PDI 4.0.0 and above) are available on code.kjube.be but sofar not much documentation has been provided.

One of the kettle steps that is available in those jar files is the Table Compare. This step does what its name says. It compares the data from two tables (provided they have the same lay-out. It'll find differences between the data in the two tables and log it. We developed this plugin for acceptance tests scenarios in large projects. Consider (as a hypothetical example :-) ) the following use case of an data integration tool migration.

Suppose you have a data warehouse which is being loaded using, well, let's say, Oracle Warehouse Builder. Now Oracle has bought Sunopsis in 2006. Since then the development of OWB has somewhat stalled :-). Sometime after Oracle has anounced the launch of a new product called ODI - Oracle Data Integrator (very appropriate name, btw). This product combines the best of both worlds (or so Oracle sales reps state) but most Oracle Warehouse Builder customers know since a long time that migrating their code from OWB to ODI is not easy. And here, out of necessity, an opportunity arises. If you are faced with a painful and costly migration, which you can only post-phone as long as your support contract allows, why not move to a cheaper data integration tool, as let's say, kettle?

The above migration scenario is exactly the type of projects we do at kJube. I'm not going to go into detail on this type of projects, but one element I do want to underline here is the following: If you cannot automate user acceptance testing you can forget about doing this type of projects. The Table Compare step does exactly this.

So what does the thing do?

Conceptually the Table Compare does the following for each pair of tables you hand it.

It will count the records in each table and make the result of that count available.
It will do a left, right and inner join between the two tables (the counts of those statistics aren't available
All the records that appear only in the right or left join are logged as 'Errors'
All the records that are common between the Reference and Compare table are put through a detailed compare on attribute level. All attributes that don't match are logged as 'Errors'.

How to use it?

Now you have gotten the conceptual explanation on the Table Compare, I guess it is time for the technical stuff. As you can see, the Table Compare step contains quite a few fields that require input.

The "Reference connection" and "Compare connection" are the database connections from which the reference/compare table data will come.
The "Reference schema field" and "Compare schema field" contain the schema names for the reference/compare table.
The "Reference table field" and "Compare table field" contain the actual table names. This means that you could compare two tables with a different name, as long as they have the same column names.
The "Key fields field" should contain a comma separated list of they fields that make up the 'primary' key of the table(s) you are comparing. The primary key is needed because without this information the two tables cannot be correctly joined.
The "Exclude fields field" contains a comma separated list of columns that you want to exclude from the comparison. E.g. because they exist in the first table, but not in the second.
The "Number of errors field" allows you to specify the name of the output column that will contain the total number of errors found for the comparison of your tables.
The "Number of reference/compare table records field" allows you to specify the name of the field that will contain the actual number of records found in each table.
The "Number of left/inner/right join errors field" allows you to specify the name of the field(s) that will contain the number of errors found for each join type.
The "Error handling key description input field" allows you specify the name of the output field for the 'where clause" of the record that gave an error.
The "Error handling reference/compare value input field" allows you to specify the output field names for the actual values that differ.

Example

If you find all of the above pretty confusing that is understandable. There is a lot of fields but most of them have little importance. They just allow you to choose how your field will be name, but have few functional importance. Still, in order to improve your understanding of the subject, we thought an example was in place.

In order to show you the example we needed some tables in an online database that we can compare. We found the information contained in the Ensembl project very suitable for this purpose. What is the project about?

The Ensembl project produces genome databases
for vertebrates and other eukaryotic species,
and makes this information freely available online.

Basically this project has a large amount of databases (one per species) that all have a similar structure. Perfect for our purpose. There are plenty of species available for comparison, but we picked:

We just picked two tables from each database and put them through the Compare Table step for demonstration purposes. The transformation is shown below (and is also available for download).

As the first step we used the data grid step to decide on which tables to run through the Compare step.

Previewing the first output ('Comparison Statistics') delivers the following:

It shows that both the 'analysis' and 'attrib_typ' table have a different number of records for the human vs chimp database. (Luckily?)

Previewing the second output ('Comparison Error Details') shows some details on the actual differences (in this case the inner join details).

Clearly the record with analysis_id=2 has different values for ALL columns in the table.

Hopefully this sample helps to understand what the Table Compare can do. The best way to see it is to download the .ktr and give it a spin. We'll also add the .ktr to the KFF project as a project template. So you'll also find the code in the next KFF release.

Improvements

We know already that the following improvements would be handy:

Allow the connections to be field names that are accepted from the previous step. That would allow to do testing across more than two connections.
Save the following statistics:

nrRecordsInnerJoin
nrRecordsLeftJoin
nrRecordsRightJoin

The 3 error fields are currently expected as input fields in the step, this should be corrected. Also their name might be more appropriately be output field :-)

... but if you have further suggestions to improve this step, please let us know.

KFF & Cookbook

2010-10-18T02:15:00.047+02:00

When we (Matt and myself) kicked off the KFF project, one of the things we wanted to include was auto-documentation. Matt made a small proof of concept to generate PDF documentation but that didn't reach finalization. At the same time, while Matt, Roland and Jos were busy writing Pentaho Kettle Solutions (Sorry, what were you thinking? You do not own a copy of this book?), the subject of auto-documentation came up too. Roland picked it up, and turned it into the kettle cookbook, a great auto-documentation tool for kettle.

Why did we want auto-documentation in the first place?

The reason we wanted auto documentation is that we are lazy. We know this. Actually we have known this since a long time. So we needed a solution that would minimize effort on our side.

Also it often turns out that customers do not really want to pay for documentation. They just see that as part of the development process and certainly don't want to pay for any time you put into documentation. So minimizing effort is also keeping costs low, which seems to please customers.

Another reason for wanting auto documentation is that over the years we witnessed projects where the documentation of data integration code was something in the line of a gigantic word document filled with screenshots - those very bulky screenshots made using MS (com)P(l)AINT. Obviously that kind of documentation stays-up-to date until 5 minutes after the next error in your data integration run. And stale documenation is even worse than no documentation.

So what was the way to go?

We quickly concluded that something or some one had to document the code for us - continuously. Since you cannot outsource everything to India; and since mind-reading solutions aren't just there yet, we thought along the lines of generating documentation from the code itself. What we didn't know is that it could even get better, and that Roland would write the auto-documenation tool and in doing so really minimized effort for every one.

About kettle documentation possibilities

Now before zooming in on the cookbook, I would light to high-light some nice documentation features that are in kettle since quite some time. The examples below are taken from KFF, namely from the batch_launcher.kjb.

1) Job/Transformation properties
Ever kettle job or transformation has a serie of meta-data tags on the properties tab, accessible by right clicking on the canvas of spoon (or through the menu).

The available tags are the following:

Name: The name of the transformation/job. This name doesn't need to be equal to the physical name of the XML file in which you want to save the code, although not aligning the physical
Description: A short description. Short as in: fits on one line.
Extended description: A full text description
Status: Draft or Production
Version: A free text version number
Created by/at: ID of creator and timestamp of creation
Modified by/at: ID of modifier and timestamp of modification

To my experience this gives quite a few fields to stick in some elementary descriptions of functionality.

2) Canvas notes

First of all the fact that there are really no lay-out restrictions in how you organize a data integration job or transformation is a strong documentation feature by itself. Many ETL tools will oblige you to always work left to right, or oblige you to always see every step on attribute level. Often that makes the view a developer has of the canvas, well, not much of an overview. In kettle you do not run into that issue.

Because of the fact that you can design jobs in a modular way (using sub-jobs), you can also ssure that you never need to design a job/transformation that looks like the one below . (For the record: I didn't design the below transformation myself.) Obviously now I'm stating that a good data integration design, makes documentation readable, which is a bit beyond pure documentation functionality, but still, it is an important thing to consider when thinking about auto-documenting your solution.

On top of the great lay-out possibilities, you can insert notes on the canvas of any job/transformation . They allow for free text comments (without lay-out possibilities). This is good to document things that still need finalizing, to highlighte certain elements of your job/transformation, important remarks like 'don't ever change this setting', etc.

Although the notes aren't actually linked to any steps, the vicinity of a note to a step is good enough to show what step the comment actually belongs to. And in case you really want to link your comments to specific steps there also are 'Step descriptions'.

3) Step descriptions
Step description are available through a simple right click on the step you want to document.

A step description dialog opens up and you can take down any comments related to the step you clicked in free text format (no formatting).

All in all, kettle as a tool, has great lay-out possiblities and sufficient documentation 'place holders' to stuff your comments in. The next thing is to get that information back out.

The Cookbook

As I wrote in the intro of this post, Roland Bouman put together an auto-documentation tool for kettle during the writing of Pentaho Kettle Solutions. He presented this to the Pentaho Community even before the release of the book, both in a webcast as well as on the Pentaho Community Gathering 2010 in Cascais (presentation here).

What does the cookbook do? Well, basically it will read all kettle jobs and transformations in a specific directory (INPUT_DIR) and generate html documentation for this code in another directory (OUTPUT_DIR) using the software you already have installed, namely kettle. In other words, if you are a kettle user, you just need to tell the cookbook code where our code is and where you want to documentation to go. I'm not sure if it could get more simple than that. Yet, as far as I know this is the only data integration tool that actually is capable of auto-documenting itself!

Cookbook features

The feature I'd like to show is that all your code is transformed into html pages which maintain the folder structure that you might have given to your project. In my example I've auto-documented the /kff/reusable folder, which looks like this:

So basically per job/transformation you have 1 html page, which is located in a directory structure that matches perfectly your original directory structure. Plain and simple.

Obviously the tree view shown here is clickable and allows you to navigate directly to any job/transformation you might want to explore.

On each page (for each job/transformation) quite an extensive amount of information is listed out. First you find the meta-data tags from the properties tab. The below screenshot matches the batch_launcher.kjb properties as shown above. Note that the fields "version" and "status" aren't exported for some reason but apart from that all the fields are there.

After the meta-data elements, the parameters a job might expect are listed out. In case of our batch_launcher.kjb these are the following. Since the named parameters are quite important for the understanding of a transformation, it is appropriate they are listed on top of the page.

Next you'll find an export of the actual canvas you see in spoon in your documentation, including all the notes. Now this is true magic. The screenshots in the documentation are exactly like what you see on the canvas in spoon. And the steps are clickable. The'll bring you right to the job or transformation that the step refers to, or to the description of the step. In other words, you can drill down from jobs to sub-jobs to transformations to steps as you would in spoon. That is no less than amazing!

The step descriptions themselves are listed lower on the page. In the below screenshot you'll see the step descriptions we entered for the step 'kff_logging_init' before. (Note that page breaks are lost.)

However if you look at the step descriptions that do not just launch another job or transformation you even get some of the actual code. Look at this table input step where you actually get the SQL code that is executed.

All in all, the cookbook generates amazingly detailed documentation. In case you aren't convinced by the screenshots and explanation above, please check for yourself below (or in a full browser window).

<p>&amp;amp;lt;p&amp;amp;gt;&amp;amp;lt;p&amp;amp;gt;&amp;amp;lt;p&amp;amp;gt;&amp;amp;lt;p&amp;amp;gt;&amp;amp;lt;br /&amp;amp;gt; &amp;amp;lt;p&amp;amp;gt;Your browser does not support iframes.&amp;amp;lt;/p&amp;amp;gt;&amp;amp;lt;/p&amp;amp;gt;&amp;amp;lt;/p&amp;amp;gt;&amp;amp;lt;/p&amp;amp;gt;&amp;amp;lt;/p&amp;amp;gt;</p>

KFF & Kettle-Cookbook
After the above explanation it doesn't need much clarification that integrating KFF and the cookbook was peanuts. The KFF directory structure is clear.

/kff/projects/my_customer/my_project/code -->contains your code

/kff/projects/my_customer/my_project/doc --> contains the documentation

So the INPUT_DIR and OUTPUT_DIR for connecting the cookbook to KFF are clear. The only thing needed was to add a step to the batch_launcher.kjb which called the top level job of the Cookbook and pass it two variables.

As I said, it was extremely simple to connect KFF to Cookbook

So from our next release on, if you download and install KFF, you'll automatically have a download of the Kettle-Cookbook in there, and whether you want it or not, all your projects will be auto-documented. You just need to figure out how to share the /kff/projects/my_customer/my_project/doc directory with people who would actually like to read the manual.

A big thanks to Roland!

IE6

2010-10-14T21:01:00.002+02:00

Thank you Google people for throwing this message in our faces several times a day!! Unfortunately some of our clients are large organisations (banks, government, etc) who really believe that Internet Explorer 6 was the end of web browsing evolution. So please Google people, stop developing for the web, there really is no future in that. )-:

PCG10 in figures

2010-10-12T07:15:00.000+02:00

PCG10, the Pentaho Community Meeting 2010 in Lisbon is over (safe to say after over 2 weeks), and attention on the live blog is slowly dying. So I thought, full attention on KFF now.

But Paul Stoellberger twittered the following statistics yesterday:

which made me think of looking up some of the Google Analytics statistics to share. After all, we are into business intelligence, aren't we?

So what has been the interest in the PCG10 presentations I added to the live blog post?

Here's the general statistics for the first 2 weeks:

Obviously the first days was highest, dropping after the 5th day. However, even though we had only 15 page views or so per day after that initial period, people still spend some time reading the pages. I'm curious how long that attention will last.

So where are the visitors from. Well, mainly it would seem that the Pentaho Community Event is a American-European party. Attention from other continents is extremely limited.

Top 20 countries (out of 37) are listed below. I was mostly amazed by Brazil. I would have expected more attention from that country, given the push to organize PCG next year :-) And I was pleasanty surprised to see that adding the Dutch and Belgian contribution together (no contributions from Luxemburg) would put the Benelux in third position. Small countries, ahoi!

And further, well, no more comments, let the figures speak for themselves I would say !

Pentaho Kettle Solutions Overview

2010-10-08T18:06:00.004+02:00

Dear Kettle friends,

Great news! Copies of our new book Pentaho Kettle Solutions are finally shipping. Roland, Jos and myself worked really hard on it and, as you can probably imagine, we were really happy when we finally got the physical version of our book in our hands.

So let's take a look at what's in this book, what the concept behind it was and give you an overview of the content...

The concept

Given the fact that Maria's book called Pentaho Data Integration 3.2 was due when we started, we knew that a beginners guide would be ready by the time that this book was going to be ready. As such we opted to look at what the data warehouse professional might need when he or she would start to work with Kettle. Fortunately there is already a good and well known check-list out there to see if you covered everything ETL related and it's called The 34 subsystems of ETL, a concept by Ralph Kimball that was first featured in his book The Data Warehouse Lifecycle Toolkit. And so we asked Mr Kimballs permission to use his list which he kindly provided. He was also gracious enough to review the related chapter of our book.

By using this approach we allow the users to flip to a certain chapter in our book and directly get the information they want on the problem they are facing at that time. For example, Change Data Capturing (subsystem 2, a.k.a. CDC) is handled in Chapter 6: Data Extraction.

In other words: we did not start with the capabilities of Kettle. We did not take every step or feature of Kettle as a starting point. In fact, there are plenty of steps we did not cover in this book. However, everywhere a step or feature needed to be explained while covering all the sub-systems we did so as clearly as we could. Rest assured though; since this book handles just about every topic related to data integration, all of the basic and 99% of the advanced features of Kettle are indeed covered in this book ;-)

The content

After a gentle introduction into how ETL tools came about and more importantly how and why Kettle came into existence, the book covers 5 main parts:

1. Getting started

This part starts with the a primer that explains the need for data integration and takes you by the hand into the wonderful world of ETL.

Then all the various building blocks of Kettle are explained. This is especially interesting for folks with prior data integration experience, perhaps with other tools, as they can read all about the design principles and concepts behind Kettle.

After that the installation and configuration of Kettle is covered. Since the installation is a simple unzip, that includes a detailed description of all the available tools and configuration files.

Finally, you'll get hands-on experience in the last chapter of the first part titled "An example ETL Solution - Sakila". This chapter explains in great detail how a small but complex data warehouse can be created using Kettle.

2. ETL

In this part you'll first encounter a detailed overview of the 34 sub-systems of ETL after which the art of Data Extraction is covered in detail. That includes extracting information from all sorts of file types, databases, working with ERP and CRM systems, Data profilng and CDC.

This is followed by chapter 7 "Cleansing and Conforming" in which the various data cleansing and validation steps are covered as well as error handling, auditing, deduplication and last but not least scripting and regular expressions.

Finally this second part of the book will cover everything related to star schemas including the handling of dimension tables (chapter 8), loading of fact tables (chapter 9) and working with OLAP data (chapter 10).

3. Management and deployment

The third main part of the book deals with everything related to the management and deployment of your data integration solution. First you'll read all about the ETL development lifecycle (chapter 11), scheduling and monitoring (chapter 12), versioning and migration (chapter 13) and lineage and auditing (chapter 14). As you can guess from the titles of the chapters, a lot of best practices, do's-and-don'ts are covered in this part.

4. Performance and scalability

The 4th part of our book really dives into the often highly technical topics surrounding performance tuning (chapter 15), parallelization, clustering and partitioning (chapter 16), dynamic clustering in the cloud (chapter 17) and real-time data integration (chapter 18).

It's personally hope that the book will lead to more performance related JIRA cases since chapter 15 explains how you can detect bottlenecks :-)

5. Advanced topics

The last part conveniently titled "Advanced topics" deals with things we thought were interesting to a data warehouse engineer or ETL developer that is faced with concepts like Data Vault management (chapter 19), handling complex data formats (chapter 20) or web services (chapter 21). Indispensable in case you want to embed Kettle into your own software is chapter 22 : Kettle integration. It contains many Java code samples that explain to you how you can execute jobs and transformations or even assemble them dynamically.

Last but certainly not least since it's probably one of the most interesting chapters for a Java developer is chapter 23: Extending Kettle. This chapter explains to you how you can develop step, job-entry, partitioning or database type plugins for Kettle in great detail so that you can get started with your own components in no time.

I hope that this overview of our new brain-child gives you an idea of what you might be buying into. Since all books are essentially a compromise between page count, time and money I'm sure there will be the occasional typo or lack of precision but rest assured that we did our utmost best on this one. After all, we did each spend over 6 months on it...

Feel free to ask about specific topics you might be interested in to see if they are covered ;-)

Until next time,

Matt

Twitter real-time BI

2010-10-04T21:54:00.000+02:00

Something which a lot of you people already knew, but which I just discovered, is that my (and your) Tweets are being watched. Not in 'big brother is watching you', but rather as 'big marketeer is studying you'.

What happened:

This week I made a quick twitter reply to the following post of @magicaltrout in which he was complaining his employer didn't pay him (yet).

I made a quick reply to this that went as follows:

Next thing that happened - within minutes - I had a new follower on Twitter. Check this.

Clearly some companies scan twitter to figure out whether you are a potential customer, then add you to their followers list, probably with the idea of following up even closer, or to start spamming you.

When I looked into the Twitter account, it actually belonged to a Fastmkn.com, a company that actually sets up this kind of services for their customers. Their website was clear about their services:

So, when you tweet, know your tweets are being 'mined', watch out what your write, and make sure you have your hand on the anti-spam button.

Pi

2010-10-03T16:28:00.003+02:00

PCG10 KFF presentation

2010-09-30T16:37:00.002+02:00

Unfortunately I wasn't able to blog my own presentation live during the Pentaho Community Gathering in Cascais, Portugal (PCG10), last saturday. The Live Blog page drew a lot of attention, during and after the event - statistics will follow - and I even got a few times the question whether I would still write a summary of the KFF presentation to go with the slides. Well, I will do no such thing! Instead however ...

Since the whole objective of our presentation was to somehow "launch" KFF - except for a handfull of insiders, no one within the Pentaho Community heard about KFF before - I taught it would be worthwile to write a full walk-through of the presentation for all the persons that might visit the 'PCG10 Live Blog'. So here it goes, no summary, but the full presentation in blog format, plus some little extra's at the end. Enjoy.

KFF, as presented at the Pentaho Community Gathering 2010

As the title slide of my presentation suggests, KFF is all about Pentaho Data Integration, often better known as kettle. KFF has ambitions to be an exciting addition to the existing toolset kettle, spoon, kitchen (all clearly visible in the picture) and at the same time be a stimulator for improvement of these tools.

Why oh why?

Any, and I mean any, consultant that has worked at least once with a data integration tool, be it Informatica, Datastage, MS Integration Services, Business Objects Data Integration, Talend (somehow forgot to name this one at PCG10), Sunopsis - Oracle Warehouse Builder - Oracle Data Integration, has been confronted with the fact that some elementary things are not available out of the box in any of these tools. I think about:

A job/transformation logging without set-up or configuration
Integrated alerting mechanisms (for when things go wrong)
Integrated reporting

as part of the alerting or
just to understand the health of your data integration server

Guideliness for a multi-environment (DEV, TST, UAI, PRD) set-up
Easy code versioning and migration between environments
Automated archiving of your code
... etc

After some years - too many I would say - I came to the conclusion that whatever the data integration technology, I was always rewriting the same concept over and over again. And all ustomers have seemed more than happy with the "frameworks" I've built. So I started wondering, how it was possible that data integration vendors were not covering the above requirements with a standard solution, if the requirements are the same across all customers. I took the discussion up with Matt, and he felt the same.

Once we realized this, re-implementing the same concepts again and again became hard to bare.

Luckily Matt and myself had the chance to do a lot of projects together, using kettle, and we started building something we could re-use on our projects back in 2005. With every new project we did with kJube, our 'solution' grew, and we got more and more conviced that we needed to share this.

So in June 2010 we listed out all we had and decided to clean the code and package a first version of what we had to show and share on the Pentaho Community Gathering.

We noticed soon that the first version couldn't include nearly all we had ready. What we present at PCG10, is just a basic version to show you all what were doing. The whole release schedule willt ake until january 2011, if new additions or change requests interfere.

So what is KFF?

We decided to call our solution the Kettle Franchising Factory.

Franchising seemed a nice term because that remained nicely within the existing kettle, spoon, kitchen, chef, carte, etc metaphor. It indicates that the KFF objective is to scale up your data integration restaurant to multiple 'locations' where you cook the same food. That's basically what we want. Make kettle deployments multi environment, multi customer, whilst keeping the set-up standard.

The term Factory refers to the fact that we want every part of the process to go as speedy and automatic as possible. This factory contains all the tools to deploy kettle solutions as swift as possible.

The tools through which we reach those goals are several:

Some of the requirements we meet through proposing set-up standards. We try to make as few things dependend on standards or guideliness, everything should be configurable, but large data integration deployments stay neat and clean only if some clear set-up standards are respected. Also, standards on parametrization need to be imposed if you want to make your code flexible enough to run on multiple environments without further modifications.
A lot of functionality is implemented using reusable kettle jobs and transformations, often using named variables.
Quite a few kettle plugins have been written too. We believe that when certain actions can be simplified by providing a kettle plugin, that we should provide that plugin.
Up to now we have 4 project templates we want to include with the KFF. Some "projects" always have the same structure if one follows best practices, so why should we rewrite things.
Scripting. Although limited, there is also some scripting involved in KFF.

So let's go into details

A first element of the KFF we want to show is the 'batch_launcher.kjb'. This kettle job is designed to be a wrapper around your existing ETL code or one of the templates we'll ship with KFF. The objective is make all calls to re-usable logic as logging, archiving etc in this wrapper without the need to modify your code.

What does this job do (as of today):

The first step of this job will read the right configuration file(s) for your current project/environment. For this we've developped a step called the 'environment configurator'. So based upon some input parameters, the environment configurator will override any variables that (might) have been see in kettle.properties to ensure that the right variables are used.
The job 'kff_logging_init' will

create logging tables (in case they didn't exist yet), currently on MySQL or Oracle,
clean up logging tables in case there should be data in there
check whether the previous run for this project finished (succesfully)
creates a 'batch run'

The next job calls one of our project templates currently the datawarehouse template but can easily be replaced by the top level job of your data integration project
After the data integration code has finished, 'kff_logging_reports' generates standard reports on top of the logging tables . The reports are kept with the kitchen logs.
'kff_logging_archive'

closes the 'batch_run' based on results in the logging tables and
archives the logging tables (more on that later)

'kff_backup_code' makes a zip file of the data integration code which is tagged with the same batch_run_id as the kitchen log file and the generated reports.

How does the environment configuration work, and why is it necessary? Well, the fact kettle standard only provides one kettle.properties file in which to put all your parameters is kind of limiting to setting up a multi-environment kettle project. The way you actually switch between environments in a flexible way is actually by changing the content of variables. So we created the environment configurator. I'm not gonna elaborate on this again, since I've blogged about this plug-in in august when we first released it. I believe that blog-post elaborates more than enough on the usage of this step.

Obviously the environment configurator is something that works when you execute code through kitchen, that is in batch mode. However whenever you fire up spoon, it will just read the properties files in your $KETTLE_HOME directory. In order to overcome the problem also in the development interface.

Consequently, if you have correctly set up your configuration files, the kff_spoon_launcher.sh [No windows script available yet. We do accept contributions from people running Windows as OS.] will automatically set the right configuration files at run time and fire up spoon on the environment you want. As a little addition, nothing more than a little hack, we also change the background of your kettle canvas. That way you see whether you are logged on in DEV, TST, UAI or PRD, which is good to know when you want to launch some code from the kettle interface.

So how about that system to create logging tables? Well, the logging tables we use are the standard job, transformation and step logging tables. We tried to stick as much to the existing PDI logging and just add on top of that.

What did we add:

We implemented the concept of a batch logging table. For every time you launch a batch process, in this table a record will be logged that covers your whole batch run. In casu it will log the execution of the top level job. So yes, this is nothing but job logging, but since the top level job has a specific meaning within a batch process, isolating it's logging opens up possibilities.
We also implemented the concept of a rejects logging table. Kettle has great error handling, however one feature we felt was missing is to standardize that error handling. Our reject plug-in merges all records that have been rejected by an output step into a common format and inserts them into our reject logging table. The full records is preserved, so information could theoretically be reprocessed later. [Question Pedro Alvez: "Is the reprocessing part of KFF? Answer: No, since we don't believe automation of that is straight forward enough.]
Logging tables are created on the fly. Why? Well, whenever you are running your jobs/transformations on a new environment you get that nasty errors that your logging tables don't exist. Why should you be bothered with that. If they don't exist, we create them.

Creating the logging tables on the fly wasn't just done because we like everything to go automatically. Suppose you would want to run two batch processes in parallel. In a set-up with a single set of logging tables your logging information would get mixed up. Not in our set-up. You can simply define a differrent set of logging tables for the second batch run and your logging stays nicely separated.
Obviously to implement the above, you need to be able to change your logging settings in your jobs and transformations at run-time. For this Matt has written some nifty logging parameters injection code that actually injects the log table information and log connection into the jobs and transformations. More about that on the next slide.
At the end of the batch run we also archive logging information. Even if you have been using different sets of logging tables, all information is merged back together, allowing historical reporting on your data integration processes. Also, the archive tables avoid that your logging tables fill up and make the kettle development interface become sluggish when visualizing the logging.

The rejects step isn't the only plug-in we have written over the last few years. The next slide illustrates some other steps that have been developed.

Trim strings
Date-time calculator
Table compare

I have blogged about these steps before, so again, I will not write this out again. Also, one step that isn't mentioned here, but which we developed too, and has been contributed back to kettle 4.0 is the data grid step.

Another aspect of KFF are project templates. For the moment, what we have is rather meager - only the datawarehouse template is available -, but we do have quite some stuff in the pipeline that we want to deploy.

The datawarehouse template should grow out to be a 'sample' datawarehouse project containing lots of best practices and possibly a lot of reusable dimensions (as in date dimension, time dimension, currency dimension, country dimension, ...)
The data vault generator is a contribution from Edwin Weber which came to us through Jos van Dongen. We are still looking into how we can add it. But it seems promissing.
The campaign manager is a mailing application, also know as norman-mailer, which we use internally at kJube. It allows you to easily read out a number of email addresses, send mails, and capture reponses from POP3.
The db-compare template does an automatic compare of the data in a list of tables in two databases. It will log all differences between the data in the two tables. It is something we've used for UAI testing when we need to prove our customer that UAI and PRD are alligned.

After the presentation Roland Bouman came to me with a great idea for another template. I will not reveal anything as he has his hands full with the cookbook for the time being, and we are busy with KFF. When the time is ripe, you'll hear about this template too.

So to sum it all up: KFF pretends to be a big box, with all of the below contents.

We don't expect all of this to be in there from day one. Actually the day we presented KFF at PCG10 was day 1, so have some patience and let us add what we have over the next months.

How will KFF move forward?

Well we believe the first step was releasing something to the community. We'll keep on doing that. The code for the project is fully open source (GPL) and available no Google Code. Check code.kjube.be or go to the kettle-franchising project on google code. We'll listen to your feedback and adapt where possible!

Also we'll follow these basic guidelines:

Make KFF deployment as simple as possible. As is simple as a kettle deploy is impossible since kettle is deployed within KFF, but if you know kettle, you know what we mean.
We also believe that some of the functionality we have built doesn't belong in KFF but rather in kettle itself. We'll push those things back to Pentaho. (We'll try to find time to discuss with the kettle architect :-) )
If and when something should be automated/simplified in a plug-in we'll do so.
We believe we should integrate with other project around kettle, as the cookbook.

Who's in on this?

For the moment, Matt and myself are the drives behind this "project". Older version have been in production with some of kJube's customers for years. Sometimes they contribute, sometimes they are just happy it does what it should.

We hope to welcome a lot of users and contributers over next months.

Feedback

is always welcome!

Thanks!

PCG10 on PCG11

2010-09-29T16:58:00.002+02:00

Since I've been blogging so much on PCG10, I thought it was worth to quickly gather all the ideas that I heard on PCG11 at PCG10. Probably I was able to catch only a fraction of them, but I count on the community to add the rest of the ideas as comments (or on the Pentaho Forums and Wiki, where they belong in order to get a good discussion going).

Improvement idea nbr 1: shorter twitter tag

I'm not sure if this idea needs much explanation or even discussion? The hash tag PentahoMeetup10 was annoyingly long for people tweeting from the room. If the name of the Pentaho Community Gathering doesn't change (see improvement idea 2) I vote for #PCG11.

Improvement idea nbr 2: rename the Pentaho Community Gathering

I've heard rumours (@julianhyde) that the name Pentaho Open World would become the new name. I'm not sure if this is a strategic hint to L. Ellison to make Oracle buy Pentaho. But then again, Julian knows more about the company strategy than I do.

Improvement idea nbr 3: location

THE QUESTION at PCG10 was: "How will we ever top this location?". Indeed Cascais and surroundings were an amazing location! The hotel where the event was held, with a view over the sunny beach was fantastic! A tower of cupcakes and sweets in the room! Private espresso machines for PCG10! Nice little restaurants with fresh fish dishes right outside the door for a great lunch break! Webdetails and Xpand-IT even managed to organize a cloudless sky and +25°C temperatures.

The question to ask might well be: "Will any one dare to organize PCG11?"

So how go about setting up PCG11? Do a poll in the community on prefered locations? Asking different contenders for a proposal and let the best win? I don't have the answer, but the organizer of PCG11 will have a hard time topping PCG10, that is for sure.

A name that came up quite a lot for PCG11, was Brazil. Quite a few Brazilians have been following the live blog, and it seems that there's a very active Pentaho Community out there. But Summer in Brazil is coming real soon, which brings us to improvement idea nbr 4.

Improvement idea nbr 4: timing

Another interesting idea mentioned was to go from a yearly to a half-yearly event. That would perfectly fit with PCG switches between the Northern and Southern hemisphere. We could have a PCG11-South and a PCG11-North. Or Up and Down? Or ... well whatever.

Improvement idea nbr 5: presentation formats

On PCG10, all 15 presentations were either slideshows and/or demo's with exception of Dan's (codek1) "presentation". He just went up front with a short prepared speech actually interviewing the audience on methodology. That resulted into a very interesting groups discussion.

So this might actually lead to an idea for varying the format of presentations. Some idea's I heard:

groups discussions on a specific topic (who's interested can participate)
a "what sucks session" (proposed by Grumpy)
architecture sessions around a white board

There are many possibilities, and with 15 presentations in one day (a heavy schedule) a change of format is most welcome.

Improvement idea nbr 6: extending PCG to a full OSBI event

Based on Aaron's presentation at PCG10, where the idea was discussed that Pentaho BI server is more and more becoming a Pentaho BI APPLICATION server, which might/will also support JasperReports, BIRT, etc the discussion to invite also these open source projects to the community table. PCG would then become a true open source BI event. Personally I find that a very challenging idea, however it does raise some practical questions. PCG is growing quickly as it is, adding even more momentum would make organisation of the event just too tough for any of the partners to take on? How to balance the agenda between Pentaho / Non-Pentaho stuf, after all it's a Pentaho sponsored event?

So, that's it, I've posted whatever I remembered from the nice talks with great people, if a faboulous surrounding, accompanied wit great food, wine and beer. It's the best way these ideas won't fade. Most of these ideas come from great minds and fine people in the community. I hope posting them helps to stimulate the discussion. One thing is sure: PCG will keep on getting better.

PCG10 participants

2010-09-29T14:59:00.017+02:00

Three days after the event, some mails are going around, trying to reconstruct the "who was who". Indeed the Pentaho Community Event is growing, and I believe many discovered only when seeing the group picture that they didn't get round to meet quite a few of the participants.

I discovered too that I missed to opportunity to get to know some people. So based on the mails that have been circulating and a 'tagged' group picture (thank you Jens Bleuel) I'm trying to put together the PCG10 Participant list.

It is work in progress, so please people, help me sticking the right name to the right person. Also, should any one rather remain anonymous, drop me a mail, I'll rename you to Mr X(action).

Nbr	Name (First name - Last name)	Twitter	from
1	Roland Bouman	@rolandbouman	.nl
2	Matt Casters	@mattcasters	.be
3	Håkon Torjus Bommen		.no
4	Marco Gomes		.pt
5	Jos van Dongen aka Grumpy	@josvandongen	.nl
6	Carlos Amorim		.es
7	Jens Bleuel		.de
8	Pedro Alves	@pmalves	.pt
9	Nikolai Sandved	@NikolaiSandved	.no
10	Gunter Rombauts		.be
11	Jochen Olejnik		.de
12	Tom Barber	@magicaltrout	.uk
13	Nuno Brites		.pt
14	David Duque		.pt
15	Slawomir Chodnicki	@slawo_ch	.de
16	Pedro Pinheiro		.pt
17	Nelson Sousa		.pt
18	Nuno Severo		.pt
19	Paula Clemente		.pt
20	Pedro Martins		.pt
21	Samatar Hassan		.fr
22	André Simões	@ITXpander	.pt
23	Rui Gonçalves		.es
24	Dan Keeley	@codek1	.uk
25	Anthony Carter		.ir
26	Julian Hyde	@julianhyde	.us
27	Rob van Winden		.nl
28	Pompei Popescu		.ro
29	Jan Aertsen	@jan_aertsen	.be
30	Ingo Klose	@i_klose	.de
31	Sergio Ramazzina	@serasoftitaly	.it
32	Martin Stangeland		.no
33	Dragos Matea		.ro
34	Juan José Ortilles		.es
35	Paul Stoellberger	@pstoellberger	.at
36	Doug Moran aka Caveman	@doug_moran	.us
37	Thomas Morgner		.us
38	Kees Romijn		.nl
39	Aaron Phillips	@phytodata	.us

... and the following are people that were present, but somehow dropped out of the group picture. Maybe they were on the beach?

Picture	Name (First name - Last name)	Twitter	from
	Nuno Moreira	@webdetails	.pt
	Bart Maertens	@bartmaer	.be
	Juliana Alves		.pt

PCG10 in pictures

2010-09-28T12:33:00.002+02:00

After blogging PCG10, Doug kindly asked me whether I could also host the event pictures. So I've quickly added a picture gallery on the kJube website.

There are many more participants out there that took pictures, may I ask every one to mail me their pics, or preferably a link to an archive or so. I'll make sure it all ends up in the gallery.

I've created the following categories:

1) The day before: Most people arrived the day or evening before PCG10 in Cascais. I've added those pictures in here.

http://pictures.kjube.be/#1

2) PCG10: All picture from the Pentaho Community Gathering in Hotel Albatroz, including some pictures of the 2 hour lazy lunch break.

http://pictures.kjube.be/#2

3) Saturday night: After being exposed to a vast amount of presentation, PCG participants break loose.

http://pictures.kjube.be/#3

4) Bowling and later: Sunday morning a bowling event was planned (where large amounts of coffee were consumed). After that many people drifted of to Sintra or just hung out at Cascais

http://pictures.kjube.be/#4

5) Cascais and surroundings: Sceneries of Cascais. An amazing location for PCG10.

http://pictures.kjube.be/#0

With many thanks to the current contributers:

Kees Romijn
Jens Bleuel
Jan Aertsen

There are many more participants out there that took pictures, may I ask every one to mail me their pics, or preferably a link to an archive or so. I'll make sure it all ends up in the gallery.

Pentaho Community Gathering (Live)

2010-09-25T12:05:00.094+02:00

It's september 25th , I'm sitting in Cascais, hotel Albatroz, where the Pentaho Community Gathering 2010 is happening. I'll add some stuff 'live' to our blog as presentations happen.

Remarks:

September 26th, I revisited the post, cleaned up a bit, added the missing video and some slide presentations that came in late. I tried to leave the 'live' feeling though.
September 28th, after I sobered up, I fixed some deadlinks (presentations of Roland and André are now correctly linked), added Nuno's presentation, and added some links and thank you's to the organizers of this great event.

The agenda is pretty crammed so we'll have to see whether we'll manage to stay on track.

Another thought that passes my mind is whether people will actually be able to refrain from running outside to catch some sun. The view from the meeting room says it all.

10h15 - Dough Moran
Doug Moran kicked off the meeting by presenting every one to one another and thanking WebDetails and Xpand-IT for organizing the event.

Oh yeah, T-shirts to be distributed later.

With this, Jos van Dongen, industry analyst, informs the world the conference has kicked off.

10h30 - Pedro Pinheiro - CDA [presentation coming up]
Pedro explains CDA, community data access, a server side solution for data access usable for dashboarding and reporting.

Since it's a server side solution it's a bit hard to "show" what it "looks like", but more will be revealed during the later presentations (by WebDetails) where dashboarding/reporting tools will use CDA.

10h54 - Julian Hyde - Mondrian stuff [presentation]
Hej, we are ahead of schedule? Can Julian keep it so? I'm not sure as he's calmly taking off with some slides from previous community meetings as well as his kid.

Anyhow, Mondrian is undergoing a full rewrite. Some of the code goes back 9 yeas no, so rewriting all that involves A LOT of stuff. Currently Julian seems to wonder if the code will ever build again - see his previous blog post on that - but he's confident he'll get things running (if his youngest doesn't turn off his PC to often).

So what can we expect?

Attribute oriented analysis
Physical models
Composite keys
Measure groups: cubes with multiple fact tables eliminating the need for virtual cubes
Improved schema validation

As far as transition to Mondrian 4.0 is concerned, Julian says it won't be easy, but Mondrian will remain backwards compatible towards version 3. Workbench will need a rework due to the modifications in Mondrian, that is if Pentaho wants to keep workbench. But there are other options. Agile BI or the Metadata Editor might be extended to serve the purpose. The decision hasn't been made yet. A long beta process is foreseen.

Short coffee break now, next is Matt Casters

11h39 - Matt Casters - Dynamic ETL / Metadata Injection [no presentation, check demo below]
Matt goes over the history of ETL tools has undergone from quick hacks, over frameworks, over code-generators to real data integration engines as we know them. This presentation is about "what is next"?

Matt shows the example of dynamically loading a csv file into a table. In this use case you don't know the .csv file name upfront, neither do you know the field names, data types etc. What the meta data injector does is passing all the right information to your transformation?

Anyhow, to say it with Matt's words, cut the talk, just show us the demo.

In order to enable this kind of meta data injection, a rework of steps is needed, so it'll take some time before this functionality is available throughout PDI. Also, probably some kind of light weight UI will be needed for the design of these dynamic ETL solutions.

The call to the community is: please provide use cases for dynamic ETL.

12h00 - Aaron Philips (@phytodata) - Plug-ins and extension points [presentation]
The BI server is becoming a business intelligence oriented application server rather than just a BI solution server. Eg. CDA (presented earlier on) has been developed as a plug-in that runs as an application on the BI server.

(Aaron's presentation seems extremely well written out, so I guess it'll be self-explanatory when it will be published later on. We'll add links as soon as all presentations are added online.)

A very interesting idea presented as an illustration of a BI server extension is an alternative to xactions (Yeah!), being a GroovyEngine plugin for the BIserver. This triggers interesting remarks from the community though. We already have job scheduling mechanism namely PDI, why aren't we using this.

Doug's reply to the matter is that both options are open. The platform will offer possibilities for plugins and you can go one way or the other. Julian wonders why we need two times the same functionality. Seems like the whole discussion evolves around whether Pentaho want to offer a BI server or a BI application server. In the first case Pentaho would offer BI functionality, while in the second case they offer a platform to run BI applications on, even external ones like BIRT reports, Jaspersoft reports, ... Interesting discussions.

A remarkable fact to add, is that Aaron's presence on PCG10, is on specific request from the community. Some months ago a poll was launched by community members to make sure that attendance of Pentaho developers that have ear for the needs of the community is wanted. The results of the poll were clear: "Ship Aaron to PCG10". Will other Pentaho developers score better next year? Or will Aaron remain the uncrowned community hero? To be continued.

Presentation finished at 12h32, so we are still on our challenging schedule.

12h30 - Nelson Sousa - CDE (Community Dashboard Editor) [presentation coming up]

Nelson kicks off wildly - but claims he has done wilder things - with a CDE demo. It shows clearly how you can click together your dashboard (row after column after row after column after row ...), based on CDF components, CDA elements, ...

(The demo itself is pretty interesting. It's a dashboard showing Tweet statistics.)
The dashboard editor generates .html, .js, .css files which goes into the BI server.

For more information on CDE: http://webdetails.pt/

Lunch break

While all presenters have been respecting the time table, it seems that most of the community couldn't resist to stay out a bit longer for lunch. So in the end we picked up the agenda with a half hour delay.

14h30 - Tom Barber and Paul Stoellberger - PAT (Pentaho Analysis Tool) [presentation / presentation]

Paul Stoellberger kicked off PAT presentation with two slides and dived immediately into the demo part, demo-ing all slice & dice, drill down/across, filter etc functionalities of PAT, both in stand-alone mode or as part of the Pentaho BI server.

For the moment Paul and Tom aren't adding new features because they want to focus on getting a stable 1.0 out there. Obviously there are some interesting ideas for the PAT future as predictive analytics (including WEKA) or adding new charting options (using protovis). But for now feedback from the community on stability and bug reporting are the highest on the request list.

After Paul concluded, Tom presented PAT ideas on modular and collaborative BI with OSGI. The original idea was to work around collaborative BI only, but the ideas have expanded, ... and remain mostly only ideas for now. However the baseline idea is that currently Pentaho doesn't support collaboration in any way. The CDF has the possibility to insert some comments, and of course you can mail report links etc, but that is about where it ends. So the idea is to build this in using OSGI, a module system for Java allowing you to install new modules without stopping or rebooting the server. Next thing Tom starts of a demo on some of the basic features of making 'PAT RESTfull'.

14h30 - Jan Aertsen and Matt Casters - KFF (Kettle Kitchen Factory) [presentation]

I agree Julian, it's hard to blog your own presentation!
(30/09 but I finally added the full walk-through here)

15h00 - Nuno Moreira - Pentaho Dashboards, breaking barriers [presentation]

Nuno shows a visually stunning presentation (no images as below text might suggest though) and is asking us to think of dashboards as:

a sexy stripper
a sexy stripper doing a lap dance
a sexy stripper doing a lap dance which you can disassemble (?) [not my words]
a sexy stripper doing a lap dance which you can disassemble and talk to also.
a sexy stripper doing a lap dance which you can disassemble and talk to also, and who allows you to squeeze her
a sexy stripper doing a lap dance which you can disassemble and talk to also, and who allows you to squeeze her and who doesn't mind you share her with your friends.

No need to say that this way of looking at dashboards really captured the attention of the Pentaho crowd. Obviously Nuno was using a metaphor to talk about different levels of customizability of and interaction with dashboards.

Short coffee break
... with wonderful cup cakes: ... made by the organizer of the whole event
(A big big thank you for that!)

15h30 - Jos van Dongen (aka Jos von Dongen, aka Grumpy) - Data mining [presentation]

"Is data mining the newest piece of shit? ... I don't think so.", says Jos, and kicks of his presentation.

Jos walks us through the different data mining tools and techniques: decision trees, neural networks, regression analysis, ..., explains the differences between supervised vs unsupervised learning, splitting your data sets, etc.

He worked out the examples in Weka / Kettle and showed it a shortlive demo. Personally I believe that the Weka / Kettle integration is an extremely powerful feature (which many commercial data mining / ETL tools) don't even offer today. I really like the demo and hope to start using this type of functionality soon.

16h00 - Dan [Codek] - Approaches to implementations and methodology [no presentation]

Dan is trying to get us back on schedule by keeping his presentation short. No slides prepared, just a short series of ideas sketched on paper as a guideline for his talk. Since he moved from consulting to being responsible for business intelligence in a real company, he's interested on how people manage their projects.

Basically his talk started with the question who is using SCRUM and ... I filmed the rest.

16h30 - André Simões - PDI job/transformation framework [presentation]

André Simões, aka ITXpander, aka 'The useless guy on IRC', talks about an ETL framework including ETL chaining, ETL scheduling, building in check points and making self-contained ETL processes to ensure restartability, etc. Great stuff. A merger between this and KFF has been decided on the spot. A clear indication that there is a need for this kind of utilities.

Another addition to the presentation: Pentaho Reporting in Confluence.

(Sorry for the limited notes, as this was close to KFF I was to interested in understanding the presentation.)

In the meantime the heavy schedule starts to weigh on the participants. Having a sunny beach right outside the room doesn't make it easier.

But the final list of presenters are know for being able to keep their presentation juicy and spicy, so no doubt the audience will remain present.

17h00 - Pedro Alves - CCC (Community Charting Components) [presentation]

Pedro explored 20 charting libraries to see which one was the best to add to Pentaho as the existing charting is crap. He toyed with the idea to write a charting metadata layer allowing to plug-in all existing charting layers, but that idea was quickly tossed aside as it would add to much layers of complexity.

So he backed out and thought about what users want. Users don't care about the library you use for charting, they just want that you can create the visualizations they need. So he looked for a visualization library rather than a charting library, being protovis. On top of this, Pedro started developing CCC (Community Charting Components), a charting library based on protovis. This allows you to always go back to the visualization library and make/adjust your chart as you want.

Next Pedro did a demo on how the CCC fit in with CDA, CDF and CDE.

Group picture session

who's who

17h30 - Roland Bouman - Kettle Cookbook [presentation]

Roland elaborates on dominant users, positive eating experiences, having the guts, communism and Mao's manual which brings him straight to the kettle-cookbook.

Since Roland is a huge fan of the RTFM theorem, he considered it was time to ensure that based on the ETL code - actually the ultimate documentation, why can't the users read that ...- it was time to automatically create documentation, because who actually wants to write documentation, it's even more boring than reading it.

The kettle-cookbook auto documentation tool is developed in kettle under LGPL license. It will scan a directory of kettle code (.ktr / .kjb) and will generate cross-linked.html pages with a TOC, including diagrams and an overview of all variables, connections, fields, ...

18h00 - Jens Bleuel - Concept and realization for a PDI watchdog [presentation]
Jens has the hard task to actually still kick some life into a crowd that has been hit with tons of slides and demos throughout the day.

Basically the watchdog checks whether .ktr/.kjb are alive. Jens walked us through the code he wrote for this.

... and this concludes the presentations. Over and out. I'm wasted of blogging all day.
Maybe I'll add some more pictures later about the evening part of the event.

I heard that quite a few people are actually reading the blog post as it grows
... which I didn't really expect (honestly). But while you guys are at it, please
leave a few comments on this way of 'reporting' on the event
(so we can continue to improve). Thank you !!!

KFF - (slowly) coming out of the kitchen closet

2010-09-21T15:53:00.001+02:00

Last week we release a new version of kff-plugins.jar. This contains the following plug-ins:

In short:

Data grid: a plug-in that allows you to enter data in an spreadsheet like grid which you then can use in your transformations. Very handy for ETL configuration data which you would have to put in a table or csv otherwise. See this blogpost for more info.
Date/time calculator: It assembles a date/time field based on 7 input fields being: century, year, month, date, hour, minutes, seconds. Very useful when reading in AS400 tables created in the context of RPG programming. More information here. The Season ID calculator does something similar.
Rejects: A generic component for error handling. It will converge all error records into one common format so all your rejects fit in one and the same output file or table. We'll elaborate on this one soon in an extra blogpost. Documentation will be added to the KFF pages.
Table compare: This thing does what it says. It compares the data from two tables with the same lay-out. It'll find differences between the data in the two tables and log it. Very handy for acceptance tests. Again, this deserves a separate blog post. Probably we'll make a project template for this one too.
Trim strings: Allows you to trim strings, all fields at once, or a field list at your choosing. A bit more complex and versatile than the standard trim functionality in kettle.
kJube decoder: A piece of machinery that needs 20 pages to describe. Until we get to that, I won't try here.

We hope to get documentation online an we'll provide some blog posts to draw your attention to the new functionalities.

Enjoy !

Vista

2010-09-19T13:05:00.001+02:00

I just found back an old picture from a visit to MediaMarkt, the local electronics retailer. It dates from the release of Vista. For some reason Microsoft wanted to publicize the release of Vista on all the lockers at the entrance of the shop.

I'm not sure if there is some Mac or Linux fan working there, but somehow this publicity kind of turned out wrong.

ER models and the EAV model

2010-09-13T12:15:00.002+02:00

The last two years I've been involved in setting up a (generic) data model for an insurance company. The data model covers (or rather should cover) customers, products, due payments and benefits, financial reserves, actual payments (in/out), etc ... for all possible life insurance products which are maintained in several different systems. This model is intended to be 'hub' or global operational data store (as defined by Inmon in "The Corporate Information Factory").

The challenge lies in the 'all possible'. This insurer does a lot of group insurances. Due to the fact that most companies these days do 'shop around' for their group insurances these policies tend to be highly customized. So much that the systems in which they administer these insurance policies are often modified to include one more insurance policy. Or in other words, any new customer may lead to a new or changed product, including a change in the data model of the operational system. Often the capability of adapting the operational system is key to getting the new customer. That may seem strange, but if you know that these customers are companies with 1000's of employees with significant life insurance plans, well it makes sense from a cost-benefit point of view.

And that is exactly where the data modelling challenge start. How do you model a product that is nearly endlessly customizable? Before you know it, you end up with a entity-attribute-value data model that looks like this. (Original: Barry Williams, databaseanswers.org)

However, the entity-attribute-value data model is intended for sparse attributes only. Obviously. If this model would work outside of that scope, every one would use it for any application. More generic is hardly possible. Or did I hear some one suggesting "meta-thing" as an entity? :-)

Anyhow, to model the correct solution, the first step is to to analyse the sparsity of entities/attributes and then deciding what data goes into the standard ER-model and what part of the data goes into the EAV-model. The next step however is to see how you can set-up a successful marriage between your ER and EAV model. I found some good information on how to handle this for bio-medial databases, which helps to understand strengths/weakness of both models and how to combine them. Building on that I kind of sifted through all attributes and decided whether to put them in my ER-model or in my EAV-kind-of-model, and that seems to work out so far.

Next is the question on how to report on this, as I fear for database explosion if I turn this kind of data into a multi-dimensional model. I'll post my findings ... when I find them.

kettle history lesson

2010-09-03T12:00:00.001+02:00

A VERY TRUE tale about the real origin of kettle a.k.a. Pentaho Data Integration.

It is the year 2000. Most of the IT world is wondering what to do after the Y2K bug (except for counting the $$ wasted or earned). In Europe, the CEE reacts by introducing 'the euro'. The switch of national currencies to unified Euro currency generates again work for the whole of the IT sector. In the US votes are counted and recounted to ensure that Bush Jr can become president. Slobodan Milosovic on the other hand disappears from the stage. In Russia Vladimir Putin is the new man. Hopes grow that maybe the human genome mapping will explain why these people actually come to power? Microsoft is judged a monopoly by District Judge Thomas Penfield Jackson; he orders the company to be split up. (Bill Gates and Microsoft will go to the supreme court). In the mean time Bill starts touting the idea that your kitchen will become Microsoft powered. In Belgium a business intelligence specialist wants to snatch that idea away from Bill and starts to hack in his basement in an attempt to out-code Microsoft. In less than no time, he launches a series of products named kitchen, pan, spoon, kettle, menu, waiter ... that will shake the world.

Yes, kettle, now know as Pentaho Data Integration, was originally intended as a kitchen automation system. Or did any one actually believe that those kitchen terms were really intended as a metaphor for data integration technology? Come on, did you seriously buy that story? No no no, the real goal of kettle 0.1.3 - the first version I came across - was to be able to code kitchen recipes using spoon, and have kitchen execute them. A full automated kitchen process, the perfect support for the modern working woman!

For those who find this hard to believe, I have found an original .ktr from that period, know as the cake mix! This shows clearly what the original intentions for kettle were. The screenshot of the .ktr is (as usually is the case with a kettle transformation) completely self-explanatory.

I've opened it using kettle 3.2.3. As amazing as it may seem, backward compatibility for PDI seems to go all the way back to that version 0.1.3. I even tried to add a piece of functionality in the end to invite the very active Pentaho Community Members to the cake party. It integrates perfectly.

An interesting aspect, to which I want do draw your attention is the KQL, Kitchen Query Language. I had completely forgotten about this feature until I rediscovered this piece of code. KQL was a kind of GIS implemenation on house level, or rather on kitchen level, allowing to specify every shelf in your kitchen closets, fridge and yes even basement. Ladies and gentlemen, we are (or were) talking 3 dimensional Kitchen GIS (BTW, project name was KIS) here and that more than 10 years ago. This query language actually unlocked all physical objects in your kitchen through a syntax very similar to SQL. Example screenshot below.

In retrospect, I don't know what most readers think about this, but I find the whole set-up of this project mind-blowing and extremely far ahead of its time. But unfortunately the project was aborted ...

So what happened to the original plans for kettle? Well seemingly there were a few issues with the business plan for kitchen automation, which made our business intelligence specialist rapidly change his plans. (For your information, Microsoft is also slowly discovering that MS in every kitchen machine is not turning out a huge success.)

One major issue with the concept was that by automating kitchen tasks you free up time for the people who do the kitchen work. No statistics are needed to show that (even until today) it is mostly women that do the kitchen work. A consequence of all this free time, was that more time was spent on shopping. This made the investment in kettle - kitchen automation system actually a very expensive adventure instead of an intended saving. Once male investors understood this negative (perverse) effect of the kettle kitchen automation on their household income they massively abandoned the project.
Fridge APIs turned out to be a true headache. Most of the electronics producers were investing a lot in the reduction of CFC emission, amongs others through intelligence leak detection systems. Because of the competitive edge such systems could give them, most producers were not willing to open up access to the internals of their systems.

What happened finally is history. Our guy re-applied his coding to the field he was most familiar with, namely business intelligence and data integration. He open sourced his efforts and Pentaho recognized his genius. Today every data integration specialist is moving over from Datastage, Informatica, BODI, OWB, ... to Pentaho Data Integration.

KFF environment configurator

2010-08-25T11:32:00.001+02:00

While running the (parametrized) data integration code on different customers/environments, you need to carefully keep track of which kettle.properties file you are using. The environment configurator solves that problem .

About kettle variables
The kettle.properties file is well know to all kettle-developers. For those of you who don't fit into that category (and for some strange reason still read this post): the file contains all environment variables that you want to use in jobs/transformations you execute through either spoon or kitchen. The file sits in your home directory, that is ${HOME}/.kettle and you can add whatever variables you want.

How would you typically use these environment variables? A simple example will illustrate this.

Suppose you use a standard directory for incoming flat file data you need to process. In that case you can create a variable in the kettle.properties file for this directory.

IN_FILE_DIR=/kff/projects/my_customer/my_application/data/in/

The variable would then be available in all kettle jobs and transformations you will develop. In this case for instance in the Text file input step.

Now if you are running projects for multiple customers, or you are running the same code on different environments (as in development, unit test, acceptance test, production ...) the input directory for your flat files will most likely be different. In order to keep your code generic you would obviously continue to work with the same variable IN_FILE_DIR, but there is only space for 1 kettle.properties file on your system.

Some might say that you can solve this issue by using named parameters which you can pass from the kitchen command line to your jobs/transformations. However if the amount of variables starts to grow, that becomes quite cumbersome to manage.

Environment configurator
In order to avoid manual switching between kettle.properties files for the above scenarios - as I assume many of you are doing - we implemented a little step which we have called the environment configurator.

Basically this step will read a kettle.properties file (which you can place in the location of your choice). It will read the .properties file, parse it and set the appropriate environment variables. Any values that are located in your $HOME/.kettle/kettle.properties will be overwritten.

Now if you insert the environment configurator as the first step in your data integration job, as we've done in the KFF-, you can control perfectly which variables will be used to execute your code.

Basically the environment configurator step does expect three input variables which are used throughout the kettle franchising factory, our framework for management and rapid deployment of (multiple) data integration solutions across customers and lifecycles (see more on Google Code about this framework)

KFF_CUSTOMER      The customer for which you are runing
KFF_APPLICATION   The application code you are running
KFF_LIFECYCLE     The environment (DEV, TST, UAT, PRD) you are runnnig

or you can specify the path to your kettle.properties file. (Full details in the documentation.)

Vision
For the moment we've released this kettle plugin as a quick hack for our own projects, to simplify our multi-customer/multi-environment set-up. However a lot of improvements and extension are imaginable. We would love to get the discussion going.

Some food for thought:

How many environments do you need? And what naming conventions could we use? Out of the top of my head I can think of the following, but we've only "implemented" the first 4.

DEV: development
TST: unit test
UAI: user acceptance and integration test
PRD: production
DRP: mirror of production for performance testing
...

Shouldn't kettle slowly evolve in such a way that the kettle.properties file becomes part of your project code? Or maybe two levels or kettle.properties could exist, the original kettle.properties that contains variables for all your projects, and a project specific one that contains project specific variables.
If variables become project specific, wouldn't it make sense to be able to edit them inside kettle in a grid that shows the different lifecycle environments and value you are using in each environment. As illustrated below:

Variable	DEV	TST	UAI	PRD
IN_FILE_DIR	/DEV/data/in/	/TST/data/in/	/UAI/data/in/	/PRD/data/in/
DWH_SERVER	localhost	localhost	DWH_UAI	DWH_PRD
DWH_DATABASE	MySQL_DWH_DEV	MySQL_DWH_TST	MySQL_DWH	MySQL_DWH
...

Get it
You can get your copy of the environment configurator on Google Code, in the downloads section. All feedback is appreciated. We are very curious to see which use cases you will find for this plug-in.

What's cooking backwards compatible?

2010-08-25T08:16:00.002+02:00

Roland Bouman just released the "kettle-cookbook" on Google Code. At first, if you look at the name, and are a bit used to the kettle kitchen terminology (kettle, kitchen, pan, spoon, chef, ...), you might expect this to be some manual on how to cook up the best data integration jobs and transformations using kettle. But it is something different. The cookbook is an auto documentation tool for kettle jobs and transformations.

Since we have just released a new project with over 300 jobs and transformations, we had been looking into documentation ourselves, but it seems Roland beat us to it. Time to give the cookbook a try (and see whether we can contribute).

Step 1: Installing the cookbook
... equals unzipping the code into a directory. Since our standard set-up is to have all the re-usables under the same directory, namely /kff/reusable, I unzipped the cookbook under /kjube/reusables/cookbook. No pain here.

Step 2: Running the cookbook against a directory of transformations/jobs
I have a data warehousing project template which uses the following root folder:

/kff/projects/templates/datawarehouse/code/

with the following subdirectory structure and dummy jobs.

/kff/p../code/pre pre-processing

/kff/p../code/stg jobs to load staging area of the DWH

/kff/p../code/ods jobs to load ODS area of the DWH

/kff/p../code/dwh jobs to load multidimensional DWH

/kff/p../code/pst post-processing

So I pointed the cookbook at the "/kff/p../code/" directory to get a full documentation of my template project.

jaertsen@Jaybox:/kff/software/pdi/3.2.3$ sh kitchen.sh -file:/kff/reusable/cookbook/pdi/document-all.kjb -param:"INPUT_DIR"=/kff/projects/templates/datawarehouse/code/ -param:"OUTPUT_DIR"=/kff/projects/templates/datawarehouse/doc/

What did I get? Only documentation for the jobs and transformations in the root folder? What happened here?

After some digging it seems that the first step in the "get-kettle-job-and-trans-files-from-directory.ktr" transformation is a "Get File Names" step. That step differs between the 3.2.x version - which most of our customers are still on - and the 4.0.0 version, released recently. Basically, there is a flag "include subdirectories" in 4.0.0 version, which wasn't there before. So much for backwards compatibility, but that explains my issue.

So, even though I have no intention upgrading any of my customers yet, I ran the cookbook with version 4.0.0.

jaertsen@Jaybox:/kff/software/pdi/4.0.0$ sh kitchen.sh -file:/kff/reusable/cookbook/pdi/document-all.kjb -param:"INPUT_DIR"=/kff/projects/templates/datawarehouse/code/ -param:"OUTPUT_DIR"=/kff/projects/templates/datawarehouse/doc/

... and problem solved.

With great thanks to Roland for a wonderful documentation tool. I'll suggest a modification on Google Code for backwards compatibility.

The Data Grid Step

2010-08-13T11:19:00.016+02:00

Half a year ago I was happy to be involved in helping the folks from kJube get a project on the road. As the head of data integration at Pentaho I'm very happy to very occasionally be involved in the die-hard work that goes along with the implementation of BI projects because it allows me to touch base with the real world beyond the world of case tracking systems and software architectural dreams. If you can help a friend out at the same time it makes the occasion even better.

At one point it became clear that defining small data sets in Pentaho Data Integration (Kettle) was not as easy as it should be. Obviously it's nice to sit in an ivory tower and say that all information should be defined externally in a file or database table. However, setting up an XML file for a few lines of data is in a lot of cases over the top.

The question also is this: do you want to have ETL related information close to the ETL user or inside some external data source? Is it easier to manage the few lines of data in a user interface like Spoon or in an XML file somewhere? Answering those questions reveals your preference. In any case, as a result of the discussions and idea exchanges with kJube we decided that there was a need for a new "Data Grid" step in Kettle that would allow the ETL user to enter a few lines of constant data in an easy and clear way. Since ideas are often more valuable than a few lines of code, the step emerged and was thrown into the project quickly thereafter.

The use-case is the following: you have a number of company subsidiaries that all use the same database structure on different systems. Because all database structures are the same for these companies we want to create a loop in Kettle. Here is a screen shot from the actual project:

The transformation under "SMORDERCUSLOOP" generates a number of result rows. These rows are used to loop with in the sub-sequent jobs that are executed in parallel.

So we need a semi-fixed list of companies and systems and we can choose to define these in an external file or database or, as is the case here, in a "Data Grid" step:

As you can see, defining the set of constant rows is very easy with the new "Data Grid" step. In fact, everyone involved liked this step so much that it was contributed to the Kettle project. It is available in version 4 and upward in the "Input" category. For version 3.2 projects we have made the source code available under the Kettle Franchise Factory (KFF) project umbrella.

Jan & I are going to release more software that resulted from our adventures under the KFF umbrella. Follow this blog to learn more about KFF and/or more plugins in the near future. We'll also be more than happy to share project experiences at the upcoming Pentaho Community Event in Lisbon, Portugal.

Matt

Time for a time-out

2010-08-12T17:59:00.002+02:00

When you open kettle (sorry Pentaho Data Integration), it is so polite to re-open the transformations and jobs you had open the last time you were working. A nice feature, however ...when kettle tries to open jobs/transformations with connections to a server that isn't available anymore (because I shut down my VPN or I switched location), it will try to find that server. And that can take a long time.

Example: Below I opened kettle with 3 jobs 'remembered'. Those jobs each contain 4 Oracle connections and 1 AS/400 connection. All connections are shared. The time that passes between launching kettle and getting the three error boxes is about 54 Mississipi :-). In the meantime you cannot do anything in the kettle interface. It doesn't react to anything until all error messages are on the screen. Knowing that I sometimes work on over 10 jobs/transformations, that sometimes makes a very long Mississipi.

In case you are confronted with this issue from time to time, the trick to avoid this kind of behaviour is so ensure that you have no network connection at all when you start up PDI. Unplug the cable or disable your wifi for a second, and that'll solve the issue. No more frustrated waiting.

In the meantime I have posted a Jira to ask the folks at Pentaho to make sure the time out happens a little bit sooner. A minor bug, in one of the major data integration tools [I tend to disagree with the Gartner boyz]:-)

Release 4.666 (aka bitter pain edition)

2010-08-10T15:39:00.005+02:00

As software grows, software grows old. Time has an impact on everything. It is inevitable, even for intangible things as software. Sooner or later the beautiful piece of software you are working with today, will start to fall apart. Maybe the aging will start to show in the user interface. Little wrinkles will show that the GUI isn't able to keep up with 'fashion'. Maybe you'll notice the product getting a little slow in understanding, having a hard time to grasp the latest standards, trends and concepts.

But the real aging of software lies under the skin, in the code that gets clogged up with more lines and lines of extra code where maybe a re-write was in order. Quick fixes, not exactly written as they should have been, but that no one dares to touch again afterward. Little modifications in the code to close that sales deal with customer x. Some extra lines to make everything go smooth on platform y. An extra call to avoid a crash if z occurs. If you have been involved in software development, you know this is all inevitable.

So sooner or later any software needs to be replaced. There is no way around that.

I believe it is clear that many of the existing (proprietary) BI/DWH tools on the market have reached the point where there product needs (or needed) a rewrite. Some examples include the following.

Business Objects have rewritten their immensely popular reporting product from the ground up. Business Objects 6.5 was the last "old release", Business Objects XI is the new release.
IBM have (finally) rewritten Ascential's Datastage (after their acquisition of the product at the beginning of this century). They have integrated it into the IBM Websphere family. Datastage 7.5 was the last release of the old breed. Infosphere Datastage 8.0 is the new product release.
Oracle is coming up soon with a new version of their data integration tool. Oracle Warehouse Builder will cease to exist after 11g and will be replaced with Oracle Data Integration (a cross over between OWB and Sunopsis).

Probably there are few more examples out there, but already these three share some striking similarities:

All of the existing products have been around since before the year 2000, became very popular, even market leaders in the BI or data integration market segment, and gained a large customer base;
In each case the vendor has done a complete or significant product rewrite in order to assure they have a product that can keep up with market demands;
Marketing wise, none of the vendors really choose to position the new product as such on the market, they all positioned the new software as an upgrade of the existing product. IBM choose to maintain the product name and even version numbering, Business Objects kept the name and went from version 6.5 to XI. Oracle positions the rewrite of OWB as a merging of the best features of OWB and Sunopsis, while in reality not much will be left of OWB;
Last but not least, in all cases the 'upgrade' scenario doesn't drill down to an actual upgrade, but rather results in a costly (and painfull) migration scenario to a new software;
In all too many cases customers blindly accepted the upgrade and paid for costs.

Indeed, many customers have invested so much into these technologies that they cannot or dare not imagine a life without this software. But are these customers truly locked in by the years and years of investment in this software, IF the upgrade really is a migration? If you need to migrate, why not migrate to another platform.

Almost 2 years ago we published a white paper (that is still available on our website). This paper showed the cost differences in proprietary (Oracle, Microsoft) versus open source (Pentaho) data integration software. (( I believe we were slightly ahead of Mark Madsen, although our study was only a three pager of course ;) )). Anyhow in this study we also discussed the migration scenario. (Read the white paper if you want the details.)

Basically we discussed 3 scenario's:

Scenario 1: Remain closed source, in other words, stay with your proprietary vendor and pay for the extra/new licenses you might need in the future. That means no need to rework anything. Pay license costs where needed, and build new functionalities as you need them.
Scenario 2: Go open source. Rip and replace! Remove your proprietary solution and replace it completely with open source. Throw away your old licenses (kind of a cost saver) but rewrite everything you have (or still need).
Scenario 3: Have both. Put open source next to the proprietary solution for the extra/new functionality and go for a slow migration. You are stuck with your license cost, but will not buy new functionality (remain with old version of the software). I the mean time you deploy open source next to it.

Basically the conclusion back then was that often the best scenario was to go for open source along side closed source because a rip and replace was too expensive. Basically the migration cost for rewriting your ETL jobs (or reports) - that is, the effort of your IT resources (or even off-shore resources) - makes a business case for rip and replace very expensive (on the short term). In the long run, rip and replace is the cheapest, but where's the CIO that will go for a project that has the best ROI only after 5 years?

I believe it is clear to everyone that in the case of the "painful upgrade", the above charts change a bit. Due to the fact that the upgrade really is a migration cost, we'll see the red curve as well as the purple shifting up in the first two years. Remaining with your proprietary vendor will cost a big effort from your IT resources, just as migrating to a new software would. In that case, clearly option number two, rip and replace, becomes the best scenario.

So, please, if you are one of those customers, that feels cornered by a company proposing you a product upgrade which actually is the release from hell. Consider a real migration as a serious alternative option.

The one billion dollar query

2010-08-09T18:01:00.002+02:00

... or the cost of bad data warehouse design.
(last week an IT resource of one of our customers told me this story.)

Since some time we are working with a customer to improve their data warehouse architecture. The top management of this company has been ignoring the need for data warehouse solutions for at least 10 years. Sure, investments have been made, but always on a departmental or project basis. No one ever bothered looking a the big picture. Different data warehouse architects were present (or not) over time, each using different design techniques. All initiatives have resulted into so called "information silo's". The consequence is a heterogenous mix of copies of operational tables, the thing called 'operational data store', data warehouses of different types and OLAP cubes. The whole of this runs on an IBM mainframe combined with a Windows Server running SAS 9.1.

The result of this data warehouse back-end fiasco, is that end-users within the company have taken things into their own hands and started building their own solutions. Some use Excel to elaborate complex analytical constructions, others have gained near-admin access to the SAS reporting server(s) so they can actually build their own OLAP cubes and reports. IT has lost control of what is going on and currently IT hardly has any feeling with what real business intellingence needs are.

For some time now we've been trying to inventorize all the problems and issues there are today, and what the cost is for the company. A hard task, as many of the costs are hidden. The following "cost example" was a little gem that showed up just because one of the IT guys (girl actually) - who has put a lot of effort in listing out all the issues and costs - bothered to manually monitor user activity for some time.

Apparantly what happened is that a user has/had been running a query, through QMF, on a part of the data warehouse with a particularly bad data model design. The result was a query that used up around 30.000 CPU seconds, running in a job class with top priority and no limitations (in other words, the mainframe administrator believed that that job actually had the right to consume all those resources), so if she wouldn't have stopped it, it would have continued to run (until the user bothered to end it).

JOBNAME STEPNAME PROCSTEP JOBID OWNER C SIO CPU% CPU-TIME
D003359Q DB2CQMFB DB2CQMFB JOB12332 D003359 R 0.00 15.71 29112.05

I guess, most of you, also the ones that never work on mainframe, do realize that mainframe computing costs are high. So does the customer. They already moved from an "individual mainframe" to a 'hosted mode' in order to save costs. And still their infrastructure costs are high.

Anyhow to make a long story short, I called the hosting company, to inquire what might be the approximate cost of this particular query - which ironically never returned any result because we had it killed. I'm not naming any figures, but the number I was given, was as high a the net monthly salary of the IT analyst that discovered the issue.

Some inquiry with the users, learned us that they actually launch this (type of) query regularly at the end of the month. They estimated somewhere between 5 to 10 of these queries per month. I leave the maths up to you! Anyhow, I believe this to be good material to go and talk to the management of this company and tell them a story about the benefits of good data modeling and architecture design.

Visiting customers ...

2010-08-06T13:05:00.002+02:00

... should not exclude fun.

The holy war: iPhone vs Android

2010-07-31T00:16:00.001+02:00

While I'm still in Italy, enjoying the sun, I couldn't resist copying Umberto Eco's famous article on Mac vs Dos. Not very original of me. Even more after modifying some of the first lines to make the whole thing a little more actual, I just gave up trying to be as witty as Umberto Eco. He out-intellectuals me a 100.000 times even while he sleeps. So if you don't like this, just conclude I took too much sun.

--

Dear friends, earthlings, gadgetlovers, nerds and freaks, I guess it's finally time to reach a final decision on what keps us busy for many days, weeks and some of you months. Is the earth flat, almost flat, a bit round, kind of a bal or a perfect sphere? Are we better of without a government, as Hobbes said (and many Belgians experience daily), or is Hobbes just a tiger that comes alive when another imaginary character thinks he does? Who's really the president of Russia, has he been gone, or will he be back and is anyone really waiting for him? Does the iPod and/or kindle kill iBooks and video on demand or rather do they fuel it? Will Twitter replace the phone? Whether computers kill inspiration or do they just inspire you to copy copy copy what Umberto wrote?

One can continue with: whether Nostradamus was a terrorist; whether Obama will start driving an electric car or will America introduce genetically modified, petrol fueled fish and seabirds? Will more Italians migrate to Belgium now that not only the queen but also the premier will be or Italian origin? Insufficient consideration has been given to the new underground religious war which is modifying the modern world. It's an old idea of mine, but I find that whenever I tell people about it they immediately agree with me.

The fact is that the world is starting to get divided between users of the iPhone and users of Android phones. I am firmly of the opinion that the iPhone is Catholic and that Android is Protestant. Indeed, the iPhone is counter-reformist and has been influenced by the ratio studiorum of the Jesuits. It is cheerful, friendly, conciliatory; it tells the faithful how they must proceed step by step to reach -- if not the kingdom of Heaven -- the moment in which their document is printed. It is catechistic: The essence of revelation is dealt with via simple formulae and sumptuous (although beautiful) icons. Everyone has a right to salvation.

The Linux powered phone is Protestant, or even Calvinistic. It allows free interpretation of scripture, demands difficult personal decisions, imposes a subtle hermeneutics upon the user, and takes for granted the idea that not all can achieve salvation. To make the system work you need to interpret the program yourself: Far away from the baroque community of revelers, the user is closed within the loneliness of his own inner torment.

You may object that, with the passage to Android, the Linux power phone universe has come to resemble more closely the counter-reformist tolerance of the iPhone. It's true: Android represents an Anglican-style schism, big ceremonies in the cathedral, but there is always the possibility of a return to Linux to change things in accordance with bizarre decisions: When it comes down to it, you can decide to ordain women and gays if you want to.

Naturally, the Catholicism and Protestantism of the two systems have nothing to do with the cultural and religious positions of their users. One may wonder whether, as time goes by, the use of one system rather than another leads to profound inner changes. Can you use Android and still be a fan of Megan Fox? And more: Would Cicero have communicated using Seesmic, HootSuite or Twitdroid? Would Descartes have programmed in for the iPhone store or for the Android market?

And machine code, which lies beneath and decides the destiny of both systems (or environments, if you prefer)? Ah, that belongs to the Old Testament, and is talmudic and cabalistic. The Jewish lobby, as always....

Converting AS/400 (RPG) dates using kettle

2010-07-30T07:11:00.016+02:00

I'm not an RPG programmer. I don't even have a basic understanding of RPG, however as a BI.DWH architect I have come across a few AS400 applications written in RPG. A recurring phenomenon seems to me to split up date and date/time fields in separate numeric fields.

To store a date/time it seems a common practice in RPG is to create 7 numerical fields, each of maximum 2 positions. Example:

DTCRCA8: Record creation date.time - century part
DTCRYA8: Record creation date.time - year part
DTCRMA8: Record creation date/time - month part
DTCRDA8: Record creation date.time - day part
HRCRHA8: Record creation date.time - hour part
HRCRMA8: Record creation date.time - minutes part
HRCRSA8: Record creation date.time - seconds part

So I've come across this type of date structures in an AS400 database and needed to read those columns to transform them into a date format that could be stored in a MySQL or Oracle database.

To read out this information isn't very hard using kettle. Creating a connection to an AS400 system is standard connectivity in PDI. And a Javascript step with some simple functions will take care of merging the seven fields to one data field. However a number of data quality issues can arise with this type of date structures in AS400 and that is where the coding becomes tedious.

Obviously, with the database fields being integer, any value could occur in these fields.

You could values in the century field that range from anywhere between 0 and 99. Most likely only the values 19 or 20 are correct to you, unless you are reading out information from a database for archeological purposes.
You could have the hour field contain values like 24, 25, 26, ... . The minutes or seconds fields values of 60 and above. What about the 67th month of the year? Catch my drift?

What could also happen is one or more of the fields being blank. How would you translate this into a useful date? Century: 20 - Year: 3 - Month: null - Day: 15. January 15th 2003? February 15th 2003? Pick your pick. Obviously the field shouldn't be null. A regular zero poses the same challenge.
What do you do when the date fraction is correct, but the time part isn't? Or vice versa?
Sometimes the programmer who wrote the RPG program might have thought it enough to put it 9 and 0 for the century. I've seen RPG programs where only one digit was dedicated to the century, so it just depends on the RPG specialist that passed by on what your program might write down. So don't be amazed to find both the values 20 and 0 in the century field, both meaning the same.
Is 24 a valid hour? If so, should you add a day to the date fraction?

Once you've gone down the road of the handling the date conversion with some Javascript in PDI you risk to have to modify your Javascript in a growing series of transformations in which you are using dates. And since dates are pretty common in most applications, you are bound to do conversions in many of your transformations. If a concept as "record creation date/time" is used in the database design, you'll run into at least one date conversion for every single table you are extracting.

As I wrote, we (kJube) have come across the problem in some projects. The way we tackled this is by writing a custom plugin for PDI, which handles the conversion including all data quality checks. It checks the value ranges and defines a standard way of handling exceptions. The result looks extremly simple.

In the simplicity lies the immediate advantage of the plugin:

Anyone can use this logic without any need for whatever understanding of coding, even the simplest of Java scripts.
You can even use drop down lists to select the incoming fields from a list, eliminating the probability of typos.
All date.time conversions will be done in exactly the same way.
Writing date.time conversions with this plugin is a time saver. If you need to extract 200 archives (tables) with an average of 2 date fields per archive, you have just saved yourself writing 400 times the same formula over and over again.

Additionally I guess you'd also gain some performance in using Java coding over Javascript to do this conversion. Especially over large volumes and with complex logic to check/correct the data quality of the dates, that could mean something.

I believe this to be a clear demonstration of the value of the plugin system that PDI offers for simplifying data integration work. It is this type of features that lower project cost as well as system maintenance cost.

For those interested in the plugin (or any extension or modified version of it), don't hesitate to contact us.

Datastage vs Pentaho popularity

2010-07-29T13:14:00.006+02:00

Over the last few months we've been working hard on a rip and replace project to migrate a customer from IBM Datastage to Pentaho Data Integration. Hard work, but it was interesting to see that it can be done, even with a business case that shows payback within a year. (More about that later.)

Anyhow, having found a customer that wants to leave behind Datastage (a solid tool that I've used on multiple projects in the past) to revert to an open source alternative as Pentaho Data Integration (which continues to gain "followers"), this project made we wonder about how both tools compare in popularity. Roland Bouman wrote a blog-post a few days ago comparing the Oracle to MySQL (as well as a few other databases) using Google Trends. I did the same thing and that turned up these results.

It would seem that Pentaho Data Integration (or rather Pentaho as a whole, because PDI isn't really marketed separately) has overpassed Datastage search volumes somewhere late 2007, beginning 2008. Actually much earlier than I would have thought.

Since the result surprised me, I went back to Roland's blog and checked out the comments. Many people suggested that there were better statistics.

So I checked Google Insight with the following query:

The results were even more outspoken:

Finally I checked out StackExchange, (even including Informatica this time) again with striking results on the popularity of Pentaho.

I guess kJube has been on track with the new trends, doing all those Pentaho projects over the last 5 years.

New template

2010-06-11T12:38:00.003+02:00

All,
As you can see, we updated our blog look.
With thanks to Abu Farhan for the nice accordion template.
Jan

Lower Costs With Open Source BI

2010-05-27T18:22:00.004+02:00

Industry analyst Mark Madsen just released a very interesting report on the cost of open source business intelligence based on profound research of license cost of main BI vendors.

How about this for cost savings per user ?

Full report here.

kettle evolution

2010-05-26T09:21:00.005+02:00

kettle has surely evolved since it sprang into existence out of the mind of the Matt Casters.

Just how much you can see here :-)

A great thanks to all of the contributors. Since I work on an almost daily basis with kettle, it's great to see so much involvement and enthousiasm from so many people.

Motivational economics

2010-05-25T12:06:00.001+02:00

A tale about motivation and open source.

my Dantesque travel through Roma Termini Metro Hell

2010-05-25T10:28:00.002+02:00

Due to works on the Termini metro station travellers are required to confront a great deal of stairs (at least 300 to my count of which none automatic) to reach the A line. Since there are only 2 (!!) metrolines, A and B, in Rome, a quick calculation learns me that 50% of Rome's metro has become hostile territory for the physically disabled, elderly, mothers with children, etc.

For the young, healthy, fitness center inscribed among us it means a lot of sweat and curse, that is if you are not used to a 30 degree celsius, damp, poorly ventilated and overcrowded metro. And to my knowledge only Romans fall into the category of people who actually are used to this kind of torture.

Obviously your investment in public transport is naturally prolongued by the general popularity (read lack of affordable alternatives) of this very Italian contemporary adaptation of Dante's Inferno. There are at least 3 or 4 circles of damned to pass before your gentle guide allows you to enter the vehicle.

With the summer coming and temperatures rising the experience will become all the more dazzling.
I cannot wait to suck up more Roman culture. Stay tuned for more ... unless some one pushes me on the rails in the next few minutes.

Nel mezzo del cammin verso lavoro
mi ritrovai in una metropolitina scura

kettle vs BODI - 'out of the box' performance comparison

2010-05-24T06:35:00.002+02:00

I was cleaning up my laptop this weekend and ran into a forgotten file with a quick performance comparison between PDI and BODI I did for a customer. Now if I say "performance comparison", please don't think about a laboratory like test with fully documented results and full control over all variables. On the contrary, our approach to this performance comparison was extremely lean, for the simple reason that we were doing a PDI POC on functionality, not on performance. So the performance test was something for which we were allowed to take 2 hours time max.

Anyway the set-up was the following:
1) PDI and BODI installed on the same machine
2) Reading/writing from/to the same database server
3) Take 3 existing (simple) BODI jobs and convert them (without thinking) into PDI jobs

I guess 1) and 2) don't need much comment. I guess running on the same machine makes the test results kind of comparable. If that doesn't what does. Also since we were reading/writing data from/to the same database server, I believe we kind of excluded network or io issues in the comparison. About point 3) I still want to have a quick word.

We wanted to work on everyday simple jobs without spending time on them, because that is what a real world scenario looks like. Most ETL developers I know just grab the ETL tool and start bashing. Many of them don't really master all the tricks for performance tuning. So if you are looking for a tool that is performing well, I guess, what you mean is that you are looking for a tool that is performing well 'out of the box' or in a scenario where no product expert is invited to spend 3 days on fine-tuning your code and infrastructure. Depending on your needs, you might agree or not, but that was our philosophy.

Although the executed code doesn't matter much, I still give a bit of background on what type of jobs we ran.

Job/Transformation 1: Read 20 mio rows, split the stream in 2, perform in each sort a stream on different fields, count the amount of resulting records from both stream and write the output (+/- 20 lines) to an output table.
Job/Transformation 2: Read 20 mio rows, perform an in memory lookup for one of the colums to a table with approximately 10.000 rows and write the results to a table.
Job/Transformation 3: Read 20 mio rows, denormalize them and write to disk

Anyway these were the results.

Transformation	BODI (sec)	PDI (sec)	Difference
Transformation 1	4260	1501	184% faster
Transformation 2	1563	1035	51% faster
Transformation 3	5048	1054	379% faster

Or in other words, even in the "worst case" PDI was 50% quicker than Business Objects Data Integrator. And that in an out of the box without any tuning scenario.

Want more information: contact kJube

Mass editing kettle transformations

2010-05-22T11:41:00.005+02:00

Hi,

Quickly wanted to share this. It ain't no rocket science, but it's pretty usefull.

This morning, I quickly needed to point 50 transformations at a new server. Since I didn't parametrize the hostname of the server I found myself with 50 transformations containing this:

rather than this:

Luckily all kettle transformations are human readable XML formatted text files. No proprietary format like some commercial venders prefer, but plain text.

So this little command solved my problems in milliseconds.

perl -pi -w -e 's/10\.89\.0\.191/\$\{HOSTNAME\}/g;' *.ktr

-e means execute the following line of code.
-i means edit in-place
-w write warnings
-p loop

Next thing to do is just making sure that ${HOSTNAME} is added to the kettle.properties file and all worries are over.

With thanks to ...

big blue is watching you

2010-05-21T21:42:00.049+02:00

I often mix business and pleasure trips. This weekend I had to pass by my parents in law in Rome, but I took the opportunity to pass by a customer of us.

Now this customer has asked us to migrate 300+ ETL jobs written in IBM Datastage (the product IBM bought from Ascential, remember) to Pentaho Data Integration. In other words, the customer didn't want to pay a >100k€ license cost if they could get the same (or more :-), if you ask me ) functionality for free (or at 10% of the cost adding the Pentaho support subscription) from open source ETL software.

So I'm on a late plane flight, booked a hotel somewhere between the airport and our customer. What's the first thing I see when I enter the Marriot hotel:

Now can someone tell me what the chances are that some one who's one his way to discuss the suppression of IBM Datastage for a customer ends up in the middle of a BI convention of IBM, full of Datastage experts and nerds.

For a moment I thought they were on to us, I mean, really, what are the chances ?? But then again, we all know Big Blue isn't watching. They are using most of their revenue from BI on expensive sales conferences to keep those customers hooked.

A pitty I arrived late and was tired, I might have gained a few customers :-)))

And that's why they call it open source

2010-05-12T02:06:00.004+02:00

A friend asked me why, if he launched PDI (spoon), he could actually see the code running on his machine :-) If you perform a ps -aux on your machine will running spoon, indeed you see some impressive list of jar files that are being used.

Not yet the full source code, but still ...

The pen - the ultimate report designer

2010-05-11T23:04:00.002+02:00

What we IT guys with all our fancy tools and software tend to forget, is that sometimes the pen is mightier than the laptop. When you are meeting with a customer and trying to capture those requirements for a report or dashboard he wants to see, the very best tool to work with remains the pen.

Of course, back in the time when I used to posses a nice Fujitsu tablet PC, most customers were extremely impressed when I was just drawing/drafting their dashboard on my tablet, hooked up to the beamer.

But as I moved over to linux some years ago, I've left tablet experiences for what they are, back to paper and pencil. And believe me, it works just as well.

So do yourself a favour. If you have need to an analysis to design some reports and dashboards for your customers, go out and make that investment in pencils.

.spoonrc

2010-04-15T23:09:00.004+02:00

Yesterday I resized the left window pane of spoon in such a way I couldn't enlarge it anymore.

Extremely annoying. Adding a new step becomes an excruciating experience.

But the solution was simple. Just remove .spoonrc in the .kettle directory and all your settings are back to basics.

It proved to be a know bug too.

ORA-12155: TNS:received bad datatype in NSWMARKER packet

2010-04-14T00:58:00.001+02:00

You just have to love Oracle error messages. Actually, they sometimes make me think of fortune coockies. You never know what you'll find.

ORA-12155: TNS:received bad datatype in NSWMARKER packet

Cause: Internal error during break handling.

Action: Not normally visible to the user. For further details, turn on tracing and reexecute the operation. If error persists, contact Worldwide Customer Support.

Mmmmmmmmmm

I'll miss the intgeration

2010-04-13T22:20:00.000+02:00

PDI 4.0 is reaching GA status soon according to the posts on the Pentaho forums.

Around the second or third week of April we'll release our release candidate and the stable release of PDI is scheduled to hit the market in mid-may.

That means I'll upgrade my machine and I'll have to miss the mythical 3.2.3 Intgeration splash screen that has been amusing me for the last few months.

Or should I post a jira to keep this feature?

Merge step - watch out for database sort

2010-04-06T01:10:00.002+02:00

The merge step in kettle, provides a great functionality to identify new, changed, identical and deleted records when comparing 2 different tables. This allows you to split your incoming data in such a way that you can handle inserts, updates and (logical) deletes in the most efficient way as you can filter out all 'identical' records, and avoiding the extra round trips to the database an insert/update would create.

I used the step in an ETL flow where I needed to compare between information from an Oracle and a DB2/400 database.The DB2/400 is the original data, while I keep a copy of that same data in our operational datastore on Oracle. So in order to find what has changed since my last batch load, I compare both tables, cross platform, using the merge step.

When testing the transformation however I noticed some funny behaviour. Notwithstanding the fact that both data flows mapped to the merge step were sorted on the same fields (a requirement for the merge step to work) still the refresh of the target table didn't run not correct.

After some digging I figured out that both databases used different sort settings as show below. The DB2 on AS/400 sorted character fields differently than the Oracle database. Now off course I could have done the sort using which would have solved my sort problems, but then again sorting and aggregating is something better left to the database(s). They are just better at that.

Still, it was a little 'bug' that took me some time to figure out. Once I figured out the issue, the solution was easy, I added the NLS_SORT parameter to the Oracle connection which set the sort settings on Oracle the same as on AS400 at least for the duration of that session. Enough to solve my problem.

This allowed me to keep the sort on the database (more efficient than having the ETL tool solve that) and still have correct results. So for those of you who use this functionality cross database: check you sort results carefully before using the merge step !

J

bye bye fly

2010-03-13T10:14:00.002+01:00

Finally plane commuting ends. After 4 months of flying up and down beween bxl and fco I'm on 'my last flight'. The flight's delayed of course, according to great brussels airline tradition but none is talking cancellation until now.
I'm looking forward to being with my wife and daughter every day again.
It was nice flying but trop is te veel en te veel is trop.
J

kubuntu kde globe wallpaper

2010-03-11T02:02:00.001+01:00

Hi,

While I was cleaning up the desktop of my laptop, I noticed (toying with the screensaver) that under wallpapers the option 'globe' had appeared.

After enabling it, I noticed that some Google Earth View like kind of map had appeared as my desktop wallpaper. Not just an image, but a 'live' view (or simulated live view) of the planet. Cool.

So now, while I'm hacking my way through the night, I can actually follow where on the planet it is night, and where it is daytime. Maybe this will help me to get back to a normal day rhythm?

Anyway, it is tremendously pleasant to stumble upon such nice features in kde. I know it's eye candy. But it's very well done, for free and 'just there', without need for installing. I'm not entirely sure how easy a Windows 7 user can get something similar. But then again, I haven't touched Windows for some time :-)

J

Stop getting burned

2010-03-10T23:08:00.006+01:00

I guess this add have been out there for some time already, but still, for those who didn't see it yet, and want to have a laugh, check out Pentaho's pop-art marketing on commercial BI vendors. Those guys at Pentaho must have a lot of fun :-)

sudo to the rescue

2010-03-10T12:41:00.003+01:00

user defined command line parameters in the kitchen

2010-03-08T15:26:00.005+01:00

Hello,

Just recently I stumbled upon a way to pass custom command line parameters to PDI jobs that are executed through kitchen. Somehow, this isn't documented in the user documentation , so I thought a few lines were in place.

According to the Pentaho documenation, kitchen supports the following series of command line parameters:

file: xml file (job) to execute
log: log file
level: logging level (Nothing, Error, Minimal, Basic, ... Rowlevel)
rep: repository name
user: user to log in to repository
pass: password to log in to repository
listdir: list directories in repository
listjobs: list repository jobs
listrep: list available repositories
norep: don't log into repository
dir: set repository directory
job: repository job to run
export: file to export a job/trf too (including all related jobs/trf)

These are however all standard parameters. If you want to pass your own parameter into the job you are launching, I thought I was down to postional arguments, however that seemed not to be true.

An undocumented parameter - at least it wasn't in any user documentation I was capable of finding - is the parameter called param, used to pass named parameters. E.g. you could specify the following when executing a job using kitchen:

sh kitchen.sh -file=/daily_run.kjb -level=Basic -param:AFFILIATE=affiliate01 -logfile=/daily_run.log

This would pass the value "affiliate01" to the variable AFFILIATE in the job "daily_run.kjb". For this to work the job needs to have the input parameter defined in the parameters tab of the job properties.

Simple as that.

Cheers,

J.

Some links that could have brought you there:
- Kitchen user documentation
- Pentaho forum: "pass env variables into job via kitchen from cmd line"
- Matt's blog (--> where I found the mustard :-) )
- Jira by Sven Boden who actually asked for this feature

333 rule to keep your BI apps in check

2010-03-07T12:50:00.004+01:00

I really loved this post of Boris Evelson on Information Management Blogs, March 3, 2010.

The essence of the 333 rule:

Whenever there is a request for a new report that requires new data source or additional data sets from existing sources, first create manual extracts, pull them into text files and create "reports" on top of these extracts
If, and only If, after 3 weeks, the new reports are still being actively used, they then point the reports directly to the operational data sources.
If, and only If, after 3 months, the reports are still being actively used, they then create new DW tables and populate them with the new data sets.
If, and only If, after 3 quarters, the reports are still being actively used, they then completely productionalize the process with QA, UAT, DR and other production controls, risk management, and operational procedures.

Although this is a great rule of thumb for assuring that your BI environment doesn't get clogged up with junk, the rule kind of creates a difficulty in budget management. Of course you need to explain your business users that the "cheap" report you created for them initially, based on a simple extract, will need to be "rewritten" 3 times. That is they will have to pay several times over for the exact same functionality. Not impossible, but you'd better take that communication upfront. And you'll have to do some user "education" to explain them the why and how.

Cheers,

J

The Excel Piramid

2010-02-25T12:34:00.002+01:00

Old post blog post from 12/11/04, as it was on our website. I don't know if that blog will continue to exist, so I've posted a copy here. Unfortunately, 5 years later, I still keep on encountering Excel construction workers on a regular basis. It just won't go away.

Anyhow, here's the post.

"To my utmost frustration I keep on encountering them at several client sides. Excel pyramids. Just like their Egyptian counterparts it takes years to make them, it remains a mystery how exacly they have been built, they are built for the glory of the top-man and finally that same top-man gets burried under it (or at least that's what he deserves)!

To support continuous demand for reports, companies often turn to Excel. Obvious, as it remains the most the most available tool. But as time passes by more requests come in, time presses and one Excel sheets gets built on top of another. Chains of Excel reporting spring to life. One linked to the other, (ab)using the vlookup function to fetch data from on sheet to the other, copy pasting linked data from one sheet to the other until a highly complex spiderweb of linked Excel sheets has been built that none dare to touch.

The question I ask myself is why nobody pulls the emergency break in time. I've seen situations where for years (> 10 years) companies built one Excel on top of the other, developping monthly "procedures" to accurately update the collection of sheets that have grown to gigabytes of data each month, copying that "reporting-database-folder" over and over again.

The update procedures become more cumbersome every month, until in the end the company finds itself with a department of 20 people purely dedicated to relinking and recalculation Excel sheets.

It's nice to see when finally they see the light shining on the outside of the piramid and realize they need to change. But finding your way through the maze inside the pyramid, to actually understand how you get from chaos to an automated solution is far from evident. And as usual customer that are deepest in the sh** are the ones most eager to see results.

J