JBoss.orgCommunity Documentation

Teiid Designer User Guide

7.8.0

Legal Notice

1. Introduction
1.1. What is Teiid Designer?
1.2. Metadata Overview
1.2.1. What is Metadata
1.2.2. Business and Technical Metadata
1.2.3. Design-Time and Runtime Metadata
1.2.4. Source and View Metadata
1.3. It's all in the Modeling...
1.3.1. What Are Models?
1.3.2. How is a Model Defined?
1.3.3. Guiding through the process
1.3.4. Model Classes and Types
1.3.5. Models and VDBs
1.3.6. Model Validation
1.3.7. Testing Your Models
1.3.8. Model Object Extensions
2. Dive Right In!
2.1. Guide Example
2.1.1. Model a JDBC Source
2.2. Cheat Sheet Example
2.2.1. Consume a SOAP Web Service
3. New Model Wizards
3.1. Creating New Relational Source Model
3.1.1. Generate File Translator Procedures
3.1.2. Generate Web Service Translator Procedures
3.1.3. Copy From Existing Model
3.2. Creating New Relational View Model
3.2.1. Copy From Existing Model
3.2.2. Transform From Existing Model
3.3. Creating XML Document View Model
3.3.1. Copy From Existing Model
3.3.2. Build XML Documents From XML Schema
3.4. Creating XML Schema Model
3.4.1. Copy From Existing Model
3.5. Creating Web Service View Model
3.5.1. Copy From Existing Model
3.5.2. Build From Existing WSDL File(s) or URL
3.5.3. Build From Relational Models
3.5.4. Build From XML Document View Models
4. Importers
4.1. Import DDL
4.2. Import From JDBC Database
4.3. Import From Flat File Source
4.4. Import From XML Data File Source
4.5. Import From Salesforce
4.6. Import Metadata From Text File
4.6.1. Import Relational Model (XML Format)
4.6.2. Import Relational Tables (CSV Format)
4.6.3. Import Relational View Tables (CSV Format)
4.6.4. Import Datatypes (CSV Format)
4.7. Import WSDL into Relational Models
4.7.1. Circular References in WSDL Schemas
4.8. Import WSDL Into Web Service
4.8.1. Import WSDL From Workspace Location
4.8.2. Import WSDL From File System Location
4.8.3. Import WSDL From URL
4.9. XSD Schema File
5. Creating and Editing Model Objects
5.1. Creating New Model Objects
5.1.1. New Child Action
5.1.2. New Sibling Action
5.1.3. New Association Action
5.2. Model Object Editors
5.2.1. Transformation Editor
5.2.2. Input Set Editor (XML)
5.2.3. Choice Editor (XML)
5.2.4. Recursion Editor (XML)
5.2.5. Operation Editor
5.3. Managing Model Object Extensions
5.3.1. Create New MED
5.3.2. Edit MED
5.3.3. Extending Models With MEDs
5.3.4. Setting Extended Property Values
6. Metadata-specific Modeling
6.1. Relational Source Modeling
6.1.1. Source Function
6.1.2. Create Relational Table Wizard
6.2. Relational View Modeling
6.2.1. Create Materialized Views
6.2.2. Create Relational View Table Wizard
6.3. XML Document Modeling
6.3.1. Create XML View Documents from schema
6.4. Web Services Modeling
6.4.1. Create Web Service Action
6.4.2. Web Services War Generation
7. Editing Models and Projects
7.1. Rename A Model
7.2. Move Model
7.3. Save Copy of Model
7.4. Clone Project
8. Testing Your Models
8.1. Manage Connection Profiles
8.1.1. Set Connection Profile for Source Model
8.1.2. View Connection Profile for Source Model
8.1.3. Remove Connection Profile from Source Model
8.2. Previewing Data For a Model
8.2.1. Preview Relational Table or View
8.2.2. Preview Relational Table With Access Pattern
8.2.3. Preview Relational Procedure
8.2.4. Preview Web Service Operation
8.2.5. Sample SQL Results for Preview Data
8.2.6. Execution Plans
8.3. Testing With Your VDB
8.3.1. Creating Data Sources
8.3.2. Execute VDB from Model Explorer
8.3.3. Deploy VDB from Model Explorer
8.3.4. Executing a Deployed VDB
9. Searching
9.1. Finding Model Objects
9.2. Search Transformation SQL
9.3. Search Models Via Metadata Properties
A. Supported Data Sources
B. User Preferences
B.1. Teiid Designer Preferences
B.1.1. Diagram Preferences
B.1.2. Diagram Printing Preferences
B.1.3. Editor Preferences
B.1.4. Validation Preferences
C. Teiid Designer Ui Reference
C.1. Teiid Designer Perspectives
C.1.1. Teiid Designer Perspective
C.1.2. Opening a Perspective
C.1.3. Further information
C.2. Teiid Designer Views
C.2.1. Model Explorer View
C.2.2. Outline View
C.2.3. Teiid View
C.2.4. Properties View
C.2.5. Description View
C.2.6. Problems View
C.2.7. Search Results View
C.2.8. Datatype Hierarchy View
C.2.9. Teiid Model Classes View
C.2.10. System Catalog View
C.2.11. SQL Reserved Words View
C.2.12. Model Extension Definition Registry View (MED Registry View)
C.2.13. Guides View
C.2.14. Status View
C.2.15. Cheat Sheets View
C.3. Editors
C.3.1. Model Editor
C.3.2. VDB Editor
C.3.3. Model Extension Definition Editor
C.4. Teiid Designer Main Menu
C.4.1. File Menu
C.4.2. Edit Menu
C.4.3. Refactor Menu
C.4.4. Navigate Menu
C.4.5. Search Menu
C.4.6. Project Menu
C.4.7. Metadata Menu
C.4.8. Run Menu
C.4.9. Window Menu
C.4.10. Help Menu

The Teiid Designer User's Guide provides detailed descriptions of Teiid Designer features and functionality.

Metadata is data about data. A piece of metadata, called a meta object in the Teiid Designer, contains information about a specific information structure, irrespective of whatever individual data fields that may comprise that structure.

Let’s use the example of a very basic database, an address book. Within your address book you certainly have a field or column for the ZIP code (or postal code number). Assuming that the address book services addresses within the United States, you can surmise the following about the column or field for the ZIP code:

This definition represents metadata about the ZIP code data in the address book database. It abstracts information from the database itself and becomes useful to describe the content of your enterprise information systems and to determine how a column in one enterprise information source relates to another, and how those two columns could be used together for a new purpose

You can think of this metadata in several contexts:

Editing Metadata vs. Editing Data

The Teiid Designer helps you to create and describe an abstract graphic representation of your data structure of your data in the original data sources. It also describes whether those data sources are composed of Relational databases, text files, data streams, legacy database systems, or some other information type.

The Teiid Designer allows you to create, edit, and link these graphically-represented meta objects that are really a description of your data, and not the data itself.

So when this documentation describes the process of creating, deleting, or editing these meta objects, remember that you are not, in fact, modifying the underlying data.

Metadata Models

A metadata model represents a collection of metadata information that describes a complete structure of data.

In a previous example we described the field ZIPCode as a metadata object in an address book database. This meta object represents a single distinct bit of metadata information. We alluded to its parent table, StreetAddress. These meta objects, and others that would describe the other tables and columns within the database, would all combine to form a Source Metadata model for whichever enterprise information system hosts all the objects.

You can have Source Models within your collection of metadata models These model physical data storage locations. You can also have View Models, which model the business view of the data. Each contains one type of metadata or another. For more information about difference between Source and View metadata, (see Section 1.2.4, “Source and View Metadata”).

NOTE: For detailed information about creating models from your metadata, see Section 1.3, “It's all in the Modeling...”

Metadata can include different types of information about a piece of data.

Note that the terms technical and business metadata, refer to the content of the metadata, namely what type of information is contained in the metadata. Don’t confuse these with the terms “physical” and “view” metadata that indicate what the metadata represents. For more information, (see Section 1.2.4, “Source and View Metadata”).

Technical Metadata

Technical metadata represents information that describes how to access the data in its original native data storage. Technical metadata includes things such as datatype, the name of the data in the enterprise information system, and other information that describes the way the native enterprise information system identifies the meta object

Using our example of an address book database, the following represent the technical metadata we know about the ZIP code column:

  • Named ZIPCode

  • Nine characters long

  • A string

  • Located in the StreetAddress table

  • Uses SQL Query Language

These bits of information describe the data and information required to access and process the data in the enterprise information system.

Business Metadata

Business metadata represents additional information about a piece of data, not necessarily related to its physical storage in the enterprise information system or data access requirements. It can also represent descriptions, business rules, and other additional information about a piece of data.

Continuing with our example of the ZIP Code column in the address book database, the following represents business metadata we may know about the ZIP code:

  • The first five characters represent the five ZIP code numbers, the final four represent the ZIP Plus Four digits if available, or 0000 if not

  • The application used to populate this field in the database strictly enforces the integrity of the data format

Although the first might seem technical, it does not directly relate to the physical storage of the data. It represents a business rule applied to the contents of the column, not the contents themselves.

The second, of course, represents some business information about the way the column was populated. This information, although useful to associate with our definition of the column, does not reflect the physical storage of the data.

Teiid Designer software distinguishes between design-time metadata and run-time metadata. This distinction becomes important if you use the Teiid Designer Server. Design-time data is laden with details and representations that help the user understand and efficiently organize metadata. Much of that detail is unnecessary to the underlying system that runs the Virtual Database that you will create. Any information that is not absolutely necessary to running the Virtual Database is stripped out of the run-time metadata to ensure maximum system performance.

Design-Time Metadata

Design-time metadata refers to data within your local directory that you have created or have imported. You can model this metadata in the Teiid Designer, adding Source and View metadata.

Runtime Metadata

Once you have adequately modeled your enterprise information systems, including the necessary technical metadata that describes the physical structure of your sources, you can use the metadata for data access.

To prepare the metadata for use in the Teiid Designer Server, you take a snapshot of a metadata model for the Teiid Designer Server to use when resolving queries from your client applications. This run-time metadata represents a static version of design-time metadata you created or imported. This snapshot is in the form of a Virtual Database definition, or VDB.

As you create this runtime metadata, the Teiid Designer:

You can continue to work with the design-time metadata, but once you have created a runtime metadata model, it remains static.

In addition to the distinction between business and technical metadata, you should know the difference between Source Metadata and View Metadata.

Source and View metadata refer to what the metadata represents, not its content.

Source Metadata directly represents metadata for an enterprise information system and captures exactly where and how the data is maintained. Source Metadata sounds similar to technical metadata, but Source Metadata can contain both technical and business metadata. When you model Source Metadata, you are modeling the data that your enterprise information systems contain.

View Metadata, on the other hand, represent tailored views that transform the Source Metadata into the terminology and domain of different applications. View Metadata, too, can contain both technical and business metadata. When you model View Metadata, you’re modeling the data as your applications (and your enterprise) ultimately use it.

Modeling Your Source Metadata

When you model the Source Metadata within your enterprise information systems, you capture some detailed information, including:

The Source Metadata captures this detailed technical metadata to provide a map of the data, the location of the data, and how you access it.

This collection of Source Metadata comprises a direct mapping of the information sources within your enterprise. If you use the Teiid Designer Server for information integration, this technical metadata plays an integral part in query resolution.

For example, our ZIPCode column and its parent table StreetAddress map directly to fields within our hypothetical address book database.

To extend our example, we might have a second source of information, a comma-separated text file provided by a marketing research vendor. This text file can supply additional demographic information based upon address or ZIP code. This text file would represent another Enterprise Information System (EIS), and the meta objects in its Source Model would describe each comma-separated value.

Modeling Your View Metadata

When you create View Metadata, you are not describing the nature of your physical data storage. Instead, you describe the way your enterprise uses the information in its day-to-day operations.

View Metadata derives its classes and attributes from other metadata. You can derive View Metadata from Source Metadata that describes the ultimate sources for the metadata or even from other View Metadata. However, when you model View Metadata, you create special “views” on your existing enterprise information systems that you can tailor to your business use or application expectations. This View Metadata offers many benefits:

Our example enterprise information sources, the address book database, and the vendor-supplied comma-delimited text file, reside in two different native storage formats and therefore have two Source Metadata models. However, they can represent one business need: a pool of addresses for a mass mailing.

By creating a View Metadata model, we could accurately show that this single View Table, the AddressPool, contains information from the two enterprise information systems. The View Metadata model not only shows from where it gets the information, but also the SQL operations it performs to select its information from its source models.

This View Metadata can not only reflect and describe how your organization uses that information, but, if your enterprise uses the Teiid Designer Server, your applications can use the View Metadata to resolve queries.

To create this View Metadata, you create a view and define a transformation for that view, a special query that enables you to select information from the source (or even other view) metadata models. For more information, see “Section 5.2.1, “Transformation Editor”.”

Metadata Transformations

By modeling View Metadata, you can illustrate the business view of your enterprise information sources. View Metadata models not only describe that business view, but also illustrate how the meta objects within the View Metadata models derive their information from other metadata models.

Let’s return to the example of our address book database and the vendor’s comma-separated list. We want to generate the View Metadata model, Address Pool, from these enterprise information systems.


The transformation that joins these metadata models to create the virtual Address Pool metadata model contains a SQL query, called a union, that determines what information to draw from the source metadata and what to do with it.

The resulting Address Pool contains not only the address information from our Address Book database, but also that from our vendor-supplied text file.

SQL in Transformations

Transformations contain SQL queries that SELECT the appropriate attributes from the information sources.

For example, from the sources the transformation could select relevant address columns, including first name, last name, street address, city, state, and ZIP code. Although the metadata models could contain other columns and tables, such as phone number, fax number, e-mail address, and Web URL, the transformation acts as a filter and populates the Address Pool metadata model with only the data essential to building our Address Pool.

You can add other SQL logic to the transformation query to transform the data information. For example, the address book database uses a nine-character string that represents the ZIP Plus Four. The transformation could perform any SQL-supported logic upon the ZIPCode column to substring this information into the format we want for the Address Pool View metadata model.

Mapping XML Transformations

When you model View Metadata, you can also create a View XML Document model. This View Document lets you select information from within your other data sources, just like a regular View Metadata model, but you can also map the results to tags within an XML document.


In this example, the Address Pool View Metadata model still selects its information from the Address Book Database and the Vendor Text File, but it also maps the resulting columns into tags in the Address XML document.

A model is a representation of a set of information constructs. A familiar model is the relational model, which defines tables composed of columns and containing records of data. Another familiar model is the XML model, which defines hierarchical data sets.

In Teiid Designer, models are used to define the entities, and relationships between those entities, required to fully define the integration of information sets so that they may be accessed in a uniform manner, using a single API and access protocol. The file extension used for these models is .xmi ( Example: NorthwindOracle.xmi ) which adheres to the XMI syntax defined by the OMG.

Below is an example of the partial contents of a model file.


Note

Model files should never be modified "by hand". While it is possible to do so, there is the possibility that you may corrupt the file such that it cannot be used within the Teiid Designer system.

The fundamental models in Teiid Designer define the structural and data characteristics of the information contained in data sources. These are referred to as source models (represented by ). Teiid Designer uses the information in source models to federate the information in multiple sources, so that from a user's viewpoint these all appear to be in a single source.


In addition to source models, Teiid Designer provides the ability to define a variety of view models(represented by ). These can be used to define a layer of abstraction above the physical (or source) layer, so that information can be presented to end users and consuming applications in business terms rather than as it is physically stored. Views are mapped to sources using transformations between models. These business views can be in a variety of forms:

  • Relational Tables and Views

  • XML

  • Web services

  • Functions

For full list of supported model types see Chapter 3, New Model Wizards

A third model type, logical, provides the ability to define models from a logical or structural perspective.

To make the process of using Teiid Designer to build models more as easy as posssible, a guides view (Section C.2.13, “Guides View”) has been introduced. It provides action sets which bring together the actions necessary to develop models for specific use-cases. Action sets are available for the following scenerios:

  • Consuming a SOAP Web Service
  • Creating a REST WAR archive
  • Creating a SOAP WAR archive
  • Modelling from a Flat File Source (a text file)
  • Modelling from a JDBC Data Source
  • Modelling from a Local XML File Source
  • Modelling from a Remote XML File Source
  • Connecting to a Teiid Server

Teiid Designer in conjunction with Teiid provides an extensible framework to define custom properties for model objects over-and-above what is defined in the metamodel. These custom property values are added to your VDB and included in your runtime metadata. This additional metadata is available to use in your custom translators for both source query manipulation as well as adjusting your result set data being returned.

In the 7.6 release, Teiid Designer introduces a new Model Extension Definition (MED) framework that will replace the current EMF-based Model Extension metamodel in a later 8.0 release.

This new MED framework provides the following improvements:

Also see: Section 5.3, “Managing Model Object Extensions” and Section C.3.3, “Model Extension Definition Editor”.

The purpose of a MED is to define one or more sets of extension properties. Each set of extension properties pertains to one model object type (or metaclass). Each MED consists of the following:

A MED file is an XML file with an extension of "mxd." A MED schema file (see attached modelExtension.xsd file) is used to validate a MED file. Here is a sample MED file:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<modelExtension xmlns:p="http://org.teiid.modelExtension/2011"
		xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	metamodelUri="http://www.metamatrix.com/metamodels/Relational"
    	namespacePrefix="mymodelextension" namespaceUri="org.my.extension.mymodelextension"
    	version="1"
    	xsi:schemaLocation="http://org.teiid.modelExtension/2011 modelExtension.xsd"
    	xmlns="http://org.teiid.modelExtension/2011">
    <p:description>This is my model extension</p:description>
    <p:extendedMetaclass name="com.metamatrix.metamodels.relational.impl.BaseTableImpl">
        <p:property advanced="false" index="true" masked="false" name="copyable" required="false" type="boolean">
            <p:description locale="en_US">Indicates if table can be copied</p:description>
            <p:display locale="en_US">Copyable</p:display>
        </p:property>
    </p:extendedMetaclass>
</modelExtension>
				

The MED Registry is where the MEDs used by Designer are stored. MED files can be edited by opening the .mxd file in the Section C.3.3, “Model Extension Definition Editor”.

We are going to dive right into a couple examples of common tasks in this section. These examples will give you a quick introduction to the capabilities that are built into Designer to assist you with common design tasks. Specifically, we will introduce the following concepts:

  • Guides

    The Guides View is a good starting point for many common modeling tasks. The view includes categorized Modeling Actions and also links to Cheat Sheets for common tasks. The categorized Modeling Actions simply group together all of the actions that you'll need to accomplish a task. You can launch the actions directly from the Guides view, rather than hunting through the various Designer menus.

  • Cheat Sheets

    The Cheat Sheets go beyond even the categorized Action Sets, and walk you step-by-step through some common tasks. At each step, the data entered in the previous step is carried through the process when possible.

After seeing the Guides and Cheat Sheets in action, subsequent chapters will offer detailed explanations of the various concepts and actions.

In this section, we introduce the Guides View by walking through a simple example. For this example, we will follow the Model JDBC Source Action Set. The actions appear in the following order:

The action names are self explanatory. We will create a new "Model Project" in the workspace, then define our connection properties to a MySQL database. We will then connect to the database and import the 'metadata', creating a source model in Designer. Next we will 'preview' the database contents. Finally we will define a 'VDB' and then deploy it to a running Teiid Server to execute.

This section shows how to Model a JDBC Source, using the Guide View action set. We will connect to a MySQL database for this example, but you can use the same process to connect to any supported database.

  1. Open Guides View

    To open the Teiid Designer's Guides view, select the main menu's Window > Show View > Other... and select the Teiid Designer > Guides view in the dialog.

    The Guides view is shown below, with the Model JDBC Source Action Set selected:


  2. Define Teiid Model Project

    The Define Teiid Model Project action launches the New Model Project Wizard. In the Action Set list, double-click the action (or select it, then click 'Execute selected action'). The wizard is launched as shown below:


    Enter a project name, e.g. 'MyProject' for the name. Then click Next. The next page of the wizard is shown below:


    Under 'Create Folders', de-select 'schemas' and 'web_services' - we won't need them for this example. Now, click Finish to exit the wizard. The project has now been created - your Model Explorer view should like like this:


  3. Create JDBC connection

    The Create JDBC connection action will create the 'Connection profile' for your database. The connection profile defines the properties and driver to be used when connecting to the database. In the Action Set list, double-click the action (or select it, then click 'Execute selected action'). The wizard is launched as shown below:


    Select the type of database that you are connecting to (e.g. MySQL), and enter a name for the connection profile, e.g. 'TestMySQL'. Click Next.


    Now, select the driver and enter the login properties for your database. Click Finish to complete the profile creation.

  4. Create source model for JDBC data source

    The Create source model for JDBC data source action will now utilitze the Connection profile that you just created, to import the metadata from the database to create your Teiid Source Model. In the Action Set list, double-click the action (or select it, then click 'Execute selected action'). The wizard is launched as shown below:


    On this page, select the 'TestMySQL' Connection profile that you created in the previous step. Click Next.


    On this page, select the database metadata that you want to import. When finished, click Next.


    On this page, select the specific objects from the database that you want to import. When finished, click Next.


    Finally, choose the name for the model to be created (defaults to 'profileName'.xmi). The 'Into Folder' field defines the target location for your new model. Select the 'MyProject/sources' folder. Now, click Finish. The source model has now been created - your Model Explorer view should like like this:


  5. Preview Data

    All execution capabilities in Designer (Preview Data, VDB execution) require you to connect to a running Teiid Server. See Section C.2.3, “Teiid View” for instructions on establishing a Teiid Server connection. Once you are connected to a Teiid Server, you can proceed with the following steps.

    The Preview Data action allows you to preview a sample of data rows from your source. In the Action Set list, double-click the action (or select it, then click 'Execute selected action'). In the dialog, select the source table you want to preview, as shown below:


    After selecting the table, click OK. Now, the preview results will be displayed:


  6. Define VDB

    The Define VDB action allows you to create a VDB (Virtual Database) artifact for deployment to a Teiid Server. In the Action Set list, double-click the action (or select it, then click 'Execute selected action'). The following dialog is displayed:


    In the dialog, select the target 'In Folder' location where the VDB will be placed. Enter a Name for the VDB, for example 'myVDB'. Finally, select the models that will be included in the VDB. When finished, click Finish. The VDB will be created in your Teiid Model Project - as shown in the following figure.


  7. Execute VDB

    Finally, the Execute VDB action allows you to execute your VDB and run sample queries against it. In the Action Set list, double-click the action (or select it, then click 'Execute selected action'). In the dialog, select the VDB you want to execute, then click OK. The VDB will be deployed and executed, and the perpective will switch to the 'Database Development' perspective. You can now run queries against the VDB, as show in the following example:


In this section, we introduce Cheat Sheets by walking through a simple example. For this example, we will follow the Consume a SOAP Web Service Cheat Sheet.

This section shows how to consume a SOAP Web Service, using a Cheat Sheet. We will demonstrate connection to a publicly accessible web service. You can use this process as an example for modeling other web services

  1. Open the Cheat Sheet

    You can access the Cheat Sheet from the Designer Menu. From the Designer main menu, select Window > Show View > Other..., then select Help > Cheat Sheets in the dialog.

    Alternately, you can access the Cheat Sheet from the Guide View. A sample Guide view is shown below, with the Consume a SOAP Web Service Action Set selected:


    To open the Cheat Sheet from the Guide View, expand the Cheat Sheet section in the lower portion of the Guide View, then select the Consume a SOAP Web Service link.

  2. Begin the Cheat Sheet

    The Consume a SOAP Web Service Cheat Sheet is shown below:


    To start the Cheat Sheet process, expand the Introduction section, then select Click to Begin. The Create New Teiid Model Project section opens, as shown.


    Note that each section of the sheet has basic instructions outlining what to do at each step. Click next to Launch New Teiid Model Project Wizard to launch the 'New Project' wizard.

    Follow the wizard to create a new Model Project. For this example, we will use SOAPProj for our project name. On the second page of the wizard, select the 'sources' and 'views' folders. Click Finish. The new project is created.

    In the Cheat Sheet, you can advance to the next step - once the wizard has completed. Click to advance to the next step.

  3. Create SOAP Web Service Connection

    This section of the Cheat Sheet provides instructions for creating a connection profile for the SOAP Web Service, as shown below:


    Click next to Launch Create SOAP Connection Profile Wizard to launch the wizard. The first page of the wizard is shown below:


    The Web Services Data Source (SOAP) profile type will be selected. Enter CountryInfoConn for the profile name, then click Next. The next page of the wizard is shown below:


    The connection profile properties are entered on this page. Click on the URL... button, then enter the following URL: http://www.oorsprong.org/websamples.countryinfo/CountryInfoService.wso?WSDL

    Select 'None' for SecurityType, then click OK to complete the wizard. In the Cheat Sheet, you can now continue - once the wizard has completed. Click to advance to the next step.

  4. Create Models from SOAP Connection

    This section of the Cheat Sheet provides instructions for creating relational models using the previously-created connection profile for the SOAP Web Service, as shown below:


    Click next to Launch the Consume SOAP Web Service Wizard to launch the wizard. The first page of the wizard is shown below:


    For Connection Profile, select the previously-created CountryInfoConn profile. The available WSDL Operations will then be displayed under Select the desired WSDL Operations. Select only the first CapitalCity Operation for this example. Click Next to proceed to the next page, as shown below:


    On the Model Definition page, the source and view model info section will be pre-filled. We will keep the names and location defaults for the source and view models. Click Next to proceed to the next page, as shown below:


    On the Procedure Definition page, the CapitalCity Operation will be selected since it is the only one used for this example. On the Request tab, select the sCountryISOCode element - then click the Add button. This will add the selected element to the request. Now select the Response tab, as shown below:


    On the Response tab, select the Body sub-tab. In the Schema Contents, select the CapitalCityResult, then click the Add button. This will add the selected element to the response.

    Select the Wrapper Procedure tab to see the full Generated Procedure SQL, as shown below.


    Click Finish to exit the wizard. In the Cheat Sheet, you can now continue. Click to advance to the next step.

  5. Create VDB

    This section of the Cheat Sheet provides instructions for creating a VDB using the models that you created in the previous step. The Cheat Sheet section is shown below:


    Click next to Launch New VDB Wizard to launch the wizard. Follow the steps to create a VDB in your workspace. When complete, exit the wizard. In the Cheat Sheet, you can now continue. Click to advance to the next step.

  6. Test VDB

    This final section of the Cheat Sheet provides instructions for executing the VDB created in the previous step. Click next to Launch Execute VDB Dialog to launch the wizard. Select the previously-created VDB to execute it.

Models are the primary resource used by the Teiid Designer. Creating models can be accomplished by either directly importing existing metadata or by creating them using one of several New Model wizard options. This section describes these wizards in detail.

Use one of the following options to launch the New Model Wizard.

New Model Wizard

Note

Model names are required to be unique within Designer. When specifying model names in new model wizards and dialogues error messages will be presented and you will prevented from entering an existing name.


Create New Relational Source Model

Note

You can change the target location (i.e. project or folder) by selecting the Browse... button and selecting a project or folder within your workspace.

  • In addition to creating a new empty relational source model, the following builder options are available:

    • Copy from existing model of the same model class.

This builder option allows construction of a relational model containing one or more of the procedures required for accessing file-based data via a file translator.

This builder option allows construction of a relational model containing one or more of the procedures required for accessing web-service-based XML data via a web s translator.

This builder option performs a structural copy of the contents of an existing model to a newly defined model. You can choose a full copy or select individual model components for copy.

Create New Relational View Model

Note

You can change the target location (i.e. project or folder) by selecting the Browse... button and selecting a project or folder within your workspace.

  • In addition to creating a new empty relational view model, the following builder options are available:

    • Copy from existing model of the same model class.

    • Transform from existing model.

This builder option performs a structural copy of the contents of an existing model to a newly defined model. You can choose a full copy or select individual model components for copy.

Create XML Document View Model

Note

You can change the target location (i.e. project or folder) by selecting the Browse... button and selecting a project or folder within your workspace.

  • In addition to creating a new empty XML document view model, the following builder options are available:

    • Copy from existing model of the same model class.

    • Build XML documents from XML schema.

This builder option performs a structural copy of the contents of an existing model to a newly defined model. You can choose a full copy or select individual model components for copy.

This option creates an XML View document model based on a selected XML schema and its dependencies.

Create XML Schema Model

Note

You can change the target location (i.e. project or folder) by selecting the Browse... button and selecting a project or folder within your workspace.

  • In addition to creating a new empty XML schema model, the following builder option is available:

    • Copy from existing model of the same model class.

This builder option performs a structural copy of the contents of an existing model to a newly defined model. You can choose a full copy or select individual model components for copy.

Create Web Service View Model

Note

You can change the target location (i.e. project or folder) by selecting the Browse... button and selecting a project or folder within your workspace.

  • In addition to creating a new empty web service view model, the following builder options are available:

    • Copy from existing model of the same model class.

    • Build from existing WSDL file(s) or URL.

This builder option performs a structural copy of the contents of an existing model to a newly defined model. You can choose a full copy or select individual model components for copy.

The Import Wizard provides a means to create a model based on the structure of a data source, to convert existing metadata (i.e. WSDL or XML Schema) into a source model or to load existing metadata files into the current VDB.

To launch the Import Wizard, choose the File > Import action or select a project, folder or model in the tree and right-click choose "Import..."


During the Finish processing, a monitor will be displayed providing feedback on the import progress.


When your import is finished your source model will be opened in an editor and show a diagram containing the your getTextFiles() procedure.


In addition, the view model will be opened in an editor and will show the generated view tables containing the completed SQL required to access the data in your flat file using the "getTextFiles" procedure above and the Teiid TEXTTABLE() function. The following figure is an example of a generated view table.


When finished, the new or changed relational model's package diagram will be displayed showing your new tables.


You can turn your WSDL file (local or URL) into a queryable relational procedures that represent your desired request and response web service structure defined through your WSDL's schema definition. This importer is accessed by launching Eclipse's "Import..." action and selecting the "Teiid Designer > WSDL File or URL >> Source and View Model (SOAP)" option. Web Services Connection Profile defined by a WSDL file in your workspace or defined by a URL. Designer will interpret the WSDL, locate any associated or dependent XML schema files, generate a physical model to invoke the service, and generate virtual models containg procedures to build and parse the XML declared as the service messages.

In the Model Explorer you can see the importer created the following a single physical model containing a single procedure called invoke. This model and procedure correspond to the single port declared in the WSDL.

A single view model was also created containing your new procedures named after the operations declared in the WSDL. For each operation a wrapper procedure was created which can be previewed in Designer. Below is an example dependency diagram showing the sources for the wrapper procedure as request, response procedures and the invoke() source procedure.


You can create a Web Service model by selecting a WSDL file in your workspace, importing WSDL files from the file system or by defining a URL. The Teiid Designer will interpret the WSDL, locate any associated or dependent XML Schema files, generate an XML View of the schema components and create a Web Service model representing the interfaces and operations defined in the WSDL.

Detailed steps for each of these options is described below, as well as a description of how the wizard handles WSDL errors.

In order to successfully generate Web Services from WSDL, the WSDL must be error free. WSDL validation is performed during Step 3 above. If errors do exist, a error summary dialog will be displayed (shown below) and you will not be able to Finish the wizard until the WSDL problems are fixed or you re-import and select a valid WSDL file.


In order to successfully generate Web Services from WSDL, the WSDL must be error free. WSDL validation is performed during Step 3 above. If errors do exist, a error summary dialog will be displayed (shown below) and you will not be able to Finish the wizard until the WSDL problems are fixed or you re-import and select a valid WSDL file.


In order to successfully generate Web Services from WSDL, the WSDL must be error free. WSDL validation is performed during Step 3 above. If errors do exist, a error summary dialog will be displayed (shown below) and you will not be able to Finish the wizard until the WSDL problems are fixed or you re-import and select a valid WSDL file.


This section summarizes Teiid Designer features for creating and editing existing model objects contained in your models.

As discussed in the introduction, Section 1.1, “What is Teiid Designer?”, Teiid Designer provides a framework to model various types of metadata. Each metamodel type has a set of parent-child relationships that establish constraints on what can be created and where. You cannot, for example, create a column attribute in a stored procedure, nor can you create a mapping class column in a Web service operation's output message.

The Teiid Designer provides a common set of actions to create new children of these models as well as children of children.

  • To create new associations between model objects in the Section C.3.1.1, “Diagram Editor”:

    • Step 1 - Select two objects you wish to associate. For example, select columns in different base tables.

    • Step 2 - Right-click. From the pop-up menu, select New Association > Foreign Key Relationship..

    • Step 3 - The new relationship link is displayed in the diagram. The Column, Foreign Key, Primary Key reference properties are properly set on the selected columns, new primary key and new foreign key.

    OR

    • Step 1 - Select a column in table.

    • Step 2 - Drag the column to another table and drag over a column and drop onto this column. The target column should highlight in Yellow.

    • Step 3 - The new relationship link is displayed in the diagram. The Column, Foreign Key, Primary Key reference properties are properly set on the selected columns, new primary key and new foreign key.

  • To create new associations between model objects in the Section C.3.1.2, “Table Editor”:

    • Step 1 - Select two objects you wish to associate. For example, select columns in different base tables.

    • Step 2 - Right-click. From the pop-up menu, select New Association > Foreign Key Relationship..

    • Step 3 - New Foreign Key and Primary Key objects will be added to the contents of their respective tabs in the Table Editor. The Column, Foreign Key, Primary Key reference properties are properly set on the selected columns, new primary key and new foreign key.

The primary actions for editing model objects are:

These actions are presented in Teiid Designer's main Edit menu and also in the right-click context menus for model objects selected in the Section C.2.1, “Model Explorer View”, Section C.3.1.1, “Diagram Editor” and Section C.3.1.2, “Table Editor”.

Modeling Sub-Menu

In addition to the New Child/Sibling/Association menus available for object creation Designer provides a Modeling > sub-menu which presents various object-specific actions which can be performed.

If you select a source table, for instance, the modeling menu below would be presented:


If a view table is selected, the menu would reflect the actions related to virtual operations:


Teiid Designer also provides specialized object editors to handle complex model objects and their unique properties. These editors include:

This section describes these editors in detail.

The Teiid Designer's Transformation Editor enables you to create the query transformations that describe how to derive your virtual metadata information from physical metadata sources or other virtual metadata and how to update the sources.

The Transformation Editor provides a robust set of tools you can use to create these SQL queries. You can use these tools, or you can simply type a SQL query into the Transformation Editor.

To edit a transformation you can:

If a Model Editor is not currently open for the selected object's model, a Model Editor will be opened.

After the corresponding transformation diagram is opened in the Diagram Editor, the Transformation Editor is displayed in the lower section of the Diagram Editor.


If this virtual class supports updates, the tabs on the bottom of the Transformation Editor allow you to enter SQL for each type of query this virtual class supports. If this virtual class does not support updates, only the SELECT tab is available.

You can enter separate SQL queries on each available tab to accommodate that type of query.

Within the Transformation Editor, you can:

  • Disable specific update transformation types on this virtual class.

  • Start your transformation with a provided SQL Template.

  • Build or edit a criteria clause to use in your transformation.

  • Build or edit an expression to use in your transformation.

  • Find and replace a string within your transformation.

  • Validate the transformation to ensure its content contains no errors.

  • Reconcile target attributes to ensure the symbols in your transformation match the attributes in your virtual metadata class.

You can also set preferences that impact the display of your Transformation Editor. For more information, see Section B.1.3.3, “Transformation Editor Preferences”

  • The Transformation Editor toolbar actions are summarized below.

    • Prevew Virtual Data - executes a simple preview query for the target table or procedure of the transformation being edited.

    • Search Transformations - provides a simple way select and edit another transformation based SQL text search criteria.

    • Edit Transformation - provides a simple way to change which transformation to edit without searching in a diagram or the Model Explorer. Simply click the action and select from a list of views, tables, procedures or operations from the currently edited model.

    • Cursor Position (line, column) - shows the current line and column position of the insertion cursor. For example, Cursor Position(1,4) indicates that the cursor is presently located at column 4 of line 1.

    • Supports Update - checkbox allows you to enable or disable updates for the current transformation target. If 'Supports Update' is checked, the editor shows four tabs at the bottom for the Select, Update, Insert and Delete transformations. If 'Supports Update' is unchecked, all updates are disabled and only the Select transformation is displayed.

    • Reconcile - allows you to resolve any discrepancies between the transformation symbols and the target attributes. Pressing this button will display the "Reconcile Virtual Target Attributes" dialog box in which you can resolve discrepancies. See Section 5.2.1.1, “Using the Reconciler” for more information about the Reconciler Dialog.

    • Save/Validate - saves edits to the current transformation and validates the transformation SQL. Any Warning or Error messages will be displayed at the bottom of the editor in the messages area. If the SQL validates without error, the message area is not displayed.

    • Criteria Builder - allows you to build a criteria clause in your transformation. The button will enable if the cursor position is within a query that allows a criteria. Pressing the button will launch the Criteria Builder dialog. If the Criteria Builder is launched inside an existing criteria, that criteria will be displayed for edit, otherwise the Criteria Builder will be initially empty. See Section 5.2.1.3, “Using the Criteria Builder” for further information.

    • Expression Builder - allows you to build an expression within your transformation. The button will enable if the cursor position is at a location that allows an expression. Pressing the button will launch the Expression Builder dialog. If the Expression Builder is launched inside an existing expression, that expression will be displayed for edit, otherwise the Expression Builder will be initially empty. See Section 5.2.1.4, “Using the Expression Builder” for further information.

    • Expand Select * - allows you to expand a "SELECT *" clause into a SELECT clause which contains all of the SELECT symbols. The button will enable only if the cursor is within a query that contains a SELECT * clause that can be expanded.

    • Increase Font Size - increases the font size of all editor text by 1.

    • Decrease Font Size - decreases the font size of all editor text by 1.

    • Show/Hide Messages - toggles the display of the message area at the bottom of the transformation editor.

    • Optimize SQL - when toggled 'ON', will use the short names of all SQL symbols that can be optimized. Some symbol names may remain fully qualified in the event of a duplicate name or if the optimizer is unable to optimize it. When the action is toggled 'OFF', all symbol names will be fully-qualified.

    • Import SQL Text - allows you to import a sql statement from a text file on your file system. Pressing this button will display an import dialog in which you can navigate to the file.

    • Export SQL Text - allows you to export the currently displayed SQL statement into a text file on your file system. Pressing this button will display an export dialog in which you can choose the location for export.

    • Close "X" - closes the transformation editor.

  • The Transformation Editor context menu can be displayed by Rt-Clicking within the editor's text area. The context menu is show below:


    Following is a summary of the context menu actions:

The Transformation Editor’s Reconciler offers you a quick, graphical means to reconcile the Target View attributes and the Transformation SQL. As you make changes, the overall status will appear at the top of the dialog to assist you in successfully completing your edits.

To launch the Reconciler, click on the Reconcile Transformation button in the Transformation Editor. The Reconciler Dialog is shown below:


To summarize the different sections of the dialog:

Once you are finished defining the bindings and resolving datatypes, click 'OK' to accept the changes. The transformation SQL will change to reflect your edits.

This dialog is accessible from the Reconciler dialog (See Section 5.2.1.1, “Using the Reconciler”) and offers you a quick way to resolve datatype conflicts between a target attribute and its SQL Symbol. The Datatype Resolver Dialog is shown below:


To summarize the different sections of the dialog:

  • Target Attribute - SQL Symbol Table: This table shows all target attribute - SQL Symbol bindings from the Reconciler Dialog which have a type conflict. Select on a table row to populate the lower Edit Panel

  • Edit Panel: The lower panel shows the Target Attribute and SQL Symbol datatype information for the selected binding. You can resolve the conflict in one of the following ways:

    • Virtual Target Attribute: Resolve the type conflict by changing the target attribute type to be compatible with the SQL Symbol type. The attribute's current runtime type is shown, along with a potential new datatype - and some button controls:

      • Apply Button: If the suggested datatype is acceptable, click 'Apply' to allow the attribute type to be changed.
      • Convert All Button: If you wish to change all of the attribute types in the table to be compatible with its corresponding SQL Symbol datatype, click the 'Convert All' button.
      • Change Button: If the suggested datatype is not acceptable, click 'Change' to choose your own datatype from a datatype dialog.
    • SQL Symbol: Resolve the type conflict by applying a CONVERT function to the SQL Symbol, so that its type is compatible with the target attribute type. The SQL Symbol's current type is shown, along with a suggested CONVERT function - and two button controls:

      • Apply Button: If the suggested CONVERT function is acceptable, click 'Apply' to apply the CONVERT function to the SQL Symbol.
      • Convert All Button: If you wish to apply a CONVERT function to all of the SQL Symbols in the table so that their datatype is compatible with the corresponding attribute datateyp, click the 'Convert All' button.

Once you are finished resolving datatypes, click 'OK' to accept the changes. You are directed back to the Reconciler Dialog, which will be updated to reflect your edits.

The Transformation Editor’s Criteria Builder offers you a quick, graphical means to build criteria clauses in your transformations based on meta objects in your diagram. If you launch the Criteria Builder with your cursor within an existing criteria in your transformation SQL, the builder will open in Edit mode. If your cursor is not in an existing criteria location, the builder will open in create mode and allow you to create it from scratch.

This procedure provides an example of building a criteria clause using the Criteria Builder. When building your own criteria, you can mix and match the values and constants with whatever logic you need to build powerful and complex criteria.

The criteria clause displays in the Criteria tree.

You can create complex criteria by joining other criteria with this one. To join criteria with this one, select the criteria in the Criteria tree and click:

  • Delete to remove the selected criterion.

  • AND to create a new criterion that must also be true.

  • OR to create a new criterion that can be true instead of the selected criterion.

  • NOT to establish negative criterion.

If you join a criterion to the one you just completed, you build the expression the same way, using the Expression Editors panel and the Predicate Editor panel. You can create complex, nested criteria by judicious use of the AND and OR buttons.

Once you have created the complete criteria you want, click OK to add it to your transformation.

The Transformation Editor’s Expression Builder offers you a quick, graphical means to build expressions in your transformations. This Expression Builder lets you create:

The Input Set represents a special class that contains attributes from a parent mapping class. When you create mapping classes for an XML Document model, the Teiid Designer automatically adds an Input Set to all XML transformation diagrams for mapping classes beneath the highest node in the Document meta object.

The Input Set proves especially useful for information integration using the Teiid Designer Server. Through the Input Set, you can access a row of data generated by any XML transformation in a mapping class higher in the XML document’s hierarchy. You can use Input Set attributes, which are individual columns from the rows of data, within the criteria of an XML transformation query of the child mapping class.

You cannot use the Input Set attributes within the SELECT portion of the XML transformation query.

To use an Input Set, you must use the Input Set Editor to bind attributes from parent classes.

Once you have created an Input Set, you can use the attributes within it as source material for the XML transformation diagram’s query.

The Input Set only serves to enable data flow between nested mapping classes. If you use the Teiid Designer Server for data access, your applications cannot directly query an Input Set. Input Sets only display in the XML transformation diagram to which they belong. Input Sets do not display on the Section C.2.1, “Model Explorer View” view and you cannot use them as you would a normal class, such as for source classes in other transformations.

To open the Input Set Editor, either double-click the input set in the Mapping Transformation Diagram or click the edit button on the Input Set in the diagram. (see below)



The Input Parameters table contains a list of mapping attributes within the input set and the mapping attributes bound to input set mapping attributes. The tree on the right displays the parent mapping classes and the attributes available from each.

Using the Input Set Editor, you can:

  • Add a mapping attribute from a parent mapping class to the Input Set. In the tree on the right, select the symbol for which you want to create an attribute and click New. The item displays in the Input Parameters and Mapping Class Bindings table.

  • Delete a mapping attribute from the Input Set. Click the row in the Input Parameters and Mapping Class Bindings table that you want to delete and click Delete. The Teiid Designer removes this row from the table and this mapping attribute from your Input Set.

  • Bind and Unbind Input Parameters.

Once you have created the mapping attributes within the Input Set that you need, you can use the Input Set Parameters within a mapping class transformation to produce mapping attributes you can map to your XML document.

Within an XML Document model, a choice compositor defines all possible document data structures (sometimes called fragments) that can appear at that location in an XML instance document. When the Teiid Designer Server populates an XML instance document at runtime based upon your virtual XML document, it will choose the first fragment that matches the criteria you specify within the Choice Editor.

To view the choice editor, right-click on the choice node in the mapping diagram's XML Document tree view and select Edit from the right-click pop up menu.



The table on this panel displays fragment options for the choice, each represented by the top node of the document fragment.

The Summary tab, shown below, displays a SQL-like version of the current choice criteria.


The XML Schema upon which you based the XML Document model determines the nature of the options available to the choice. A schema you share with other, external sources (such as business partners) might include information that you do not want to include within XML files.

For example, Sample Financial Services shares an XML schema with its partners Example Mutual Insurance, Illustrative Brokerage, and FinancialPartners.com. The partners created the schema broadly, to cover all possibilities for information they might need to interchange. As such, the customer information XML document might include a choice compositor based on a list of all products all companies offer.

However, Sample Financial does not offer a credit card; so it could exclude those elements from the XML documents its Teiid Designer Server creates since it will never have credit card information for an XML document.

The table on the Choice Editor contains the Include column. By default, all elements specified by the schema are included. You can click to remove the checkmark beside any element you do not want to include within your XML documents generated by this virtual XML document metadata model. By removing the checkmark, you are not removing the element from the XML Document model; you are merely telling the Teiid Designer Server that it will never use this element as part of the choice.

You cannot edit criteria for excluded elements. However, if you exclude an option for which you have established a criteria, Teiid Designer will retain the criteria if you want to include the option in the future.

Some XML schemas define data structures that contain self-referencing elements or datatypes. When generating XML documents, such data structures can produce an endless repetition of nested tags. This self-nesting pattern is known as recursion.

When generating virtual documents from XML Schema, the Teiid Designer detects recursive data structures in the XML Schema model and halts the recursive nesting pattern after two cycles. These two cycles serve different purposes when mapping the document:

You can recognize a mapping class located at the second, recursive document fragment by the looping arrow button in the top-left-hand corner of the diagram object as shown below.

When you model a virtual document based on an XML Schema model containing recursion, you can choose whether to treat the nested fragments as recursive. You should only use recursion when the data access pattern from your data source(s) is also recursive; in other words, when the same query transformation should be executed over and over to generate and map the nested document's data content.

By default, the Teiid Designer does not mark the recursive fragments in document models to execute recursively in the Teiid Designer Server. To take advantage of this behavior, you must open the Recursion Editor in the recursive mapping class Section C.3.1.1.5, “Mapping Transformation Diagram”, mark the transformation query as recursive, and specify the recursion limit properties.

The Recursion Editor lets you enable and limit recursion. The Recursion Editor button only displays on mapping classes, which have recursive patterns. For example, if you have an element named Employee which contains a element named Supervisor which itself contains an Employee element nested within it, you might need to limit the number of times the elements are nested within the document.

You can set the following conditions to limit the recursion:

To open the Recursion Editor, click on the Recursion Editor button on the displayed mapping class.



Note

The Teiid Designer Server will evaluate this condition each time it recursively performs this query. If this criteria clause evaluates false, the Teiid Designer Server performs the query recursively again unless it has reached the Count Limit. If the criteria evaluates true, the Teiid Designer Server performs the mapping for the current level and ends its recursive loop.

When you have created the criteria, it displays in the Limit Condition box.

When the Teiid Designer Server dynamically populates your XML documents at runtime, it will use the recursion specifications you entered here.

Extending a model adds extra properties to its model objects. One good use of these extension properties is for passing data to a customized Teiid translator. The Designer model extension framework consists of:

This chapter discusses various features targeted at defining and managing metamodel-specific objects.

In Teiid Designer 7.6 a new action, New Child > Base Table..., was added to aid in creating tables, complete with all of it's child entities. This action replaces the original New Child > Base Table, which only created the empty table.


Running the action will display the Create Relational Table wizard. The wizard page contains 5 tabbed panels representing the various properties and components that make up the possible definition of a relational table. Enter your table name, define columns, keys, constraints and other options, then click OK.

This wizard is designed to provide feedback as to the completeness of the relational table information as well as the validation state of the table and it's components. Note that although errors may be displayed during editing, the wizard is designed to allow finishing with the construction of an incomplete table containing errors.

The first tab labeled Properties contains the input for the simple table properties including name, name in source, cardinality, supports update and is system table properties.


The Columns tab allows creation and editing of basic relational columns. This includes adding, deleting or moving columns as well as changing the name, datatype and length properties.


The Primary Key tab allows editing of the name, name in source and column definitions. Note that un-checking the box will clear the data. The Unique Constraint tab contains the identical information.


The Foreign Keys tab allows creating, editing and deleting multiple foreign keys.


To create a new Foreign Key, select the Add button and enter/select the properties, key references in the tables shown below. Note the Select Primary Key or Unique Constraint table will display any PK/UC existing in the selected relational model. If no tables in that model contain a PK or UC, then the table will be empty.


This section contains descriptions of various features related to creating and managing relational view model objects.

For any relational view table you can enable it's materialized view option by setting the Materialized property to TRUE and setting the Materialized Table reference, as shown in the figure below. Note that you are required to have already created your relational tables.


Designer includes a feature to assist in quickly creating materialized relational tables based on your existing view tables.

To create materialized views:

  • Step 1 - Right-click on one or more view tables in the Section C.2.1, “Model Explorer View” and select the Modeling > Create Materialized Views action.
  • Step 2 - In the Create Materialized View Model dialog specify or select a target relational model for your generated relational tables.

  • Step 2a - Selecting the browse '...' button displays the Relational Model Selector dialog where you select an existing relational model or specify a unique name for a new model.

  • Step 3 - Click OK to create relational tables corresponding to your selected view tables and automatically set the Materialized property to TRUE and the Materialized Table reference value to your newly generated table.

When finished your view tables will be configured with their new materialized properties and the corresponding relational tables will be shown in their package diagram.


In Teiid Designer 7.7 a new action, New Child > Base Table..., was added to aid in creating relational view tables, complete with its target columns and SQL transformation. This action replaces the original New Child > Base Table action, which only created the empty table.


Running the action will display the Create Relational View Table wizard. The wizard page contains 3 tabbed panels representing the various properties and components that make up the possible definition of a relational view table. Enter your table name, define the desired columns and specify the SQL transformation, then click OK.

This wizard is designed to provide feedback as to the completeness of the relational view table information, as well as the validation state of the table and it's components. Note that although errors or warnings may be displayed during editing, the wizard is designed to allow finishing even if the table definition is incomplete.

The first tab labeled Properties contains the input for the simple table properties including name and supports update.


The Columns tab allows creation and editing of basic relational columns. This includes adding, deleting or moving columns as well as changing the name, datatype and length properties.


The SQL tab allows editing of the SQL Transformation for the relational view. The desired SQL can be entered directly into the text area or a SQL Template may be selected by clicking the Templates... button.


If the Templates... button is selected on the SQL tab, the Choose a SQL Template dialog will display, as shown below.


A number of common SQL templates may be chosen using the dialog. Depending on usage context, the PROCEDURES tab may also be available in addition to the SELECT queries tab. The SQL Template dialog can also be accessed in the Transformation Editor, from the rt-click context menu.

Teiid Designer allows you to expose your VDBs via a SOAP or REST interface. JBossWS-CXF or RESTEasy wars can be generated based on models within your VDBs. This section describes these wizards in detail.

The Teiid Designer provides web service generation capabilities in the form of a JBossWS-CXF war. Once you have added your Web Service Models as described in Section 3.5, “Creating Web Service View Model” to your VDB, deployed the VDB to a running Teiid instance and created your VDB's data source, you are ready to expose the web service using the generated war.

In Teiid Designer, it is also possible to expose your VDBs over REST using a generated RESTEasy war. Also, if your target virtual model has update, insert and delete SQL defined, you can easily provide CRUD capabilities via REST. Accepted inputs into the generated REST operations are URI path parameters and/or XML/JSON. JSON is exposed over a URI that includes "json". For example, "http://{host}:{port}/{war_context}/{model_name}/resource" will accept URI path parameters and/or XML while "http://{host}:{port}/{war_context}/{model_name}/json/resource" will accept URI path parameters and/or JSON.

Once you have deployed your war file, you are ready to test it out. There are a few ways to accomplish this.

SOAP WAR Testing

REST WAR Testing

Teiid Designer offers three basic model edit actions: Rename, Move and Save As... and one project-related action, Clone Project. These actions are described below.

Because each instance of of a model contains a unique ID and each object in each model contains a unique ID, copying a project is a delicate task. For this reason, the Clone Project action was created to manage the creation of exact structural copies of all models in the source project.

As described briefly in Section 1.3.7, “Testing Your Models”, you can test your models in Teiid Designer by using the Preview Data action or test your models via your deployable VDB. These two options will be described in detail in this chapter as well as managing your required connection profiles.

Teiid Designer utilizes the Eclipse Data Tools Platform (DTP) Connection Profile framework for connection management. Connection Profiles provide a mechanism to connect to JDBC and non-JDBC sources to access metadata for constructing metadata source models. Teiid Designer also provides a custom Teiid connection profile template designed as a JDBC source to a deployed VDB.

By selecting various Teiid Designer Import options, any applicable Connection Profiles you have defined in your Database Development perspective will be available to use as your import source. From these import wizards you can also create new connection profiles or edit existing connection profiles without leaving the wizard.

The Section C.2.3, “Teiid View” provides access to running Teiid instances and shows data source and VDB artifacts deployed there. The "Create Data Source" action available on this view utilizes the available and applicable connection profiles.

Designing and working with data is often much easier when you can see the information you're working with. The Designer's Preview Data feature makes this possible and allows you to instantly preview the information described by any object, whether it's a physical table or a virtual view. In other words, you can test the views with actual data by simply selecting the table, view, procedure or XML document. Previewing information is a fast and easy way to sample the data. Of course, to run more complicated queries like what your application likely uses, simply execute the VDB Via DTP and type in any query or SQL statement.

After creating your models, you can test them by using the Preview Data action . By selecting a desired table object and executing the action, the results of a simple query will be displayed in the Section 8.2.5, “Sample SQL Results for Preview Data” view. This action is accessible throughout the Teiid Designer in various view toolbars and context menus.

There are two requirements for previewing your data:

  1. The selected object must be one of several previewable model object types
  2. All source models within the model dependency tree must reference a connection profile.

Model objects that can be previewed include: relational tables and views (including tables involving access patterns), relational procedures, Web service operations and XML document staging tables.

Note that any virtual table, view or procedure is previewable as long as all "physical" source models reference sufficient connection info. (See Section C.2.3, “Teiid View” view)

After selecting the Preview Data action, Designer will insure that all source models are associated with connection profiles and that all required passwords are set.

If the model selected for preview is a source model and there is insufficient connection info for that model, the following dialog will be displayed and the action terminated.


If any of the source models in the corresponding project require a password that can't be retrieved from an existing connection profile, the user will be queried for each missing password


Testing Your Transformations

When editing transformation SQL in the Transformation Editor, a special SQL Results data action is provided in the editor tool-bar .

You can change your transformation SQL, re-validate and preview your the data for your modified SQL.

The following sections provide steps for previewing your data. Note that all steps assume that all source models referenced by your models, either directly or through dependencies, are bound to connector bindings.

In Teiid Designer you can execute a VDB to test/query actual data.

The requirements for VDB execution are:

Teiid Designer simplifies this process via Deploy VDB and Execute VDB actions. Deploy VDB does just that, deploy a selected VDB to a running Teiid instance. Execute VDB performs the VDB deployment, creates a Teiid Connection Profile, opens the Database Development perspective and creates a connection to your VDB.

To execute a VDB, that's been deployed manually, follow the steps below:

Designer provides multiple search actions located via Teiid Designer sub-menu in Eclipses Search menu. Search menu.


The matrix indicates for a given data source how a model can be created (Desginer Import Option) and how the data source is integrated (Translator) for data access.

NOTE: The DDL Import option is an available option to build a source model for any data source. Its only indicated below when there's no specific importer created for that specific data source type.


The Teiid Designer provides options or preferences which enable customization of various modeling and UI behaviors. Preferences can be accessed via the Edit > Preferences action on the Main toolbar.


General Teiid Designer preferences include.


Teiid Designer utilizes the Eclipse Workbench environment which controls visual layout via perspectives. A Perspective defines the initial set and layout of views and editors. Within the application window, each perspective shares the same set of editors. Each perspective provides a set of functionality aimed at accomplishing a specific set of tasks.

Perspectives also control what appears in certain menus and toolbars. They define visible action sets, which you can change to customize a perspective. You can save a perspective that you build in this manner, making your own custom perspective that you can open again later.

The Teiid Designer perspective provides access to fundamental model editing and management capabilities. This perspective includes 6 main UI components (or groups of components) as shown below. They include:


Views are dockable windows which present data from your models or your modeling session in various forms. Some views support particular Section C.3.1, “Model Editor” and their content is dependent on workspace selection. This section summarizes most of the views used and available in Teiid Designer. The full list is presented in the main menu's Window > Show View > Other... dialog under the Teiid Designer category.


Teiid Designer allows you manage multiple projects containing multiple models and any corresponding or dependent resources. The Model Explorer provides a simple file-structured view of these resources.

The Model Explorer (shown below) is comprised of a toolbar and a tree view.


The toolbar consists of nine common actions:

  • Preview Data - Executes a simple preview query (SELECT * FROM ).

  • Sort Model Contents - Sorts the contents of the models based on object type and alphabetizing.

  • Refresh Markers - Refreshes error and warning markers for objects in tree.

  • Back - Displays the last "Go Into" location. (See Eclipse Help)

  • Forward - Displays the next "Go Into" location. (See Eclipse Help)

  • Up - Navigates up one folder/container location. (See Eclipse Help)

  • Collapse All - Collapses all projects.

  • Link with Editor - When object is selected in an open editor, this option auto-selects and reveals object in Model Explorer.

  • Additional Actions

The additional actions are shown in the following figure:


If Show Model Imports is checked, the imports will be displayed directly under a model resource as shown below.


The Teiid View provides a means to display and manage Teiid server instances and their contents within Designer.

To show the Teiid View click "Window > Show View > Other..." to display the Show View dialog. Choose "Teiid Designer > Teiid" view and hit OK.


To create a new Teiid instance, either right-click select New Teiid Instance action or click the same action button, , in the toolbar.

You'll get the New Teiid Instance dialog shown below.


The dialog contains two three sections. The top panel contains host name and an option to customize the new Teiid instance label in the teiid view. The second panel Teiid Admin Connection Info, pertains to the connection information required to connect to the admin URL of your installed Teiid instance. The operations Designer exposes deal with deploying and undeploying VDBs as well as managing your test data sources required by those VDBs to successfully connect and query data through the Teiid runtime engine.

The third panel, Teiid JDBC Connection Info, provides for entering the connection information for that same Teiid instance. This information is required for Designer to make JDBC connections during execution of the Preview Data feature.

Enter valid Host, Port, User name and Password information, edit any options and click Finish.

Actions available in this view include:

  • New Teiid Instance - Create a new instance of a running Teiid server

  • Teiid Server Properties - View and edit properties of an existing Teiid instance

  • Reconnect - Reconnect and refresh contents of the selected Teiid instance

  • Delete - Disconnect and delete the selected Teiid instance

  • Execute VDB - Creates a JDBC Teiid connection profile and opens the Data Tools Database Development perspective

  • Undeploy VDB - Removes the selected VDB from the Teiid instance

  • Create Data Source - Launches the New Data Source wizard

  • Delete Data Source - Removes the selected Data Source from the Teiid instance

If you chose the Use a custom label option, the text entry field will enable as shown below.


The Problems View displays validation errors, warnings, or information associated with a resource contained in open projects within your workspace.


By default, the Problems View is included in the Teiid Designer perspective. If the Problems View is not showing in the current perspective click Window > Show View > Other > Teiid Designer > Problems.

There are 5 columns in the view's table which include:

  1. Description - A description of the problem preceded by a severity icon (i.e., error , warning , or info ).

  2. Resource - The name of the resource.

  3. Path - The project name.

  4. Location - The object within the resource that has a validation error.

  5. type - Type of validation item.

To open Teiid Designer's MED Registry view, select the main menu's Window > Show View > Other... and select the Teiid Designer > Model Extension Registry view in the dialog.

The Model Extension Registry view shows the currently registered MEDs. Registered MEDs can be applied to models in the workspace (see Section 5.3, “Managing Model Object Extensions”). The Model Extension Registry view looks like this:


You can also open the view by selecting the MED Editor toolbar action in the right corner of shared sub-editor header section.

For each registered MED, the namespace prefix, namespace URI, extended model class, version, and description is shown. In addition, a flag indicating if the MED is built-in is shown. The Model Extension Registry view has toolbar actions that register a workspace MED file, unregister a user-defined MED, and copy a registered MED to the workspace. All these actions are also available via a context menu.

A MED registry keeps track of all the MEDs that are registered in a workspace. Only registered MEDs can be used to extend a model. There are 2 different types of MEDs stored in the registry:

  • Built-In MED - these are registered during Designer installation. These MEDs cannot be updated or unregistered by the user.

  • User-Defined MED - these are created by the user. These MEDs can be updated, registered, and unregistered by the user.

Note: When a workspace MED is registered it can be deleted from the workspace if desired. The registry keeps its own copy. And a registered MED can always be copied back to the workspace by using the appropriate toolbar or context menu action.

Editors are the UI components designed to assist editing your models and to maintain the state for a given model or resource in your workspace. When editing a model, the model will be opened in a Model Editor. Editing a property value, for instance, will require an open editor prior to actually changing the property.

Any number of editors can be open at once, but only one can be active at a time. The main menu bar and toolbar for Teiid Designer may contain operations that are applicable to the active editor (and removed when editor becomes inactive).

Tabs in the editor area indicate the names of models that are currently open for editing. An asterisk (*) indicates that an editor has unsaved changes.


By default, editors are stacked in the editors area, but you can choose to tile them vertically, and or horizontally in order to view multiple models simultaneously.


The Teiid Designer provides main editor views for XMI models and VDBs.

The Model Editor contains sub-editors which provide different views of the data or parts of data within an XMI model. These sub-editors, specific to model types are listed below.

  • Diagram Editor - All models except XML Schema models.

  • Table Editor - All models.

  • Simple Datatypes Editor - XML Schema models only.

  • Semantics Editor - XML Schema models only.

  • Source Editor - XML Schema models only.

The VDB Editor is a single page editor containing panels for editing description, model contents and data roles.

In addition to general Editors for models, there are detailed editors designed for editing specific model object types. These "object" editors include:

  • Transformation Editor - Manages Transformation SQL for Relational View Base Tables, Procedures and XML Web Service Operations.

  • Choice Editor - Manages properties and criteria for XML choice elements in XML Document View models.

  • Input Editor - Manages Input Set parameters used between Mapping Classes in XML Document View models.

  • Recursion Editor - Manages recursion properties for recursive XML Elements in XML Document View models.

  • Operation Editor - Manages SQL and Input Variables for Web Service Operations.

The Model Editor is comprised of sub-editors which provide multiple views of your data. The Diagram Editor provides a graphical while the Table Editor provides spreadsheet-like editing capabilities. This section describes these various sub-editors.

The Diagram Editor provides a graphical view of the a set of model components and their relationships.

Several types of diagrams are available depending on model type. They include:

You can customize various diagram visual properties via Diagram Preferences.

Each diagram provides actions via the Main toolbar, diagram toolbar and selection-based context menus. These actions will be discussed below in detail for each diagram type.

When a Diagram Editor is in focus, a set of common diagram actions is added to the application's main toolbar.


The Table Editor provides a table-based object type structured view of the contents of a model. The figure below shows a relational model viewed in the Table Editor. Common object types are displayed in individual folders/tables. All base tables, for instance, are shown in one table independent of their parentage.


You can customize Table Editor properties via Table Editor Preferences.

These are the primary features of the Table Editor:

  • Edit existing properties.

  • Add, remove or edit objects, via the main Edit menu and context menu ( Cut, Copy, Paste, Clone, Delete, Rename, Insert Rows ).

  • Paste information from your clipboard into the table.

  • Print your tables.

When a Table Editor is in focus, the Insert Table Rows action is added to the application's main toolbar.

A few Table Editor actions are contributed to the right-click menu for selected table rows. These actions, described and shown below include:

  • Table Paste - Paste common spreadsheet data (like Microsoft Excel) to set object properties.

  • Table Editor Preferences - Change table editor preferences, including customizing visible properties.

  • Insert Rows - Create multiple new sibling objects.

  • Refresh Table - Refreshes the contents of the current Table Editor to insure it is in sync with the model.


A VDB, or virtual database is a container for components used to integrate data from multiple data sources, so that they can be accessed in a federated manner through a single, uniform API. A VDB contains models, which define the structural characteristics of data sources, views, and Web services. The VDB Editor, provides the means to manage the contents of the VDB as well as its deployable (validation) state.

The VDB Editor, shown below, contains a upper and lower panels. The upper panel contains the Models tab and an Other Files tab. The lower panel contains tabs for managing Data Roles, the VDB Description and Translator Overrides.


You can manage your VDB contents by using the Add or Remove models via the buttons at the right.

Set individual model visibility via the Visibility checkbox for each model. This provides low level data access security by removing specific models and their metadata contents from schema exposed in GUI tools.

In order for a VDB to be fully queryable the "Source Name", "Translator" and "JNDI Names" must have valid values and represent deployed artifacts on your Teiid server.

If you have Designer runtime plugins installed, and have a Teiid server running, you can select a source model in the VDB Editor and right-click select "Change Translator" or "Change JNDI Data Source" which will allow you to select any applicable artifacts on your server.


If you have a default Teiid server instance defined and connected the translator and JNDI table cells will contain drop-down lists of available translator and JNDI names available on that server.

Teiid Designer provides a means to create, edit and manage translator override properties specific to a VDB via the Tranlator Overrides tab. A translator override is a set of non-default properties targeted for a specific source model's data source. So each translator override requires a target translator name like "oracle", db2, mysql, etc. and a set of non-default key-value property sets.

The VDB Editor contains a Tranlator Overrides section consisting of a List of current tranlator overrides on the left, a properties editor panel on the right and Add (+) and Remove (-) action buttons on the lower part of the panel.


To override a specific translator type, select the add translator action (+). If a default Teiid server instance is connected and available the Add Translator Override dialog (below) is presented, the user selects an existing tranlator type and clicks OK. Note that the override is only applicable to sources within the VDB, so be sure and select a translator type that corresponds to one of the VDB's source models. The properties panel on the right side of the panel will contain editiable cells for each property type based on the data-type of the property. (i.e. boolean, integer, string, etc.).


If no default Teiid server instance is available, the "Add New Translator Override" dialog is presented. Enter a unique name for the tranlator override (i.e. "oracle_override"), a valid translator type name (i.e. "oracle") and click OK. The properties panel on the right side of the panel will allow adding, editing and removing key-value string-based property sets. When editing these properties all values will be treated as type string.


The MED Editor is a multi-tabbed editor and is used to create and edit user-defined MEDs (*.mxd files) in the workspace. The MED Editor has 3 sub-editors (Overview, Properties, and Source) which share a common header section. Here are the MED sub-editor tabs:

The GUI components on the Overview and Properties sub-editors will be decorated with an error icon when the data in that GUI component has a validation error. Hovering over an error decoration displays a tooltip with the specific error message. Those error message relate to the error messages shown in the common header section. Here is an example of the error decoration:


The MED sub-editors share a header section. The header is composed of the following:

Below is an example of the shared header section which includes an error message tooltip.


There are 8 categories of actions on Teiid Designer's main menu bar.


The File menu provides actions to manage your workspace resources.


The New > sub-menu provides specific actions to create various generic workspace resources as well as Teiid Designer models and VDBs.


  • The File menu contains the following actions:

    • New > Model Project - Create user a new model project.

    • New > Folder - Create new folder within an existing project or folder.

    • New > Model - Create a new model of a specified model type and class using the Chapter 3, New Model Wizards.

    • New > Virtual Database Definition - Create a new VDB, or Virtual Database Definition.

    • Open File - Enables you to open a file for editing - including files that do not reside in the Workspace.

    • Close (Ctrl+W) - Closes the active editor. You are prompted to save changes before the file closes.

    • Close All (Shift+Ctrl+W) - Closes all open editors. You are prompted to save changes before the files close.

    • Save (Ctrl+S) - Saves the contents of the active editor.

    • Save As - Enables you to save the contents of the active editor under another file name or location.

    • Save All (Shift+Ctrl+S) - Saves the contents of all open editors.

    • Move… - Launches a Refactor > Move resource dialog..

    • Rename… (F2) - Launches a Refactor > Rename resource dialog if resource selected, else in-line rename is preformed.

    • Refresh - Refreshes the resource with the contents in the file system.

    • Convert Line Delimiters To - Alters the line delimiters for the selected files. Changes are immediate and persist until you change the delimiter again - you do not need to save the file.

    • Print (Ctrl+P) - Prints the contents of the active editor. In the Teiid Designer, this action prints the diagram in the selected editor. Allows control over orientation (portrait or landscape), scaling, margins and page order. User can also specify a subset of the pages to print (i.e., 2 through 8).

    • Switch Workspace - Opens the Workspace Launcher, from which you can switch to a different workspace. This restarts the Workbench.

    • Restart - Exits and restarts the Workbench.

    • Import - Launches the Import Wizard which provides several ways to construct or import models..

    • Export - Launches the Export Wizard which provides options for exporting model data.

    • Properties (Alt+Enter) - Opens the Properties dialog for the currently selected resource. These will include path to the resource on the file system, date of last modification and its writable or executable state.

    • Most Recent Files List - Contains a list of the most recently accessed files in the Workbench. You can open any of these files from the File menu by simply selecting the file name.

    • Exit - Closes and exits the Workbench.

The Edit menu provides actions to manage the content, structure and properties of your model and project resources. The figure below represents the Edit menu presented when a metadata model is selected.


  • The Edit menu contains the following actions:

    • New > Child - This menu is created dynamically to support the creation of whatever types of child objects can be created under the selected object.

    • New > Sibling - This menu is created dynamically to support the creation of whatever types of sibling objects can be created under the same parent as the selected object

    • New > Association - This menu is created dynamically to support the creation of whatever types of associations can be created with the selected object.

    • Modeling > - This menu is created dynamically. Various modeling operations are presented based on selected model object type.

    • Undo - Reverses the effect of the most recent command.

    • Redo - Reapplies the most recently undone command.

    • Cut - Deletes the selected object(s) and copies it to the clipboard.

    • Copy - Copies the selected object(s) to the clipboard.

    • Paste - Pastes the contents of the clipboard to the selected context.

    • Paste Special... - Provides additional paste capabilities for complex clipboard objects.

    • Clone - Duplicates the selected object in the same location with the same name. User is able to rename the new object right in the tree.

    • Delete - Deletes the selected object(s).

    • Select All - Select All objects in current view.

    • Rename - Allows a user to rename an object in the tree.

    • Find/Replace - Launches dialog that can be used to search in the current text view, such as a Transformation Editor.

    • Open - Opens the selected object in the appropriate editor.

    • Edit - Opens the selected object in the appropriate specialized editor, such as the Choice Editor or Recursion Editor..

    • Add Bookmark... - This command adds a bookmark in the active file on the line where the cursor is currently displayed.

    • Add Task... - This command adds a task in the active file on the line where the cursor is currently displayed.

The Metadata menu provides Teiid Designer-specific actions.