Example: Getting Started Transformation

For this example we open the "Getting Started Transformation" (see the sample/transformations folder of your PDI distribution) and configure a Data Service for the "Number Range" called "gst". (comparable to the screenshot above)

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

SELECT&nbsp;&nbsp; dealsize, sum(sales) as total_sales, count\(*) AS nr
FROM&nbsp;&nbsp;&nbsp;&nbsp; gst
GROUP BY dealsize
HAVING&nbsp;&nbsp; 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!
The data which is being injected is originating from the service transformation: !data-service-service.jpeg|border=1\
So for each executed query you will see 2 transformations listed on the server.

Monitoring the Example

During execution of a query, 2 transformations will be executed on the server:

# A service transformation, of human design built in Spoon to provide the service data
# An automatically generated transformation to aggregate, sort and filter the data according to the SQL query

These 2 transformations will be visible on Carte or in Spoon in the slave server monitor and can be tracked, sniff tested, paused and stopped just like any other transformation. However, it will not be possible to restart them manually since both transformations are programatically linked.

Client Examples

SQuirreL SQL

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

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

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!

Pentaho Report Designer

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

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

Pentaho Schema Workbench

Replace the current kettle-*.jar files with the ones from Kettle v5 or later.

Pentaho Interactive Reporting

Interactive reporting runs off Pentaho Metadata so this advice also works there.

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!

Pentaho Analyses (Mondrian): Analyzer / Saiku / JPivot

See Pentaho Interactive reporting: simply update the kettle-*.jar files in your Pentaho BI Server (tested with 4.1.0 EE and 4.5.0 EE) to get it to work.

Example of patching :

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!

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

select "Service"."Category" as "c0", "Service"."Country" as "c1", sum("Service"."sales_amount") as "m0" from "Service" as "Service" group by "Service"."Category", "Service"."Country"

Kettle

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!

DataCleaner

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

Jaspersoft iReport Designer

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!

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]

Examples