Teiid Designer Parts Example
Project
The following instructions will
provide a quick overview of the Data Preview and VDB Execution functionality.
This example assumes you have a compatible Teiid instance running
within an application server (i.e. JBoss AS, etc..).
Project
Contents
This project contains 2 folders (data and readme) and 3 metadata models:
The data folder contains 5 data files you can
use (described below) to load your data JDBC databases with "parts"
data.
The following "csv-formatted"
data files contain sample data which you can use to inject into your
database for testing. The names of these files reflect the names of the
5 source tables defined in
PartsSourceA and PartsSourceB. They include:
- parts_table.data
- ship_via_table.data
- status_table.data
- supplier_parts_table.data
- supplier_table.data
The readme folder, of course,
contains the html backing this page.
The metadata models, PartsSourceA, PartsSourceB and PartsVirtual represent relational metadata which
will be used by the Teiid engine to perform data access queries on your
actual databases.
Each source model contains 5
tables, PARTS, SHIP_VIA, STATUS, SUPPLIER and SUPPLIER_PARTS.
Data Flow
One of the key features of Teiid
is the ability to abstract back-end data (sources) via business models
(views).
PartsVirtual.xmi is a View Model containing a SupplierInfo table and a partsByColor procedure. Each
of these relational objects is backed by user-defined SQL.
To view the SQL:
- Expand the SupplierInfo
table in the tree and double-click the "Transformation
Diagram" object
- Double-click the "T"
arrow object in the diagram to open the Transformation Editor (or
right-click select "Edit"
action)
The SQL reflects a join of data
from BOTH PartsSourceA and PartsSourceB
sources.
You can view the SQL for partsByColor
by selecting the "Edit Another
Transformation" button 
on the Transformation
Editor tool-bar, then
selecting partsByColor
in dialog.
Setting up Data Sources
Ultimately, your data source
models (PartsSourceA & PartsSourceB)
will need to be backed by actual data so they can be queried through
Data Preview or through a JDBC connection to your deployed VDB or Virtual Data Base.
Designer connections utilize the
standard Data Tools Eclipse framework.
To set up your database for your two source models follow the steps
below:
Create
Connection Profile
- Open the "Database Development"
perspective
- In the Data Source Explorer,
select
a "New Connection Profile"
button
- Select a Connection Profile Type,
enter
a valid name (i.e. PartsSourceA/B for instance)
- Click "Next>" and
provide required properties (including defining/selecting a driver
definition)
- Test your connection and
click Finish to create the
connection profile
Note that you may
need user/admin privileges in order to both CREATE the initial schema
and to load the actual table data.
Generate
DDL from source model
- Swich to Teiid Designer perspective
- Select PartsSourceA.xmi
file in Model Explorer
- Right-click select the "Export"
action
- Open the "Teiid Designer"
category and select the Data
Definition Language (DDL) file option.
- Click "Next", choose a
database DDL export type (i.e. MySQL, Oracle, etc...), select desired
export options, select the "..." button in the Export To section and navigate to
your data folder in your PartsProject and define a file name (i.e.
PartsSourceA.ddl). (Note: This ddl
file can be saved anywhere on your file system. It's recommended to
save in your workspace for viewing/editing)
- Select Finish to create your ddl file. (You may also need
to "Refresh" your project or folder before your ddl file appears in
tree)
Creating Tables In Database
The DDL you just generated contains the DDL commands required to create
the necessary table structure in your database schema.
To create these tables:
- Select your DDL file in your workspace (under data folder) and
right-click "Open With... > SQL
File Editor"
- Set the appropriate Connection Profile options at top of
scrapbook editor. This includes a database type and your Connection
Profile name created earlier (i.e. PartsSourceA)
- Put cursor in editor and right-click select "Execute All" action.
This should result in 5 new tables created in your schema
Loading Data into your Database
Now you have data base tables
defined but no data in your database.
To load the provided data into
the proper tables:
- Switch to the Database
Development perspective
- Select your connection profile and select "Refresh" action
- Expand the connection profile until you get to your 5 Tables.
- Right click on each table and select "Data > Load..." action
- Select the Browse button
and navigate to your project data
folder and select the corresponding xxxx_table.data file.
- Select Finish to execute
the loading of the data in the file to the database.
- Repeat steps 4, 5 & 6 for each table
Set Source Connection
Profiles
In order for Teiid Designer to
preview data for a table or procedure,
each source model has to be associated with a connection profile.
To can create this association
open Teiid
Designer perspective,
right-click on a source model
(i.e. PartsSourceA) and select the "Modeling
> Set Connection Profile"
action.
The results of this action
injects Teiid-specific connection properties
into your source model. (Note:
You
can
change this association at any
time by performing the same action and selecting a different connection
profile)
Perform the same action on
PartsSourceB, but select a different
connection profile.
Create Teiid Instance
The Teiid Designer perspectives
a Teiid
view which allows for the definition of multiple Teiid server instances
and their deployed VDBs and Data Sources. Data
Sources are the “JCA-compatible Connection Factory” xml
configuration
components required for Teiid to create the necessary connections to
query data at run-time.
- Right-click in Teiid view and select “New
Teiid
Instance” action (or click the same action on the
toolbar)
- In the New Teiid Instance
dialog provide:
- URL: mm[s]://<hostname>:<port1>
(Default for Teiid is “mms://localhost:31443")
- Note:
Check
your console log during start-up for your exact admin URL. It
should look like: 15:17:51,150 INFO [RUNTIME] Teiid Admin
mms://localhost:31443
- Default user-name is “admin”
- Default password is “admin”
- Hit finish
Initial Teiid View should contain your new Teiid
server instance.
- The
Teiid view default contents will include any VDB's or Data Sources
deployed to each
server instance.
Testing Your Data
Teiid Designer provides 2 ways
to test your data:
Preview Data action allows you to perform data
checks on your source and view tables and procedures without having to
switch to Database Development perspective and connect/query via SQL
Editor.
Executing or testing a VDB
involves creating and deploying the necessary Data Sources for each
source model, creating a VDB and defining the required translator and
JNDI data source names for all source models and performing the Execute VDB action and executing ad-hoc SQL
queries via the Data Tools SQL Scrapbook.
Preview Data
Previewing data within Teiid
Designer provides a quick method for verifying your data sources and
for validating SQL results defined in your view (i.e. virtual) tables
and procedures.
Basically the Preview Data
feature is equivalent to deploying a VDB containing your metadata
models, connecting to that VDB via a Teiid JDBC Connection Profile and
querying a single table or procedure utilizing a canned simple SQL
statement (i.e. SELECT * FROM PARTS). (See below)
The Preview Data action ( 
)
will be enabled if a previewable table, procedure or web-service
operation is selected in the Model Explorer or in a diagram.
To preview data for a table:
- Expand PartsSourceA in
the Model Explorer and select Parts table
- Click the Preview Data
toolbar button
- Note: as was stated earlier in this section, there are Teiid
VDB and Data Source artifacts required to set-up the necessary JDBC
connection to perform the canned query against. While these artifacts
are being created and deployed automatically, a monitor will be
displayed showing
progress.
- The query results will be displayed in Data Tools SQL Results view (you may want to
customize the view. The example results shown below is set to "Vertical
View Orientation".
Also note when editing Virtual
tables in
the Transformation Editor, there is a special Preview Virtual Data
toolbar button 
which allows you to edit/save/test
your SQL changes repeatedly without switching to the Model Explorer or
having to select the virtual table and right-click select Modeling >
Preview Data action.
Create
Data Sources
Each Teiid VDB requires at least
one source model. For each source model, additional model properties
are required by the Teiid server to identify and connect to the
back-end data source. This is accomplished by specifying a JNDI Data
Source name in the VDB Editor. In addition, a translator name is
required for most data-source types.
By setting
the
connection profile on
each model, you can generate a compatible Data Source and deploy it to
your default Teiid instance by selecting a source model and right-click
select "Modeling
>
Create Data Source"
action.
The resulting dialog allows you
to accept the connection information extracted from your source model
or modify/change the connection profile used to create this specific
Data Source. The default name generated for your source will be the
source model name. This will also be the generated JNDI name when
source models are added to a VDB. The defaults are provided for
expediency, however you are free to name your Data Sources as you see
fit as long as you edit the VDB's source model JNDI names to
match. If they do not match, the VDB's will be "in-active" upon
deployment to Teiid.
You may also be asked to provide
a password if you elected to not save the password during creation of
your connection profiles.
Create VDB
To create a VDB, select the
PartsVirtual model in the Model Explorer, right-click select "New > Teiid VDB" and click Finish.
The new VDB is opened in a VDB
Editor page, the view model is added as well as the 2 dependent source
models. In addition the translator names are set based on source model
connection profile information and the JNDI names are generated from
the model names (as described earlier). If you specified non-default
Data Source names in earlier steps, replace the JNDI names in this
editor.
Note that you could also create
an empty VDB and click the Add... button in the editor to select one or
more models in your workspace project and proceed from there.
Execute VDB
Once your VDB is saved, you can
simply select it in your Model Explorer and right-click select
"Modeling > Execute VDB" action.
This action will deploy your VDB
to your running Teiid instance, open the Data Tools' Database
Development perspective, create and connect to a JDBC Teiid connection
profile.
From here, you can select the
Parts -Teiid Connection, right-click select "Open SQL Scrapbook", enter
SQL such as "SELECT * FROM
SUPPLIERINFO WHERE SHIPPER_ID = '30' AND QUANTITY > 400".
Summary
This example provides just a
sample of the data modeling and testing provided by Teiid Designer
backed by the Teiid query engine.
See http://www.jboss.org/teiiddesigner.html
for latest on Teiid Designer project.
