Filip Nguyen
QA Engineer
Red Hat
EII for Small Businesses
Agenda
● Why Information Integration?
● Basic Use Cases
○ What is a difference to ESB?
○ 3 databases scenario
● JBoss Teiid basics
○ overview, installation
○ running, debugging
○ Teiid Designer
● JBoss Teiid usage
○ joining databases and accessing via JDBC
○ Web services access to the data
○ XML view of data
Basic Use Cases
Why EII?
● Many Data sources
○ Databases
○ Files
○ Excel Spreadsheets
○ Web Services
● Other EIS as data sources
● We would like to be data source agnostic
● SELECT * FROM people-addresses.txt,
people.xls, vacationsWebService.wsdl
SOA and ESB
● Sounds familiar?
● Complementary - Data Services
● Similar goals
● Focus on Data Sources
● Deeper support
○ JOINS
○ Query Language transparency (SQL, Web Services,
ODATA)
Basic Use Cases
● Integrate N databases
○ Have a common view on data
○ Perform joins
○ Join tables that reside in databases from different
vendors (syntax)
○ query mirrored databases
○ provide role based security on top of the data
● Integrate Files
● Integrate Web Services
3 Databases Scenario
● People database
● Accounting database
● Elearning database
People database
● Just a list of people with name and email
○ ID
○ FIRSTNAME
○ LASTNAME
○ EMAIL
Accounting Database
● EMPLOYEE table
○ FIRSTN
○ LASTN
○ BIRTH_NUMBER
● SALARY
○ how big monthly salary did the employee have in
certain time period
Elearning database
● PUPIL
○ NICK
○ LOGIN (this is an email of the person)
● RESULTS
○ TEST_NAME
○ PUPIL_FK
○ GRADE test results of the employee
Joining People and Accounting DB
● We do not have any common key, just
names
PEOPLE.FIRSTNAME and EMPLOYEE.FIRST
PEOPLE.LASTNAME and EMPLOYEE.LAST
How to solve this?
● Task: Send an email to anybody with higher
pay raise than 20%?
○ One query on Accounting for names
○ Second query on People for emails
● Consequence
○ Lot of mess, written in Java
○ Cannot use stored procedures
○ Memory management
○ Transaction management!
JBoss Teiid Basics
JBoss Teiid
● Runs inside JBoss AS
● Powerful tooling
● Open Source & Free
What it is?
Enterprise Information Integration
JBoss AS 7 installation
● Download the ZIP
● Unpack
● jboss-as-7.1.1.Final/bin/add-user.sh
○ this will add a user for management console
● Start the server
bin/standalone.sh -c standalone.xml
● Management console: http://localhost:9990/
Teiid Installation
● Download JBoss Teiid and unpack it into AS
● Run ./standalone.sh -c standalone-teiid.xml
● Done.
Teiid Designer Modeling
● Create Virtual Databases
● Creating data sources (files,databases)
● Deploy VDBs
Teiid Designer
● Eclipse Juno
● Teiid Designer 8.x
● http://www.jboss.org/teiiddesigner
● Installation
○ Download the Eclipse Juno
○ Install JBoss Tools (update site)
○ Install Teiid Designer (update site)
○ Switch to Teiid Designer perspective
Designer basics
● Eclipse is made up of perspectives and
views
● Teiid Designer Perspective
● Servers
● Teiid Model Project
Starting the server from Designer
● Configured for standalone-teiid.xml!
● Start/Stop
Debugging
● JBoss AS and Teiid are Java apps
● Setup remote debugging!
● Attach remote debugger from Eclipse
Connection to the Databases
● We deploy JDBC datasource to the AS
○ Supports any database over JDBC driver
● Database Development Perspective
○ This is independent database development
Databases in JBoss AS
● Database Driver
○ JDBC driver
○ represented by JAR
● Connection Profile
○ Concrete connection information using JDBC driver
○ Has name
○ May have stored password
● Connection
○ Can be created using Connection Profile
● Datasource
○ Object on some JBoss AS that enables developer to
create Connections
JBoss Teiid Usage
What we need
● Create 3 Connection profiles
○ People DB
○ Accounting DB
○ Elearning DB
● Create 3 datasources on JBoss AS
○ people-ds
○ accounting-ds
○ elearning-ds
● We will do it using Eclipse, it can be done in
many different ways
● Check the datasource are alive
Simple Federation
● Goal: create VDB that allows us to query
People DB. Deploy it to the JBoss AS.
● Create Source Models
● Create VDB
● Deploy and Query using DB development
perspective
Simple View Layer
● Goal
○ Join People DB to Accounting DB
○ New field "FULLNAME"
● We need to add Accoutning DB source
● Use Reconciliation Editor
○ Allows to bind the Source to View
○ Allows to change data types
● Use Criteria Builder
○ Allows to join and filter results
Joining People and Accounting DB
● We do not have any common key, just
names
● Get Email of people who has salary higher
than 30 000
PEOPLE.FIRSTNAME and EMPLOYEE.FIRST
PEOPLE.LASTNAME and EMPLOYEE.LAST
Adding elearning database
● We do not have any common key, just login
PEOPLE.EMAIL = PUPIL.LOGIN
● Query: Average Salary of the best students
○ Non trivial to do by hand, requires lots of queries
Web Service Access
● We can access our data from the view layer
through JDBC for now. Our customers want
Web Services!
● Easy to do
○ Create Web service from the tooling
○ Deploy the web service into any web container
○ The web container can access VDB datasource
remotely :-)
○ Use SOAP UI for testing!
Adding XML view of Data
● We can give XSD and map our results to this
structure
● Accessible using JDBC!
Features/Challanges
● Datasource Configuration
● Stable tooling
● Very complex
For Students
● Diploma thesis for K-NET
● They want an integration platform to build on
● New technology
● Interesting application area
Thank you!