In this tutorial, we are going to introduce data federation over multiple and possibly different data sources. Data federation technology provides an organization with the ability to aggregate data from disparate sources into a virtual database where it can be used for more comprehensive analysis. The tool we are going to use is called Teiid from JBoss community.

Before we start, follow these instructions first to have Teiid ready in your machine. In addition, you should already have the !Books and BookStores databases installed. If you haven't installed them, see Installing the Tutorial Databases and return here when you've completed the installation.

System Setup

Create Relational Metadata Model

These steps will show you how to create a metadata model for our sample databases using Teiid Designer in Eclipse.

Select File > New > Others... from the menu bar.

Create a new project Teiid Designer > Teiid Model Project and name the project !BookFederation. Accept all the default settings by clicking "Finish".

Select File > Import... from the menu bar.

Select Teiid Designer > JDBC Database in the Import window.

Create a new Connection Profile by selecting New....

Choose PostgreSQL as the "Connection Profile Types" and type the value books in the "Name" field.

IMPORTANT: The input "Name" field in the Connection Profile MUST follow the target database name. The reason is because the value will become the namespace for each table across different databases.

Select the JDBC driver for PostgreSQL from the selector field. You may add the driver JAR from the external directory.

Fill in all the connection parameters according to your settings. Perform test connection to ensure the parameters are typed correctly. Select "Finish".

You can perform data preview through your metadata models without creating VDBs. To preview the records of !Books database:

Select "books.xmi" in "Model Explorer' tab and do right-click followed by selecting Modeling > Set Connection Profile.

Expand the "Database Connections" and select "books". Select "OK".

Expand "books.xmi" and select a table (e.g., "tb_books").

Do a right-click and select Modeling > Preview Data.

Browse the "SQL Results View" tab to see the returned data.

Troubleshooting

There might be a deployment issue when you try to preview data for other different databases. To solve it, you have to delete all the deployment files in the JBOSS_HOME/standalone/data/teiid-data/ folder, i.e., all files begin with PREVIEW_ prefix. This action will force Designer to redeploy the files and redo the connection to the new database.

Book Federation

The !BookStores database consists of 2 tables. This database has a relationship with the !BookPublication. This section will show you how to integrate both databases into a single Virtual Database (VDB).

Step 1: Create Data Source

In order for a VDB to be executable, a Data Source (or, Connection Factory) needs to be deployed in the server instance.