Example: Getting Started Transformation

...

Then we can launch Carte or the Data Integration Server to execute a query against that new virtual database table:

SELECT     dealsize, sum(sales) as total_sales, count\(*) AS nr
FROM         gst
GROUP BY dealsize
HAVING     count\(*) > 20
ORDER BY sum(sales) DESC

This query is being parsed by the server and a transformation is being generated to convert the service transformation data into the requested format: !data-service-generated.jpeg\|border=1!

Image Added

The data which is being injected is originating from the service transformation: !data-service-service.jpeg|border=1\ Image Added
So for each executed query you will see 2 transformations listed on the server.

...

Client Examples

SQuirreL SQL

!squirrel-sql-kettle-jdbc.png\|border=1,width=300!Image Added

Since SQuirrel already contains most needed jar files, configuring it simply done by adding kettle-core.jar, kettle-engine.jar as a new driver jar file along with Apache Commons VFS 1.0 and scannotation.jar

Example setup: Image Added

BIRT

The following jar files need to be added:
* kettle-core.jar
* commons HTTP client
* commons code
* commons lang
* commons logging
* commons VFS (1.0)
* log4j
* scannotation

!birt-sql-kettle-jdbc.png\|border=1,width=300!Image Added

Pentaho Report Designer

!report-designer-sql-kettle-jdbc.png\|width=300!Image Added

Simply replace the kettle-*.jar files in the lib/ folder with new files from Kettle v5.0-M1 or higher.

...

You need a BI Server that uses the PDI 5.0 jar files or you can use an older version and update the kettle-core, kettle-db and kettle-engine jar files in the /tomcat/webapps/pentaho/WEB-INF/lib/ folder

!pentaho-interactive-reporting-sql-kettle-jdbc1.png\|border=1,width=300!Image Added

Pentaho Analyses (Mondrian): Analyzer / Saiku / JPivot

...

matt@kettle:~/pentaho/4.5.0-ee/server/biserver-ee$ rm ./tomcat/webapps/pentaho/WEB-INF/lib/kettle-core-4.3.0-GA.jar
matt@kettle:~/pentaho/4.5.0-ee/server/biserver-ee$ rm ./tomcat/webapps/pentaho/WEB-INF/lib/kettle-engine-4.3.0-GA.jar
matt@kettle:~/pentaho/4.5.0-ee/server/biserver-ee$ rm ./tomcat/webapps/pentaho/WEB-INF/lib/kettle-db-4.3.0-GA.jar
matt@kettle:~/pentaho/4.5.0-ee/server/biserver-ee$ cp /kettle/5.0/lib/kettle-core.jar ./tomcat/webapps/pentaho/WEB-INF/lib/
matt@kettle:~/pentaho/4.5.0-ee/server/biserver-ee$ cp /kettle/5.0/lib/kettle-db.jar ./tomcat/webapps/pentaho/WEB-INF/lib/
matt@kettle:~/pentaho/4.5.0-ee/server/biserver-ee$ cp /kettle/5.0/lib/kettle-engine.jar ./tomcat/webapps/pentaho/WEB-INF/lib/

Screen shot:

!pentaho-analyzer-sql-kettle-jdbc1.png\|border=1!Image Added

Fun fact: Mondrian generates the following SQL for the report shown above:

...

You can query a remote service transformation with any Kettle v5 or higher client.  You can query the service through the database explorer and the various database steps (for example the Table Input step).

!spoon-sql-kettle-jdbc1.png\|width=300!Image Added

DataCleaner

*TODO: ask project owners to change the current old driver class to the new thin one.*

...

Partial success as I'm getting some XML parsing errors.  However, adding the aforementioned jar files at least allow you to get back query fields:

!jaspersoft-sql-kettle-jdbc.png\|width=300!Image Added

To be investigated.

QlikView

[http://tiqview.tumblr.com/post/29820190073/stream-data-from-pentaho-kettle-into-qlikview-via-jdbc|http://tiqview.tumblr.com/post/29820190073/stream-data-from-pentaho-kettle-into-qlikview-via-jdbc]see the TIQView blog: Stream Data from Pentaho Kettle into QlikView via JDBC