Background

The Teiid project (http://www.jboss.org/teiid/) is a data virtualization system allowing applications to use data from multiple, heterogeneous data stores. Teiid includes a standard, easy-to-use JDBC driver making it possible to provide these Virtual Databases as a standard option when connecting to data from the Pentaho Platform and client tools.

Use Case

A logical place to begin leveraging Teiid would involve enhancing Pentaho's common connection dialog used by Pentaho Data Integration, Report Designer (Version 3.5), Metadata Editor, and Aggregation Designer.

Figure 1 - Common Connection Dialog

User Example

Example provided by codek - contact me through the forums for more info.

Note: This was all done with Teiid 6.0, but 6.1 is imminent on release and contains a lot of usability improvements. I tested against Pentaho 1.7.0 running on Tomcat 5.5 - The process shouldn't be significantly different for Pentaho 3.0

Here's what I did:

1. Download Teiid designer and install into Ganymede eclipse as per instructions here:

http://www.jboss.org/teiiddesigner/downloads.html Note: If using windows, and Teiid designer 6.0 then make sure your workspace is in a directory where there are no spaces, i.e. c:\workspace rather then the default "c:\documents and settings" as this doesn't work with the teiid designer.

2. Follow the quick start example carefully here:

http://docs.jboss.org/teiid/6.0/quick-start-guide/en-US/html/

Notes:

a. Create a source model for each data source. I created one for my Oracle database, and one for Salesforce.

b. Create a view model that combines the two source models

c. Edit the join of the 2 tables in the view model

d. Create a virtual database, and include the 3^rdjoin model.

e. Make sure the bindings are correct for your models in the vdb.

f. Test your vdb using the execute button in eclipse and get it working before proceeding!

3. Download and Deploy the embedded teiid to tomcat as per here:

https://www.jboss.org/community/wiki/DeployingTeiidEmbeddedinJBossAS

Create a datasource like so:

<Resource name="jdbc/TEIID" auth="Container" type="javax.sql.DataSource"

url="jdbc:metamatrix:TeiidDB@/teiid/deploy.properties"

driverClassName="com.metamatrix.jdbc.EmbeddedDriver"

dqp.useNonDelegateClassloader="true"

metamatrix.xatxnmgr.enabled="false"

/>

(Note I installed in c:\teiid so that explains @/teiid/deploy.properties)

Again, in 6.1 the dqp.useNonDelegateClassLoader is not required.

The Teiid team are currently working on providing a guide for Tomcat deployment too. (But following the JBoss one does work fine!)
4. Copy your vdb (virtual database file) from eclipse to c:\teiid\deploy

Note: To work around a 6.0 bug relating to authenticating against the rdbms (oracle) you must also do this:

a. Delete the oracle connector from configurations.xml

b. I also unzipped the vdb, and replaced the passwords in the config file with plain text rather than encrypted ones - I don't think this step is necessary if you do a.
5. Create an xaction that queries from this data source. Run it!

Note: If you update your vdb, I restarted tomcat just to be sure - this may not be necessary?

If you get stuck then definately post for help in the Teiid forums on the jboss website - they are very helpful.