Friday, April 15, 2011

Pentaho Data Integration: Excel Writer Step (Plugin)

Pentaho Data Integration (Kettle): Excel Writer Step

One great thing about Kettle is that everybody with Java coding skills can just write a plugin for it and hence extend its functionality. One plugin that I want to highlight is Slawo's Excel Writer Step which in my opinion is extremely useful. Why? 

  • Quick and easy report creation: Design your report in Excel and tell the Excel Writer step where to place the data. Here's a really cool feature: You can tell the step in which cell to place the data. So you can even use the step several times to write to different areas within the same spreadsheet. Learn how to do this be reading Slawo's excellent tutorial.
  • Report bursting can be now set up within Kettle: Imagine a scenario where each country manager only receives a report with data related to their country. You can set up a process now in Kettle that takes care of this.
  • There is always a business user who loves Excel above everything, even if you have the fanciest BI website. You can output reports to Excel using Pentaho Report Designer, but in this case the charts will only be images. If it is required to have native Excel charts, the Kettle Excel plugin in your best friend.

You can download the Excel Writer Plugin from here.

SQLPower Wabit

SQLPower Wabit: Is it the one-reporting-tool-does-it-all solution?

There are quite different kinds of reports: ranging from dashboard/high level summary reports to very granular data  tables spanning 20 or so pages. 

As a report designer you usually have to satisfy various clients' needs: The Operations guy wants have a very detailed report, the CEO a high level summary/dashboard style report and so on. 

Now Pentaho Report Designer (PRD) is an excellent tool with millions of formatting options etc, but it is more geared towards a very granular/tabular output. If you want to create a dashboard style report, you have to use sub reports. You cannot currently design sub reports within the same window, which is quite inconvenient especially for layouting purposes. Plus every new report that you create has by default the reporting sections required for a table output style (report header, details, report footer) ... so what am I supposed to do if I only want to use one chart? Do I place it in the header, details or footer? 
I guess you know where I am getting to: Why not start with a blank canvas and let the user decide what reporting object(s) they want to use? 
As much as I like the Pentaho Report Designer for very granular reports, I still find it unexplainable why there is no functionality that allows dashboard style reports (it's more of a user interface problem I guess). I am not talking about interactive dashboards like the ones offered by Pentaho CDF (Community Dashboard Framework), just a combination of static charts, summary tables, crosstabs, etc. 

While CDF has really great functionality, it's not possible to email the dashboard report (imagine a scenario when the CEO is on holidays and wants his high level summary to be delivered by email). 

I had a quick 5 minutes look at iReport which seems to have the same shortcomings as PRD. So all of them were not really what I was looking for - the one-reporting-tool-does-it-all solution. I kept on search and found SQLPOWER Wabit:

Reading the specs on their website, this just seemed to be the tool I was looking for. I installed it and within 5 minutes I had a dashboard style report created without even reading a manual (which tells a lot about the UI). My report sourced data from Mondrian for a crosstab, and from a MySQL DB for a chart and a summary table. I ddin't have to be bother by using subreports, I could just place the required reporting objects on the canvas and I could use more than one data source as well for the report objects. Nice! Actually, very nice! 

Coming back to the initial question: Is it the one-reporting-tool-does-it-all solution? For now SQLPower doesn't seem to offer the millions of formatting options PDR and iReport have, but then it is only in version 1.3.4 and my report was quite nice looking for the 5 minutes I spend on it.

Overall, SQLPOWER Wabit seems to be a very promising product (did I mention that there is an open source version?). It even offers a visual interface to query OLAP cubes. You can download it from here and give it a test ride.