Feeding Kettle output into a report.

[pgw]

Hi,
Can someone point me to an example of a Pentaho action sequence that runs a Kettle transformation and uses the output as the input to a report? Does there have to be an intermediate file or is it possible to pass the data to the report in memory?


Thanks,

Paul

[alpheratz]

I have the same problem..I'll try to give a specific example...

I have a CSV file, as follows:

1,11,111
2,22,222
3,33,333
4,44,444
5,55,555

I want to produce a graph with the first column as the X-Axis and the
other two columns as data series values.

I have used kettle/spoon to define a transformation:

text input -> xml output

I define element names for each field so that
when I run this with spoon, it produces a file on disk as:

<?xml version="1.0" encoding="UTF-8"?>
<Rows>
<Row><X>1</X> <Series1>11</Series1> <Series2>111</Series2> </Row>
<Row><X>2</X> <Series1>22</Series1> <Series2>222</Series2> </Row>
<Row><X>3</X> <Series1>33</Series1> <Series2>333</Series2> </Row>
<Row><X>4</X> <Series1>44</Series1> <Series2>444</Series2> </Row>
<Row><X>5</X> <Series1>55</Series1> <Series2>555</Series2> </Row>
</Rows>

So far so good!

Except:

I can see an intermediate file created on disk...I assumed that Pentaho would use kettle "in-process" without the need for a disk file.

According to:

http://www.nicholasgoodman.com/bt/bl...ration-kettle/

"Just to be clear on what you’re seeing…

Pentaho users gain the full power of the Pentaho Data Ingegration tool (proper ETL sorts, groups, XML, csv, xml, filters, calculations, database lookups, aggregations, etc) when used as a SOURCE for their reports. A full ETL tool where you don’t HAVE to persist the data to tables or files or anything."

But I can see that the file IS created...thus I have a potential performance issue?

Now I am completely stuck. I want to produce a graph.

I have created an xaction that uses the 'csv.ktr' file I created
earlier.

When I 'Test' the xaction I get a display of:

===

Action Successful
--------------------------------------------------------------------------------


Field1 Field2 Field3
1 11 111
2 22 222
3 33 333
4 44 444
5 55 555

===

This is a reflection of the XML file. OK.

The question now is: how to produce a chart with X along the X-axis and
the two series drawn out?

I have tried adding a Pentaho Report "Process Actions" but I then get:

===
Failed
--------------------------------------------------------------------------------

Error: Base.ERROR_0004 - Component validation failed - java.lang.NullPointerException (org.pentaho.plugin.jfreereport.ReportWizardSpecCo mponent)
Debug: Starting execute of samples/etl/bob/untitled.xaction (org.pentaho.core.solution.SolutionEngine)
Debug: Getting runtime context and data (org.pentaho.core.solution.SolutionEngine)
Debug: Loading action sequence definition file (org.pentaho.core.solution.SolutionEngine)
Debug: SolutionRepository.DEBUG_FILE_PATH - getFile path=C:\pentaho-demo\pentaho-solutions\samples\etl\bob\untitled.xaction (org.pentaho.repository.filebased.solution.Solutio nRepository)
Debug: audit: instanceId=6470a305-8b66-11db-b2e2-37b72e9597ed, objectId=org.pentaho.core.runtime.RuntimeContext, messageType=action_sequence_start (org.pentaho.core.runtime.RuntimeContext)
Error: Base.ERROR_0004 - Component validation failed - java.lang.NullPointerException (org.pentaho.plugin.jfreereport.ReportWizardSpecCo mponent)
Error: RuntimeContext.ERROR_0006 - Action components do not validate: untitled.xaction (org.pentaho.core.runtime.RuntimeContext)
Error: SolutionEngine.ERROR_0006 - Action sequence validation failed (org.pentaho.core.solution.SolutionEngine)



Server Version 1.2.0 build 470 RC3
===

This is a 50 sec. job with Excel! I confess to feeling frustrated. If I
had XML to start with, I could probably do OK, but...

Any help gratefully accepted.

Alph.

[ngoodman]

Great thread.... Once you get this down you'll love the ability to use Kettle as the source for your reports.

First off, the example on my blog (and the one that ships with Pentaho demo server) does do an XML output but that's unnecessary. When I use Kettle as a source for a report I usually don't do an XML Output I use a "Dummy Step." Pentaho will EXECUTE the transform and "read" rows from whatever operator you specify in the "<importstep>XML Output</importstep>." Never needs to hit the disk.

Second, once you have the rule-result you should be able to use the other component definitions to build charts and reports. There's not a ton of information on that, but a good place to start is in samples/charts.

Hope that helps!
Nick

[alpheratz]

I have tried to establish Read CSV -> Dummy Output with no success:

2006/12/15 07:25:20 - be.ibridge.kettle.trans.Trans - Transformation is in preview mode...
2006/12/15 07:25:20 - be.ibridge.kettle.trans.Trans - Dispatching started for filename [C:\pentaho-demo\pentaho-solutions\samples\etl\bob\csv.ktr]
2006/12/15 07:25:20 - be.ibridge.kettle.trans.Trans - Nr of arguments detected:10
2006/12/15 07:25:20 - be.ibridge.kettle.trans.Trans - This is not a replay transformation
2006/12/15 07:25:20 - be.ibridge.kettle.trans.Trans - This transformation can be replayed with replay date: 2006/12/15 07:25:20
2006/12/15 07:25:20 - be.ibridge.kettle.trans.Trans - Initialising 2 steps...
2006/12/15 07:25:20 - Read CSV.0 - Starting to run...
2006/12/15 07:25:20 - Read CSV.0 - Opening file: D:\DEVELOPMENT\pentaho\x.csv
2006/12/15 07:25:20 - Output.0 - Starting to run...
2006/12/15 07:25:20 - Read CSV.0 - Finished processing (I=6, O=0, R=0, W=5, U=0, E=0
2006/12/15 07:25:20 - Output.0 - Finished processing (I=0, O=0, R=5, W=0, U=0, E=0
2006/12/15 07:25:20 - Spoon - The transformation has finished!!


You can see that "Dummy Output" writes nothing...so there is nothing even when previewing in spoon.

I have to say that I am finding Pentaho frustrating...I can see its power but try as I (and my colleagues...it's not "just me") do, I can't get to that value.

I'm going to have a hard time convincing my client to use Pentaho in a complex client-server situation if I can't even make a 2 series chart from a CSV disk file!

I *CAN* do everything I need with BIRT on its own, but as I said I can see the greater potential in adopting Pentaho...I just can't realise that potential.

Ideally: I'd like to see an end-to-end demo (I think that Pentaho is selling itself short with the existing demos, to be honest, espcially when it comes to kettle [it can do 100 million rows at Amazon but I can't read 5 with it! :-()

Sorry for the 'spew' but I really shoulnd't be having troubles with SUCH a simple situation.

Cheers,

Alph

[MattCasters]

> it can do 100 million rows at Amazon but I can't read 5 with it! :-()

I would love to hear the story behind that remark. Really I do.

On the Dummy: you can indeed do a preview on that in Spoon. Select the step, hit the preview button, done.
Incidently, that's how I write most of the loading transformation, with a dummy at the end. If the preview shows that I'm getting the desired output, I'm going for the physical persistence. (table, file)

Listen, more often than not, the source of all these issues is not the capabilities of this or that reporting engine. It's the (lack of) datamodel that's at the core of the problem most of the times.
If you're so desperate that you need to let Kettle drive your reports, take this piece of advice: make a multi-dimensional model. It will make your life a lot easier and it's fairly easy to do with Kettle.
You have pre-built steps for slowly changing and junk dimensions that take the pain out of it.

All the best,

Matt

[alpheratz]

>> it can do 100 million rows at Amazon but I can't read 5 with it! :-()

> I would love to hear the story behind that remark. Really I do.

"Matt Casters on Data Integration" : "Walking on Clouds"

http://www.ibridge.be/?p=23

:-)

The other worry I had was...once the Dummy output actually did produce output (which would presumably still be CSV), could the report studio actually USE it...it only takes XML, JDBC and a few limited types.

How to deal with this?

Really..I HAVE RTFMd (and so has a colleague)...guess our brains aren't firing on all cylinders.

Again: a pleae..could someone give up 5mins of their time to help us poor souls out...an example of end to end csv -> chart wold be worth a 1000 forum entries.

Alph

[pgw]

I agree with Alpheratz - an example would be very useful.

[ngoodman]

Then you should check out the example I've posted here:

http://www.nicholasgoodman.com/bt/bl...from-csv-file/

It's a CSV to Chart example that you can download, tinker, and examine.

Happy Holidays,
Nick

[alpheratz]

I really appreciate that. That was really good of you.

I was 95% of the way there but couldn't get anything working.

However...(there's always a however, isn't there :-( )

What I REALLY meant by end-end is to intgrate data sourced from Kettle into a report--one that was previously designed by report designer (or BIRT, perhaps).

I think you've probably only saved a few tens of questions at the moment ;-)

Where am I ultimately trying to get? Good question!

I want to get to a position where I provide access to a number of (wierd and wonderful == legacy, grungy) data sources. I'm sure (?!) I can make the necessary Pentaho components to do that...

I then want to be able to tell other people (even less Pentaho literate than I and my colleagues) "here...you design whatever reports you want using these 'ere datasources. I'll put them into the repository of 'common reports' and everyone will benefit."

With this worldview, I ned to integrate with the 'real' reporting functionality (I'll need to mix numerous dynamic charts, tables, text, images, etc. with static text & images) from numerous datasources all in the one report with flexibility in layout, text, template, etc.

I can see how to do all that with BIRT.

I don't think your (much appreciated, really!) recent example shows me that.

I think an example that did all THAT WOULD save you lots of forum postings (+ let you fill in a few blanks in the story you tell you clients[?] + make a kick a** demo!)

However, the Pentaho software seems to offer me more 'headroom'/power/flexibility. Perhaps I wont' need it all NOW but I like to plan ahead when I can.

The Pentaho business also seems to offer a better support 'story' (indeed, this very sequence is testament to that and is an excellent argument for when I talk with my clients...)

I'm going to have a further 'play' now to see if I can progress things on my own.

Again thanks. Much appreciated and happy christmas.

[ganeshsrvnn]

I need to use kettle for custom transformation inside a portal.is it possible or not .i need immediate reply pals.can it be hot coded to use for my app so that it will be called using a UI ,which performs transformation at the backend.and return the result to my portal.is it possible using this graphical tool.Need suggestions..............