Home

Teiid Designer User Guide 1

1. 218 6 3 XML Document Ex SUE ERE USER URS 222 6 3 1 Create XML View Documents from schema 222 6 4 Web Services Modeling 226 6 4 1 Create Web Service Action en 226 6 4 2 Web Services War Generation 228 Editing Models and Projects 241 fo Abs etn Fs IOUS PRU 241 px nique E m 242 7 3 Save Copy of Model 244 7A CONG TT Tn 246 8 Managing 7066 bares ardua Dead aga a ina dude pa 249 XC IU Em IB M 249 82 Eding a VDB C 250 Toa VDE a 250 9 4 Multi sou rce Binding SUPPOM a eeetes 250 Ba SUDO oen E E E PU OmU 251 6 6 Re sing VDBS EEn EE EEE EE EEEE 252 9 Testing Your Models 259 9 1 Manage Connection Profiles 259 9 1 1 Set Connection Profile for Source Model 259 9 1 2 View Connection Profile for S
2. 163 5 2 4 Create View Model Objects Wizards 164 5 3 Moder Object ze i 165 Rc MEC Ie dea celis den Tr 167 0 9 2 Input Set EdIt r XML iaces cerita Conde xa pano 185 5 3 9 Choice Editor 187 5 34 Recursion Editor AMLE RR 190 Deon DCT ANON ENOT M o DTE 194 5 4 Managing Model Object Extensions 195 5 4 1 Create New MED qu Ede uto uro 196 sro 197 5 4 3 Extending Models With MEDS 201 5 4 4 Setting Extended Property Values 202 6 Metadata specific Modeling ccccccccc ccc cec ees eeeeeeeeeeeeeeeceeeeeaeseeaeeeeeseaeeeeeseseeaes 205 6 1 Relational Source Modeling 205 FUNCION 205 6 1 2 Create Relational Table Wizard 208 6 2 Relational View Modeling mmm 214 6 2 1 Create Materialized Views ccc cccccccccecccecccucecucecucecuuecuuecuuecuuecunecauecans 214 6 2 2 Create Relational View Table Wizard
3. PF an 19 2 1 2 Defining a Teiid Server 20 Server Version Status Panel CURRERE 21 ANN MERE 22 2 2 1 Model a JDBC Source 22 2 9 Oheat Sheer EXamlple cei a ee e ose resa mide tai uM 37 2 3 1 Consume SOAP Web Service 37 3 New Model Wizards ccc cccccc ccc ec cece ee eee eea eee eceeeeee ese emen nme nennen nennen nene nenas 51 3 1 Creating New Relational Source Model 52 3 1 1 Generate File Translator Procedures 53 3 1 2 Generate Web Service Translator Procedures 54 21 3 Gopy From Existing Model si daten pr bates eue sea bx 56 3 2 Creating New Relational View Model 57 3 2 1 CODY From Existing pde e andes 58 3 2 2 Transform From Existing Model 59 3 3 Creating XML Document View Model ccccceccecceeeeeeeeeeeeeeeeeeseeeeeeeaeeaeeseseeaes 59 3 3 1 Copy From Existing Model cccccccceceeceeceeeeseeceeeecece
4. VDB Translator Overrides DataRole Description Figure D 57 VDB Editor 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 366 VDB Editor Multi ource Source Name Translator Name JNDI Name SOLSerVer Customer Accounts Figure D 58 Change Translator or Data Source Actions 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 D 3 2 1 Editing Data Roles Teiid Designer provides a means to create edit
5. Figure 6 1 New Source Function Action zm amp Base Table gt Index gt E PARTS Modeling gt EL SHIP VIA TI gt E STATUS Open With gt E SUPPLIER gt B SUPPLIER PARTS Copy gt getPartinfo Paste gt 1 Data Source Paste Specia Fe Source Function v Custom Diagram The resulting source function will be added to your model and will be represented by the 205 Chapter 6 Metadata specific Create Source Function Create New Source Function Press OK to finish getEmployeelnto Input Parameters Include Name employeelD integer string string string Return Parameter employeelnfa clob Properties Descript Cancel Figure 6 2 Create New Source Function Dialog When finished the new source function will be displayed in your model s package diagram 206 Source Function Figure 6 3 New Source Function In Package Diagram After saving your model your new source function will be available for use in your transformations If you open the Expression builder your source functions will be selectable in the Function drop down selector for a Category named for the model as shown in the example below 207 Chapter 6 Metadata specific Expression Builder Initial Expression GETPRODUCTINFO ProductsOracle PRODUCTDATA INSTR_ID lige SOL View GETPRODUCTINFOlProductsOracle PRODUCTDATA I
6. Name without file extension 23962930517375020849 lt Back Cancel s Figure 4 63 Namespace Resolution Dialog 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 144 XSD Schema File TEE DEA Create Web Service from WSDL File Validation Problems or more selected WSDL files have errors Please correct or remove the filelsito proceed WSDL Validation Panel message wsdl file X 1 Cannot find the declaration of element wsdl definitions http terraservice net terraservice2 asm 238 Figure 4 64 WSDL Validation Problems Dialog 4 10 XSD Schema File You can import XML Schema file XSD files using the steps below e Step 1 n Model Explorer choose the File gt Import action EI in the toolbar or select a project folder or model in the tree and choose Import e Step 2 Select the import option Metadata Modeling gt XSD Schemas and click Next gt Step 3 Select either Import XSD Schemas from file system or Import XSD Schemas via URL and click Next gt e Step 4a If importing from file system the Import XSD Files d
7. DDL file contents CREATE TABLE ProductData ProductiD ProductName ProductType ISSUER EXCHANGE DJiComponent SsPSOO0Companent MASIO00Component AMEXINTComponent PrimaryBusiness VARCHAR 10 NOT NULL VARCHARI6O VARCHAR L5 VARCHAR L0 VARCHAR LQ NUMERIC 1 NOT NULL NUMERIC 1 NOT NULL NUMERIC 1 NOT NULL NUMERIC 1 NOT NULL VARCHAR 30 GO generated from ProductSymbols lt Back Figure 4 2 DDL Import Options Canc el Step 4 If NEXT gt is pressed a difference report is presented for viewing or de selecting individual relational entities Press Finish to complete 13 Chapter 4 Importers Import DBL Review Model Updates Review the changes that will be applied to your model to bring it up to date with the source metadata Uncheck any changes you do not wish to apply Only In Old Model 0 0 fo Changes 4 Only In New Model nu o Sod file home blafond Runtime Workspaces test_7 1 0 E DDLTest ProductsSQLS amp ProductData 5j se Fish Figure 4 3 Review DDL Updates Dialog 4 2 Import From JDBC Database You can create relational source models from your JDBC source schema data using the steps below 74 Import From JDBC Database Step 1 In Model Explorer choose the File gt Import action py in the toolbar or select a project folder or model in the tree and choose Import Step 2 Select the import option Metadata Modeling
8. Background Color Settings Dependency Diagram Custom Diagram Mapping Diagram I Relationship Diagram E Transformation Diagram 7 Custom Relationship Diagram m Package Diagram Model Size Warn If Model Size is Larger Than 20000 Relationship Options Show Role Names Show Multiplicity Restore Defaults Apply Figure C 3 Diagram Preferences Panel C 1 2 Diagram Printing Preferences Diagram print options are stored as preferences These can be accessed through this preference page by right click on diagram Page Setup action or via the Modify Diagram Printing Preferences action located on the vertical diagram toolbar 302 Editor Preferences Diagram Printing cow Diagram Print Orientation Portrait Landscape Scaling Rt tao One page Rt ta One page high as many as necessary wide 3 Rt to One page wide as many as necessary high e Adjust to normal size M 0 0 Left oo Page Order Over then down 5 Down then over Restore Defaults Apply Figure C 4 Diagram Preferences Panel C 1 3 Editor Preferences C 1 3 1 XML Document Preferences XML Document Mapping Preferences provide ways to customize Section 0 3 1 1 4 Mapping Diagram and Section 5 3 4 Hecursion Editor XML behavior 303 Appendix C User Preferences Editor ame P XML Mapping Diagram Settings Auto expand tree w
9. Modeling Primary Key E Undo Delete multiple objects Redo Unique Constraint Figure 5 5 New Child Action In Table Editor Step 3 The selected tab in the Table Editor changes to the tab for the child object type the new model object row is displayed and the row s name table cell is highlighted for renaming 5 1 2 New Sibling Action To create new sibling model objects in the Section D 2 1 Model Explorer View Step 1 Select the object to which you want to add a sibling For example you can add a column sibling to a column Step2 Right click on that object From the pop up menu select New Sibling You can now select the sibling object you would like to add m v PARTS s A PART a New Sibling Access Pattern E PART Mew Association 2 Modeling F Foreign Key P PK PA 7 E SHIP VIA a Undo Delete multiple objects Pj Primary Key EY SHIPR pedo FY New Child Unique Constraint Figure 5 6 New Sibling Action In Model Explorer Step 3 The new model object displays on the Section D 2 1 Model Explorer View and is highlighted for renaming To create new sibling model objects in the Section D 3 1 1 Diagram Editor 154 New Sibling Action e Step 1 Select the object to which you want to add a sibling For example you can add a column sibling to a column Step2 Right click on th
10. Figure 5 30 Function Panel Selected From the Category drop down list choose the type of function you want to add By default the Teiid Designer System offers the following categories Conversion for functions that convert one datatype into another Datetime for functions that handle date or time information Miscellaneous for other functions Numeric for mathematic and other numeric functions e String for string manipulation functions 182 Transformation Editor e Step 5 From the Function drop down list select the function you want The table beneath the drop down lists displays the number of arguments required for this function e Step 6 Click Apply Step 7 Your function displays in the tree at the top Sub nodes display for each argument you need to set for this function 183 Chapter 5 Creating and Editi Expression Builder Initial Expression lt undefined gt SOL View wv CONCAT Books SourceB AUTHORS FIRSTNAME lt undefined gt amp Books SourceB AUTHORS FIRSTNAME SEPT En Expression Editors Attribute Constant Function Choose Attribute below to replace selected undefined v Books SourceB AUTHORS FY Books SourceB AUTHORS AUTHOR ID A Books SourceB AUTHORS FIRSTNAME FA Books SourceB AUTHORS LASTNAME FY Books SourceB AUTHORS MIDDLEINIT lt v InputSet INPUT isbn 7 Books Sourced BOOK AUTHORS El Books SourceA BOOK AUTHORS ISBN FY Books So
11. XMI Configirarian a _ M gt Filesets PUES e gt Server Cetals ae Ei Server Cenfiguracion ecitable true l Slocalicst last February 19 2013 1 54 26 link i i Data Sources rike T ids E Translatcrs lacatiar ae sc VEBS nane amp g e3erviceview xml gt pa AJS size 22 759 Dez ng Imererfphanrarm esaracrammingavard praects sOAPEWanmple Lis Stabes staresevice vig xmi Figure 1 1 Teiid Designer Why Use Teiid Designer Chapter 1 Introduction Teiid Designer is a visual tool that enables rapid model driven definition integration and testing of data services without programming With Teiid Designer not only do you map from data sources to target formats using a visual tool but you can also resolve semantic differences create virtual data structures at a physical or logical level use declarative interfaces to integrate aggregate and transform the data on its way from source to a target format which is compatible and optimized for consumption by your applications This allows you to abstract the structure of the information you expose to and use in your applications from the underlying physical data structures With Teiid Designer data services are defined quickly the resulting artifacts are easy to maintain and reuse and all the valuable work and related metadata are saved for later reference You can use Teiid Design
12. Convert to Enterprise Datatypes Show Model Statistics Build Model Imports Figure D 34 SQL Reservered Words Action D 2 12 Model Extension Definition Registry View MED Registry View To open Teiid Designer s MED Registry view select the main menu s Window gt Show View gt Other and select the Teiid Designer gt 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 4 Managing Model Object Extensions The Model Extension Registry view looks like this 339 Appendix D Teiid Designer Ui Model Extension Registry 2 Problems Error Log Built In Namespace Prefix Namespace URI Model v ext custom org teiid designer extension deprecated Relati mymodelextension mymodelextension Relati xv rest org teiid designer extension rest Relati v salesforce org teiid designer extension salesforce Relati v sourcefunction org teiid designer extension sourcefunction Relati Figure D 35 System Catalog View 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
13. Figure 4 30 Target Model Selection Dialog e Step 5a If you are updating an existing relational model the next page will be Review Model Updates page Any differences Press Finish to create your models and tables 108 Import From Salesforce Create Relational Model from SalesForce Data Model Review Model Updates Review the changes that will be applied to your model to bring it up to date with the source metadata Uncheck any changes you do not wish to apply Only In Old Model on Changes Only In New Model Mew Wood kd file TestSF 1314365845645 temp xmi cur Salesforce Selection Details salesforce this selection is an Addition lt Back Next gt Cancel Anish Figure 4 31 Review Model Updates Dialog When finished the new or changed relational model s package diagram will be displayed showing your new tables 109 Chapter 4 Importers Ca AccountsSFModel xmi 3 Id string 18 F FK Account Accountid P Id PK Id string 18 IsDeleted boolean j MasterRecordld string 18 Name string 255 Type string 40 Parentld string 18 BillingStreet string 255 BillingCity string 40 EFE gH o LL 7 Package Diagram EF Table Editor Figure 4 32 New Saleforce Tables Diagram 4 7 Import Metadata From Text File he Teiid Designer provides various import options for parsing comma delimite
14. Figure D 1 Teiid Designer Perspective Layout 312 Opening a Perspective D 1 2 Opening a Perspective There are two ways to open a perspective Using the Open Perspective button E on the shortcut bar Choosing a perspective from the Window Open Perspective menu o open a perspective by using the shortcut bar button e Step 1 Click on the Open Perspective button E e Step 2 A menu appears showing the same choices as shown on the Window gt Open Perspective menu Choose Other from the menu ke Debug lava Browsing Figure D 2 Perspectives Menu e Step3 In the Select Perspective dialog choose Teiid Designer and click OK 313 Appendix D Teiid Designer Ui Open Perspective 2 ava Browsing 29 Java EE t Java Type Hierarchy ar JavaScript 1 JBoss AS 22 jBPM jPDL 3 gf JMX He JPA Plug in Development Remote System Explorer Resource Seam default 50 Team Synchronizing 71 Teiid Designer web ge Web Dewelopment X XML Cancel Figure D 3 Select Perspective Dialog The Teiid Designer perspective is now displayed There are few additional features of perspectives to take note of he title of the window will indicate which perspective is in use Designer PartsProject ParntsSourceA xmi Boss Developer Studio Hle Edit Refactor Navigate Search Project Metadata Run Window Help Figure D 4 Workbench Window Title
15. For details select a specific editor listed below e Section 5 3 1 Transformation Editor Section 5 3 2 Input Set Editor XML Section 5 3 3 Choice Editor XML Section 5 3 4 Recursion Editor XML Section 5 3 5 Operation Editor D 3 2 VDB Editor 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 365 Appendix D Teiid Designer Ui YahooVDB vdb 3 UDF Jars Other Files i im Customer Accounts xmi Ca MarketData xmi En MyPortfolio xmi Source Name Translator Name JNDI Name Gia Products xmi Customer Accounts sqlserver Customer Ai x YahooUDF xmi
16. JDBC Database Source Model and click Next Step 3 Select existing or previous connection profile from the drop down selector or press New button to launch the New Connection Profile dialog See Eclispe Data Tools documentation or Edit to modify change an existing connection profile prior to selection i Note the Connection Profile selection list will be populated with only JDBC Database connections Import Database via JDBC Select a JDBC source configuration Select the JDBC source configuration for the database to be imported into a relational model Connection Profile DBC Metadata Processor Properties Driver URL User Name Password Cancel Figure 4 4 Select JDBC Source Configuration Dialog 19 Chapter 4 Importers Because JDBC databases are different special processing of your metadata to be required in order to convert datatypes or to interpret your metadata The JDBC Metadata Processor drop down selector will be auto selected based on your selected connection profile Special processors are available for DB2 Modeshape ODBC Oracle PostgeSQL SQL Server and Sybase For all other DB s ajdefault JDBC processor is available Step 4 After selecting a Connection Profile input password if not provided Press Next gt or Finish if enabled Import Database via JDBC Select a JDBC source configuration Press the Next gt button to continue or the Hnish button
17. New Window New Editor Open Perspective gt Show View gt Customize Perspective Save Perspective As Reset Perspective Close Perspective Close All Perspectives Navigation Preferences Figure D 79 Window Menu The Preferences action launches the Preferences dialog which can be used to set preferences and default values for many features of Teiid Designer If you wish to customize a perspective to include one or more Teiid Designer views select the Show View Other action and expand the Teiid Designer category to show the available views 384 Help Menu Show View Team Designer fa Datatypes Description w Metadata Favorites tat Model Explorer ip t Relationship Navigator rd System Catalag lt Tags Wh Teiid Model Classes Cancel Figure D 80 Show View Dialog D 4 10 Help Menu The Help Menu shown below contains no Teiid Designer specific actions See Eclipse Workbench documentation for details Welcome Help Contents Search Dynamic Help Key Assist Shift Ctrl L Tips and Tricks Cheat Sheets Project Examples Report Problem Red Hat Web Site Check for Updates Install New Software About JBoss Developer Studio Figure D 81 Help Menu The individual actions are described below 385 Appendix D Teiid Designer Ui 386 Welcome Shifts to the Welcome perspective which contains links to documen
18. To open Teiid Designer s System Catalog view select the main menu s Window gt Show View gt Other and select the Teiid Designer gt System Catalog view in the dialog 337 Appendix D Teiid Designer Ui gt VirtualDatabases gt E Schemas gt E Tables gt Datatypes gt E Columns gt Keys gt E KeyColumns gt EB Procedures gt E ProcedureParams gt Properties gt getCharacterVDBResource A getBinaryvVDBResource gt getvDBResourcePaths gt E ReferenceKeyColumns getXMLSchemas document string b E schemas Figure D 32 System Catalog View D 2 11 SQL Reserved Words View To open Teiid Designer s SQL Reserved Words view select the main menu s Window Show View Other and select the Teiid Designer SQL Reserved Words view in the dialog 338 Model Extension Definition Registry View MED Registry View SQL Reserved Words 23 N Problems Alter Text any character any String Se Matching Reserved Words SEARCH SECOND SELECT SENSITIVE SESSION USER SET Figure D 33 System Catalog View You can also display the view by selecting the the main menu s Metadata gt Show SQL Reserved Words action as shown below Designer roject Run Window Help Update Model from Source Show System Catalog T Show Datatype Hierarchy Show SQL Reserved Words
19. Working sets O Add project to working sets Working sets v Select Figure 2 5 New Project Wizard Enter a project name e g MyProject for the name Then click Next The next page of the wizard is shown below 24 Model a JDBC Source New Model Project Model Project Options Click Finish to create folders for your new project Create Folder um m O functions 0000000000 0 jJ 2 lt Back Next gt Cancel Finish Figure 2 6 New Project Folders 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 25 Chapter 2 Dive Right In 127 Model Explorer 33 gt E Outline mw x 15 6 E Y amp MyProject sources views Figure 2 7 Model Explorer 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 26 Model a JDBC Source New Connection Profile Connection Profile Create a MySQL connection profile Connection Profile Types type filter
20. lt EDITION gt 2 lt EDITION gt lt TYPE gt Hardback lt TYPE gt lt AUTHOR_ID gt 49 lt AUTHOR_ID gt lt input gt Figure 6 26 Sample XML Input When using a JSON document your values should match the order of your procedure input parameters as well Here is the input for the above insert procedure 1 ISBN 1 55615 484 4 TITLE Code Complete SUBTITLE A Practical Handbook of Software Construction PUBLISHER 5 PUBLISH YEAR 1993 EDITION 1 TYPE Hardback AUTHOR ID 31 Figure 6 27 Sample JSON Input Step 2 Now we need to identify our procedure as REST eligible To do this we add enable REST properties for the procedure s via the Modeling gt Enable context menu option 234 Web Services War Generation 1 Ea I New Child New Sibling New Association Modeling c Undo Disable REST for Procedures 7 gt Redo Y Create Web Service 2 Enable REST for Pre Preview Data for Ge This will enable two new properties in the property tab for all procedures defined in the model The two required properties are defined in the table below Table 6 2 Required Extended Properties for RESTful Procedures Property Name Description Rest Method The HTTP method that will determine the REST mapping of this procedure Supported methods are GET PUT POST and DELETE URI The resource path to the procedure For example if use books
21. 240 Chapter 7 Editing Models and Projects 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 7 1 Rename A Model e To rename a model in your workspace e Step 1 Select a model in the Section 0 2 1 Model Explorer View e Step 2 Right click select the Refactor gt Rename action b 5 BooksProject b PartsProject gt 25 wsDLImportProje M m AllCustamerAccounts EF MERE New New Child Modeling Open Open With Copy TE Paste ne 5 gu een a Paste Specia Delete Refactor py Import zy Expert Ctri V Rename Namespace URI Move Figure 7 1 Refactor Rename Action In Model Explorer Step 3 Specify unique model name in the Rename Model File dialog Click OK 241 Chapter 7 Editing Models and Rename Model File Figure 7 2 Rename Model File Dialog 7 2 Move Model To move a model in your workspace e Step 1 Select a model in the Section 0 2 1 Model Explorer View e Step 2 Right click select the Refactor gt Move action 242 m AllCustomerAccounts 2 Sources ar Northwind xmi New P 05 BooksProject New Child b PartsProject Modeling WSDLImportProject Open Open With Copy Paste Paste Special M Delete Refactar EL Impart ny Export 21 Refresh Ctrl V Ctrl V
22. Baoks xmi BooksProject bookCallectian Pro irnings 1 item The type referenced by column book edition must be marked as an enterprise datatype Books xmi JBooksProject bookColle ction E Pro Figure D 26 Problems View By default the Problems View is included in Teiid Designer perspective If the Problems View is not showing in the current perspective click Window gt Show View gt Other gt Teiid Designer gt 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 a warning or info 2 Resource The 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 D 2 6 1 Toolbar Menu Click the upside down triangle icon to open the View Menu icon to see various options including sorting filtering displayed columns and much more 332 Search Results View D 2 6 2 Context Menu Additional actions are available by selecting a problem and right click to open a context menu Problems 3 l error 1 warning 0 others Description Errors 1 item 3 SQL statement is amp Warnings 1 item amp The type reference Copy Ctrl c J amp Delete Delete Select All Ctrl A Show In Shift Alt w gt Properties Alt Enter Figure D 27 Problems View Context Menu Go To
23. Chapter 4 Importers e Step 6 On the View Model Definition page select the target folder location where your new view model will be created You can also select an existing model for your new view tables Press Finish to generate your models and finish the wizard m Import From Flat File Source View Model Definition Press the Anish button to finish Selected Data Ale PlayerD ata tet View Model Definition ___________ Location Testtmilmport Name MyEmployeeViews xmi odel Status EXISTING MODEL New view tables will be created in your existing view model MyEmplayeeViews xmi an FINISH New view table name PlayerDataTable speck ees 0 Figure 4 20 View Model Definition Page 96 Import From Flat File Source When your import is finished your source model will be opened in an editor and show a diagram containing the your getTextFiles procedure Figure 4 21 Generated Flat File Procedures 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 97 Chapter 4 Importers 4 EmployeeViews xmi Ei SOURCES 3 file clob a R2 RI RI BRI BRI BRI BI BRI bI Rh T ea Transf
24. Figure D 60 VDB Data Roles Tab 368 VDB Editor D 3 2 2 Editing Translator Overrides 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 BooksVDB vdb Z3 UDF Jars Other Files Model Source Binding Definition 3 Books Output xsd d Books Output View xmi Vi ac oracle 1 Books Books 105 Data VDB Properties Overrides oracle 1 Property Value Addtional Driver Properties background validation false background validation millis blocking timeout wait millis check valid connectionsal connection url B Figure D 61 VDB Translator Overrides Tab 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
25. Generate virtual XML document model Location WSDLimportProject XML Model OnHandWSResponses Cancel Figure 4 51 Namespace Resolution Dialog 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 132 Import WSDL From File System Location zT DEA Create Web Service from WSDL File Validation Problems One or more selected WSDL files have errors Please correct or remove the filelsito proceed WSDL Validation Panel message wsdl file X cvc elt 1 Cannot find the declaration of element wsdl definitions http terraservice net terraservice2 asm Figure 4 52 WSDL Validation Problems Dialog 4 9 2 Import WSDL From File System Location You can create a Web Service model by selecting a WSDL file from your local file system e Step 1 Choose the File Import choose the File gt Import action py in the toolbar or select a project folder or model in the tree and choose Import Step 2 Select the import option Teiid Designer WSDL File or URL Web Service Model and click Next Step3 Input a valid name for your Web Service model and select the File System button Locate your fil
26. This wizard allows creating a complete relational procedure including parameters and optional result set File Customer Accounts xmi Name Name In Source amp Properties d Parameters Bj ResultSet Description Native Query UpdateCount AUTO Non Prepared Figure 5 14 Create Relational Procedure Dialog Source functions are procedures that are already deployed and accessable on your database By defining source functions in your relational model you can call these functions from within your transformation SQL and the functions will be pushed down to your database for execution 161 Chapter 5 Creating and Editi Create Relational Source Function jj No parameters defined For pi ocedure This wizard allows creating a complete relational source Function including parameters and Function speciFic nrianarkiarz Model File Customer Accounts xmi Name calculateSum Name In Source E Properties Parameters Description Update Count AUTO Function Properties w Deterministic Returnsnullonnull Variable Arguments Aggregate Properties iw Allows Distinct Allows Order by 1 Analytic Decomposable Uses Distinct Rows Figure 5 15 Create Relational Source Function Dialog User Defined Functions require additional properties such as Java Class and Method as well as a path to the jar file containing the code as shown below 162 Create R
27. a Show Dependency Diagram iz Co Ctrl C om i i Generate Dependency Report Figure 5 20 Modeling Sub Menu for Source Table Teiid Designer also provides specialized object editors to handle complex model objects and their unique properties These editors include e Section 5 3 1 Transformation Editor e Section 5 3 2 Input Set Editor XML e Section 5 3 3 Choice Editor XML e Section 5 3 4 Recursion Editor XML e Section 5 3 5 Operation Editor This section describes these editors in detail 5 3 1 Transformation Editor 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 e Double click Edit A relational view table or procedure in the Model Explorer or Diagram Editor 167 Chapter 5 Creating and Editi e A transformation node in a transformation diagram or mapping transformation diagram e A mapping class in a mapping diagram or mapping transformation diagram Right click Edit action on selected object in the Model Explorer Diagram Editor or Table Editor Arelational
28. fison as your URI value for a procedure http host port war_context model_name books 123 would execute this procedure and pass 123 in as a parameter Here s what the above example would look like in the Property tab Value VExtension ey rest Rest Method GET rest URI books fisbn G Note The generated URI will have the model name included as part of the path so full URL would look like this http host port war_context model_name books 123 If you wanted a REST service to return all books you would write your procedure just as it is above but remove the input parameter The URI 235 Chapter 6 Metadata specific property would then just be books or whatever you want and the URL would be http host port war_context model_name books Once you have added all of your procedures along with the required extended properties be sure and add the model to your VDB or synchronize if it s already included in the VDB You will then need to re deploy the VDB Important If you redeploy your VDB during development you may receive an Invalid Session Exception due to a stale connection obtained for the pool This can be corrected by flushing the data source or alternatively you could add a test query to your VDB connection s ds xml file This will insure you get a valid connection after redeploying your VDB The syntax for the test query is as follows lt check valid
29. Cancel to abort Specify location for generated modells i e Project or Folder BooksProject WebService Model bookCallection WS Interface Name bookCollection Operation Name getbookCollection Operation Definition Input Message Element bookCollection BooksNS BookSetNested m Input Message Mame bookCallectionlInput Output Message Element e bookCollection BooksNS BookSetNested Qutput Message Mame bookCollectionOuput Cancel Finish Figure 3 12 Generate A Web Service Dialog Step 4 Click Finish to generate model When model generation is complete a confirmation dialog should appear Click OK 69 Chapter 3 New Model Wizards Generation Completed Figure 3 13 Generation Completed Dialog 70 Chapter 4 Importers 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 gt Import action or select a project folder or model in the tree and right click choose Import Select Choose import source P gt Team E Designer B DDL File Source or View Model pf Designer Text File gt gt Source or View Models En File Source Flat gt gt Source and View Model En File Source XML gt gt Source and View Model B JDBC Database gt g
30. Description when the values are not supplied Position Integer Yes Yes Table B 3 Data Usage for Primary Keys Yes Used in the index calculations PRIMARY Type In Designer In Metadata Required Description KEY API FullName String See the KeyRecord See Table NamelnSource String ColumnIDs Collection ForeignKeyIDs Collection Extends KeyRecord Table B 4 Data Usage for Unique Keys UNIQUE KEY Type In Designer In Metadata Required Description API FullName String see the KeyRecord See Table NamelnSource String ColumnIDs Collection ForeignKeyIDs Collection Table B 5 Data Usage for Indexes INDEX Type In Designer In Metadata Required Description API FullName String See the KeyRecord See Table NamelnSource String ColumnIDs Collection 292 Table B 6 Data Usage for Access Patterns ACCESS PATTERNS In Metadata API Type In Designer Required Description FullName String See the KeyRecord See Table NamelnSource String ColumnIDs Collection Table B 7 Data Usage for Result Sets RESULT SET In Metadata In Designer Type Required Description API FullName String See DataType NamelnSource String ColumnIDs Collection Table B 8 Data Usage for Foreign Keys FOREIGN In Metadata In Designer Type Required Des
31. Figure 2 12 Select Database Objects On this page select the specific objects from the database that you want to import When finished click Next 31 Chapter 2 Dive Right In 1 Import Database via JDBC Specify Import Options Press the Next gt button to continue or the Finish button to finish Relational Model Definition Model Name TestMySQLxmi 00000000000 metMysQLxmi de Into Folder MyProject sources MyProjectsources Jl Make target a view model O Update if existing model selected Include Catalog For Fully Qualified Names Model Object Names Tables Procedures Columns etc Use Fully Qualified Names Example partssupplier dbo PARTS Change Case For All Characters Case Options Back Next gt Cancel Finish Figure 2 13 Import Options Finally choose the name for the model to be created defaults to orofileName 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 32 Model a JDBC Source 127 Model Explorer 33 g Outline z 69 Hs amp MyProject gt sources v Og
32. Figure 7 3 Refactor Move Action In Model Explorer Step 3 Select a new location i e Project or Folder and click OK Move Morthwind xmi Select the move destinatian AllCustomerAccounts gt Cg Northwind xmi Ee Sources b 5 BooksProject b 1 PartsProject gt 125 wsDLImportProject Cancel Figure 7 4 Move Model Dialog Move Model Unde Redo Rename Namespace URI 243 Chapter 7 Editing Models and 7 3 Save Copy of Model The Save As action performs a similar function as the Refactor gt Rename action except the renamed model is a structural copy of the original model To create a duplicate model using Save As Step 1 Open the model you wish to copy in a Model Editor by double clicking the model in Section D 2 1 Model Explorer View or right click select Open action Step 2 Select the editor tab for the model you opened e Step 3 Select File gt Save As action to open the Save Model As dialog Figure 7 5 Select Editor Tab 244 Save Copy of Model Save Model As Select a destination for the copy AllCustomerAccounts Ea Northwind xmi gt Sources gt 5 BooksProject gt PartsProject i WSDLImportProject Enter the new model name Northwin d West Ready to copy cme ox Figure 7 6 Save Model As Dialog e Step 4 Enter a unique model name in the new model name text field and click OK e Step 5 If depende
33. New A new target attribute will be added to the bottom of the Attribute Symbol table bound to the selected SQL symbol 173 Chapter 5 Creating and Editi Null This button allows you to bind null to a target attribute instead of binding a SQL Symbol to it Select a row in the Attribute Symbol table then click Null The target attribute will be bound to null If it was originally bound to a SQL Symbol the symbol will be released to the Unmatched Symbol list Function This button allows you to define an expression instead of just a SQL Symbol for the binding To define the expression select a row in the Attribute Symbol table then click the Function button The Expression Builder Dialog will display allowing you to define any type of expression See Section 5 3 1 4 Using the Expression Builder for further information about the Expression Builder SQL Display The current transformation SQL is shown at the bottom of the reconciler dialog As you add remove SQL symbols and make other changes you can see the SQL display change to reflect those changes When you OK the dialog this SQL will be your new transformation SQL If desired the SQL Display can be hidden by un checking the Show SQL Display checkbox 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 5 3 1 2 Using the Datatype Resolver This di
34. Operations Input Variables v 9 bookCollection operation s procedure Edit variables IN author string IM edition string IN isbn string IM publishDate string Procedure ve ilh A The variables representing values from the request document that are available tc The body of the selected operation s procedure itis not necessary to include BEGIN END clauses CREATE VIRTUAL PROCEDURE BEGIN DECLARE string VARIABLES IN isbn xpathvalue BOOKCOLLECTION WS BOOKCOLLECTION GETBOOKCOLLECTION bookCo t bookCollection book isbn DECLARE string VARIABLES IM title xpathvalue BOOKCOLLECTION WS BOOKCOLLECTION GETBOOKCOLLECTION baokCo t bookcollection book title DECLARE string VARIABLES IM subtitle xpathvalue Figure 5 40 Operation Editor The Operations section contains all interfaces and operations currently defined in the model 194 ackage Diagram 4 Table Editor Operation Editor Managing Model Object Extensions Selecting an operation will display the variables related to the input parameter s content in the Input Variables section and the body of its procedure minus the CREATE VIRTUAL PROCEDURE BEGIN END keywords and the input variable declarations and assignments in the Procedure section When pasting in SQL do not include the CREATE VIRTUAL PROCEDURE BEGIN END keywords Input variables will be automatically generat
35. Properties Displays the operating system s file properties dialog for the selected file D 4 7 Metadata Menu The Metadata menu provides Teiid Designer specific actions oases Rum Window Help ate Show System Catalog Show Datatype Hierarchy Figure D 77 Metadata Menu The Metadata menu contains the following actions Update Model from Source If the selected model is a relational source model that was originally created via JDBC Import then the model will be updated based on changes in the database schema Show System Catalog Opens the Section D 2 10 System Catalog View Show Datatype Hierarchy Opens the Section D 2 10 System Catalog View Re resolve References Analyzes references within models to other model components Convert to Enterprise Datatypes Adds an additional property to simple datatypes within your selected schema model to label them as enterprise datatypes Show Model Statistics Opens the Model Statistics dialog for the selected model Build Model Imports Reconciles all model import dependencies for the selected model D 4 8 Run Menu Teiid Designer currently does not contribute actions to the Run menu See Eclipse documention for details 383 Appendix D Teiid Designer Ui Q External Tools gt Figure D 78 Window Menu D 4 9 Window Menu The Window menu shown below contains no Teiid Designer specific actions See Eclipse Workbench documentation for details
36. SOAP Web Service by creating source and view models based on the Web Service WSDL amp Click to Begin t Create New Teiid Model Project Create SOAP Web Service Connection Create Models From SOAP Connection t Create VDB t Test VDB Figure 2 21 Consume SOAP Web Service Cheat Sheet 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 Fi Cheat Sheets 52 Consume a SOAP Web Service Create New Teiid Model Project Follow the steps below to create a Teiid Model Project Launch New Teiid Model Project Wizard 1 Specify unique project name vt 4 2 Click Next gt twice to get to Model Project Options page 3 Check folders you wish the wizard to create in your project 4 Click Finish Create SOAP Web Service Connection Create Models From SOAP Connection Create VDB k Test VDB Figure 2 22 Create Model Project 39 Chapter 2 Dive Right In 40 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 Mw to advance to the next step C
37. Your query results will be displayed in the Section 9 2 5 Sample SQL Results for Preview Data view The view will automatically open or get focus if not visible in your perspective 9 2 2 Preview Relational Table With Access Pattern o preview a relational table or view with access pattern Step 1 Select a relational table or view in the Section D 2 1 Model Explorer View or diagram that contains an access pattern The table or view can be in a view model as well as a source model e Step 2 Right click select the Preview Data action You can also select the same action in the tool bar of either the Section D 2 1 Model Explorer View or diagram e Step 3 A column input dialog is presented Select each access pattern and enter a value for each required column i Note If data entered does not match the column datatype String integer etc an error message will be displayed in the dialog header When all required values are entered click the OK button to execute the query 263 Chapter 9 Testing Your Models ud Preview Data To preview data from this table select an access pattern and provide a vi All access pattern column values are set Select OK to continue Access Patterns Required Columns 7E MameAndIn PLIBLISHER ID long MAME sktringrz55 Figure 9 5 Access Pattern Column Input Dialog Step 4 Your query results will be displayed in the Section 9 2 5 Sa
38. gt Cancel Figure 2 30 Consume SOAP Wizard 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 48 Consume a SOAP Web Service Create Relational Model from Web Service Procedure Definition Press the Finish button to finish Operations CapitalCity Overwrite existing procedures for this operation Request Response Wrapper Procedure Generated Procedure Name CapitalCity Generated SQL Statement CREATE VIRTUAL PROCEDURE BEGIN SELECT t FROM TABLE EXEC CountryInfoServiceView CapitalCity request CountryInfoServiceView CapitalCity sCountryISO Code AS request TABLE EXEC CountryInfoService invoke SOAPT11 null REQUEST xml out null AS response TABLE EXEC CountryInfoServiceView CapitalCity response RESPONSE result AS t END D Back Next gt Cancel Figure 2 31 Consume SOAP Wizard Click Finish to exit the wizard In the Cheat Sheet you can now continue Click IM 3 to advance to the next step 49 Chapter 2 Dive Right In D 50 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 Cheat She
39. main toolbar P 100 A 4 Bg Figure D 41 Main Toolbar Diagram Actions e The actions include 3 Zoom In Zoom to Level e gm Zoom Out Increase Font Size Decrease Font Size 347 Appendix D Teiid Designer Ui Perform Diagram Layout D 3 1 1 1 Package Diagram The Package Diagram provides a graphical view of the contents of a model container be it the model itself a relational catalog or schema PartsOraclell xmi 3 i Em SUPPLIER ID string 10 PART ID string 4 QUANTITY bigdecimal FK SPLIER PRTS SPLY SHIPPER ID bigdecimal Ep Fure ES SUPPLIER ID string 10 SUPPLIER string 30 ES SUPPLIER STATUS bigdecimal Fy SUPPLIER_CITY string 30 ES SUPPLIER STATE stringi F FK SPLIER STATS P PK SUPPLIER E SHIPPER ID bigdecimal SHIPPER NAME string 30 PK SHIP VIA fia Package Diagram 5 Table Editor F FK SPLIER PRTS SPLY F FK SPLIER PRTS PRTS PK SUPPLIER PARTS PK SUPPLIER FK SPLIER STATS PK STATUS PK STATUS EY STATUS ID bigdecimal 4 STATUS NAME string 30 FK SPLIER PRTS PRTS PART PART PK PARTS PART PART V PK Figure 0 42 Package Diagram Example e Package Diagram toolbar actions include 348 Model Editor e Go R
40. 2 1 Model Explorer View 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 315 Appendix D Teiid Designer Ui The Model Explorer shown below is comprised of a toolbar and a tree view zu Outline O 69 AllCustomerAccounts gt 5 BooksProject MT g PartsProject gt data D readme Parts vdb Og PartsSourceA xmi gt PartsSourceB xmi v f PartsVirtual xmi D import declarations 4 Package Diagram Supplierinfo A partsByColor gt SampleViews xmi Default Server Server Mo default carver defined Targeted Version 7 7 8 Figure D 7 Model Explorer View The toolbar consists of nine common actions Preview Data Executes a simple preview query SELECT FROM Ja 2 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 316 Model Explorer View Up Navigates up one folder container location See Eclipse Help Collapse All Collapses all projects B Link with Editor When object is selected in an open editor this option auto selects and
41. 9000071 G 71000 Newb1e 350 260 8654 9000073 133 State St Rainier Adelaid Marinovic 9000072 G 67000 Newbie 316 550 3499 9000073 125 Sta Nealon General 9000073 G 77000 COO 203 420 3113 9000075 129 State St Hartfor Garahana Jarrod Cvjetkovic 9000074 G 66000 Associate 620 430 1782 9000073 132 Neely Petronella Goravica 3000075 80000 CEO 230 320 6330 9000076 134 State Cancel 9 Figure 4 16 Data File Source Selection Import From Flat File Source e Step 5a Character Delimited Option The primary purpose of this importer is to help you create a view table containing the transformation required to query the user defined data file This page presents a number of options you can use to customize the Generated SQL Statement shown in the bottom panel for the character delimited option Specify header options Column names in header header line number and first data line number Parse selected row changed character delimiter and edit the TEXTTABLE function options See the Teiid User s Guide for details on the TEXTTABLE function If columns names are not defined in a file header or if you wish to modify or create custom columns you can use the ADD DELETE UP DOWN to manage the column info in your SQL When finished with this page click Next 91 Chapter 4 Importers m Import From Flat File Source x Flat File Delimited Columns Parser Settings _ i Pres
42. Bar e The shortcut bar may contain multiple perspectives The perspective button which is pressed in indicates that it is the current perspective To display the full name of the perspectives right click the perspective bar and select Show Text and conversely select Hide Text to only show icons e To quickly switch between open perspectives select the desired perspective button Notice that the set of views is different for each of the perspectives 314 Further information ET Cr Teiid Desig lg Database D Figure D 5 Workbench Window Title Bar D 1 3 Further information For more details on perspectives views and other Eclipse workbench details see formal Eclipse Documentation http nelp eclipse org ganymede index jsp 0 2 Designer Views Views are dockable windows which present data from your models or your modeling session in various forms Some views support particular Section D 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 Show View gt E Team Teiid Designer fe Datatypes Description w Metadata Faworites Model Explorer ip Relationship Mavigator System Catalog lt Tags Wh Teiid Model Classes Figure D 6 Eclipse Show View Dialog D
43. Bo import From XML File Source XML Data File Import Options Press the Next gt button to continue XML Ale rts xml XML File Contents Column Info 8 partssupplier Root Path partssupplier parts v e parts Add Column Name For Ordinality Data Type De 8 id id oO string E name E string le color color i string G weight weight n string P e parts b e parts e parts b e parts ses zi Generated SQL Statement SELECT Kr AS Id 5 name A color AS color A weight AS weight EXEC PartxMLSource getTexthiles parts AS f XMLTABLEU partssupplier parts PASSING XMLPARSE DOCUMENT f file COLUMNS id string PATH id name string PATH name texti color string PATH color text v string PATH AS A lt Back ao Figure 4 26 XML File Delimited Columns Options Page e Step 6 On the View Model Definition page select the target folder location where your new view model will be created You can also select an existing model for your new view tables i Note The Model Status section which will indicate the validity of the model name whether the model exists or not Lastly enter a unique valid view table name Press Finish to generate your models and finish the wizard 104 Import From Salesforce ELM ae eye mui rupe eb Import From XML File Source View Model Definition
44. FA PART ID PART ID http www metamatrix com metamodels Relational Column E PART WEIGHT PART WEIGHT http www metamatrix com metamodels Relational Column a PartsVirtual PartsVirtual http www metamatrix com metamodels Core ModelAnnotation partsByColor partsByColor http www metamatrix com metamodels Relational Procedure Ga Found 10 matches PartsProject PartsSourceB xmi Figure D 28 Search Results View The toolbar actions for the Search Results view are Show Next Match Navigates down one row in the view Show Previous Match Navigates up one row in the view Remove Selected Matches Removes selected results from the view Remove All Matches Clears the view Search Launches the MoTeiid Designerearch Dialog Previous Search Results Select previous search results from history You can also perform some of these actions via the right click menu 334 Datatype Hierarchy View Fu Problems Search 24 feature Name pattern Part 26 matches Ld Found 8 matches PartsProject PartsSourceA xmi PART _ ID httz Mirae wem ob ete am amem Panem side m amem nd ID S ati Show Shaw In ain RT Next Match ctr ati ID htt ral 4 Previous Match Ctri PARTS http TE Expand All PartsSource i rami EE ore FY PART WEIGH ana cehe l SUPPLIER_PF 3 Remove Match D Cg Found 1 match g
45. Input Sets do not display on the Section D 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 B lt lt Input Set gt gt Input Set pj isbn string Figure 5 33 Edit Input Set Button Input Set Editor Input Parameters Mapping Class Bindings isbn string B boak isbn string aer isbn string type string E title string E publisher string E publishDate string E edition string Figure 5 34 Input Set Editor Panel 186 Choice Editor XML 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
46. Only used for System metadata PrimitiveTypelD String No Only used for System metadata Variety Type Integer No Only used for System metadata VarietyProps Collection Table B 10 Data Usage for Procedures PROCEDURE Type In Designer In Metadata No Required API Only used for System metadata Description FullName String Yes Yes Yes Name of the column NamelnSource String Yes Yes Yes Name of the column in source system IsFunction Boolean Yes Yes Determines if this function IsVirtual Boolean Yes Yes If Function then UDF else stored procedure ParametersIDs Collection Yes Yes Parameter List ResultSetID String Yes Yes Result set columns UpdateCount Integer Yes Yes Update count defines the number of sources being updated only applicable 295 Appendix B Designer Metadata PROCEDURE Type In Designer API In Metadata Required Description Table B 11 Data Usage for Procedure Parameters for virtual procedures PROCEDURE Type In Designer In Metadata Required Description PARAMETER API ObjectID String Same as Column FullName String Same as Column namelnSource String Same as Column defaultValue String Same as Column RuntimeType String Same as Column DatatypeObj
47. REST WAR War File Dialog Table 6 3 Field Descriptions Field Name Description Name The name of the generated war file Connection JNDI Name The JNDI connection name to the deployed Teiid source VDB War File Save Location The folder where the generated WAR file should be saved Include RESTEasy Jars in lib Folder of WAR If selected the RESTEasy jars and there dependent jars will be included in the lib foled of the generated WAR If not selected the jars will not be included This should be de selected in environments where RESTEasy is installed in the classpath of the server installation to avoid conflicts Step 6 Click OK to generate the REST war When war generation is complete a confirmation dialog should appear Click OK 23 Chapter 6 Metadata specific REST WAR File Created The REST WAR file BooksREST was successfully created Figure 6 29 Generation Completed Dialog 6 4 2 3 Deploying Your Generated WAR File Once you have generated your war file you will need to deploy it to your JBoss AS instance There are a few ways to accomplish this From JBDS or JBoss Tools 1 Insure target JBossAS is configured and running 2 Select your WAR file in the Model Explorer view If you didn t generate your war to that location you can copy and paste it there 3 Right click on the WAR file and select Mark as Deployable This will cause you WAR file to be automatically deploy
48. Required Description API Identifier String Yes No No Not required Description String Yes vdb xml No Used by System metadata ProducerName String Yes No No Not required ProducerVersiorstring Yes No No Not required Provider String Yes No No Not required TimeLastChang amp tring Yes No No Not required TimeLastProducstting Yes No No Not required ModellDs Collection Yes vdb xml Yes Defines the model list in a VDB Table B 14 Data Usage for Annotations ANNOTATION Type In Designer Metadata Required Description API FullName String Yes Yes No System metadata as description on procedure parameter NamelnSource String Yes No No Not required Description String Yes No No Not required 298 Appendix C User Preferences The Teiid Designer provides options or preferences which enable customization of various modeling and UI behaviors Preferences can be accessed via the Edit gt Preferences action on the Main toolbar Preferences JBoss 10015 ModeShape gt Plug in Development Project Archives gt Remote Systems gt Run Debug gt Server gt gt Spring Team Teiid Designer Diagram Diagram Printing Editor Validation TestNG Validation gt Web gt Web Services XML Figure C 1 Preferences Dialog C 1 Teiid Designer Preferences General Teiid Designer preferences include Enable auto creating of a source model s data source on Teiid Server in
49. Sibling action but allows you to create multiple children at the same time All new rows will correspond to an object of the same type as the selected object and be located under the same parent as the selected object To Insert Rows in a table Step 1 Select a table row to insert rows after Step 2 Right click select Insert Rows action or select the Insert Rows action on the main toolbar The following dialog will be displayed 362 Model Editor Insert Table Rows Insert Rows Maximum of 1000 20 rows will be inserted Number of Rows 20 Cancel Figure D 55 Editing String Property Step 3 Edit the Number of Rows value in the dialog or use the up down buttons to change the value Step 4 Select OK in dialog The desired number of rows new model objects will be added after the original selected table row D 3 1 3 Simple Datatypes Editor The Simple Datatype Editor provides a form based properties view of XML Schema data 363 Appendix D Teiid Designer Ui type filter text Simple Datatypes E Delete Clear Datatypes in BookDatatypes xsd BookType CommaSeparatedList PublicationDate PublicationYear anySimpleType anyvURI base64Binary bigdecimal biginteger blob boolean byte char clab date dateTime decimal double duration ENTITY float Table dtr Simple Datatype Editor Target Namespace http www metamatrix com XMLSch
50. Teiid server VDB s behave like standard JDBC database schema which can be connected to queried and updated based on how the VDB is configured Since VDB s are just databases once they are deployed they can be used as sources to other view model transformations This allows creating and deploying re usable or common VDB s in multiple layers depending on your business needs 1 3 6 1 VDB Content and Structure In Designer the VDB file names use a vdb file extension VDBs are structurally just ZIP archive files containing 3 folders META INF e contains vdb xml definition file e runtime inf e contains a binary INDEX file for each model included in your VDB e project folder name gt contains of the models you will be adding in the VDB Editor i e xmi and xsd files When deployed the metadata is consumed by Teiid in order to create the necessary runtime metadata for your model definitions 13 Chapter 1 Introduction The vdb xml file contains e VDB name version properties e contained model information name translator name connection info e translator info e data role definitions for the referenced models e import VDB references The vdb xml file example below highlights the basic model information lt xml version 1 0 encoding UTF 8 standalone yes gt vdb version 1 name Financials gt model visible true type VIRTUAL name US CustomerAccounts path Financials US_CustomerAccounts xm
51. TestMySQL xmi gt import declarations 2 Package Diagram amp Accounts ACCOUNT Accounts CUSTOMER amp Accounts HOLDINGS A Accounts PRODUCT 0 Data Source WES WF O YO O yY Figure 2 14 Model Explorer Preview Data All execution capabilities in Designer Preview Data VDB execution require you to connect to a running Teiid Server See 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 33 Chapter 2 Dive Right In Table or Procedure Selection Select table or procedure from workspace and click OK 2 MyProject 7 sources v Ge TestMySQL xmi P import declarations 2 ta Package Diagram Accounts ACCOUNT amp Accounts CUSTOMER Accounts HOLDINGS Accounts PRODUCT 3 Data Source gt VIEWS Figure 2 15 Select Preview Table After selecting the table click OK Now the preview results will be displayed 2 Problems Error Log 501 Results zi Execution Plan query expression here Status Result1 Status Operation Date cc ACCOUNT_ID SSN STATUS Succe select fro Jun 8 201
52. 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 9 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 G Note Any virtual table view or procedure is previewable as long as all physical source models reference sufficient connection info See 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 261 Chapter 9 Testing Your Models Preview Not Available Preview not available for model gt gt ProductsSQLServer xmi A Use Modeling g
53. 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 3 1 4 Using the Expression Builder for further information SE 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 all A Increase Font Size increases the font size of all editor text by 1 id A 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 g b c Optimize SQL when toggled ON will use the short names of all SQL symbols that 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 28 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 Transformation Editor Export SQL Text allows you to export the currently displayed SQL statement into a text f
54. and default view entity properties 305 Appendix C User Preferences Editor v Da XML Document Mapping Settings Transformation Table VDB SQL Clauses M Start Clauses on New Line Indent Clause Content Auto expand default SELECT FROM on construction Diagram Layout View and Sources 3 Tree Layout Miscellaneous Default String Length 10 Figure C 7 Transformation Editor Preferences Panel C 1 3 4 VDB Editor Preferences Section D 3 2 VDB Editor Preferences provide a way to customize VDB editor behavior Editor ow XML Document Mapping Settings Transformation Figure C 8 VDB Editor Preferences Panel C 1 4 Validation Preferences Validation Preferences provide a way to customize the severity of some of the rules checked during model validation The Validation preference pages shown below include the validation preferences for Core Relational XML and XSD XML Schema models 306 Validation Preferences Validation cov Specify the Severity Level for validation related occurrences Relational Core Validation Control Settings Multiple EQbjects with same uuid Ignare String functions SUBSTRING LOCATE and INSERT 0 to 1 base check Warning Figure C 9 Core Model Validation Preferences Panel Validation qw ow Specify the Severity Level for walldatlon related occur
55. and manage data roles specific to a VDB Once deployed within a Teiid server with the security option turned on by default any query run against this VDB via a Teiid JDBC connection will adhere to the data access permissions defined by the VDB s data roles The VDB Editor contains a VDB Data Holes section consisting of a List of current data roles and New Edit and Hemove action buttons Data Roles VDB Descriptian Data Role Description NoAccountHoldingsAccess Client cannot access customer account holdings financial product info VirtualOnbhy Client users only access virtual layer Access requests to physical sources wi Figure D 59 VDB Data Roles Panel Clicking New or Edit will launch the New VDB Data Role editor dialog Speicify a unique data role name add a optional description and modify the individual model element CRUD values by check or unchecking entries in the models section 367 Appendix D Teiid Designer Ui New VDB Data Role New VDB Data Role Select Anish to save data role Mame Invento ny Description Allow usage of temporary tables Apply this rale to All Users Remove Edit Models Model Create Read Update Delete Execute Alter WF ProductsSQL xmi gt amp ProductData gt ProductSymbols System Tables Access Allow this role ta access SYSADMIN model Cancel Finish
56. click Next e Step 3 On the next page select an existing Web Service Connection Profile from the list or click the New Button to create a new profile 120 Import WSDL into Relational Models 4 Create Relational Model from Web Service Source and WSDL Operations Definition Press the Next gt button to continue Connection Profile Countrylnfo New Eai WSDL URL or Location httpJ www oorsprong org websamples countryinfo CountrylInfoService wso W5SDL Validate WS Select Port CountryInfoServiceSoap Binding 5OAP11 Service Mode PAYLOAD Select the desired WSDL Operations Operation gt Lountryint PhoneLode Deselect All CountryName gt CurrencyName w gt FullCountryinfo FullCountryinfoAllCountries gt LanguagelSOCode E amp E LanguageName Select alt Selection Details Operation CountryName binding CountryinfoServiceSoapBinding port CountryInfoService5oap service CountryInfoService id ihttpJ www oorsprong org websamples countryinfo CountrylnfoService amp oapBinding CountryMarr Inr Le Aare i ntre Arn Aan EE 2 Back net gt Cancel Finis Figure 4 41 WSDL Source Selection e Step 4 Select individual Web Service Operations to model The default behavior of this page selects all available operations in the tree Operations can be de selected if they are not being modeled The Select
57. contain objects that are not only unrelated but can be from different containers and even models 350 Ga PartsOraclell xmi 3 PartsView xmi 3 N C PART ID string 4 PART NAME string 255 f PART COLOR string 30 PK PARTS PART WEIGHT string 255 a SS m FK SPLIER PRTS PRTS E PK PARTS Ee Fal gp Ma Custom Diagram F Table Editor Figure D 44 Package Diagram Example Custom Diagram toolbar actions include Go Refresh Diagram Re draws diagram Show Parent Diagram Navigates to diagram for parent object if available Preview Data Executes a simple preview query SELECT FROM B Add To Diagram Add objects selected in Model Explorer to diagram Model Editor SUPPLIER ID string 10 EY PART ID stringi4 QUANTITY bigdecimal SHIPPER ID bigdecimal F FK SPLIER PRTS SPLY F FK SPLIER PRTS PRTS P PK SUPPLIER PARTS 351 Appendix D Teiid Designer Ui B Remove From Diagram Removed objects selected in diagram from diagram Lom Clear Diagram Remove all objects from diagram gm Save Diagram as Image Save the diagram image to file in JPG or BMP format Modify Diagram Printing Preferences Modify page layout information for printing diagrams Includes margins orientation etc Show Hide Page Grid Show current page boundaries as grid in diagram Since Custom Diagrams do not re
58. containing a view table for your selected flat file source file Within the view table will be generated SQL transformation containing the getTextFiles procedure from your source model as well as the column definitions and parameters required for the Teiid XMLTABLE function used to query the data file You can also choose to update an existing view model instead of creating a new view model The XMLTABLE function uses XQuery to produce tabular ouptut The XMLTABLE function is implicitly a nested table and may be correlated to preceeding FROM clause entries XMLTABLE is part of the SQL XML 2006 specification xqucery expressiom PASSING TCOLUMNS COLUMN 222 AS name COLUMN name FOR ORDINALITY datatype DEFAULT expression PATH Str rng y Teiid Designer will construct the full SQL statement for each view table in the form STLA T A entryDate AS entryDate A internalAudit AS internalAudit FROM EXEC AS f XMLTABLE XMLNAMESPACES http www kaptest com schema 1 0 party AS pty pty students student PASSING XMLPARSE DOCUMENT f file COLUMNS entryDate FOR ORDINALITY internalAudit string PATH internalAudit AS A To import from your XML data file source follow the steps below e Step 1 In Model Explorer choose the File gt Import action EI in the toolbar or select a project folder or model in the tree and choose Import Step2 Select
59. containing one or more of the procedures required for accessing web service based XML data via a web s translator e To create anew relational model containing web service based translator procedures complete Create New Relational Source Model above and continue with these additional steps e Step 5 Select the model builder labeled Generate Web Service Translator Procedures and click Next gt The Generate Web Service Translator Procedures dialog will be displayed e Step 6 Check one ore more of the Available Web Services Translator Procedures then Click Finish 54 Generate Web Service Translator Procedures f New Madel Wizard Generate Web Service Translator Procedures Generate default relational procedures compatible with Teiid Web Se desired procedures and Finish Available Web Service Translator Procedures invoke binding in String action in STRING request in OBJECT endpoir vi invokeHttp action in STRING request in OBJECT endpoint in STRING r p X 2 lt Back Next gt Figure 3 3 Generate Web Service Translator Procedures Dialog 55 Chapter 3 New Model Wizards 3 1 3 Copy 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 To create a new relational model by copying contents from another relational source model complete
60. 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 e 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 5 3 1 3 Using the Criteria Builder The Transformation Editor s Criteria Builder offers you a quick graphical means to build criteria clauses in your transformations based
61. for the SOAP Web Service as shown below 43 Chapter 2 Dive Right In ay Cheat Sheets T 5 Consume a SOAP Web Service v Introduction v amp Create New Teiid Model Project Create SOAP Web Service Connection Create Models From SOAP Connection Follow the steps below to create models using the previously defined connection profile X Launch the Consume SOAP Web Service Wizard 1i 1 Select the connection profile then press Validate WSDL eee 2 Select the Port and Service Mode as desired 3 Select one or more operations under Select the desired WSDL Operations Click Next gt 4 Select the location and name for the Source and View Models to be created Click Next gt 5 For each operation define the request and response XML documents Select the Operation Select the Request tab then select desired element s under schema contents and Add to Element Info Select the Response tab then select desired element s under schema contents and Add to Column Info Select the Wrapper Procedure tab to view the generated procedure SQL 6 When all operation s have been defined click Finish Create VDB Test VDB Figure 2 26 Create Models from SOAP Connection next to Launch the Consume SOAP Web Service Wizard to launch the wizard The first page of the wizard is shown below 44 Consume a SOAP Web Service m Create Relational Model from Web Service Create
62. like Microsoft Excel to set object properties e Table Editor Preferences Change table editor preferences including customizing visible properties 359 Appendix D Teiid Designer Ui Insert Rows Create multiple new sibling objects Table Refreshes the contents of the current Table Editor to insure it is in sync with the model Mame Mame In Source Syste mm mum Lu New Child gt New Sibling New Association Modeling Ctrl X HE Copy Ctrl C Clone ol Delete parti Rename F2 pS T adled d Ctrl O co Add to Metadata Favorites U u a o s 1H Open in Relationship Navigator 4odelF 1S D Run As adel F oe 5 Debug As 4odel F m E Profile As Model F ae alidate idled s U Mark as Deployable Team Compare With Replace With Guvnor Table Editor Preferences Fj Insert Rows 1 8 KB GS Refresh Table 1 1 JPEG image Tue 07 Sep 201 Figure D 49 Table Editor Example D 3 1 2 1 Editing Properties You can edit properties for an object by double clicking a table cell 360 Model Editor For String properties the table cell will become an in place text editor field Base Tables ES Columns Foreign ation Mame CATEGORIES CHSTOMERCUSTORMERDEMO Cl US TOMERDEMOGRAPHICS CL CUSTOMERS Cl EMPLOYEES Figure D 50 Editing String Property If a property is of a boolean true or
63. modeling manually MS Access JDBC access JDBC Importer 285 Appendix A Supported Data So Data Source Translator Type Designer Import Option MS Excel JDBC excel odbc JDBC Importer MS SQL Server JDBC sqlserver JDBC Importer MySQL JDBC mysqi5 mysql JDBC Importer Netezza JDBC netezza JDBC Importer Oracle JDBC oracle JDBC Importer PostgreSQL JDBC postgresql JDBC Importer Salesforce com salesforce SalesForce Importer SAP Gateway ws File Source XML Importer SAP R 3 ws SAP Services Registry ws WSDL Importer Sybase ASE JDBC sybase JDBC Importer Teradata JDBC teradata JDBC Importer Teiid JDBC teiid JDBC Importer Web Services SOAP WSDL ws WSDL or URL Importer Web Services Rest OData File Source XML Importer 286 Appendix B Designer Metadata Usage Requirements In Teiid Runtime Based on the metadata exposed by the Teiid Desinger the below table shows which fields are required and how that information is being used in Teiid runtime currently as of Teiid version 7 5 A ODS file attached if you like to modify Table B 1 Data Usage for Tables TABLE Type In Designer Metadata Required Description API FullName String Yes Yes Yes Name of the Table NamelnSource String Yes Yes Yes Name of Table in the source system for view this can be empty also used on variety of use cases Cardinality Integer Yes Yes Y
64. 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 e Named ZIPCode Nine characters long e string e Located in the StreetAddress table e 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 Design Time and Runtime Metadata 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 e 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 he 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 b
65. table including columns unique keys and foreign keys definition Properties P Primary Key lil Unique Constraint F Foreign Keys Include PK Partinfo Name In Source PK Partlnfa Column Name C ID Cancel Figure 6 8 Primary Key Tab The Foreign Keys tab allows creating editing and deleting multiple foreign keys 212 Create Relational Table Wizard Create Relational Table Create Relational Table Click to add new object to model Figure 6 9 Foreign Keys Tab To create a new Foreign Key select the Add button and enter select the properties key references in the tables shown below 213 Chapter 6 Metadata specific Create Foreign Key Create Foreign Key Enter valid name select a key or constraint select column references and press OK Name FK Supplier Name In Source FK Supplier Foreign Key Multiplicity Unique Key Multiplicity Select Primary Key or Unique Constraint P Table A PK_A Table A UC A P BEBB PK BB W BBBB UC BBB TABLE X PK A Select Column References To FK name category IM quantity Cancel Figure 6 10 Create Foreign Key Dialog 6 2 Relational View Modeling This section contains descriptions of various features related to creating and managing relational view model objects 6 2 1 Create Materialized Views For any relational view table you can enable it s materialized view option by setting the Materialized p
66. text Name Description optional owe j ewe me _ Figure 2 8 Connection Profile Name and Type 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 27 Chapter 2 Dive Right In New Connection Profile Specify a Driver and Connection Details Select a driver from the drop down and provide login details for the connection Drivers MySQL JDBC Driver 1 A Properties Optional 4 URL jdbc mysqal localhost 3306 database Save password s Connect when the wizard completes Test Connection Connect every time the workbench is started e Back Wet Cancel Finish Figure 2 9 Connection Profile properties 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 28 Model a JDBC Source Import Database via JDBC Import Database via JDBC Select the JDBC source confi
67. the Teiid Designer perspective or prompt which will always ask you if you wish to open the Teiid Designer perspective Check and update imports during save occasionally editing a model may add or remove objects in one model that reference objects in another model Model Imports keep track of these dependencies within each model A validation error or warning may appear during a build Checking this box will automatically check and update imports during the save process This will result in any unneeded imports being removed from the model or any required imports added to the model If unchecked no updating of imports will be performed Default Teiid Server Version determines what version of server modelling will be targeted at if no teiid server has been defined in the Servers View Thus it is possible to still design models without the need to define and connect to a server However possible values are confined to the teiid runtime clients installed 300 Diagram Preferences Telid Designer Specify values for general Designer preferences Preview Data VDB Execution Enable auto creating of a source model s data source on server VOB Execution Enable Preview Enable Preview Teid Cleanup Telid Designer _ Always open editor without prompting Open Designer perspective when a model is opened _ Always open Never open w Prompt Check and update imports during save B X X Default Teilid Server Versio
68. 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 e What information does the metadata contain see Section 1 2 2 Business and Technical Metadata e What data does the metadata represent see Section 1 2 4 Source and View Metadata How will my organization use and manage this metadata see Section 1 2 3 Design Time and Runtime Metadata 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 objec
69. 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 D 4 3 Refactor Menu The Refactor menu provides Teiid Designer specific actions for file level changes to the models 3 9 Appendix D Teiid Designer Ui Figure D 72 Refactor Menu he Refactor menu contains the following actions Undo Undo the last refactor command Redo Redo the last undone refactor command Move Move a model from one container folder or project to another Rename Rename a model D 4 4 Navigate Menu Teiid Designer currently does not contribute actions to the Navigate menu See Eclipse documention for details Go To gt Open Spring Bean Shift Alt B 29 Open Seam Component Shif
70. to Account Accountld Create XMI View Docu wa Add to Metadata Favorit Ctrl X Figure 6 18 Create XML View Documents Action The action will query you for a target XML Document model You can either select an existing XML Document model from your workspace or enter a unique model name and the wizard will create a new model for you BooksxXMLiest gt 3 BookDatatypes xsd b Ea Books xsd gt E Bookslnput xsd d BooksXML xmi Parts Test gt Cg Northwind xmi b Parts Supplier xmi PartsTestVDB vdb Gg ProductsOracle xmi Select or create XML View model 9 Enter name and click OK to create a new XML View Model in folder BooksXMLTest Cancel Figure 6 19 Select or Create XML View Model Dialog 223 Chapter 6 Metadata specific After selecting or creating your new XML Document model the XML Document builder page will be displayed This page is explained in greater detail in Section 3 3 2 Build XML Documents From XML Schema section 224 Create XML View Documents from schema m Build XML Documents From XML Schema Select XML Schema Select XML Schema file then select Schema Root Elements Or press Anish to create an empty XML Document XML Schema Ale oksXMLTest Books xsd Select one or more Schema Root Elements to generate Virtual Documents Schema Root Elements Virtual Documents bookListing BooksNS BookSetFlat e bookCallection BooksNS BookSetNe
71. to finish Connection Profile PartsOraclell New Edit JDBC Metadata Processor Oracle Properties Driver Oracle 11 Thin Driver URL jdbc oracle thin 2 dbO025 www mydb com 1521 db25 User Name partssupplier Password Cancel Figure 4 5 Select JDBC Source Configuration Dialog e Step 5 On the Select Database Metadata page select the types of objects in the database to import Press Next or Finish if enabled 76 Import From JDBC Database Import Database via JDBC Select Database Metadata Press the Next gt button to continue or the Anish button to finish Foreign Keys Table Types Indexes SYNONYM Unique Only Approximations Allowed Procedures Cancel Anish Figure 4 6 Select Database Metadata Dialog Step 6 On the Select Database Objects page view the contents of the schema or change selections Select which database schema objects will be used to construct relational objects Press Next gt or Finish if enabled 11 Chapter 4 Importers ric CES Import Database via JDBC Select Database Objects Select the objects to import from database PartsOraclell Primary Key Imported Foreign 2 PartsOracle11 e Calumns TABLE CAT TABLE SCHEM TABLE MAME COLUMN MAME DATA TYPE TYF PARTSSUPPLIER PARTS PART ID 1 CH PARTSSUPPLIER PARTS PART 12 WAR PARTSSUPPLIER PARTS PART COLOR 12 VAR PARTSSUPPLIER PARTS PAR
72. user selects an existing tranlator type and clicks OK 369 Appendix D Teiid Designer Ui Add Translator Override postgresql salesforce sglserver sybase Figure D 62 Add Translator Override Dialog 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 3 0 Model Extension Definition Editor Add New Translator Override A Teiid server is nat available so translator types are not available A translator name and translator type are both required when creating a translator override Press OK when finished Figure D 63 Add New Translator Override Dialog D 3 3 Model Extension Definition Editor 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 Overview Sub Editor this editor is where the general MED information is managed This information includes the namespace prefix namespace URI extended model class and the descript
73. xmi does not exist and will be created and contain the required service procedure View Model Definition ee Location SOAPProj views Name CountryInfoServiceView xmi Status View model CountryInfoServiceView xmi does not exist and will be created and contain your gene procedures Procedure Generation Options Userspecified Procedures recommended Define user specified request and response procedures from your WSDL schema elements This and response 5 Default Procedures Generate default request and response procedures A new procedure will be generated for each cot lt Back Net Cancel Figure 2 28 Consume SOAP Wizard 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 Consume a SOAP Web Service _ Create Relational Model from Web Service Procedure Definition No columns are defined for the response procedure result set for the operation CapitalCity Operations CapitalCity Overwrite existing procedures for this operation Request Response Wrapper Procedure Generated Procedure Nam
74. 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 dentification of datatype e Storage formats e Constraints e Source specific locations and names 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 Source and View Metadata 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 descr
75. 2 Tr 2 19980003 CSTO1003 Persona 3 19980004 CSTO1004 Persona 4 19980005 CSTO1005 Persone 5 19980006 CSTO1006 Persona CETO007 Percnna a Total 17 records shown Figure 2 16 Preview Results 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 34 Model a JDBC Source New VDB New VDB Press the Finish button to finish In Folder MyProject m VDB myVDB Description Selected Model Add Ga TestMySQL xmi Remove Figure 2 17 New VDB In the dialog select the target 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 35 Chapter 2 Dive Right In 157 Model Explorer 2 N Outline EH 69 E t5 MyProject gt sources views Figure 2 18 Model Explorer 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 a
76. 3 Type query expression here INSTR ID NAME Status A Operation Date Succee select fror Aug 28 201C Transient org teiid d Succee select fror Aug 28 2010 Transient org teiid d Figure 9 8 SQL Results View 9 2 6 Execution Plans PRDOIOB88 PRD01089 PRD01090 PRD01091 PRD01092 PRD01093 PRDO1094 PRD01095 PRDO1O96 PRD01097 PRD01220 PRD01095 13 PRDO1100 PRD01101 5 PRDO1102 6 PRD01103 ass AMAA Amazon com Incorporated Juniper Networks Incorporated Red Hat Incorporated Boston Scientific Corporation Pfizer Inc British Biotechnology plc Unisys Corporation Honeywell International Hilton Hotels Corporation Hilton Hotels Corporation Novell Incorporated Inex Pharmaceuticals Incorpora Cytovax Biotechnologies Incorpc Commonwealth Biotechnologie si Sh St St St Co Mercury Interactive Corporation Fidelity Freedom Income Fund I Hl 4 When Preview Data is executed the Teiid Execution Plan is also displayed as shown below The Execution Plan may also be obtained by rt clicking on a previewable object then selecting Modeling Show Execution Plan in the context menu 267 Ste Ste Stg Mu Chapter 9 Testing Your Models E SQL Results amp Teiid Execution Plan zz Preview Data for myTbl SQL selec
77. 3 b O de http www eclipse org emf 2002 Ecore de http www eclipse org emf 2002 Mapping de http www eclipse org uml2 3 0 0 UML dee http www eclipse org xsd 2002 XSD de http www metamatrix com metamodels Compare htto www metamatrix com metamodels Core dee htto www metamatrix com metamodels Dependency de metamatrix com metamodels Diagram dee http www metamatrix com metamodels Extension de http www metarnatrix com metamaodels JDBC de http www metamatrix com metamodels MetaMatrixFunction de http www metamatrix com metamodels Relational gt AccessPattern gt RelationalEntity com metamatrix metamodels r E BaseTable gt Table com metamatrix metamadels relational Base H Catalog gt RelationalEntity com metamatrix metamodels relatior H Column gt RelationalEntity com metamatrix metamodels relatior H ColumnSet gt RelationalEntity com metamatrix metamodels rela DirectionKind ForeignKey gt Relationship com metamatrix metamodels relatior Index gt RelationalEntity com metamatrix metamodels relational LogicalRelationship gt Relationship com metamatrix metamodels LogicalRelationshipEnd gt RelationalEntity com metamatrix meta MultiplicibyKind NullableType SF SF SF SF SF NZ NFO NF n 2 OO g s T Figure D 31 Datatype Hierarchy View D 2 10 System Catalog View
78. 4KB 03 10 2012 standalone ha xml 19 9KB 03 10 2012 standalone teiid xml Li teiid security roles properties 106 bytes 10 19 2012 teiid security users properties 95bytes 10 19 2012 Figure D 17 Teiid Configuration File Selection e Click Finish and your new server configuration will be opened in Teiid Editor for viewing In this editor you can test both Teiid admin and JDBC connections 325 Appendix D Teiid Designer Ui ce Server 23 1 JBoss 7 1 Runtime Server Teiid Server Overview Display Name eiid 8 2 Host JBoss Server JBoss 7 1 Runtime Server Administration Connection Administration is performed via the jboss management configuration Test Administration Connection JDBC Connection User name user Password Port 31000 Test JDBC Connection Figure D 18 Teiid Editor Actions available in this view include d New Teiid Instance Create a new instance of a running Teiid server 4 Teiid Server Properties View and edit properties of an existing Teiid instance Reconnect and refresh contents of the selected Teiid instance Delete Disconnect and delete the selected Telid instance 326 Properties View 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 R
79. Boolean Yes Yes 2 Used in System Metadata IsCurrency Boolean Yes Yes No Only used for System metadata IsFixedLength Boolean Yes Yes No Only used for System metadata IsTranformationBoolearameter Yes 2 2 2 SearchType Integer Yes Yes Yes Used for defining the capability of the source Length Integer Yes Yes 2 Used in System Metadata Scale Integer Yes Yes 2 Used in System Metadata 290 COLUMN Type In Designer In Metadata Required Description API Precision Integer Yes Yes Used in System Metadata CharOctetLengtmteger Yes Yes No only used for System metadata Radix Integer Yes Yes 2 Used in System Metadata DistinctValues Integer Yes Yes Yes Used for cost calculations System metadata NullValues Integer Yes Yes Yes Used for cost calculations System metadata MinValue String Yes Yes Yes Used for cost calculations System metadata MaxValue String Yes Yes Yes Used for cost calculations System metadata Format String Yes Yes No Only used for System metadata RuntimeType String Yes DataType Yes Data Type NativeType otring Yes Yes Yes Translators can use this field to further plan DatatypeObjectlBtring Yes 2 2 DefaultValue String Yes Yes Yes Used for Insert and procedure execute operations 291 Appendix B Designer Metadata COLUMN In Metadata API Type In Designer Required
80. Create New Relational Source Model above and continue with these additional steps Step 5 Select the model builder labeled Copy from existing model of the same model class and click Next The Copy Existing Model dialog will be displayed e Step 6 Select an existing relational model from the workspace using the browse button Step 7 Check the Copy all descriptions option if desired Click Finish 56 Creating New Relational View Model New Model Wizard Copy an Existing Model Select an existing model to be copied Existing Model USCustomers xmi Model Content D 8 ACCOUNT gt ACCOUNTHOLDINGS gt CUSTOMER D Data Source Copy all descriptions 08 Figure 3 4 An Existing Model Dialog 3 2 Creating New Relational View Model Create New Relational View Model To create a new empty relational view model Step 1 Launch the New Model Wizard e Step 2 Specify a unique model name Step 3 Select Relational option from Model Class drop down menu e Step 4 Select View Model from Model Type drop down menu e Step 5 Click Finish 57 Chapter 3 New Model Wizards n addition to creating a new empty relational view model the following builder options are available Copy from existing model of the same model class e Transform from existing model 3 2 1 Copy From Existing Model This builder option performs a structural copy of the contents of an exis
81. DB from Model Explorer E SQL Results 3 Type query expression here Status Operation Date Connection Profile Succee SELECT FROM PartsSaurceA Parts Oct 15 2010 Parts Teiid Connection PART ID PART NAME PART COLOR PART l Nut Red J 2 P301 Bolt Green 12 3 iP302 Screw Blue 13 4 P303 Bolt Green Er 5 P304 Cam Green 18 B Figure 9 13 SQL Results View 9 3 3 Deploy VDB from Model Explorer You can also deploy your VDB first by selecting it in the Model Explorer and dragging dropping it onto a connected Teiid instance in the Servers view or right click select Modeling Deploy action Once deployed you can select the VDB in the Teiid View and right click select the Execute VDB action there This will create a Teiid Connection Profile specific for that VDB open the Database Development perspective and create a connection to your VDB Continue with Step s 2 and 3 above 2 1 Chapter 9 Testing Your Models Ho Instance There is no connected default Teiid instance available Create new Teiid instance or reconnect to existing Teiid and retry Deploy VDB Figure 9 14 No Teiid Instance Defined 9 3 4 Executing a Deployed VDB To execute a VDB that s been deployed manually follow the steps below o execute a VDB that s been deployed manually follow the steps below Step 1 Open the Database Development perspective Step 2 Select the Dat
82. DBC Source v amp Create Teiid Model Project z Create JDBC connection Create source model for DBC data source Preview Data Create VDB Execute VDB Description no action selected gt Cheat Sheets Cheat sheets for Teiid Designer use cases 2 Create Model from JDBC Source 2 Create Model from Flat File Source 2 Consume a SOAP Web Service 2 Create Model from XML Local File Source 2 Create Model from XML Remote Source 2 Create multi source VDB Create and test a VDB Figure D 36 Guides View The upper Action Sets section provides categorized sets of actions Select the desired category in the dropdown then the related actions for the selected category are displayed in the list below it Execute an action by clicking the Execute selected action link or double clicking on the action 341 Appendix D Teiid Designer Ui The lower Cheat Sheets section provides a list of available Cheat Sheet links which will launch the appropriate Cheat Sheet to guide you step by step through the selected process D 2 14 Status View To open Teiid Designer s Status view select the main menu s Window Show View Other and select the Teiid Designer Status view in the dialog The Status view provides a quick overview status of the selected project A sample Status view for a project is shown below A Guides 1 Status 23 F Project Status Enable Lnange project Project Test
83. Editor 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 358 Model Editor Base Tables FY Columns Location Name Name In Source System Cardinality Supports Up Materialized Logical Relat PARTS PARTS false 16 pue Talse SHIP VIA SHIP VIA false 3 true false STATUS STATUS false 3 true false SUPPLIER SUPPLIER false 16 true false SUPPLIER SUPPLIER PARTS i false 227 true false Package Diagram 5 Table Editor Figure D 48 Table Editor Example 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 e Paste information from your clipboard into the table Print your tables When Table Editor is in focus the Insert Table Rows action um 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 B T Paste Paste common spreadsheet data
84. Flat File Column Format Definition Press the Next gt button to continue Selected Data Hle EmplayeeData txt File Preview Options Number of lines in file 52 Number of preview lines 20 Select Column Format Character delimited Fixed width File Contents Preview LastName FirstName MiddleName EmpId Department AnnualSalary Title HomePhone Ma Kisselmeyer Abbiegale Tikvica 9000059 G 64000 00 MGMT WannaBe 670 270 7947 90 Glore Diodie Vojvoda 9000060 G 71000 Associate 480 650 9750 9000073 127 State Dawson Pinckney Ostoja 9000061 G 71000 Associate 110 400 3600 9000073 135 Sta Waldrip Trixie Curic 9000062 G 57000 Newble 820 710 7045 9000073 136 State St Kitchen 21 1 1 9000063 G 60000 MGMT WannaBe 660 390 3785 9000073 138 Sta Wakeman Gerard Vlahovic 9000064 G 78000 Newbie 700 190 5880 9000073 130 State Rafferty Dock Korda 9000065 G 70000 Newbie 400 190 6192 9000073 128 State St Kersavage Zelda Mjesecevic 9000066 G 56000 MGMT WannaBe 802 930 1482 9000073 zZummer Gerda Milan 9000067 G 69000 Newb1e 920 100 9701 9000073 131 State 51 Davies Allwyne Radic 9000068 G 61000 Associate 470 820 6096 9000073 126 State Deanford Abe Skrabalo 9000069 G 67000 Associate 907 660 8233 9000073 124 Stat Garcia Orsal Ucovic 9000070 ML 79000 CFO 480 490 7710 9000075 150 State St Pl Zook Orson Bendevis
85. Format e Section 4 7 2 Import Relational Tables CSV Format e Section 4 7 3 Import Relational View Tables CSV Format e 4 7 1 Import Relational Model XML Format e To create relational tables from imported xml text file metadata e Perform Steps 1 through 3 above and select the Relational Model XML Format import type then click Next gt m Chapter 4 Importers E Import Metadata From Text File Select Import Type Select the type of text file import desired Import Type Relational Model XML Format Purpose Imports relational tables views procedures and indexes from an XML Hle into a relational source model Sample Ale Format lt re lational model gt lt table name ProductData namelnSource dbo products ProductData description2 Basic stocks or bond cardinality 100 materialized FALSE suppa column name e ProductiD description2 Unique ID for this product datatype varcl column name ProductName datatype varchar length 60 nullable NULLABLE lt primary key name PK ProductlD namelnSource dbo products ProductData PK P column name PraductiD columnz lt primarny key gt lt table gt lt table name ProductSymbols namelnSource dbo products ProductSymbols cardin column nam ProductiD description Unique ID for this product datatype varcl column name Symboldtype datatype numeric length 10 radix 10 precisio lt foreign key name FK_ProductiD uniq
86. L 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 gt Show View gt 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 22 Model a JDBC Source A Guides x i Modeling Actions Action Sets Model JDBC Source v amp Define Teiid Model Project amp Create JDBC connection Create source model for JDBC data source Preview Data g Define VDB Execute VDB Description no action selected Cheat Sheets Cheat sheets for Teiid Designer use cases Create Model from JDBC Source 2 Create Model from Flat File Source 2 Consume a SOAP Web Service 2 Create Model from XML Local File Source Figure 2 4 Guides View 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 23 Chapter 2 Dive Right In New Model Project New Model Project Create a new model project Projectname mro Use default location Location home mdrillin Runtime Workspaces 7_7_x MyProject Browse
87. NSTR 101 amp ProductsOracle PRODUCTDATA INSTR ID Expression Editors Attribute 5 Constant Function Value is unchanged Category PRODUCTSORACLE Function Argument Name Value PRODUCTID ProductsOracle PRODUCTDATA INSTR_ID Cancel Figure 6 4 Source Function In Expression Builder 6 1 2 Create Relational Table Wizard In Teiid Designer 7 6 a new action New Child gt 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 208 Create Relational Table Wizard New Child Base Table Modeling Index Procedure Open Open With Copy face Paste Ctrl V x Custom Diagram Paste Special Ctrl V ry Figure 6 5 New Base Table Wizard Action 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 The first tab labeled Properties contains the input for the simple table properties including name name in source cardinality supports up
88. Press the Finish button to finish PartsViewTable Figure 4 27 View Model Definition Page 4 6 Import From Salesforce e You create relational source models from your Salesforce connection using the steps below 105 Chapter 4 Importers e Step 1 In Model Explorer choose the File gt Import action py in the toolbar or select a project folder or model in the tree and choose Import e Step 2 Select the import option Teiid Designer gt Salesforce gt gt Source Model and click Next gt e Step 3 Select existing or previous connection profile from the drop down selector or press New button to launch the New Connection Profile dialog See Eclispe Data Tools documentation or Edit to modify change an existing connection profile prior to selection i Note The Connection Profile selection list will be populated with only Salesforce connection profiles Create Relational Model from SalesForce Data Model SalesForce Credentials Validate the SalesForce Connection Profile Connection Profile 1 New edit Properties URL lt default gt User Name joeBlow bogus ortg Password Er Figure 4 28 Select Salesforce Credentials Dialog Step 4 After selecting a Connection Profile input password if not provided Press Next gt to display the Salesforce Objects selection page 106 Import From Salesfor
89. Proj V Source Connections Vl Sources t XML Schema Views VDBs 4 Model validation OFF i Test Figure D 37 Status View The status view is broken down into common project areas Source Connections all Source Connections are fully defined Sources Source Models exist XML Schema XML Schemas exist Views View Models exist VDBs VDBs exist and are deployable Model Validation Status all Models pass validation Test all defined VDBs pass validation 342 Cheat Sheets View The status of each area is denoted by an icon A green check indicates OK a red x indicates errors and a warning icon indicates potential problems The project can be changed by selecting the Change Project button D 2 15 Cheat Sheets View To open Cheat Sheets view select the main menu s Window Show View Other and select the Help Cheat Sheets view in the dialog The Cheat Sheets view is a standard Eclipse Help concept Cheat Sheets provide step by step assistance for common process workflows Teiid Designer has contributed to the Eclipse help framework to provide assistance for many common modeling tasks The Guides View see Section D 2 13 Guides View provides links to these Cheat Sheets as previously described A sample Cheat Sheet is shown below Meuse EP gn Create Model from JDBC Source v Introduction This cheat sheet shows you how to create a model from a JDBC sour
90. R PARTS SHIPPER ID PartsSourceB SUPPLIER FY PartsSourceB SUPPLIER SUPPLIER ID E PartsSourceB SUPPLIER SUPPLIER NAME FY PartsSourceB SUPPLIER SUPPLIER STATUS FA PartsSourceB SUPPLIER SUPPLIER CITY FA PartsSourceB SUPPL Figure 5 25 Editing String Property The two tabs at the top Tree View and SQL View show the current contents of the criteria you have built The Criteria Editor at the bottom allows you to build a criteria clause To build a criteria clause you must add information to the left side of the predicate select a comparison operator and add a value to the right side Step 3 The radio buttons on either side of the Predicate Editor let you choose what type of content to place in that side of your predicate Click the radio button of the type of content you want to place in your criteria You can click 177 Chapter 5 Creating and Editi Attribute to add an attribute to the predicate If you click the Attribute radio button the Predicate Editor looks like this FA PartsSourceA SUPPLIER PARTS PART ID FY PartsSourceA SUPPLIER PARTS QUANTITY FA PartsSourceA SUPPLIER PARTS SHIPPER ID v PartsSourceB SUPPLIER FA PartsSourceB SUPPLIER SUPPLIER ID FA PartsSourceB SUPPLIER SUPPLIER NAME FA PartsSourceB SUPPLIER SUPPLIER STATUS FA PartsSourceB SUPPLIER SUPPLIER CITY Figure 5 26 Attribute Panel From the tree select the attribute you want to add to the expression You ca
91. Relational Model from Web Service Press the Next gt button to continue Connection CountryInfoConn v WSDL URL or Location http www oorsprong org websamples countryinfo CountryInfoService wso WSDL Select Port CountryInfoServiceSoap Binding SOAP11 Service Mode PAYLOAD Select the desired WSDL Operations Operation CapitalCity E CountriesUsingCurrency E CountryCurrency amp CountrvFlaa Selection Details Operation CapitalCity binding CountryInfoServiceSoapBinding port CountryInfoServiceSoap service CountryInfoService lt Back Next gt Cancel Figure 2 27 Consume SOAP Wizard 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 45 Chapter 2 Dive Right In rm Create Relational Model from Web Service Models Definition All inputs OK Click Next to define custom procedures amp ource Model Definition Location SOAPProj sources Name CountryInfoService xmi Status Source model CountryInfoService
92. S Edit Refactor Navigate Search Project Metadata Run Window Help New Shift Alt N gt Teiid Model Project Open File Project Close Ctrl W C Folder Close All shitt Ctri W Hh Teiid Metadata Model 9 Teiid VDB Example F3 Other Ctrl N Figure D 70 File Menu he File menu contains the following actions 3 6 S New gt Model Project Create user a new model project New gt Folder Create new folder within existing project or folder E New Model Create a new model of a specified model type and class using the Chapter 3 New Model Wizards TF M New gt 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 E Save Ctrl S Saves the contents of the active editor File Menu 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 gt Move resource dialog Rename F2 Launches a Refactor gt Rename resource dialog if resource selected else in line rename is
93. S xmi LJ vi Multi source amp f Add Column Column Alias theSource Name Add Source Name Translator Name JNDI Delete products oracle oracle puso Praducts55 sglserver i Products55 Synchronize All Figure 8 2 VDB Editor s Source Binding Definition 8 5 UDF support In Teiid Designer you can create manage and use User Defined Functions UDFs These functions allow you to perform simple or complex java operations on your data during runtime This is accomplished by deploying your custom UDF jars on your server and creating a scalar function representation of your function method to use in your view transformation In the VDB Editor you have the option of including your UDF jars as part of the VDB artifact If included in the VDB the jars will automatically be deployed to the server for you when the VDB is deployed The figure below illustrates a sample project setup which includes a UDF Jar in a lib folder under a project Ld ProductsExample YahooUDFTest v lib TestUDF jar Customer Accounts xmi gt Sg MarketData xmi gt 95 MyPortfolio xmi b Ga Products xmi YahooUDF xmi import declarations 0 Package Diagram ConcatNull YahooVDB vdb Figure 8 3 UDF Jar In Project 251 Chapter 8 Managing VDBs This figure shows a VDB containing the several models including the user s Function model Ca Customer Accounts xmi Ca J YahooVDB vdb Z3 Y
94. T WEIGHT 12 VAR vw PARTS SUPPLIER TABLE SHIP VIA STATUS SUPPLIER SUPPLIER PARTS Show Only Selected Schemas lt Back Next gt Cancel Finish Figure 4 7 Select Database Options Dialog e Step 7 On the Specify Import Options page specify desired Model Name as well as any other options used to customize the naming of your relational objects Press Finish to complete 78 Import From JDBC Database Import Database via JDBC Specify Import Options Specify the name location and import options for the new relational model of database PartsOraclell xmi Relational Model Definition Model Name partsOraclell xmi Into Folder Parts Test Update if existing model selected Include Catalog For Fully Qualified Names Model Object Names Tables Procedures Columns etc Use Fully Qualified Names Example partssupplier dbo PARTS Change Case For All Characters Case Options Make All Upper Case Example Suppliers gt SUPPLIERS Make All Lower Case Example SUPPLIERS gt suppliers Cancel Anish Figure 4 8 Specify Import Options Dialog During the Finish processing a monitor will be displayed providing feedback on the import progress 19 Chapter 4 Importers Progress Information Importing from JDBC database PartsOraclell FOF Processing database object 1 of 5 creating BaseTable PARTS Figure 4 9 JDBC I
95. TS PART ID PartsSourceB PARTS PART NAME PartsSourceB PARTS PART COLOR PartsSourceB PARTS PART WEIGHT VALUES INPUTS PART ID INPUTS PART NAME INPUTS PART COLOR INPUTS PART WEIGHT END Figure 10 5 Insert SQL Example If you wish to view the selected object and its SQL in a Model Editor you can click the Edit button An editor will be opened if not already open If an editor is open its tab will be selected In addition the Transformation Editor will be opened and you can perform Find Replace Ctrl F actions to highlight your original searched text string and edit your SQL if you wish 10 3 Search Models Via Metadata Properties The Teiid Designer provides a search capability to find model objects that are characterized by one or more metadata property values 283 Chapter 10 Searching To search your models using metadata e Step 1 Select Search gt Metadata action on the main Teiid Designer toolbar F which opens the Search dialog Search Object Type Any Object Class Data Type Any Simple Type Include subtypes Runtime Type bigdecimal PSOE Where Name Contains Does NOT Contain Exact Match Search Text case insensitive any string any character Scope Workspace Selected resources Customize Cancel Search Figure 10 6 Metadata Search Dialog e Step 2 Specify desired search options for Object Type Data Type and P
96. Teiid Designer User Guide 1 8 1 0 EE WPNEROGUICHIOIN 1 1 1 Whats Designer Caf vodka 1 1 2 Metadata OVEIWICW 2 Whati Metadata ET 2 1 2 2 Business and Technical Metadata 4 1 2 3 Design Time and Runtime Metadata 5 1 2 4 Source and View Metadata 6 1 3 It s all in the Modeling 10 je x NOOS rq 10 1 3 2 How is a Model Defined nemen nenne 11 1 3 3 Guiding through the process 12 1 3 4 Targeting the Right Teiid Server 12 1 3 5 Model Classes and Types 13 The Nini Database TTE 13 13 7 MOEI Validator ne ER AEAEE TRE 15 1 3 8 Testing Your Models 15 1 9 9 Model Object Extensi n S 2 EE 16 UM gud ge 19 2 1 Targeting the Teiid Server ssssssssssssssssesseneeee nne 19 2 11 Server Version PrelererleO acecepbore
97. 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 42 Consume a SOAP Web Service E New connection profile Web Service Connection Properties Click Next or Finish Properties Profile Name CountryInfoConn Profile Description Connection URL or File Path http www oorsprong org websamples countryinfo CountryInfoService Workspace File System Description The URL defined for this connection profile provides the hook to inject the Endpoint property into model via the Modeling gt Set Connection Profile action This Endpoint value is required by the translator see Import gt Teiid Designer gt WSDL File or URL gt gt Source and View Model SOAP Back Next gt _ Figure 2 25 SOAP Connection Properties 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 CS to advance to the next step Create Models from SOAP Connection This section of the Cheat Sheet provides instructions for creating relational models using the previously created connection profile
98. The XML structure of your file will be presented to ald in selecting data elements to define your table column dat Select XML Import Mode XML file on local file system CJ XML file via remote URL 2 lt Back Figure 4 24 XML Import File Options Step 4 Select existing or previous connection profile from the drop down selector or press New button to launch the New Connection Profile dialog See Eclispe Data Tools documentation or Edit to modify change an existing connection profile prior to selection After selecting a Connection Profile the XML data file from the connection profile will be displayed in the Available Data Files panel Check the the data file you wish to process The data from this file along with your custom import options will be used to construct a view table containing the required SQL transformation for retrieving your data and returning a result set Lastly enter or unique source model name in the Source Model Definition section at the bottom of the page or select an existing source model using the browse button 101 Chapter 4 Importers i Note The Model Status section which will indicate the validity of the model name whether the model exists or not and whether the model already contains the getTextFiles procedure In this case the source model nor the procedure will be generated When finished with this page click Next gt Import From XML File Source XML Data Fi
99. ____ 0 l0 L lbG b b b l UU 0o t Diagram Table Editor Figure D 47 Mapping Transformation Diagram Example Mapping Transformation Diagram toolbar actions include G9 Refresh Diagram Re draws diagram Show Parent Diagram Navigates to diagram for parent object if available Preview Data Executes a simple preview query SELECT FROM B New Mapping Link Create a mapping link between selected mapping extent i e XML element or attribute and mapping class column Remove Mapping Link Delete mapping link between selected mapping extent i e XML element or attribute and mapping class column E E Add Transformation Sources Add selected sources to transformation iB Add Union Transformation Sources Add selected sources as union sources 35 Appendix D Teiid Designer Ui 124 en Remove Transformation Sources Removed sources selected in diagram from transformation s Clear Transformation Remove all sources from transformation El gt Open Transformation Reconciler dialog Save Diagram as Image Save the diagram image to file in JPG or BMP format Modify Diagram Printing Preferences Modify page layout information for printing diagrams Includes margins orientation etc Context menus for Mapping Transformation Diagrams identical capabilities to the Transformation Diagram with the addition of managing and editing Input Sets D 3 1 2 Table
100. a deployed Data Source When creating VDBs you do not need to have deployed data sources on your Teiid server but if you wish to test your VDB the data sources need to be present 268 Creating Data Sources Teiid Designer provides a Create Data Source action so you can create compatible data sources for your source model If you wish to create a data source for a specific model you can select that source model in your workspace and select the Modeling gt Create Data Source action This will extract the connection profile data from your source model and create a corresponding data source on your default Teiid server You can also create data sources from the Servers view Select a Teiid server instance in the Servers view and right click select the Create Data Source action This will launch the Create Data Source Dialog shown below Create Data Source Create Data Source All inputs are valid Select Anish to create data source Teiid Server mmc lacalhast 31443 Name BooksDB2 Connection Source Use Model Connection Into Model M Use Connection Profile Info Connection Prafile BooksDB Connection Properties Name Value password user name books connection url jdbc db2 dbO00255 org mydbs com 50000 driver class com ibm db2 jcc DB2Driver Figure 9 10 Create Data Source Dialog You can either select and existing Connection Profile from
101. abase Connections folder and choose the New action to display the New Connection Profile dialog 2 2 Executing a Deployed VDB New Connection Profile Connection Profile Create a Teiid connection profile Connection Profile Types type filter text Ingres ModeShape MysQL Oracle PostgreSQL SQL Server SOLite ES Sybase ASA ES Sybase ASE Name Products Cancel Finish Figure 9 15 New Connection Profile Dialog e Step Enter unique name for your profile select an existing connection profile type and hit Next Step 4 In the Teiid Profile Wizard page select the New Driver Definition button to locate and select the Teiid client jar on your file system Configure your URL using your VDB Name Host Port Username default admin and Password default teiid 2 3 Chapter 9 Testing Your Models Tetid Profile Wizard Specify a Driver and Connection Details Select a driver from the drop down and provide login details for the connection Drivers Teiid Server JDBC Driver Properties General VDB Mame Products 31000 Username admin Password SSL Connection ee ee ee Oe Connect when the wizard completes Test Connection Connect every time the workbench is started lt Back Cancel Figure 9 16 Teiid Connection Profile Dialog e Step 5 Select Next to view a summary of your new Teiid Connection Profile 2 4 Executing a Deploye
102. able s supports update property to false If this is unchecked the default value will be true 3 3 Creating XML Document View Model Create XML Document View Model e create a new empty XML document view model e Step 1 Launch the New Model Wizard Step 2 Specify a unique model name Step 3 Select XML option from Model Class drop down menu e Step 4 Select View Model from Model Type drop down menu e Step 5 Click Finish i 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 3 3 1 Copy 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 59 Chapter 3 New Model Wizards e To create a new relational model by copying contents from another XML document view model complete Create XML Document View Model above and continue with these additional steps e Step 5 Select the model builder labeled Copy from existing model of the same model class and click Next The Copy Existing Model dialog will be displayed e Step 6 Select an existing relational model from the wor
103. adata Ale to Import and Destination Model or model folder Select Source Text Ale br Release 7 6 planning SampleRelatianalXML xmll Target Location ProductsFromXMLText xmi Browse Hle Contents lt xml version 1 0 encoding UTF 8 gt lt relational model name myRelationalModel xmins http www teiid org xsi schemaLocation http www teiid org file home blafond Documents Teiid Designer Rele lt table name ProductData namelnSource dbo products ProductData description Basic stocks or bond data table amp second line amp 3 xa amp 3 x9 amp 3 x cardinality 100 materialized true supportsUpdate true system true gt lt column name ProductlD description2 Unique ID for this product namelnSource dbo products ProductData ProductiD datatype varchar length 11 nullable NO NULLS gt lt gt lt column name ProductName namelnSource dbo products ProductData Produ datatype varchar length 60 nullable NULLABLE gt lt colurmnm gt column name Productlype namelnSource dbo products ProductData Product datatype varchar length2 15 nullable NULLABLE gt lt column gt T name i Figure 4 35 Select Source Text File and Target Relational Model Page If the target model contains named children tables views procedures that conflict with the objects being imported a dialog will be displayed giving you options on how to proceed including
104. ahooUDF xmi Ci Model Details Source Binding Definition Models UDF Jars Other Files Model Gig Customer Accounts xmi Yal pem Gig MarketData xmi D GA MyPortfolio xmi Location YahooUDFTest Gig Products xmi YahooUDF xmi Figure 8 4 VDB Models When a UDF model is added to a VDB each scalar function is interrogated and it s referenced UDF jar if available is added to the VDB as well as shown in the UDF Jars tab in the editor as shown below YahooVDB vdb 3 YahooUDF xmi Lg MarketData xr File Location in VDB Description TestUDFjar lib Figure 8 5 UDF Jars in VDB 8 6 Reusing VDBs Teiid 8 1 introduced the ability to treat your deployed VDB as just another database where the database category is your VDB name and each visible model in your VDB is treated as a schema 252 Reusing VDBs This is accomplished via a new lt import vdb gt element in the vdb xml definition see Teiid VDB Reuse section https docs jooss org author display TEIID VDB Reuse By allowing VDB s to referenced other VDBs users can create reusable database components and reduce the amount of modeling required to create complex transformations The sample vdb xml file below highlights the import vdb element and the corresponding import vdb reference within the view mode
105. alog is accessible from the Reconciler dialog See Section 5 3 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 174 Transformation Editor Resolve Datatype Conflicts Datatype Status One more of the Bindings has type conflict Attribute SQL Bindings Virtual Target Attributes SOL Symbol SHIPPER ID biginteger SHIPPER ID bigdecimal EDIT Virtual Target Attribute SHIPPER ID Runtime Type n biginteger integer xs decimal SQL Symbol SHIPPER ID Runtime Type bigdecimal Converted Symbol convert SHIPPER ID biginteger AS SHIPPER ID Warning this type conversion may result in loss of precision Figure 5 24 Datatype Resolver Dialog 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 175 Chapter 5 Creating and Editi Virtual Target Attribute Resolve the type conflict by changing the target attribute type to be compatible with the SQL Symbol type The attribute s
106. at object From the pop up menu select New Sibling You can now select the sibling object you would like to add New Child New Sibling Al Access Pattern New Association Column Modeling gt F Foreign Key lt 2 Undo Delete multiple objects Fi Primary Key Unique Constraint Figure 5 7 New Sibling Action In Diagram e Step 3 The new model object displays on the diagram and is highlighted for renaming To create new sibling model objects in the Section D 3 1 2 Table Editor Step 1 Select the row for the object to which you want to add a sibling For example you can add a column sibling to a column e Step 2 Right click on a row From the pop up menu select New Sibling You can now select the sibling object you would like to add 155 Chapter 5 Creating and Editi PartsDB2 xmi 3 PartsViews xmi F Foreign Keys F Primary Keys baraton Mame Name In Source system Cardinality Supports Up Materialized PARTS PARTS SHIP VIA SHIP VIA STATUS STATUS SUPPLIER SUPPLIER SUPPLIER PARTS SUPPLIER Base Tables FY Columns false n eese mae gt gg Catalog Modeling gt Index New Child New Sibling Mew Association L Logical Relationshi E Procedure a Undo Delete multiple objects Redo Gs Schema Copy Ctrl C Custom Diagram Table Paste Figure 5 8 New Sibling Action In Table Editor St
107. 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 Eh e 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 3 1 1 Using the Reconciler for more information about the Reconciler Dialog Vs 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 169 Chapter 5 Creating and Editi 170 E 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 3 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
108. book rt click then select Teiid 7 x Get Execution Plan 2 6 Executing a Deployed VDB SOL Results 4 Teiid Execution Plan N Preview Data for myTbl SOL select from vModel myTbl Execution Plan ST _ ES FFSFSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS C Text Plan Tree lt xml version 1 0 encoding UTF 8 standalone yes gt lt node name AccessNode gt lt property name Output Columns gt value INTKEY integer lt value gt lt Value gt STRINGKEY string lt value gt value INTNUM integer lt value gt lt value gt STRINGNUM string lt value gt lt Value gt FLOATNUM double lt value gt lt Value gt LONGNUM long lt value gt lt Value gt DOUBLENUM double lt value gt arrra IRA hai erda a MES aarm la eam Figure 9 20 Teiid Execution Plan View 277 2 8 Chapter 10 Searching Designer provides multiple search actions located via Teiid Designer sub menu in Eclipses Search menu Search menu Search Ctrl H El File EP Remote Pointcut Matches S Beans Text gt Teiid Designer gt Figure 10 1 Search Options The individual actions in the Teiid Designer sub menu are described below Transformations Launches the Transformation Search dialog User can search models in the workspace for matching SQL text Search results appear in the dialog and user can select and vi
109. but just very deep so in such a case it is possible to increase the depth limit by setting the JVM property WsdlSchemaHandlerRecursiveDepth to a larger value eg D WsdlSchemaHandlerRecursiveDepth 800 This should only be used with caution as on some systems it is possible the JVM throws a StackOverFlow exception before the new depth limit is reached X C Warning 2 G9 Detected possible circular references in the response schema for one or more of the selected operations which may adversely affect the generated transformations If desired you may use the Default Procedures option instead This warning was triggered at a recursive depth of 750 If you know this was not a circular reference the recursive depth can be increased by setting the following java property and restarting DWsdlschemaHandlerRecursiveDepth Figure 4 45 Warning message displayed if depth limit has been reached 4 9 Import WSDL Into Web Service 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 here are three options for selecting the WSDL for your Web Service generation e Workspace Location File System Locatio
110. cccccec ccc eceec cence cence eceeeeeceeeeseeseceeeeseeeeseeeeaeeeeaeaes 383 AO RUN MENU ge 383 D 4 9 Window Menu cccccccececeeeesececeeeeececeeceaeseseseeaeseseseeaeseseseeaeaesesenaes 384 D 4 10 Help Menu sees menn nnne nennen nnn nnns 385 vi Chapter 1 Introduction The Teiid Designer User s Guide provides detailed descriptions of Teiid Designer features and functionality 1 1 What is Teiid Designer Teiid Designer is an Eclipse based graphical modeling tool for modeling analyzing integrating and testing multiple data sources to produce Relational XML and Web Service Views that expose your business data X Teid Designer US ale Device xir Fie Eni Refactor Mevigate SegrcF Broject Metacata Help vB E nm v tO Bee Se Se owl 1009 v a EA LZ lode Fwol m F Qutlne iy Project DI f stateServeevernuri t Fi awe BIA F 28115 Stes E deploy edm Peace 5ace5ervice ds xmi zzaceSe vice xri xmi E Import declarations 35 amp Diagram gt AL GerAllState ite regdes A GetAlStete no resparse I Getc lState ac R GetStatel ate requess bd et Statal io response Gl GetStatel ao Bi test sey H LSStates vcb Default Telid Server Server localhost Ergeted Version 8 2 0 F mal a Deszzrlatian jm oU rn
111. ce Create Relational Model from SalesForce Data Model Select the SalesForce objects to Model Press the Next gt button to continue SalesForce Objects Column Details Account Visible Name Name in Source Type Searchable Updatal Account Partner Account ID Id id True False Account Share Deleted IsDeleted boolean True False Activity History Master Record IE MasterRecordld reference True False Additional Directo Account Name Name string True True Apex Class Account Type Type picklist True True Apex Trigger Parent Account Parentid reference True True Approval Billing Street Billing Street textarea True True Approval Request Billing City BillingCity string True True Asset Billing State Prot BillingState string True True Assignment Rule Billing Zip Po sta BillingPostalCod string True True Attachment Billing Country i BillingCountry string True True Business Hours Shipping Street ShippingStreet textarea True Tue Select All Deselect All Cancel Figure 4 29 Select Salesforce Objects Dialog e Step 5 On the Target Model Selection page specify the target folder location for your generated model a unique model name and select desired import options Press Next or Finish if enabled 107 Chapter 4 Importers Create Relational Model from SalesForce Data Model x E Target Model Selection Press the Next gt b
112. ce 48 Click to Restart Create New Teiid Model Project Follow the steps below to create a Teiid Model Project Launch Mew Teiid Model Project Wizard pu 1 Specify unique project name vt d 2 Click Next gt twice to get to Model Project Options page 3 Check folders you wish the wizard to create in your project 4 Click Finish Create New JDBC Source Connection Create Source Model From JDBC Connection Test JDBC Source Data Create VDB t Test VDB Figure D 38 Cheat Sheet Sample 343 Appendix D Teiid Designer Ui D 3 Editors Editors are the Ul 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 Figure D 39 Editor Tabs 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 si
113. ch To use the Expression Builder e Step 1 In the Transformation Editor click the location where you want to insert the function e Step 2 Click the Expression Builder button 29 The SQL Expression Builder displays 180 Transformation Editor Jm Expression Builder Initial Expression lt undefined gt ITALI SOL View 1 undefined PartsSourceA SUPPLIER PARTS FY PartsSourceA SUPPLIER PARTS SUPPLIER ID EY PartsSourceA SUPPLIER PARTS PART ID FA PartsSourceA SUPPLIER PARTS QUANTITY FA PartsSourceA SUPPLIER PARTS SHIPPER ID gt 8 PartsSourceB SUPPLIER Figure 5 29 Expression Builder The two tabs at the top Tree View and SQL View show the current contents of the expression you have built To build an expression you must specify the type of expression you want to build and populate it In most cases you will use the Expression Builder to construct a complex expression e Step 3 Click the Function radio button to add a function e Step 4 The Expression Editor displays the Function editor 181 Chapter 5 Creating and Editi Expression Builder Initial Expression X undefined SOL View gt Expression Editors Attribute Constant amp Function Apply to replace selected undefinedz with Function below Category STRING Function f Argument Mame Value STRINGL undefined STRING2 undefined Cancel
114. ck the Parse Selected Row button A dialog will be displayed showing the list of columns and the resulting column data If your column data is not what you expected you ll need to adjust your settings accordingly Parsed Column Data The following list contains the parsed column data from the selected line Parsed Column Data lastName Glore ArstName Diodie MiddleMame Vojvoda Empld 9000060 Department G AnnualSalary 71000 Title Associate HomePhone 480 650 9750 Mgrld 9000073 Street 177 State St City Los Angeles state CA ZipCode 10005 Figure 4 18 Parse Column Data Dialog Step 5b Fixed Column Width Option The primary purpose of this importer is to help you create a view table containing the transformation required to query the user defined data file This page presents a number of options you can use to customize the Generated SQL Statement shown in the bottom panel for the fixed column width option Specify header options Column names in header header line number and first data line number Parse selected row changed character delimiter and edit the TEXTTABLE function options See the Teiid User s Guide for details on the TEXTTABLE function If columns names are not defined in a file header or if you wish to modify or create custom columns you can use the ADD DELETE UP DOWN to manage the column info in your SQL You can also utilize the curs
115. column The SQL Symbols are the symbols that are projected from the SQL transformation A symbo itis displayed next to the attribute If a target attribute is unbound its row is referred to as being bound to a target attribute when is highlighted in red The transformation is not valid until all attributes have a corresponding SQL symbol binding 172 Transformation Editor Here are a few things you can do in the table section Lock Target Attributes To lock the target attribute ordering check the Lock Target Attributes checkbox This will lock the attributes in place Re Order Attributes To change the ordering of the target attributes use the Top Up Swap Down and Bottom controls beneath the table Select or multi select the table rows then click the desired action button Delete Attributes To delete one or more of the target attributes select the table row s you want to delete then click the Delete button Resolve Types If an Attribute SQL Symbol binding has a datatype conflict a message will be displayed To assist in resolving the datatype conflict a Datatype Resolver Dialog is provided Click on the table row then click the Resolver button to display the dialog See Section 5 3 1 2 Using the Datatype Resolver for further information Unmatched SQL Symbols list This list is to the right of the attribute symbol binding table and shows the SQL symbols fr
116. connection sql gt some arbitrary sql lt check valid connection sql gt e Step 3 If you have not already done so you will need to create a data source for your VDB This can be done in the Teiid View of Designer Right click on your deployed VDB and select Create Data Source The Generate REST WAR dialog will ask you for the JNDI name for your created source so that it can connect to your VDB Step 4 Right click on the VDB containing your virtual model s with REST eligible procedures and select the Modeling gt Generate RESTEasy War action If there are no procedures that are REST eligible the Generate RESTEasy War option will not be enabled kg DODKS XITII JDOOKRSISES IVV M New b gt oo xmi Book AESTW Modeling 2 Deploy m e Execute VDB Ta Open With p Generate JBossWS CXF War C su s sd ara Step 5 Fill in missing properties in the REST War Generation Wizard shown below 236 Web Services War Generation Create REST WAR File Create a REST WAR file to deploy as a REST Web Service Enter the required information then click OK to create the WAR file REST WAR Creation Information Context Name BooksREST VDB JNDI Name REPLACE WITH VDB JNDI NAME REST WAR File Save Location home tejones Runtime Workspaces WARFiles Change Include RESTEasy Jars in lib Folder of WAR Restore Default Figure 6 28 Generate a
117. create the WAR file WAR Creation Information Context Name PartfolioVDB Web Server Host localhost Web Server Port 8080 VDB JNDI Name PortfolioVDB Security When using HTTPBasic security a local Teiid connection is required i None HTTPBasic WS Security Username Token HTTPBasic Options Realm Role W5 5Security Options Username Password General Options _ Enable MTOM Target namespace http teiid org WAR File Save Location Users macuser WARFiles Change Chapter 6 Metadata specific Table 6 1 Field Descriptions Field Name Description Name The name of the generated war file Host The server host name or IP Port The server port VDB JNDI Name The JNDI connection name to the deployed Teiid source VDB Security options e None no username password required to connect to the VDB through the generated web service e HTTP Basic the specified security realm and role will be used The default realm value is the realm that comes out of the box with Teiid telid security The role needs to be defined in the appropriate security mechanism In the case of Teiid use the teiid security roles properties file When using HTTPBasic a local Teiid connection using the PassthroughAuthentication property is required See the Teiid user s manual for details on PassthroughAuthentication WS Security a password callback class
118. creation and editing of basic relational columns This includes adding deleting or moving columns as well as changing the name datatype and length properties 219 Chapter 6 Metadata specific Create Relational View Table Create Relational View Table ib No columns defined for table This wizard allows creating a complete relational view table including SQL definition Properties Columns SQL Jp Move Down Column Name Data Type Length Figure 6 15 Columns Tab 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 220 Create Relational View Table Wizard Create Relational View Table Create Relational View Table b No columns defined for table This wizard allows creating a complete relational view table including SOL definition Columns Start with a SQL Template or enter the desired SQL Templates Transformation SQL SELECT TABLEA COL1 TABLEA COL2 TABLEB COL1 FROM TABLEA TABLEB WHERE TABLEA COL1 TABLEB COL1 Figure 6 16 SQL Tab If the Templates button is selected on the SQL tab the Choose a SQL Template dialog will display as shown below 221 Chapter 6 Metadata specific Choose a SQL Template Choose a SQL Template Choose a SQL Template from the available o
119. cription KEY API FullName String See the KeyRecord See Table NamelnSource String ColumnIDs Collection UniqueKeyID String Table B 9 Data Usage for Data Types DATA TYPE In Metadata API In Designer Type Required Description FullName String No Only used for System metadata NamelnSource String No Only used for System metadata Length Integer No Only used for System metadata 293 Appendix B Designer Metadata DATA TYPE Type In Designer Metadata Required Description API PrecisionLengthlnteger No Only used for System metadata Scale Integer No Only used for System metadata Radix Integer No Only used for System metadata IsSigned Boolean No Only used for System metadata IsAutolncremenBoolean No Only used for System metadata IsCaseSensitiveBoolean No Only used for System metadata Type Integer No Only used for System metadata SearchType Integer No Only used for System metadata NullType Integer No Only used for System metadata JavaClassNameString Yes Maps to runtime type based on java class name Runtime No Only used for System metadata DatatypelD String No Only used for System metadata 294 DATA TYPE Type In Designer In Metadata Required API Description BaseTypelD String No
120. ct for Telid Designer the VDB or Virtual DataBase This section describes the details of how to create edit deploy and test your VDBs 8 1 Creating a VDB To create an empty VDB launch Eclipse s New wizard open the Teiid Designer category folder and select Teiid VDB You can also select one or more models in a model project right click and select New gt Teiid VDB action Launching this wizard will open the New VDB dialog If you launched with one or more models selected the dialog will contain the pre selected models for inclusion in the new VDB New VDE New Press the Anish button to finish In Falder Baoks P raj ect ja Description Join of two books sources i Selected Models amp BooksView xmi ig Books SourceA xmi emove g Books SourceB xmi Cancel Figure 8 1 New VDB Dialog 249 Chapter 8 Managing VDBs i Note A VDB is scoped to be aware of models and files within the same model project as the VDB You will not be allowed to add models to a VDB that exist in a different project 8 2 Editing a VDB To Edit an existing VDB select the VDB in the explorer and right click select Open action or simply double click the VDB The VDB will be opened in a VDB Editor See the Section D 3 2 VDB Editor section 8 3 Test a VDB For details on how to test your VDB see Section 9 3 Testing With Your VDB section 8 4 Multi source Binding Support Designer now
121. cter any String Matching Model Objects PART COLOR E PART NAME El PART WEIGHT PARTS jfPartsProject PartsSourceA xmi PARTS PART COLOR PartsProject PartsSourceB xmi PARTS PART COLOR PartsProject PartsVirtual xmi PARTS PART COLOR E PartsProject PartsVirtual xmi parts olor NewProcedureRe Cancel Figure 10 3 Find Model Object Dialog e Step 2 Begin typing word or partial word in the Type Object Name field Wild card characters will be honored As you type the objects which match the desired name will be displayed in the Matching Model Objects list If there are more than one objects with the same name the locations or paths of the objects are displayed in the Locations list Step3 If more than one object exists with the desired name select the one of the locations Step4 Click OK If editor is not open for the object s model an editor will open The desired object should end up displayed in a diagram if applicable and selected 10 2 Search Transformation SQL The Teiid Designer provides a search capability to string values present in transformation SQL text To search for string values in your transformations SQL 281 Chapter 10 Searching Step 1 Select Search gt Transformations action on the Teiid Designer main menu Search Tranformations Input Case Sensitive Perform Search Search Status Search complete for Parts Virtual Tables View
122. ction 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 36 Cheat Sheet Example TestMySQL xmi B8 myVDB vdb E SQL Scrapbook0 T Connection profile SELECT FROM Accounts ACCOUNT 5 SQL Results 3 4f Teiid Execution Plan Type query expression here Status Result Status Operation Date ACCOUNT ID SSN STATUS V Succe select fro Jun 8 2012 FEEC Succe SELECT Jun 8 2012 19980003 CSTO1003 Personal 3 19980004 CSTO1004 Personal 4 19980005 CSTO1005 Personal 5 19980006 CSTO1006 Personal Figure 2 19 Execute VDB Example 2 3 Cheat Sheet 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 2 3 1 Consume a SOAP Web Service 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 ot
123. ctor Import Export Figure D 10 Sample Context Menu D 2 2 Outline View The Outline View is a utility view which provides both at tree view dedicated to a specific model open in an editor and a scaled thumbnail diagram representative of the diagram open in the corresponding Diagram Editor You can show the Outline View by clicking on its tab If there is no open editors the view indicates that Outline is not available If a Model Editor is open then the root of the displayed tree will be the model for the editor that is currently in focus in Teiid Designer tab on top D 2 2 1 Outline Tree View This tree view provides the same basic editing and navigation behavior as the Model Explorer One additional capability is the drag and drop feature which provides re ordering and re parenting of objects in a model 318 Outline View iz Model Explorer Outli arts Virtual xmi gt import declarations 4 Package Diagram D Supplierlnfo FA partsByColor Figure D 11 Outline View D 2 2 2 Outline Thumbnail View The Outline View also offers you a way to view a thumbnail sketch your diagram regardless of its size To view this diagram thumbnail from the Outline panel click the Diagram Overview button Figure D 12 Outline View The view contains a thumbnail of your entire diagram The shaded portion represents the portion visible in the Diagram Editor view 319 App
124. d VDB Profle Wizard Summary Information gathered from previous pages Propert Value Name Products Description Auto connect at startup false Auto connect on finish true Database Products Hast localhost Port 31000 Username admin SSL protocol false Save password 7 true URL jdbc teiid Products mm localhost 31000 Figure 9 17 Teiid Connection Profile Summary Step 6 Select Finish e Step 7 Select your new Teiid connection profile and right click select Open SQL Scrapbook enter your designer SQL i e SELECT FROM TableXXXX select all text and right click select Execute Selected Text 275 Chapter 9 Testing Your Models Parts vdb Parts Teiid Connecti Z Database Stat Figure 9 18 SQL Scrapbook Editor e Step 8 Results of query should be displayed in the SQL Results view on the Result1 tab SQL Results Zi ux query expression here Status Operation Date Connection Profile Succee SELECT FROM PartsSourceA Parts Oct 15 2010 Parts Teiid Connection PART ID PART NAME PART COLOR 1 P300 Nut Red 2 P301 Bolt Green 3 P302 Screw Blue 13 4 P303 Bolt Green 017 5 P304 Cam Green 18 Figure 9 19 SQL Results View Step 9 The query Execution Plan should also be displayed on the Teiid Execution Plan view tab The Execution Plan can also be generated without running the query In the SQL scrap
125. d text file metadata into models This is accomplished via the Import Teiid Designer Designer Text File gt gt Source or View Models option e Step 1 In Designer choose the File gt Import action py in the toolbar or select a project folder or model in the tree and choose Import Step2 Select the import option Teiid Designer gt Designer Text File gt gt Source or View Models and click Next Step 3 Select an import type via the drop down menu shown below 110 Accountld string 18 UserOrGroupld string 18 FK Account Account AccountAccessLevel string 40 OppartunityAccessLevel stringl40 ZERO TO MANT EY CaseAccessLevel string 40 FK Account Accountid ZERO TO ContactAccessLevel string 40 RowCause string 40 FK Account IsDeleted boolean Import Relational Model XML Format m Import Metadata From Text File x Select Import Type A D Select the type of text file import desired d Import Type Relational Model XML Format Purpose Imp relational soy Relational Tables CSV Format Relational Virtual Tables CSV Format Sample Hle Fe Datatypes CSV Format relational mod lel lt table name ProductData namelnSourcez dbo products ProductData Figure 4 33 Import Wizard These steps are required for each type are defined below e Section 4 7 1 Import Relational Model XML
126. date and is system table properties 209 Chapter 6 Metadata specific Create Relational Table Create Relational Table F No calumns defined for table This wizard allows creating a complete relational table including columns unique keys and foreign keys definition 19 Properties Columns P Primary Key Unique Constraint F Foreign Keys Model File SimpleRelational xmi Name PartslInfa Mame In Source Partslnfo Supports Update Is System Table Cancel Figure 6 6 Properties Tab 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 210 Create Relational Table Wizard Create Relational Table Create Relational Table Click OK to add new object to model This wizard allows creating a complete relational table including columns unique keys and foreign keys definition Column Name Data Type ID string name string category string quantity string Cancel Figure 6 7 Columns Tab 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 211 Chapter 6 Metadata specific Create Relational Table Create Relational Table Click OK to add new object to model This wizard allows creating a complete relational
127. 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 5 3 3 Choice Editor XML 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 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 Figure 5 35 Opening The Choice Editor 187 Chapter 5 Creating and Editi Def Choice Editor bookSetMixed choice o Include Criteria I I hardcoverBook i 5 AudioBooks bookSetMixedDocument MappingClass 2 softcoverBook UCASE AudioBooks bookSetMixedDocument Mappi 3 i amp audioBook i AudioBooks bookSetMixedDocument MappingClass cat Summary Figure 5 36 The Choice Editor The table on this panel displays fragment options for the choice each r
128. dicates if data sources that match the default name should be auto created if they do not exist on Teiid server Enable Preview If the Designer Runtime feature is installed and a Teiid Instance is defined Teiid Designer will automatically keep the preview artifacts VDBs in sync with the workspace models Unchecking this preference will disable preview feature and not create preview artifacts e Enable Preview Teiid Cleanup If operating Designer with Enable Preview TRUE then this preference will result in automatic clean up of your preview artifacts from your Teiid servers 299 Appendix C User Preferences Any preview VDBs or preview data sources will be undeployed from your servers as part of Eclipse s shut down process Always open editor without prompting To change edit a model it must be opened for editing Checking this box will automatically open the model in an editor if the user attempts to perform a change in a model If unchecked the user will be informed that an editor will be opened before the operation is completed e Open Designer perspective when model is opened If a model is opened via importing projects the New gt Teiid Metadata Model menu and the Teiid Designer perspective is not open you may want to automatically open the perspective and beging working on your model This preference has 3 settings Always open which means always open the perspective without prompting never open which means do not open
129. dure that will insert a row and return an XMLLiteral object Web Services War Generation SOURCES ea Transformation Diagram Table Editor Transformation Editor ICREATE VIRTUAL PROCEDURE BEGIN DECLARE integer VARIABLES insert count 0 INSERT INTO RestfulBooks VirtualBooks RestfulBooks VirtualBooks ISBN RestfulBooks VirtualBool RestfulBooks VirtualBooks PUBLISH YEAR RestfulBooks VirtualBooks EDITION RestfulBooks VirtualB RestfulBooks rputBook title in RestfulBooks rputBook subtitle in RestfulBoaks rputBook publisher ii RestfulBooks rputBook type in RestfulBooks rputBook author id VARIABLES insert count VARIABLES ROWCOUNT IF VARIABLES insert count 1 BEGIN SELECT XMLCOMMENT Insert was successful AS result END ELSE BEGIN SELECT XMLCOMMENT Insert failed AS result END ENL The input format for the REST procedure could be URI parameters an XML JSON document or some combination of both When using an XML document your root node should be lt input gt and the XML nodes should correspond to order of the procedure s input parameters For example here is the input for the above insert procedure 233 Chapter 6 Metadata specific input lt SBN gt 0 13 014714 1 99999 lt ISBN gt lt TITLE gt The XML Handbook lt TITLE gt lt SUBTITLE gt Updated Edition SUBTITLE lt PUBLISHER gt 16 lt PUBLISHER gt PUBLISH YEAR gt 2000 lt PUBLISH YEAR gt
130. e CapitalCity request BODY HEADER Schema Contents 3 Element Info 3 v CapitalCity sequence sCountrylsOCode Gg string e sCountryISOCc Cenaa Seenen SOL Statement CREATE VIRTUAL PROCEDURE BEGIN SELECT XMLELEMENT NAME CapitalCity XMLNAMESPACES DEFAULT http www oorsprong org lt Back Next gt Cancel Figure 2 29 Consume SOAP Wizard 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 sCountrylSOCode element then click the Add button This will add the selected element to the request Now select the Response tab as shown below 47 Chapter 2 Dive Right In ereate Relational Model from Web Service Procedure Definition Press the Finish button to finish Operations CapitalCity Overwrite existing procedures for this operation Request Response wrapper Procedure Generated Procedure Name CapitalCity response BODY HEADER Schema Column Info e CapitalCityResponse Root Path ns CapitalCityResponse Name Ordinality Data 1 CapitalCityResult O string sequence Add CapitalCityResult Delete string BEEN Up Generated SOL Statement eee SOL Statement CREATE VIRTUAL PROCEDURE BEGIN SELECT t FROM Bak Next
131. e mapping transformation for this fragment is no different from the first except that you can access the first cycle s mapping class attributes plus you have the opportunity to specify the conditions that will halt the recursion 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 Teiid Designer does not mark the recursive fragments in document models to execute recursively in the Teiid Server To take advantage of this behavior you must open the Recursion Editor in the recursive mapping class Section D 3 1 1 5 Mapping Transformation Diagram mark the transformation query as recursive and specify the recursion limit properties 5 3 4 1 The Recursion Editor 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 contain
132. e system WSDL file in the selection dialog and click OK 133 Chapter 4 Importers Create Web Service from WSDL File WSDL File Selection Press the Next gt button to continue web Service Model Name WSDL Filles E x E fhome blafond Test Designer Folder example files wsdls OnHand wsdl E NN rm NN Figure 4 53 WSDL File Selection Dialog e Step 4 The next page is titled Namespace Resolution This page identifies successful and errant WSDL namespace resolution The main WSDL document will essentially always be resolved since the workspace file chooser is used to obtain the path Problems will occur when the main WSDL file imports other WSDL files that cannot be resolved If no errors select Next to proceed or Finish if enabled to complete with default options 134 Import WSDL From File System Location Create Web Service from WSDL File Namespace Resolution All namespaces are resolved Select Next to modify the created schemas workspace locations Namespaces e Namespace Path http dladb07 fhome blafond Test Designer Folder example files wsdls OnHand wsdl Cancel Finish Figure 4 54 Namespace Resolution Dialog e Step 5 The next page WSDL Operations Selection allows customizing the resulting content of your Web Service model by selecting deselecting various operations and interfaces in the following dialog 135 Chapter 4 Importers T 1 DEA C
133. e up under the Extension category and are prefixed with a MED s namespace prefix If there is an initial value for an extension property it will be set to the default value using the property definition found in the MED 202 Setting Extended Property Values E Properties Description rl Property Value YExtension mymodelextension Copyable true Y Info Object URI Misc Cardinality nid Logical Relationships Materialized false Materialized Table 0 ANewBaseTable Figure 5 48 Properties View For Extended Model Object 203 204 Chapter 6 Metadata specific Modeling This chapter discusses various features targeted at defining and managing metamodel specific objects 6 1 Relational Source Modeling 6 1 1 Source Function To improve ability to utilize database functions within View transformations a new New Child gt Source Function action and wizard was added to assist in building a source procedure that conforms to a function structure including input and output parameters Prior to Teiid Designer 7 5 these functions were required to be modeled as User Defined functions Running the action will display the Create New Source Function dialog Enter your database function name define input parameters including datatype and length specify output parameter info set options and click OK PartsSupplier xmi New gt import declarations 2 New Child ta Package Diagram
134. eate a new view model containing a view table for your selected flat file source file Within the view table will be generated SQL transformation containing the getTextFiles procedure from your source model as well as the column definitions and parameters required for the Teiid TEXTTABLE function used to query the data file You can also choose to update an existing view model instead of creating a new view model The TEXTTABLE function as defined in the Teiid documentation processes character input to produce tabular ouptut It supports both fixed and delimited file format parsing The function itself defines what columns it projects The TEXTTABLE function is implicitly a nested table and may be correlated to preceeding FROM clause entries express om COLUMNS COLUMN E ehar ESCAPE char HEADER integer SKIP integer AS name Teiid Designer will construct the full SQL statement for each view table in the form SLING IE A Name A Sport A Position A Team A City A StateCode A AnnualSalary PROM EXEC PlayerDataSource getTextFiles PlayerData txt AS f S Namen string oportet cring Osta SEring Team string City string StateCode string nus icq HEADER 2 SKIP 3 AS A To import from your flat file source follow the steps below e Step 1 In Model Explorer choose the File gt Import action py in the toolbar or select a project folder or model in the tr
135. ectlBtring Same as Column Length Integer Same as Column Radix Integer Same as Column Scale Integer Same as Column NullType Integer Same as Column Precision Integer Same as Column Position Integer Same as Column Type String Yes Defines parameter is IN OUT RETURN Optional Boolean No Defines if the parameter is optional or not only 296 PROCEDURE Type In Designer In Metadata Required Description PARAMETER API used system metadata Table B 12 Data Usage for SQL Transformations Type In Designer In Metadata Required Description API VirtualGroupNars ing Yes No Yes See Table the properties defined on Table TransformedObjstilhg Yes No Yes See Table the properties defined on Table No Yes See Table the properties defined on Table Transformation Satring Yes No Yes See Table the properties defined on Table Bindings Collection Yes No Yes See Table the properties defined on Table SchemaPaths Collection Yes No Yes See Table the properties defined on Table Table B 13 Data Usage for VDBs In Designer Metadata Required Description String Yes vdb xml Yes Name of the VDB NamelnSource String 2 Not required Version String Yes vdb xml Yes VDB version 297 Appendix B Designer Metadata Type In Designer In Metadata
136. ed in the queryable wrapped procedure The next page Procedure Definition provides the means to define the details of your request and response structures In the Request tab select and double click the schema elements you wish to be input parameters for your request These will be added to the Element Info panel and the resulting generated SQL statement will be updated to reflect the new element Select the Response tab and create the response procedures result set columns in the same way Repeat this process for all operations by changing the selection target operation via the Operations selector at the top 123 Chapter 4 Importers O Create Relational Model from Web Service Procedure Definition Press the Finish button to finish Operations FullCountryInfo Overwrite existing procedures for this operation FullCountryInfo type 7 sequence e Generated SOL Statement CREATE VIRTUAL PROCEDURE BEGIN SELECT XMLELEMENTIMAME FullCountryInfo XMLMAMESPACES DEFAULT http www oorsprong org websamples countryinfo AMLELEMENT NAME sCountrylSOCode CountryInfoServiceView FullCountrylnfo request sCountryl5OCode AS xml out END 2 Back Next gt Finis Figure 4 43 Procedure Definition Page e Step 7 Click Finish After generation the new models be found in the specified location in your workspace 124 Circular References in WSDL Schemas In t
137. ed the JBoss AS instance you have defined SoapyBooksProject BooksWS vdb J D ums Een E New P 65 MySqlBooks C g Open b MySqIBooks C open with p lt id _ Copy P MySglBooks x E uu om Delete P 2 US States Refactor Ey Import py Export 27 Refresh Mark as Deployable Validate Using the JBoss AS Administration Console 238 Web Services War Generation Using the administration console that comes with JBoss AS you can deploy WAR files The administration console is available at http host port admin console Once logged on simply use the Add a New Resource button of the Web Application WAR resource folder Manual Deployment to JBossAS It is possible to deploy the generated WAR by manually copying the file to the deploy folder of the target JBoss AS If the server is running the WAR will deploy automaticall via hot deploy Otherwise the WAR will deploy at the next start of the server 6 4 2 4 Testing Your Generated WAR Files 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 Determining Your WSDL URL You can get your WSDL URL at hitp server port jbossws services This is where all the deployed web services for the target JBossAS server will be listed Find your service and click the Endpoint Address link Th
138. ed when the Content via Element property is set on an operation s input parameter Input variables may be edited using the Edit link in the Input Variables section and may only represent XPath values to single attributes and elements within the input contents other variable declarations and assignments must be typed directly into the Procedure section Clicking the Edit link will display the following dialog Im Edit Input Variables Request Document Input Variables Check a request document component to define an input variable for the Web Service Select an input variable to procedure Uncheck a request document component to delete an existing input the XPath that will be usec variable select its value from the vw bookCollection BooksNS BookSetNested request document v le book BooksNS BookNested IM author string isbn BookTypesNS ISBN IN edition string e title xsd string IN isbn string subtitle xsd string IN publishDate string edition BookTypesNS BookEdition IN publisher string gt E authors BooksNS Authors IN subtitle string b publishinglInfarmation BooksNS Publishinglnformation XPath for IH title bookCollection book title Figure 5 41 Edit Input Variables Dialog 5 4 Managing Model Object Extensions 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 D
139. editing and management capabilities This perspective includes 6 main Ul components or groups of components as shown below They include Section D 2 1 Model Explorer View Teiid tree view of Model Objects 222 Teiid Server instance view Provides view of contents for connected instances of installed Teiid runtime Section D 3 1 Model Editor Custom editors targeted for xmi metadata model files Section D 2 4 Properties View Standard property values for selected workbench objects Section D 2 13 Guides View Guides and Status Views which enhance usability Miscellaneous Views Includes the Problems view Message Log view and the SQL Results view opened if Preview Data action is performed 311 Appendix D Teiid Designer Ui Teiid Designer Navigate Search Project Metadata Run Window Help Mave yv Brive vovit Quick Access ml Resour Project Explorer 8 H Guides W Status 5 v Modeling Actions Action Sets Model DBC Source ds xml mi Define Teiid Model Project lew xmi amp i Create JDBC connection D Create source model for DBC data s B Preview Data 8 Define YDB Execute Description no action selected gt rer awk 9999 Cheat Sheets 2 0 Final Jescriptian 7 Problems s Log Servers D errors 4 warnings 0 others gt amp Warnings 4 items 5 Ce
140. ee and choose Import e Step 2 Select the import option Teiid Designer gt File Source Flat gt gt Source and View Model and click Next gt 85 Chapter 4 Importers Select Choose import source Select an import source type filter text gt m Plug in Development gt 2 Run Debug gt Team Designer D DDL Ale gt gt Source or View Model Designer Text Ale gt gt Source or View Models En Hle Source Flat gt gt Source and View Model En Hle Source XML gt gt Source and View Model JnBC Database gt gt Source Model NM Salesforce gt gt Source Model ee WSDL Hle or URL gt gt Source Model WSDL File or URL gt gt Web Service Model S XML Schemas b E XML Figure 4 14 Import from Flat File Source Step 3 Select existing or previous connection profile from the drop down selector or press New button to launch the New Connection Profile dialog See Eclispe Data Tools documentation or Edit to modify change an existing connection profile prior to selection After selecting a Connection Profile the file contents of the folder defined in the connection profile will be displayed in the Available Data Files panel Check the the data file you wish to process The data from this file along with your custom import options will be used to 86 Import From Flat File Source construct a view table containing the required SQL transformation for retrieving your data and
141. efresh Diagram Re draws diagram Bg Show Parent Diagram Navigates to diagram for parent object if available Preview Data Executes a simple preview query SELECT FROM E Save Diagram as Image Save the diagram image to file in JPG or BMP format Modify Diagram Printing Preferences Modify page layout information for printing diagrams Includes margins orientation etc Show Hide Page Grid Show current page boundaries as grid in diagram Context menus provide a flexible means to edit model data especially from Package Diagrams Each Package Diagram represents the contents of some container i e Model Category Schema etc so New Child New Sibling and New Association actions are almost always available in addition to standard Edit actions Delete Cut Copy Paste Rename Clone A sample context menu for a relational base table is shown below 349 Appendix D Teiid Designer Ui Mew Child Mew Sibling Mew Association Modeling a Unde Delete multiple objects of Cut Ctrl x g Copy Ctrl C Clone Copy Mame gt Delete Rename Link Types gt Gs Refresh Diagram f Show Parent Diagram du Save Diagram To File Page Setup Show Page Grid Input Methods hd Figure D 43 Package Diagram Context Menu D 3 1 1 2 Custom Diagram The Custom Diagram represents a view of user defined model objects Unlike Package Diagrams Custom Diagrams can
142. eiid Designer in various view toolbars and context menus Previewable objects include e Relational table or view including tables involving access patterns e Relational procedure e Web Service operation 15 Chapter 1 Introduction e XML Document staging table i Note If attempting to preview a relational access pattern a web service operation or a relational procedure with input parameters a dialog will request values for required parameters 1 3 9 Model Object Extensions 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 Eliminate need for separate EMF metamodel e Simpler approach including reduction of extendable metamodels and metamodel objects Relational Web Services XML Document User Defined Functions and replacing EMF terminology with basic object types Allows metamodels to be extended by mu
143. eing the Guides and Cheat Sheets in action subsequent chapters will offer detailed explanations of the various concepts and actions 2 1 Targeting the Teiid Server In this section the setting of the teiid server version is demonstrated This can be achieved by either setting a preference or by defining a teiid server 2 1 1 Server Version Preference The default server version preference allows the target server version to be changed without actually having to define a teiid server in Designer The preference s list of possible values is determined by which teiid runtime client plugins have been installed into the application 19 Chapter 2 Dive Right In imd Javascript 18055 Tools Maven Model Validation hy hyn Plug in Development Project Archives Provisioning Admin Remote Systems Report Design Run Debug Server sWV TBot Preferences Team Tella Designer Diagram Diagram Printing Editor Validation Terminal Valldation Wek Web Services Preferences Teiid Desicner b 3 B r Specify values for general Designer preferences Preview Data lvDH Execution Enable auto creating of a source model s data source on Telld server VDB Exec Es Enable Preview ES Enable Preview leid Cleanup Telld Designer Always open editar without prompting Open Designer perspective when a model 15 opened Always open J Never open e Prompt Check and update imports during save Rest
144. el of all selected document entities or click Next to view Preview Generated Documents page that allows you to exclude document specific entities then click Finish i Note For deeply nested schema your total entity count may be large If so displaying the preview may take some time 63 Chapter 3 New Model Wizards New Model Wizard Preview Generated Documents Preview and edit the documents generated from the XSDs you selected Elements marked with a boxed arrow contain recursive data Elements marked with indicate that the builder stopped at the node bibliographyDocument fe bibliography BooksNS Bibliography sequence is BooksNS http www metamatrix com XMLSchema DataSets Books i xsd http www w3 org 2001 XMLSchema i BookTypesNS http www metamatrix com XML Schema DataSets Books BookDatatypes Figure 3 9 Preview Generated Documents Dialog 3 4 Creating XML Schema Model Create XML Schema Model Tocreate a new empty XML schema xsd model Step 1 Launch the New Model Wizard e Step 2 Specify a unique model name Step 3 Select XML Schema XSD option from Model Class drop down menu Step 4 Select Datatype Model from Model Type drop down menu Step 5 Click Finish e In addition to creating a new empty XML schema model the following builder option is available 64 Copy From Existing Model Copy from existing model of the same
145. elational Index Wizard Create Relational User Defined Function jj No parameters defined For procedure This wizard allows creating a complete relational user deFined Function including parameters and Function speciFic nroanertie like Fhe iua rlacc and mathad Model File Customer Accounts xmi Name getsum Name In Source Properties Update Count AUTO Non Prepared Function Properties Function Category math Java Class org blah MyClass Java Method UDF Jar Path home joe Jave myudfs myMath jar m Deterministic wf Returnsnullonnull Variable Arguments Aggregate Properties Figure 5 16 Create Relational User Defined Fuction Dialog 5 2 3 Create Relational Index Wizard Indexes can be created at the same time as your relational table object see Indexes tab in Create Relational Table image above or via New Child Index action and dialog as shown below 163 Chapter 5 Creating and Editi r x OF Create Relational Index Create Relational Index Click OK to add new object to model This wizard allows creating a complete relational index including reFerenced columns ModelFile ProducsMysQLxm ProductDataindex Name In Source Referenced Columns I Properties Description Auto Update Nullable Unique Filter Condition Figure 5 17 Create Relational Index Dialog 5 2 4 Create View Model Ob
146. ema DataSets Books BookData change Identification Name ISBN Description ISBN using very simple formatting rules Inheritance Base datatype string http www w3 org 2001 XMLSchemal change Create subtype of this datatype Prevent Restrictions Li U ne Hierarchy view Open in Datat Enterprise Information in this section is required in order to use this type in other models Enterprise Datatype Runtime type string Fixed Use Default Inherited from string Length Add Descrij tian Length K Minimum Length B Maximum Length Format Axed Use Default Add Descri tian Axed Use Default Add Descri tion Axed Use Default Whitespace Collapse 2 Fixed Use Default Enumeratians Value Description Add Use Default Edit Remove Add Use Default Edi Patterns Value 0 9 X 10 1 3 Description Remove Figure D 56 Editing String Property 364 VDB Editor D 3 1 4 Semantic Editor The Semantic Editor is a tree based editor for XML Schema elements and attributes D 3 1 5 Source Editor The Source Editor is a simple text editor which is aware of XML Schema formatting rules D 3 1 6 Model Object Editors The Model Object Editors represent specialized sub editors which are available for specific model object types
147. emoves the selected Data Source from the Teiid instance Problems ErrorLog 4f4 Servers E Console Teiid 8 2 JBoss 7 1 Runtime Server Started Synchronized b H Filesets gt Server Details b x XML Configuration Server Configuration Y R Teiid 8 2 A57 1 1 Data Sources 3 ExampleDs D Parts Supplier b gt Translators VDBs xi PartsCommon Root PartsCommon View PartsTop VDB Figure D 19 Teiid Contents in Server View D 2 4 Properties View The Properties View provides editing capabilities for the currently selected object in Teiid Designer The selection provided by whichever view or editor is currently in focus will determine the its contents 32 Appendix D Teiid Designer Ui To edit a property click a cell in the Value column As in the Table Editor each cell provides a Ul editor specific to the property type Description E F4 7H Value Object URI Searchability SEARCHABLE Selectable lsg true Updateable Ir true v Misc Distinct Value Count 41 1 Name SUPPLIER NAME Mame In Source SUPPLIER_NAME Null Value Count 41 1 Datatype B string Path http www w3 org 2001 XMLSc Mative Type varchar Nullable T NULLABLE Figure D 20 Properties View If the model for the object being edited is not open in an editor a dialog may appea
148. endix D Teiid Designer Ui To move to a specific portion of your diagram click the shaded area and drag to the position you want displayed in the Diagram Editor view taf Model Explorer pE Outline 2 2 Li Gig Northwind xmi 3 5 E Discount float 3 EmployeelD integer LastName string 20 ArstName string 10 fj Title string 30 FJ TitleOfCourtesy string 25 BirthDate timestamp HireDate timestamp Address string 60 City string 15 Ej Region string 15 PostalCode string 10 Country string 15 HomePhone string 24 Wl Dui ateie al A 1 Package Diagram E Table Editor D 2 3 Server View The Server View provides a means to display and manage server instances and their contents within the Eclipse environment Since Teiid is installed as part of a JBoss server its contents is displayed as part of its JBoss parent To show the Server View click Window gt Show View gt Other to display the Show View dialog Choose Servers gt Server view and click OK 320 Server View Error Log 4 Servers i A A ig m HB Problems Boss 7 1 Runtime Server Started Synchronized gt 4 XML Configuration gt R Filesets gt E Server Details v leild Server Configuration a Ilacalnast gt 2 Data Sources gt Translators gt VDBs Figure D 13 Server View T
149. ep3 The selected tab in the Table Editor changes to the tab for the child object type the new model object row is displayed and the row s name table cell is highlighted for renaming 5 1 3 New Association Action e create new associations between model objects in the Section D 2 1 Model Explorer View Step 1 Select two objects you wish to associate For example select columns in different base tables e Step 2 Right click From the pop up menu select New Association gt Foreign Key Relationship 156 New Association Action TwoSourceParts Gg PartsDB2 xmi SHIPPER ID bigdecimal b import declarations 2 Fm SHIPPER NAME string 30 Ha Package Diagram gt PARTS v 8 SHIP VIA SHIPPER ID bigdeci 1 New Child y EY SHIPPER NAME string PK SHIP VIA E STATUS A STATUS ID bigdecim E STATUS string rir Undo Delete multiple objects PK STATUS gt SUPPLIER bo SIIPBIIFR PARTS of Cut Mew Sibling gt LE 8 tet A LTE LL AZLISI IJ S AAA Modeling Ctrl X Figure 5 9 New Association Action In Model Explorer e Step 3 The new relationship link is displayed in the diagram j SHIPPER ID bigdecimal SHIPPER NAME string 30 PK SHIP Vi P PK SHIP VIA ZERD TO MANY 4 STATUS ID bigdecimal Ej STATUS string 30 N
150. epresented by the top node of the document fragment The Summary tab shown below displays a SQL like version of the current choice criteria Choice Editor bookSetMixed choice Def 1 hardcoverBook AudioBooks bookSethixedDocument MappingClasses bookSetMixed Hardback 2 sofkcoverBook UTASE AudioBooks bookSethixedDocument MappingClasses bookSetMixed TYPE LIKE SOF 3 audiobook AudioBooks booksSethixedDocument Mapping Classes bookSetMixed TYPE IM Books on Boot Default 3 audiobook Summary Figure 5 37 Choice Criteria Summary 5 3 3 1 Using the Choice Editor You should address each choice option by performing one of the following Specify a criteria statement for the Teiid Server to apply in order to determine which elements or elements to insert into the result document Exclude or include the option s fragment from the document 188 Choice Editor XML e Set the elements criteria test order e Set a default action that occurs if none of the criteria you set is met 5 3 3 2 Excluding Fragments 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 In
151. er RealTimeData port OnHand fhome blafond T 4 Editor http localhost Share BSM Real Time Data Design Server RealTimeData port OnHand Felder WSDLimportProject Name without file extension onHand Next gt Cancel Figure 4 56 Namespace Resolution Dialog Step 7 The last page titled XML Model Generation allows you to change the name of the XML View model if the Generate virtual XML document model is checked Input desired name or use the default name provide Select Finish to complete 137 Chapter 4 Importers Create Web Service from WSDL File XML Model Generation Ready to generate the Web service model Generate virtual XML document model Location WSDLimportProject XML Model OnHandWSResponses Cancel Figure 4 57 Namespace Resolution Dialog 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 138 Import WSDL From URL TEE DEA Create Web Service from WSDL File Validation Problems One or more selected WSDL files have errors Please correct or remove the file sito proceed WSDL Validation Panel message wsdl file X eve elt 1 Cannot find the declaration of ele
152. er option creates a Web service model based on a user defined WSDL file and its referenced schemas In addition applicable XML schema files and XML View document models optional are created e create a new relational model by copying contents from another web service view model complete Create Web Service View Model above and continue with these additional steps Step 5 Select the model builder labeled Build from existing WSDL file s or URL and click Next gt The remaining wizard steps are identical to those found using the Section 4 9 Import WSDL Into Web Service action option 3 5 3 Build From Relational Models See Section 6 4 1 Create Web Service Action 67 Chapter 3 New Model Wizards 3 5 4 Build From XML Document View Models Web Service models and their corresponding Interfaces and Operations can be generated in Teiid Designer from XML View model components Namely XML View Documents and XML View Document roots To create a new Web service model from XML components e Step1 Select either a single XML Document or single XML Document root in Section D 2 1 Model Explorer View e Step 2 Right click select Modeling gt Create Web Service action D e Step 3 Fill in missing properties in Web Service Generation Wizard shown below 68 Build From XML Document View Models Web Service Generation Wizard Generate a Web Service Click Hnish ta generate the Web Service Click
153. er to integrate multiple sources and access them using the common data access standards Web Services SOAP XML e JDBC SQL e ODBC SQL Teiid Designer is an integral part of the Teiid Designer enterprise class system for providing data services for service oriented architectures 1 2 Metadata Overview 1 2 1 What is Metadata 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 Named 41 Numeric A string Nine characters long Located in the StreetAddress table What is Metadata Comprised of two parts The first five digits represent the five ZIP code numbers the final four represent the ZIP Plus Four digits if available or 0000 if not Formatted only in integer numeric characters Errors will result if formatted as 631410 00 or 631490000 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
154. es Cardinality is used to calculate the cost of source node access TableType Integer Yes Yes Yes Table View Document XmlMappint IsVirtual Boolean Yes Yes Yes Used to find if this is source table Vs view IsSystem Boolean Yes Yes No Only used for System metadata IsMaterialized Boolean Yes Yes Yes To identify that the table is materialized 287 Appendix B Designer Metadata TABLE In Designer In Metadata API Required Description SupportsUpdat eBoolean Yes Yes Yes To allow updates on the table PrimaryKeyID String Yes KeyRecord Yes Used for creating indexes on temp tables and to create default update delete procedures ForeignKeyIDs Collection Yes List lt ForeignKey es Used in Planning of query rule raise access IndexIDs Collection Yes List lt KeyRecord es Used for creating indexes on temp tables and in planning estimate predicate cost UniqueKeyIDs Collection Yes List lt KeyRecord es Used for query planning AccessPatternl f sollection Yes List lt KeyRecord es Used for enforcing the criteria on query MaterializedT abtsiiting Yes Table Yes Reference to Materialization table insertEnabled Boolean Yes Yes Flag for checking insert procedure is enabled for view 288 TABLE In Designer M
155. eseeeeseeeeeeseeseees 59 3 3 2 Build XML Documents From XML Schema 60 3 4 Creating XML Schema Model nnne nennen 64 3 4 1 Copy From Existing Model ccccccecseceeceeceeeeeeeceeeeeeeeeeseeeeeeeaeeaeeeeaeees 65 3 5 Creating Web Service View Model 65 3 5 1 From Existing Mod l deett en 66 3 5 2 Build From Existing WSDL File s or URL 67 3 5 3 Build From Relational Models 67 3 5 4 Build From XML Document View Models 68 Teiid Designer User Guide 71 S NEIN D 71 4 2 Import From JDBC Database ioci retos taa 74 4 3 Import From Teiid Data Source Connection 80 4 4 Import From Flat File Source 84 4 5 Import From XML Data File Source 98 4 6 Import From Salesforce esses 105 4 7 Import Metadata From Text File sess 110 4 7 1 Import Relational Model XML Format 111 4 7 2 Import Relati
156. esigner model extension framework consists of Model Extension Definitions MEDs See Section 1 3 9 Model Object Extensions MED Registry keeps track of all the MEDs that are registered in a workspace Only registered MEDs can be used to extend a model See Section D 2 12 Model Extension Definition Registry View MED Registry View 195 IM title string Chapter 5 Creating and Editi MED Editor See Section D 3 3 Model Extension Definition Editor 5 4 1 Create New MED To create a new MED select the File gt New gt Other action to display the New wizard dialog Select the Teiid Designer gt Teiid Model Extension Defn option which displays the New Model Extension Definition dialog Browse and select existing project or project folder location for MED file and specify unique file name and press Finish New Model Extension Definition New Model Extension Definition Press the Anish button to finish Folder Location ExtendedParts Model Extension Definition Name Open in Extension Editor Figure 5 42 MED Editor Overview Tab 196 Edit MED 5 4 2 Edit MED To edit an MED file select an existing mxd file in your workspace and right click select the Open action The MED Editor will be opened to allow editing See Section D 3 3 Model Extension Definition Editor On the Overview tab you can specify or change the Namespace Prefix Namespace URI the Model Class yo
157. etadata Required Description API deleteEnabled Boolean Yes Yes Flag for checking delete procedure is enabled for view updateEnabled Boolean Yes Yes Flag for checking update procedure is enabled for view Select String Yes Yes Transformation Transformation for Select in case of View Yes Yes Transformation for Insert in case of View Insert Plan String Yes Yes Transformation for Update in case of View Update Plan String Yes Yes Transformation for Delete in case of View Yes Yes XML Document Yes Yes XML Document Delete Plan String Bindings Collection SchemaPaths Collection Table B 2 Data Usage for Columns COLUMN Type In Designer In Metadata Required Description API FullName String Yes Yes Yes Name of the column NamelnSource String Yes Yes Yes Name of the column in source system 289 Appendix B Designer Metadata COLUMN Type In Designer In Metadata Required Description API IsSelectable Boolean Yes Yes Yes Column is allowed in select IsUpdatable X Boolean Yes Yes Yes Column is allowed in Update Insert Delete NullType Integer Yes Yes Yes Used for validation if null value allowed IsAutolncremenf btdean Yes Yes Yes During insert used to validate if a value is required or not IsCaseSensitiveBoolean Yes Yes 2 2 IsSigned
158. ets 52 vg Consume a SOAP Web Service v gt Introduction v Create New Model Project gt Create SOAP Web Service Connection 4 Create Models From SOAP Connection Create VDB Follow the steps below to create a new VDB v Launch New VDB Wizard uw 1 Select button to browse and select project or folder location 2 Enter a unique name for your VDB 3 Enter an optional description for your VDB 4 Select the Add button to choose models to go in your VDB 5 Click Finish t Test VDE Figure 2 32 Create VDB 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 CS to advance to the next step 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 Chapter 3 New Model Wizards Models are the primary resource used by 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 The Teiid Designer currently supports the following types of models 3 1 Creating New Relational Source Model 3 2 C
159. ew SQL as well as open desired transformations for editing Metadata Launches the Search dialog User can search for models in the workspace by specifying an Object Type and or a Data Type and or a property value Search results appear in the Section D 2 7 Search Results View view and double clicking a result will open that model in the appropriate editor Find Model Object Launches the Find Model Object dialog which can be used to find an object in the workspace by specifying all or part of its name Selecting the object will open itin the appropriate editor 10 1 Finding Model Objects The Teiid Designer provides a name based search capability to quickly locate and display model objects To find a model object 2 9 Chapter 10 Searching 280 Step 1 Open the Find Model Object dialog by either action on the main Teiid Designer tool bar Teiid Designer PartsProject PartsSour lt Project Metadata Search Ctrl H Hle EP Remote Pointcut Matches S Beans Eh Find Seam References Z2 Find Seam Declarations Text Run Window Help Teiid Designer gt Relationship S Transformations f Metadata Find Model Object Figure 10 2 Find Model Object Action In Toolbar selecting the or select the same action via the main menu s Search gt Find Model Object action Search Transformation SQL Find Model Object Type Object Name any chara
160. ewForeignkey F NewForeignkey P PK STATUS Figure 5 10 New Association In Diagram e To create new associations between model objects in the Section 0 3 1 1 Diagram Editor Step 1 Select two objects you wish to associate For example select columns in different base tables e Step 2 Right click From the pop up menu select New Association gt Foreign Key Relationship 157 Chapter 5 Creating and Editi e 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 e Step2 Drag the column to another table and drag over a column and drop onto this column The target column should highlight in Yellow e 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 D 3 1 2 Table Editor Step 1 Select two objects you wish to associate For example select columns in different base tables e Step 2 Right click From the pop up menu select New Association gt Foreign Key Relationship e Step 3 New Foreign Key and Primary Key objects will be added to the contents of their respective tabs in the Table Edito
161. false type or has multiple selectable values a combo box will be displayed to change the value Supports LI true sy true true true crue true true Feri Figure D 51 Editing Boolean Value i Searchability Curren ALL EXCEP False ALL EXCEP False ALL EXCEP False ARCHABLE 7 False SEARCHABLE m ALL EXCEPT LIKE LIKE ONLY LIN SEARCHABLE SEARCHABLE False SEARCHABLE false Figure D 52 Editing Multi Value Property For multi valued properties where the available values are dynamic i e can change based on available models or data a picker button will be displayed 361 Appendix D Teiid Designer Ui Datatype ink xs long short xs int long xs inbeger string Path htto ww wa org 200 lang xs inbeger string string long xs inbeger abrir Figure D 53 Editing Multi Value With Picker An example of of this type is the relational column datatype property Editing via the table cell and clicking the button for datatype will display the following dialog Datatype ink xs long short xs ink lang xs inbeger string Path htEp Pav 3 org 200 long xs inbeger string string long xs inbeger Figure D 54 Editing Datatype Values D 3 1 2 2 Inserting Table Rows The Insert Rows action provides an additional way to create objects in a model Insert Rows action performs the same function as Insert
162. guration for the database to be imported into a relational model Connection Profile JDBC Metadata Processor JDBC default v Properties Driver MySQL JDBC Driver URL jdbc mysal localhost 3306 Accounts UserName root innt Figure 2 10 Select Connection Profile On this page select the TestMySQL Connection profile that you created in the previous step Click Next 29 Chapter 2 Dive Right In Import Database via JDBC Select Database Metadata Select the types of objects in the database to import Foreign Keys Table Typ Include Incomplete FKs Indexes Unique Only VIEW LOCAL TEMPORARY Approximations Allowed Procedures Select All Figure 2 11 Select Database Metadata On this page select the database metadata that you want to import When finished click Next 30 Model a JDBC Source Import Database via JDBC Select Database Objects Press the Next gt button to continue een Indexes Primary Key Imported Foreign TABLE CAT TABLE SCHEM TABLE COLUMN NAME DATA j TestMySQL gt O information schem x Accounts Accounts ACCOUNT ACCOUNT ID TABLE Accounts ACCOUNT SSN ACCOUNT Accounts ACCOUNT STATUS CUSTOMER Accounts ACCOUNT TYPE HOLDINGS Accounts ACCOUNT DATEOPENED Accounts ACCOUNT DATECLOSED PRODUCT 4 table object s selected to import Show Only Selected Schemas
163. he 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 CountryInfoService I binding string Ed action string 255 I request XMLLiteral Ed endpoint string 255 sd result XMLLiteral Figure 4 44 Example Web Services Wrapper Procedure 4 8 1 Circular References in WSDL Schemas It is possible for a WSDL schema to either contain a very deep set of XML type references or indeed for such references to be circular This is legal in the WSDL schema but can make 125 Chapter 4 Importers processing the schema in Designer difficult If left unchecked such circular references can result in a JVM StackOverFlow exception and exiting of the application To mitigate this possibility a depth limit of 750 references has been introduced Should the depth exceed this limit then a warning is displayed and further processing of that fragment of the schema will end It may be the case that the reference in question is not circular
164. he Transformation Editor The Reconciler Dialog is shown below mq e Reconciler Status 3 The SQL contains unmatched symbol SCOPE Reconciling Query Note The data types shown are the runtime data types Target Locked Virtual Target Attributes E SUPPLIER ID string EY SUPPLIER EY SUPPLIER STATUS bigdecimal FY SUPPLIER CITY string string EY SUPPLIER STATE string FY PART ID string QUANTITY bigdecimal El SHIPPER ID bigdecimal Show SQL Display SELECT 5 SUPPLIER ID string SUPPLIER_NAME 6 SUPPLIER STATUS bigdecimal SUPPLIER CITY string 6 SUPPLIER STATE string PART ID QUANTITY bigdecimal SHIPPER ID bigdecimal Reconcile Virtual Target Attributes s Remove them or create new attributes Unmatched SQL Syr 5 QUANTITY Attribute SQL Bindings ng 5 SHIPPER ID SQL Symbol string lt string Remove PartsDB2 SUPPLIER SUPPLIER ID SUPPLIER MAME SUPPLIER STATUS SUPPLIER CITY SUPPLIER STATE PART ID SHIPPER ID PartsSQL SUPPLIER PARTS PART ID PartsSQL SUPPLIER PARTS QUANTITY PartsSQL SUPPLIER PARTS SHI FROM PartsDB2 SUPPLIER PartsSQL SUPPLIER PARTS 2 Figure 5 23 Reconciler Dialog Cancel To summarize the different sections of the dialog Target Attributes SQL Symbol Table This table shows the target attributes in the left column and the SQL Symbols in the right
165. he partial contents of a model file verzion 2 1 l0 encoding 2 ASCII zumi XMI xmi verzion 2 2 l0 xmlnzgz xmi 2 http www omg orgz XMI xi zmmcore Model nnoetation xmi uuid 2 mmuuid hb 3b5b5f 413b 10759 953 zmodellmportz xmi uuid2 mmuuid zd815780 41410 1079 9d18 8act zmodellmportz xmi uuid 2 mmuuid 2e665394l 414 1079 9d18 Bact i mmcore Model nnotation relational BaseTable xmi uuid2 mmuuid bhbzle amp 64c 413b 10759 594d1 columns xmi uuid 2 mmuuid bbbac3c 4i3b 1079 3di8 8actf4a 1l1 24 type hret http waw ws org 2ZU0U1l EHLSchemat long gt columns gt columns xmi uuid 2 mmuuid bciee c 4i3b 1079 3di8 8actf4a 1l 2z type hret http waw ws org 2ZUU1 EHLSchematstring gt columns gt columns xmi uuid 2 mmuuid bciee ci 4i3b 1079 3di8 8actf4a 1l 2z type hret http waw ws org 2ZU0U1l EHLSchematstring gt columns gt columns xmi uuid 2 mmuuid bciee c2 4l13b 1079 3di8 8actf4a 1l 2z type hret http www mwetamatriz com metamodels SimpleDas columns gt tprimaryKEKey xmi uuid 2 mmuuid d4BSir940 413b 1079 9di18 8acf4a relational BaseTable gt Figure 1 4 Sample Model File G 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 Teiid Designer system The fundamental models in Teiid Designer define the
166. he right click context menus for model objects selected in the Section D 2 1 Model Explorer View Section D 3 1 1 Diagram Editor and Section D 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 Br Northwind xmi gt import declarations 2 fa Package Diagram gt Alphabetical list of products Categori sa New Child gt Categor New Sibling gt Current Tage New Association gt Custome Modeling w Create Web Service gt 8 Custome gt Custome Add To New Custom Diagram Figure 5 19 Modeling Sub Menu for Source Table If a view table is selected the menu would reflect the actions related to virtual operations 166 Transformation Editor v ac ustomerViews xmi D import declarations 3 fis Package Diagram Alphabetical list of products New Child Category pia gt Mew Sibli Current F ud gt New Association 4 Modelin custome oe J SB create Materialized views gt E Customel Customei Create Web Service a Custome Generate SQL Relationships Add To New Custom Diagram os x f Cut Ctrl X
167. hen model has fewer than ral mapping classes Auto expand XML document tree to level 1 Hide Mapping Class Columns by Default XML Document Mapping Upper XML Recursion Limit 10 Remove Duplicate Attributes on Merge Figure C 5 XML Document Preferences Panel C 1 3 2 Table Editor Preferences Section D 3 1 2 Table Editor Preferences provide a way to customize the order and the information content for each model object type 304 Editor Editor Preferences XML Document Mapping Settings Transformation Table Preferences for a given table will only be present if the table editor tab for it has been viewed After restoring defaults the table editor must be viewed again for the default preferences to become visible Base Tables Columns Foreign Keys Function Parameters Inputs Interfaces Operations Outputs Primary Keys Procedures Procedure Parameters Procedure Results Return Parameters Sample Messages Scalar Functions Schemas Views Columns Column Visible Location Marne Name In Saurce Length Fixed Numeric Precision Numeric Scale Nullable Auto Incremented Default Value Minimum Value Maximum Value Format Character Set Name Collation Name Restore Defaults Figure C 6 Table Editor Preferences Panel C 1 3 3 Transformation Editor Preferences Section 5 3 1 Transformation Editor Preferences provide a way to customize SQL formatting diagram layout
168. her WSDL files that cannot be resolved If no errors select Next to proceed or Finish if enabled to complete with default options 140 Import WSDL From URL Create Web Service from WSDL File WSDL File Selection Press the Next gt button to continue Web Service Model Name TerraServiceW5 Workspace Hle System WSDL Rles http terraservice net terraservice2 asmx wsdl Cancel Finish Figure 4 60 Namespace Resolution Dialog Step 5 The next page WSDL Operations Selection allows customizing the resulting content of your Web Service model by selecting deselecting various operations and interfaces in the following dialog 141 Chapter 4 Importers Create Web Service from WSDL File Namespace Resolution All namespaces are resolved Select Next to modify the created schemas workspace locations Namespaces ie Namespace http terraservice usa co mi http terraservice net terraserviceZ asmx wsdl ome _ Figure 4 61 Namespace Resolution Dialog e Step 6 The next page is titled Schema Workspace Location Selection This page lists all schemas imported by the WSDL along with any dependent schemas referenced within schemas as well as schemas embedded in the WSDL and indicates whether or not they are resolvable All resolved schemas will be created in a separate file and added to the workspace The editor panel allows you to change the default file name of the new schema f
169. her 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 37 Chapter 2 Dive Right In a Guides x W E Modeling Actions Action Sets Execute selected action Consume SOAP Web Service amp Define Teiid Model Project 4 Create Web Services SOAP connection 18 Generate relational models from WSDL Preview Data 9 Define VDB i Execute VDB Description Test a VDE by deploying to Teiid Server connecting to it via JDBC and executing queries against it via Datatools SQL Scrapbook Cheat Sheets Cheat sheets for Teiid Designer use cases Create Model from JDBC Source Create from Flat File Source 2 Consume a SOAP Web Service 2 Create Model from XML Local File Source Figure 2 20 Guides View 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 38 Consume a SOAP Web Service B Cheat Sheets 3 Consume a SOAP Web Service This cheat sheet shows you how to consume a
170. hese rules To create a VDB source model e Step 1 Deploy your VDB Step2 Launch the JDBC Import Wizard via the Import Teiid Designer JDBC Database Source Model action 253 Chapter 8 Managing VDBs e Step 3 On the first page of the wizard create select a valid connection profile for your deployed VDB The wizard will detect that the connection profile is a Teiid VDB connection and a section will be displayed on the wizard page titled Teiid VDB Source Options f Import as VDB source model is NOT checked then the wizard will continue importing as a normal JDBC import fF Import Database via JDBC 1 Select a JDBC source configuration Press the Next gt button to continue or the Finish button to finish IDEC Metadata Processor Driver Teiid 8 x URL jdbc teiid BarryParts VDB mm localhast 31000 UserName user Password Teiid VDB Source Options iw Import as VDB source model A DBC connection to a deployed VDB allows creating a read only VDB source model that you can use in your view transforma VDBs containing these view models will reference an import to the original deployed VDB and will not contain the metadata fi source model If checked only one schema in your VDB can be selected for import at a time Back Next gt C Step 4 On the 3rd page titled Select Database Objects select a single schema to use to create as VDB source model 254 Re
171. i Dale SOUC CS me Rc H 285 B Designer Metadata Usage Requirements In Teiid Runtime 287 C User Preferences ne nene nene sese sane nns 299 C 1 Teiid Designer Preferences nme 299 Teiid Designer User Guide Sleds Diagram Iit DO OE m 301 C 1 2 Diagram Printing Preferences 302 1 3 Editor Prelerell CBS su EM UMP 303 Q 1 4 ValidationiPErefererieBS X E te bI RR Ux da iw a 306 D Teiid Designer Ui Reference nennen nnne nnns 311 D 1 Teiid Designer Perspectives 311 0 1 1 Teiid Designer Perspective 311 D 1 2 Opening a Perspective 313 BW Ec issu nies mU T Um 315 D 2 Teiid Designer Views 315 D 2 1 Model Explorer View 315 D22 enim 318 aUud nut 320 D24 PODOS RT TUM 327 L 2 5 CSCI IOLT VIEW sereen eon
172. i Y gt lt property value 4097408696 name checksum gt lt property value Relational name modelClass gt lt property value false name builtIn lt property value 1592679058 INDEX name indexName gt lt property value Financials US_CustomerAccounts xmi name imports gt lt model gt model visible true type PHYSICAL name EU CustomerAccounts path CustomerAccounts xmi Y gt lt property value 525566235 name checksum gt lt property value Relational name modelClass gt lt property value false name builtIn lt property value 1119071590 INDEX name indexName gt lt source translator name postgresgql connection ndi name EU _CustomerAccounts name bs NEC eio Hide e c ONE gt lt model gt ass Fortunately Teiid Designer simplifies the management of your VDBs by providing a dedicated VDB Editor which maintains a consistent valid vdb xml file for you and assists in synchronizing 14 Model Validation your workspace models with any related models in your VDB See the Section D 3 2 VDB Editor section 1 3 7 Model Validation Models must be in a valid state in order to be used for data access Validation of a single model means that it must be in a self consistent and complete state meaning that there are no missing pieces and no references to non existent entities Validation of multiple models checks that all inter model dependencies are present a
173. ialog is displayed Click on the Browse button to find the directory that contains the XSD file s you wish to import To select all of the XSD files in the directory click the checkbox next to the folder in the left panel To select individual XSD files click the checkboxes next to the files you want in the right panel 145 Chapter 4 Importers Import XML Schema Files File system Import resources from the local file system Browse Ei BookDatatypes xsd Fi Books e Xj Bookslnput xsd Alter Types Select All Deselect All Inte folder BooksProject Browse Options Overwrite existing resources without warning complete folder structure fe Create selected folders only Advanced gt gt Add Dependent Schema Ales lt Back gt Cancel Finish Figure 4 65 Select XSD From File System e Step 4b If importing from URL select the Import XML Schemas via URL option and click OK to display the final Add XML Schema URLs wizard page 146 XSD Schema File Import XML Schema Files Add XML Schemas URLs XML schema URLs Into folder BooksProject Options Overwrite existing resources without warning Add Dependent Schema Ales Cancel Anish Figure 4 66 Add XML Schema URLs Dialog Step 5 Click the Add XML Schema URL button Enter a valid schema URL Click OK Schema will be validated and resulting entry added to the list of XML Schema URLs 147 Cha
174. ibe 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 You can expose only the information relevant to an application The application uses this View Metadata to resolve its queries to the ultimate physical data storage You can add content to existing applications that require different views of the data by adding the View Metadata to the existing View Metadata that application uses You save time and effort since you do not have to create new models nor modify your existing applications e Your applications do not need to refer to specific physical enterprise information systems offering flexibility and interchangeability As you change sources for information you do not have to change your end applications he View Metadata models document the various ways your enterprise uses the information and the different terminology that refers to that information They do so in a central location Our example enterprise information sources the address book database and the vendor supplied com
175. id Designer A preference for the targeted server version that new models will be based on he concept of the default server has been extended so that it will determine the targeted server version of new models superceding the preference 12 Model Classes and Types 1 3 5 Model Classes and Types Teiid Designer can be used to model a variety of classes of models Each of these represent a conceptually different classification of models Relational Model data that can be represented in table columns and records form Relational models can represent structures found in relational databases spreadsheets text files or simple Web services XML Model that represents the basic structures of XML documents These can be backed by XML Schemas XML models represent nested structures including recursive hierarchies XML Schema WSC standard for formally defining the structure and constraints of XML documents as well as the datatypes defining permissible values in XML documents Web Services which define Web service interfaces operations and operation input and output parameters in the form of XML Schemas Function The Function metamodel supports the capability to provide user defined functions including binary source jars to use in custom transformation SQL statements 1 3 6 The Virtual Database The critical artifact that Teiid Designer is intended to manage is the VDB or Virtual DataBase Through the
176. ied to your model to bring it up to date with the source metadata Uncheck any changes you do not wish t apply Only In Old Model 22 Changes 2 0 Only In New Model g Differences 5 3 Setect Unsetect au ial file MyProj MyM adeL xmi M ACCOUNT w CUSTOMER w f HOLDINGS fv PRODUCT I Selection Details ACCOUNT this selection is an Addition a lt Back Cancel Finish Figure 4 13 Review Model Updates Dialog 4 4 Import From Flat File Source You can import metadata from your flat file data sources and create the metamodels required to query your data in minutes Using the steps below you will define your flat file data source configure your parsing paramaters for the flat file generate a source model containing the 84 Import From Flat File Source standard Teiid flat file procedure and create view tables containing the SQL defining the column data in your flat file Teiid supports Flat Files as data sources Teiid Designer provides an Import wizard designed to assist in creating the metadata models required to access the data in your flat files As with Designer s JDBC Salesforce and WSDL importers the Flat File importer is based on utilizing a specific Data Tools Connection Profile The results of the importer will include a source model containing the getTextFiles procedures supported by Teiid The importer will also cr
177. iid Designer Ui D 4 6 Project Menu r E K i Close Project Build Working Set gt Clean Build Automatically Clone Project Build Praject Imports Build All Imports Properties Figure D 76 Project Menu The individual actions in the Project menu are described below 382 Open Project Launches the Open Project dialog Close Project Closes the currently selected project s 010 Build Validates the contents of the entire workspace Any errors or warnings will appear in the Problems View Build Project Validates the contents of the selected project s Any errors or warnings will appear in the Problems View Build Working Set Validates the contents of the selected working set Any errors or warnings will appear in the Problems View Clean Launches the Clean dialog Build Automatically Sets the Build Automatically flag on or off When on a check mark appears to the left of this menu item When this is turned on validation of changes is done automatically each time a Save is done Clone Project Launches the Clone Project dialog Build Project Imports Reconciles all model import dependencies for models contained within the selected project Metadata Menu Build All Imports Reconciles all model import dependencies for models contained within the workspace Build Packages TBD Validate Model Transformations Revalidates all transformations for the selected view model
178. ile on your file system Pressing this button will display an export dialog in which you can choose the location for export e 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 Transformation Editor ERROR SOL SELECT UPDA DELETE Figure 5 22 Transformation Editor context menu Following is a summary of the context menu actions Cut Copy Paste Typical text editor actions to cut copy or paste text within the editor Undo Redo Allows you to Undo or Redo the previous action Find Displays a Find Replace Dialog which allows you to search and replace text within the transformation Apply Template Displays the Choose a SQL Template Dialog which allows you to choose a starting SQL Template from a list of common SQL patterns See Section 6 2 2 Create Relational View Table Wizard for a description of this dialog 5 3 1 1 Using the Reconciler The Transformation Editors 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 171 Chapter 5 Creating and Editi To click launch the Reconciler on the Reconcile Transformation button in t
179. ile s If no errors select Next to proceed or Finish to complete with default option 142 Import WSDL From URL Create Web Service from WSDL File WSDL Operations Selection Press the Next gt button to continue or the Finish button to finish Select the desired WSDL Operations wv Jy Definitions file tmp terraservice23962930517375020849 wsdl X gt TerraServiceSoap 8 ConvertLonLatPtToNearestPlace i ConvertLonLatPtToUtmPt ae ConvertUtmPtToLonLatPt 3 ConvertPlaceToLonLatPt a CountPlacesinRect iR GetAreaFromPt a GetAreaFromRect iR GetAreaFromTileld i GetLatLonMetrics a GetPlaceFacts iR GetPlaceList GetPlaceListInRect 8 GetTheme i GetTileMetaFromLonLatPt a GetTileMetaFromrTileld a GetTile C anc el Fini sh Figure 4 62 Namespace Resolution Dialog Step 7 The last page titled XML Model Generation allows you to change the name of the XML View model if the Generate virtual XML document model is checked Input desired name or use the default name provide Select Finish to complete 143 Chapter 4 Importers rc LEA Create Web Service from WSDL File Schema Workspace Location Selection All workspace schema locations are valid Click Next to continue Schema Location Information e Mamespace Source Ale Target Path http terraservice usa com tmp terraservice2 WSDLImportProject terraservice2396293051737502084 4 Editor http terraservice usa com Felder WSDLimportProject
180. ill display a dialog listing the current applied MEDS and actions and buttons to add or remove MEDs from a model extract a MED from a model and save a copy of it locally as a mxd file and lastly update the version of MED in a model if it differs from a version in your MED registry eoo Manage Model Extension Definitions Manage Model Extension Definitions Add or Remove Model Extension Definitions to be associated with this model Location Test Model View Current Model Extensions for the selected model Registered Different Namespace Prefix Version Description 2 Cancel Figure 5 46 Manage Model Extension Definitions Dialog Selecting the Add button displays a list of applicable MEDS based on model class 201 Chapter 5 Creating and Editi lanle Woo Thu ons Select the Model Extension Definition s to be added to the model on Lie Te m me ee EX Leon c r 1 Tw Model Extension Definitions Namespace Prefix Version Description mymodelextension 1 This is my model extension rest 1 REST extended virtual procedure properties 3 Select at least one Model Extension Definition Figure 5 47 Add Model Extension Definitions Dialog 5 4 4 Setting Extended Property Values Extension properties are user defined properties available to any extended model object via the Properties View As shown below all extension properties are availabl
181. in Ful E T EAER R t RS UR 329 D 2 6 Problems View 331 D2 cR 333 D 2 8 Datatype Hierarchy View 335 D 2 9 Teiid Model Classes View ccccccecseceeceeceeeeceeceeeeeeeceeseeaeseeseeaeeeeseeaes 336 D 2 10 System Catalog View 337 D 2 11 SQL Reserved Words View 338 D 2 12 Model Extension Definition Registry View MED Registry View 339 PF MUS VEW REM E 340 D 2 14 Status View 2 cmuessesceteusee ert hone ta eot e pe Ute nnda eta oen lees 342 D 2 15 Gheat VIEW i rope aga E aatis eo dora ee 343 Do c m E 344 RECS MM Mod l TE t mo m 346 EOT T TT 365 D 3 3 Model Extension Definition Editor 371 D 4 Designer Main Menu eese mnm nenne 3 4 TE WGA qe 375 D 4 2 Edit Menu 378 DA Rara Or METU E T E E E EEEE 379 D 4 4 Navigate i i R 380 D 4 5 Search Menu 380 UNT TNT 382 D 4 7 Metadata Menu ccc
182. ion The Overview sub editor looks like this 3 1 Appendix D Teiid Designer Ui m un J z 1 3 mymxd mxd 23 t Overview 4 Namespace Prefix mymodelextension Namespace URI mymodelextension Model Class Relational Version 1 Description This is my model extension Overview Properties Source Figure D 64 Overview Tab Properties Sub Editor this editor is where the MED extension properties are managed Each extension property must be associated with a model object type The Properties sub editor is divided into 2 sections Extended Model Objects and Extension Properties and looks like this 3 2 Model Extension Definition Editor Tp mymxd mxd 2 d mymxa rmx amp Pro perties Extended Model Objects Manage the extended model objects that have extension properties BaseTable Extension Properties klk Manage extension properties for each extended model object ID Runtime Type Required Modifiable Advanced Masked Indexed Default Value copyable boolean v Overview Properties Source Figure D 65 Properties Tab Source this tab is a read only XML source viewer if you wish to view the details of your MED This source viewer is NOT editable 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 s
183. ion Details panel displays static information about the operation such as the names of the input and output messages and faults thrown by the operation 121 Chapter 4 Importers Click Next gt e Step 5 The next page entitled Model Definiton requires both a model location i e folder or project and a valid model name for both source and view models Use the Browse button to select existing folders or models Click Next when all the information is defined Create Relational Model from Web Service Models Definition All inputs OK Click Next gt to define custom procedures 6 Location 5tockService Name CountryInfoService xmi Status Source model CountrylnfoService xmi does not exist and will be created and contain the required invoke web service procedure View Model Definition Location StockService Name CountryInfoServiceView xmi Status View model CountrylnfoS5erviceView xmi does not exist and will be created and contain your generated procedures Procedure Generation Options User specified Procedures Define user specified request and response procedures from your WSDL schema elements Legacy Procedures Generate legacy create and extract procedures Bak Cancel Finis Figure 4 42 WSDL Source Selection 122 Import WSDL into Relational Models e Step 6 This wizard generates both request and response procedures that are us
184. ional options and choose Finish 4 7 3 Import Relational View Tables CSV Format Tocreate relational virtual tables from imported text file metadata Perform Steps 1 through 3 above and select the Relational Virtual Tables CSV Format import type then click Next 117 Chapter 4 Importers Import Metadata From Text File Select Import Type Select the type of text file import desired Import Type Relational Virtual Tables Purpose Imports virtual relational tables and procedures from a CSV text file into a relational virtual model Table data is expected to be of the form gt gt TableName SOL Statement Description A sample of typical input data is shown below Sample Ale Format VTablel SELECT FROM PMod1 PTabl PMod1 PTable2 Descrip no embedded quotes VTable2 SELECT FROM PMod1 PTab2 Descrip 2 with quotes xxx VTable3 SELECT FROM PModl PTab3 Descrip 3 Figure 4 39 Select Import Type Relational Virtual Tables CSV Format Step 4 In the next page you ll need to provide a source text file containing the metadata formatted to the specifications on the previous page 118 Import WSDL into Relational Models Import Metadata From Text File Select Source Text File and Target Virtual Relational Model Select CSV Ale to Import and Destination Model or model folder Select Source Text Hle Target Location Browse Hle Contents Cancel Fig
185. ions 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 New Child gt New Sibling Mew Association Modeling Hz Copy Ctrl C Delete Delete Select All Ctrl A Add Bookmark Add Task Figure D 71 Edit Menu he Edit menu contains the following actions New gt Child This menu is created dynamically to support the creation of whatever types of child objects can be created under the selected object New gt 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 X Undo Reverses the effect of the most recent command 3 8 Refactor Menu 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 2 Paste Pastes the contents of the clipboard to the selected context Paste Special Provides additional paste capabilities for complex clipboard objects Clone Duplicates
186. is will retrieve your web service s WSDL and the WSDL URL address will appear in the browser s address bar Endpoint Name jboss ws context books endpoint MySqlBooks BOOKS Endpoint Address http 127 0 0 1 8080 books MySglBooks BOOKS StartTime StopTime Mon Jun 11 15 16 02 CDT 2012 RequestCount ResponseCount FaultCount 0 0 0 MinProcessingTime MaxProcessingTime AvgProcessingTime 0 0 0 Now that you have your WSDL URL can use any SOAP testing tool such as the Web Service Tester that comes with JBDS and JBoss Tools or an external tool like soapUl Using the JBoss AS Administration Console Using the administration console that comes with JBoss AS you can deploy WAR files The administration console is available at http host port admin console Once logged on s simply use the Add a New Resource button of the Web Application WAR resource folder REST WAR Testing What is my URI 239 Chapter 6 Metadata specific When you modeled your REST procedures you assigned a URI for each HTTP Operation you defined along with the corresponding operation GET PUT POST or DELETE The full path of each URI is defined as war_context model_name resource for XML input output and war_context model_name json resource for JSON input output Using your REST URL you can use any testing tool with REST support such as the Web Service Tester included with JBDS and JBoss Tools or an external tool like soapUl or CURL
187. itled Schema Workspace Location Selection This page lists all schemas imported by the WSDL along with any dependent schemas referenced within schemas as well as schemas embedded in the WSDL and indicates whether or not they are resolvable All resolved schemas will be created in a separate file and added to the workspace The editor panel allows you to change the default file name of the new schema file s If no errors select Next to proceed or Finish to complete with default option 130 Import WSDL From Workspace Location Fe a Create Web Service from WSDL File Schema Workspace Location Selection All workspace schema locations are valid Click Next to continue Schema Location Information e Namespace Source Hle http flocalhast Share BSM Real Time Data Design Server RealTimeData port OnHand fhome blafond T 4 Editor http localhost Share BSM Real Time Data Design Server RealTimeData port OnHand Felder WSDLimportProject Name without file extension onHand Next gt Cancel Figure 4 50 Namespace Resolution Dialog Step 7 The last page titled XML Model Generation allows you to change the name of the XML View model if the Generate virtual XML document model is checked Input desired name or use the default name provide Select Finish to complete 131 Chapter 4 Importers Create Web Service from WSDL File XML Model Generation Ready to generate the Web service model
188. jects Wizards For view models only tables procedures standard procedures and user defined functions and indexes can be created For view tables and procedures the primary difference in the wizards is that they include a SQL Transformation tab 164 Model Object Editors Create Relational View Table amp 3 Table name cannot be null or empty This wizard allows creating a complete relational table including columns unique keys and Foreign keys definition Modelle PartsViewsami 0 In Source Transformation SQL Columns Primary Key ll Unique Constraint F Foreign Keys 1 Indexes SOL Definition SELECT TABLEA COL1 TABLEA COL2 TABLEB COL1 FROM TABLEA TABLEB WHERE TABLEA COL1 TABLEB COL1 Figure 5 18 Create Relational Table Dialog 5 3 Model Object Editors The primary actions for editing model objects are of Cut Deletes the selected object s and copies it to the clipboard Copy Copies the selected object s to the clipboard B Paste Pastes the contents of the clipboard to the selected context 165 Chapter 5 Creating and Editi 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 e Rename Allows a user to rename an object These actions are presented in Teiid Designer s main Edit menu and also in t
189. kspace using the browse button Step 7 Check the Copy all descriptions option if desired Click Finish New Model Wizard Copy an Existing Model Select an existing model to be copied Existing Model USCustomers xmi Model Contents gt 8 ACCOUNT gt ACCOUNTHOLDINGS gt E CUSTOMER gt O Data Source Copy all descriptions Cancel Figure 3 6 Copy An Existing Model Dialog 3 3 2 Build XML Documents From XML Schema This option creates an XML View document model based on a selected XML schema and its dependencies To create a new XML document view model by from XML schema complete Create XML Document View Model above and continue with these additional steps Step 5 Select the model builder labeled Build XML documents from XML schema and click Next The Select XML Schema dialog will be displayed 60 Build XML Documents From XML Schema e Step 6 Select an existing schema model from the workspace using the browse button 61 Chapter 3 New Model Wizards zm LEA New Model Wizard Select XML Schema Select XML Schema file then select Schema Root Elements Or press Anish to create an empty XML Document XML Schema Ale BooksProject Books 0 Select one or more Schema Root Elements to generate Virtual Documents Schema Root Elements Virtual Documents baokCollection BooksNS BookSetNested 1 e bibliography Booksh e bookLis
190. l s model element lt xml version 1 0 encoding UTF 8 standalone yes gt lt vdb version 1 name PartssupplierViewsVDB gt lt property value false name preview gt import vdb import data policies false version 1 name PartssupplierSourcesVDB model visible true type VIRTUAL name PartsViewModel path PartssupplierProject PartsViewModel xmi lt property value 1623826484 name checksum gt lt property value Relational name modelClass gt lt property value false name builtIn lt property value 655076658 INDEX name indexName gt property value PartssupplierSourcesVDB name 2 import vdb reference lt model gt 465 Designer exposes this capability by allowing users to import metadata from deployed VDBs via the JDBC Import option Through this import relational VDB source models are created which structurally represent the Catalog VDB Schema Model and Tables in Virtual DataBase When dealing with the these VDB source models there are some limitations or rules namely e VDB source models are read only e VDB source model name is determined by the deployed model name schema from the VDB it was imported from Model names have to be unique within a model project VDB source models have to be imported created in a project different than the project used to create and deploy the Reuse VDB The JDBC Import Wizard will restrict your options to comply with t
191. lder 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 3 5 1 Copy 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 To create a new relational model by copying contents from another web service view model complete Create Web Service View Model above and continue with these additional steps e Step 5 Select the model builder labeled Copy from existing model of the same model class and click Next The Copy Existing Model dialog will be displayed e Step 6 Select an existing relational model from the workspace using the browse button EY e Step 7 Check the Copy all descriptions option if desired Click Finish 66 Build From Existing WSDL File s or URL New Model Wizard Copy an Existing Model Select an existing model to be copied Existing Model USCustomers mi Model Contents gt 8 ACCOUNT gt ACCOUNTHOLDINGS gt amp CUSTOMER gt O Data Source Copy all descriptions Mext Cancel Figure 3 11 Copy An Existing Model Dialog 3 5 2 Build From Existing WSDL File s or URL This build
192. lder and nested functions you can create complex logic within your query transformations 5 3 2 Input Set Editor XML 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 Teiid Designer automatically 185 Chapter 5 Creating and Editi 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 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 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
193. le connectionProfile driver class oracle jdbc OracleDriver translator name oracle user name partssupplier Figure 9 1 Connection Profile Information Dialog No Connection Info Available Use Modeling gt Set Connection Profile action to select and apply connection info to model Q No connection info stored in model RelModelA xmi Figure 9 2 No Connection Info Dialog 260 Remove Connection Profile from Source Model 9 1 3 Remove Connection Profile from Source Model As a user you may not want this connection information i e URL username etc shared through your VDB Designer provides a means to remove this connection information via a Modeling Remove Connection Info action When adding a source model without connection information will require the user to supply or select the correct translator type 9 2 Previewing Data For a Model 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
194. le Source Selection Press the Next gt button to continue Data File Source Parts XML File New XML Data File Folder location usr share telid Data File Name parts xml Selected Data File parts xml Source Model Definition Location parts Name PartxMLS ource Model Status NEW MODEL Source model PartxMLSource does not exist Model with required getTextFiles procedure will be created on FINISH lt Back c Figure 4 25 XML Data File Source Selection Page e Step 5 The primary purpose of this importer is to help you create a view table containing the transformation required to query the user defined data file This page presents a number of options you can use to customize the Generated SQL Statement shown in the bottom 102 Import From XML Data File Source panel The to panel contains an XML tree view of your file contents and actions buttons you can use to create column entries displayed in the middle Column Information panel To create columns select a root XML element and right click select Set as root path action This populates the root path value Next select columns in the tree that you wish to include on your query and select Add selection as new column button You can also modify or create custom columns by using the ADD DELETE UP DOWN to manage the column info in your SQL When finished with this page click Next gt 103 Chapter 4 Importers
195. lick Window gt Show View gt Other to display the Figure D 6 Eclipse Show View Dialog dialog Choose Teiid Designer Description view and hit OK 329 Appendix D Teiid Designer Ui E Properties E Description 3 PRODUCTDATA table contains various stock attribute values including basic name primary business type etc Primary key ks PK PD INSTR ID Figure D 23 Description View You can click the edit description action in the toolbar or right click select Edit in the context menu to bring up the Edit Description dialog edit actions Properties B Description PRODUCTDATA table contains warious stock attribute walues including basic name primary business type etc Primary key ks PK_PD_INSTR_ID Select All Find Bf Edit EN Clear Input Methods BaseTable Products PRODUCruarz Figure D 24 Description View Context Menu 330 Problems View Edit Description for PRODUCTDATA Description PRODUCTDATA table contains various stock attril basic name primary business type etc Primary key ks PK PD INSTR ID Cancel Figure D 25 Edit Description Dialog D 2 6 Problems View The Problems View displays validation errors warnings or information associated with a resource contained in open projects within your workspace 331 Appendix D Teiid Designer Ui warning 0 others ion Resource Path Location Typ rors 1 ite m SQL statement is empty
196. ling Action New Association Action 5 1 1 New Child Action Tocreate new child model objects in the Section D 2 1 Model Explorer View e Step 1 Select the parent object to which you want to add a child For example you can add a package to a package or an attribute to a class Step 2 Right click on a container object From the pop up menu select New Child You can now select the child object you would like to add 151 Chapter 5 Creating and Editi i 12 TwoSourceParts Lg PartsDB2 xmi gt import declarations 2 fia Package Diagram SHIP VIA d New Sibli gt ew Siblin E New Association gt HF gt SUPPLIER Modeling gt gt SUPPLIER Primary Key gt J Data Sour vec Unique Constraint PartsSQL xm Figure 5 1 New Child Action In Model Explorer Mew Child gt Access Pattern Step 3 The new model object displays on the Section D 2 1 Model Explorer View and is highlighted for renaming E TwoSourceParts v PartsDB2 xmi gt import declarations 2 Package Diagram PARTS FA PART ID string 4 EY PART string 255 EY PART COLOR string 30 EY PART WEIGHT string 255 A B PK PARTS gt E SHIP VIA gt amp STATUS Figure 5 2 New Model Object In Explorer e create new child model objects the Section D 3 1 1 Diagram Editor e Step 1 Select the pare
197. ltiple MEDs MEDs are stored in models so no added dependency needed in VDB Also see Section 5 4 Managing Model Object Extensions and Section D 3 3 Model Extension Definition Editor 1 3 9 1 Model Extension Definition MED 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 Namespace Prefix a unique identifier Typically only a small number of letters and can be used as an abbreviation for the namespace UHI e Namespace URI a unique URI Extended Metamodel URI Model Class the metamodel URI that is being extended Each metamodel URI also has model class and that is typically what is shown in the Designer The 16 Model Object Extensions model classes supported for extension are Relational Web Service XML Document and Function Version currently not being used Description an optional description or purpose Extended Model Object Types Metaclasses a set of model object types or metaclasses that have extension properties defined Properties the extension property definitions grouped by model object type 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 lt xml version 1 0 encoding UTF 8 standalone no gt smoceVbxtemsuonme
198. ma 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 Chapter 1 Introduction 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 35 models For more information see Section 5 3 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 li
199. ment wsdl definitions http terraservice net terraservice2 asm Bak Next Cancel Figure 4 58 WSDL Validation Problems Dialog 4 9 3 Import WSDL From URL You can create a Web Service model by selecting a WSDL file based on a URL e Step 1 Choose the File Import choose the File gt Import action py in the toolbar or select a project folder or model in the tree and choose Import e Step 2 Select the import option Teiid Designer gt WSDL File or URL gt gt Web Service Model and click Next Step 3 Input a valid name for your Web Service model and select the URL button Enter a valid WSDL URL If the URL cannot be validated then an error will be displayed and the OK button disabled e f the WSDL is protected by basic HTTP authentication then this option should be selected and the appropriate username and password entered e Click OK to continue Click Next to continue 139 Chapter 4 Importers WSDL URL Enter WSDL Url http terraservice net terraservice2 asmx wsdl Security HTTPBasic User Name user Password esee Figure 4 59 WSDL URL Dialog Step 4 The next page is titled Namespace Resolution This page identifies successful and errant WSDL namespace resolution The main WSDL document will essentially always be resolved since the workspace file chooser is used to obtain the path Problems will occur when the main WSDL file imports ot
200. mimesp mbtpv ose ems oem 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 eschemehocastron https orgsetevidomodedExtension 20 modelExtension xsd miles E EDU xt ensTconm 209 lt p description This as my model extension p description gt lt p extendedMetaclass name com metamatrix metamodels relational impl BaseTableImpl gt lt p property advanced false index true masked false name copyable required false type boolean gt lt p description locale en_US gt Indicates if table can be copied lt SP description Oo display localle en US gt Copyaole pp dicolay gt lt p Property lt p extendedMetaclass gt The MED Registry is where the MEDs used by Designer stored MED files can be edited by opening the mxd file in the Section D 3 3 Model Extension Definition Editor 1 3 9 2 Model Extension Definition Registry MED Registry 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 17 Chapter 1 Int
201. model class 3 4 1 Copy 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 To create a new relational model by copying contents from another XML schema model complete Create XML Schema Model above and continue with these additional steps e Step 5 Select the model builder labeled Copy from existing model of the same model class and click Next The Copy Existing Model dialog will be displayed e Step 6 Select an existing relational model from the workspace using the browse button E e Step 7 Check the Copy all descriptions option if desired Click Finish Hew Model Wizard Copy an Existing Model Select an existing model to be copied Existing Model USCustomers xrmi Model Contents gt 8 ACCOUNT 8 ACCOUNTHOLDINGS gt E CUSTOMER gt O Data Source Figure 3 10 Copy An Existing Model Dialog 3 5 Creating Web Service View Model 65 Chapter 3 New Model Wizards Create Web Service View Model To create a new empty web service view model e Step 1 Launch the New Model Wizard e Step 2 Specify a unique model name Step 3 Select Web Service option from Model Class drop down menu e Step 4 Select View Model from Model Type drop down menu e Step 5 Click Finish i Note You can change the target location i e project or fo
202. mple SQL Hesults for Preview Data view The view will automatically open or get focus if not visible in your perspective 9 2 3 Preview Relational Procedure o preview a relational procedure e Step1 Selecta relational procedure in the Section D 2 1 Model Explorer View or diagram The procedure can be in a view model as well as a source model e Step 2 Right click select the Preview Data action You can also select the same action in the tool bar of either the Section 0 2 1 Model Explorer View or diagram e Step3 An input parameter input dialog is presented Enter a valid value for each parameter 1 Note If data entered does not match the parameter datatype String integer etc an error message will be displayed in the dialog header When all required values are entered click the OK button to execute the query 264 zzz Miflin Preview Web Service Operation Preview Data To execute this procedure input values are required All parameter values are set Select OK to continue Input Parameters colorIn string blue Figure 9 6 Procedure Parameter Input Dialog e Step 4 Your query results will be displayed in the Section 9 2 5 Sample SQL Results for Preview Data view The view will automatically open or get focus if not visible in your perspective 9 2 4 Preview Web Service Operation e To preview a Web service operation Step 1 Select a Web service ope
203. mport Progress Dialog 4 3 Import From Teiid Data Source Connection The Teiid Connection Source Model import option provides a means to create relational source models from JDBC and other deployed data sources that are not supported by other Teiid Designer importers NOTE To launch this importer you must have at minimum a Teiid 8 x server running in Designer The Teiid importer deploys a dynamic VDB to Teiid containing the selected source type then the schema as determined by Teiid is retrieved We expect to move towards this type of import in future versions of Teiid Designer You can create relational source models from your deployed data source connections using the steps below e Step 1 n Model Explorer choose the File gt Import action py in the toolbar or select a project folder or model in the tree and choose Import Step 2 Select the import option Teiid Designer Teiid Connection Source Model and click Next Step3 Select the datasource to use for the import You can create a new source if it doesnt exist as well as other source management functions Click NEXT to continue 80 Import From Teiid Data Source Connection Import using a Telid Connection Select the DataSource to use for the import Press the Next gt button to continue Server mm Localhost 9999 Data 5ources New DataSource Driver ExampleDS ih2 Delete m FlatFiLleDs teiid connec
204. multaneously 344 Editors a Ga PartsSourceA xmi 3 GA PartsVirtual xmi 3 9 t Bh h hc fe Fa gm Ay Ay Ay A Ay A A B d h ph Model Path Synchronized Visible Source Name Translator INDI Mame 8 PartsSourceA xmi PartsProject vi vi PartsSourceA db2 Parts Source IIS DERE D Tu E DET m Sens amp PartsVirtual xmi PartsProject Wi A Other Files Data Roles Path Synchronized Description Data Role Descriptio Remove Synchronize All Figure D 40 Viewing Multiple Editors 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 345 Appendix D Teiid Designer Ui 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 T
205. n 126 Import WSDL From Workspace Location URL Detailed steps for each of these options is described below as well as a description of how the wizard handles WSDL errors 4 9 1 Import WSDL From Workspace Location You can create a Web Service model by selecting a WSDL file from your workspace e Step 1 Choose the File Import choose the File gt Import action py in the toolbar or select a project folder or model in the tree and choose Import e Step 2 Select the import option Teiid Designer gt WSDL File or URL gt gt Web Service Model option shown below and click Next e Step 3 Input a valid name for your Web Service model and select the Workspace button Locate your workspace WSDL file in the selection dialog and click OK Click Next gt to continue Create Web Service from WSDL File WSDL File Selection e Press the Next gt button to continue Web Service Model Name OnHandws Workspace Hle System URL Ider Test In J BDS JBDS H45 workspace WSDLlmportProject OnHand wsdl Figure 4 46 WSDL File Selection Dialog 127 Chapter 4 Importers WSDL File Selection 5 p Oo n ni d i ya 5 d Step 4 The next page is titled Namespace Resolution This page identifies successful and errant WSDL namespace resolution The main WSDL document will essentially always be resolved since the workspace file chooser is used to obtain the path Problems will occur when
206. n 3 Calumn data rows for each table must appear immediately following the table data row 4 The Index data is expected to be of the farm gt gt INDEX IndexMame Type Uniqueness Tablespace Column 5 Index data rows must appear immediately following the table column data rows A sample of typical input data is shown below Sample Ale Format CATALOG Catalog 1 Catalog 1 Description Project_1 MyModel 1 SCHEMA Schema 1 Schema 1 Description Project l MyModel l Catalog 1 Figure 4 37 Select Import Type Relational Tables CSV Format e Step 4 In the next page you ll need to provide a source text file containing the metadata formatted to the specifications on the previous page 116 Import Relational View Tables CSV Format Import Metadata From Text File Select Source Text File and Target Relational Model Select CSV Ale to Import and Destination Model or model folder Select Source Text File ow Browse Target Location Customerlnfo xmi Browse Ale Contents Options Use default datatype string 255 Figure 4 38 Select Source Text File and Target Relational Model Step 5 Select an existing relational model as the target location for your new relational components using the Browse button to open the Relational Model Selector Dialog Select a relational model from your workspace or specify a unique name to create a new model e Step 6 Select any addit
207. n Step 3 e Step 2 Right click select Modeling gt Create Web Service action PartsSourceB xmi Te Parts Virtu abii New gt New Child gt Modeling gt Generate SQL Relationships Open Transformations Export SQL Open With gt Transformations Import SQL o 3 B copy Figure 6 21 Create Web Service Action e Step 3 In the Create Web Service dialog specify file names for the generated Input Schema file Output Schema file and Web service model Change options as desired Click Finish when done 226 Create Web Service Action Create Web Service Create Web Service Click Anish to generate Location ___ See Specify location for generated modells i e Project or Folder Browse Output Schema Model ____________ Name Parts Output Generate flat XSD Input Schema Model Name Parts Input Generate flat XSD Parts WS Generate default SQL for XML mappings Generate Web Service Cancel Finish Figure 6 22 Create Web Service Dialog Step 4 When model generation is complete a confirmation dialog should appear Click OK 22 Chapter 6 Metadata specific Generation completed successfully Generation completed successfully New Model PartsVirtual Output xsd created New Model PartsVirtual Input xsd created New Model PartsVirtual Ou
208. n applicable if no teid server defined Figure C 2 General Teiid Designer Preferences Panel C 1 1 Diagram Preferences Several diagram preferences are available to customize your diagrams Notations Standard diagram notation for Teiid Designer is based on UML notation Future releases may include alternate notations Routers The relationship link type for Package and Custom diagrams Foreign Key Primary Key relationships can be customized Available options include Orthogonal default Direct or Manual user defined breakpoints Font Settings Select font type style and size Background Color Settings Select a unique background color for each diagram type to help differentiate between types Model Size Displaying very large diagram may take a considerably long time This preference allows users to set an upper limit on the number of objects to display in a diagram If this limit is exceeded a warning is displayed to the user and the diagram is not constructed Relationship Options UML type relationships can be customized in a couple of ways Role Names and Multiplicity labels can be shown or hidden using the check boxes labeled Show Role Names and Show Multiplicity 301 Appendix C User Preferences Diagram Qv F General settings for Diagrams Notations Default Notation UML Routers Default Router Style Orthogonal Font Settings Font Type Sans Regular 8 View Change
209. n select an attribute from any of the source classes in the transformation Constant to add a hard wired constant value to the predicate If you click this radio button the Predicate Editor looks like this 5 Attribute Constant Function Apply to replace selected Attribute with Constant below Type string Value Figure 5 27 Constants Panel 178 Transformation Editor Select the datatype for this constant from the Type drop down list and enter the value in the Value edit box Function to add a function If you click the Function radio button the Predicate Editor looks like this Attribute Constant w Function Choo se Function below to replace selected Attribute CONCAT PartsSourceA SUPPLIER PARTS SHIPPER ID PartsSourceB SUPPLIER SUPPLIER STATUS Figure 5 28 Functions Click the Edit button to use the Expression Builder to construct a function to use in the predicate of your SQL Criterion For more information about the Expression Builder see Section 5 3 1 4 Using the Expression Builder Step 4 Set a value left side of the predicate and when necessary the right side of the predicate If the right side of the predicate does not require a value of some sort the Criteria Builder will not let you enter one Step 5 Click Apply e Step 6 When you have created both a Left Expression and a Right Expression in the Predicate Editor click Apply to add the crite
210. n to continue or the Finish button to finish Export DDL Importer DDL CREATE FOREIGN TABLE accounts ACCOUNT ACCOUNT ID long NOT NULL DEFAULT 0 OPTIONS ANNOTATION NAMEINSQ SSN string 10 OPTIONS ANNOTATION NAMEINSOURCE SSN NATIVE TYP STATUS string 10 OPTIONS ANNOTATION HAMEINSOURCE STATUS NAT TYPE string 10 OPTIONS ANNOTATION NAMEINSOURCE TYPE NATIVE T DATEOPENED timestamp NOT NULL DEFAULT CURRENT TIMESTAMP OPTIONS DATECLOSED timestamp NOT NULL DEFAULT 0000 00 00 00 00 00 OPTIONS A OPTIONS ANNOTATION NAMEINSOURCE accounts ACCOUNT UPDATABLE CREATE FOREIGN TABLE accounts CUSTOMER SSN string 10 NOT NULL DEFAULT OPTIONS ANNOTATION NAMEINSOURC FIRSTNAME string 64 OPTIONS ANNOTATION NAMEINSOURCE FIRSTNAME LASTNAME string 64 OPTIONS ANNOTATION NAMEINSOURCE LASTNAME ST ADDRESS string 256 OPTIONS ANNOTATION NAMEINSOURCE ST ADD APT NUMBER string 32 OPTIONS ANNOTATION NAMEINSOURCE APT NU CITY string 64 OPTIONS ANNOTATION NAMEINSOURCE CITY NATIVE TY 7 lt Back Cancel Finish Figure 4 12 Review DDL Dialog e Step 6 On the final page of the wizard a difference report is presented for viewing or de selecting individual relational entities Press Finish to complete 83 Chapter 4 Importers Import using Teikd Connection Review Model Updates sz Review the changes that will be appl
211. nd resolvable Models must always be validated when they are deployed in a VDB for data access purposes Teiid Designer will automatically validate all models whenever they are saved G Note The Project gt Build Automatically menu option must be checked When editing models the editor tabs will display a to indicate that the model has unsaved changes 1 3 8 Testing Your Models Designing and working with data is often much easier when you can see the information you re working with The Teiid 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 The preview functionality insures that data access behavior in Teiid Designer will reliably match when the VDB is deployed to the Server See for details 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 in Teiid Designer 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 Data Tools SQL Results view This action is accessible throughout the T
212. ne of the criteria you set evaluates to true You can set this default using the combo box available in the Choice Editor s toolbar to e Any of the options within the table except those you have excluded from the document THROW to throw a Teiid Server exception RECORD to record the Teiid Server exception DISCARD to place no element within the XML instance document G Note A default action for the choice criteria must be set 5 3 4 Recursion Editor XML 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 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 190 Recursion Editor XML The first cycle can be thought of as an entry condition for the recursion The mapping class located at this node defines a normal mapping transformation like that of any other in the document model The second cycle defines a mapping transformation that will be performed repeatedly until conditions are met that will halt the document instance being generated by the Teiid Server This fragment of the document model is called the recursive fragment Th
213. nsert 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 hitp host port war_context model_name json resource will accept URI path parameters and or JSON Step 1 In a virtual model add a procedure s that returns an XMLLiteral object The target of your procedure can be any models in your VDB Here is an example procedure that selects from a virtual table VirtualBooks and returns the results as an XMLLiteral 231 Chapter 6 Metadata specific SOURCES ea Transformation Diagram Table Editor Transformation Editor Fi N X CREATE VIRTUAL PROCEDURE BEGIN SELECT XMLELEMENT NAME books XMLAGG XMLELEMENT NAME book XMLFOREST RestfulBooks RestfulBooks VirtualBooks SUBTITLE RestfulBooks VirtualBooks PUBLISHER RestfulBooks VirtualBoc RestfulBooks VirtualBooks TYPE RestfulBooks VirtualBooks AUTHOR 10 AS result FROM RestfulBc RestfulBooks VirtualBooks ISBN END otice the syntax used to convert the relation table result of the select from VirtualBooks to an MLLiteral All XML functions are documented in the Scalar Functions chapter of the Teiid Reference Guide H 232 ere is an example of an update proce
214. nt models are detected the Save Model As Import References dialog is presented to give you the opportunity to change any of the dependent models imports to reference the new model or not 245 Chapter 7 Editing Models and Save Model As Import References The following model files use or reference the model to be copied Select any that should be modified to use the new copy amp AllcustamerAccounts CustomerViews xmi Select All Deselect All 9 Figure 7 7 Save Model As Dialog 7 4 Clone Project 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 e The following lists specific rules and limitations for this action This action clones a complete model project containing any number of model XMI or XSD files organized in a user defined directory structure All object references UUIDs within the original project will be replaced with new unique references Any model dependencies or internal object references are refactored to reflect the dependencies within the cloned project Any model references to models in projects external to the original project will NOT be replaced Only XMI and XSD files are cloned All other file types in your project will NOT be processed nor c
215. nt object to which you want to add a child For example you can add a package to a package or an attribute to a class Step 2 Right click on a container object From the pop up menu select New Child You can now select the child object you would like to add 152 New Child Action New Child Access Pattern New Sibling A Column New Association gt rJ Foreign Key Modeling p Ne P Primary Key Undo Create NewColumn Unique Constraint Redo Figure 5 3 New Child Action In Diagram e Step 3 The new model object displays on the diagram and is highlighted for renaming 4 bs NewColumn ci Figure 5 4 New Model Object In Diagram To create new child model objects in the Section D 3 1 2 Table Editor e Step 1 Select the row for the parent object to which you want to add a child For example to add a column click the Base Table tab and select base table row e Step 2 Right click on a table row From the pop up menu select New Child You can now select the child object you would like to add 153 Chapter 5 Creating and Editi N PartsViews xmi Base Tables Columns F Foreign Keys P Primary Keys Location Name Name In Source System Cardinality Supports Ly Em M m New Child New Sibling d A Access Pattern SHIP VIA STATUS SUPPLIER SUPPLIER New Association
216. o create your Teiid instance e Select the New action in the Server view or click the New Teiid Instance button ri in the toolbar his will launch the New Teiid Instance dialog shown below 321 Appendix D Teiid Designer Ui o New Tetid Instance Create a new Teiid Server Connection Define the Teiid Server connection properties required to perform both JDBC and Admin tasks Display Name Teiid 8 2 Final 57 JBoss Server JBoss Server New Teiid JDBC Connection Info Used Eo auto connect Eo your VDB as part of the Execute VDB and Preview Data actions The value of the lt vdbname gt will be determined at execution time User name user Password C JDBC URL Template jdbc teiid lt vdbname gt mm localhost 3 1000 Figure D 14 New Teiid Instance Dialog e Enter a unique display name for your Telid instance e Select the New button in the JBoss Server section to launch the New Server wizard Select the JBoss AS 7 1 1 server type under the JBoss Community category and click Next gt 322 Server View O New Server Define a New Server Choose the type of server to create Download additional server adapters Select the server type type Filter text F gj JBoss 455 1 JBoss AS6 x JBoss AS 7 0 S JBoss AS7 1 b 2 JBoss Enterprise Middleware JBoss Application Server 7 1 Server s hast name x localhost Server name JBoss 7 1 Runtime Se
217. om the transformation SQL that are not bound to a target table attribute Here are a few things you can do in the list section Add SQL Symbols To Add SQL Symbols to the list click the Add button You will be presented with a dialog showing all available symbols from your transformation source tables Click on the symbols you want to add then click OK Remove or Clear Symbols To remove one or more of the SQL symbols select the list items then click the Remove button To clear the entire SQL symbols list click the Clear button Sort Symbols By default the symbols are shown in the order that they appear in the SQL query To show them alphabetically in the list click the Sort button Binding Controls The Binding Controls are located between the Attribute Symbol table and the Unmatched SQL Symbols list Use these buttons to define the Attribute Symbol bindings Here are a few things you can do with the binding controls Bind This button will Bind a SQL Symbol to a target attribute Select an Unmatched SQL symbol and select a target attribute then click Bind to establish the binding Unbind This button will Unbind an Attribute Symbol binding Select an already bound attribute in the table then click Unbind The SQL Symbol will be released to the Unmatched Symbols list New This button will create a new target attribute using an Unmatched SQL Symbol Select an Unmatched Symbol from the list then click
218. on and use the actions and propertes table in the lower Extension Properties section to add remove or edit your actual extended properties Selecting the add or edit extension properties actions displays a dialog containing sections to edit general properties value definition required masked allowed values as well as display name and description values which can be internationalized 199 Chapter 5 Creating and Editi a Property Definition Enter the property s general information value infor General Enter general property characteristics Model Object BaseTable Namespace Prefix mymodelextension 10 copyable Runtime Type boolean Should only be modified by advanced users v Will be used by Telid server Value Definition Enter the characteristics of the property value value is required Mask the property value when shown to user v The value must be one of the followi ng false Fa K _ _ Use this initial value The value can only be this value i 2 _ Display Name a E t Description Figure 5 45 Edit Property Definition Dialog AX 200 Extending Models With MEDs 5 4 3 Extending Models With MEDs MEDs must be applied to a model in order for their extension properties to be available to that model s model objects To manage the applied MEDs for a specific model select the model and right click select the Modeling gt Manage Model Extension Definitions action This w
219. 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 To use the Criteria Builder e Step 1 In the Transformation Editor click the Launch Criteria Builder button E e Step 2 The Criteria Builder displays 176 Transformation Editor v EA Criteria Builder Initial Expression PartsSourceA SUPPLIER PARTS SUPPLIER ID PartsSou SQL View PartsSourceA SUPPLIER PARTS SUPPLIER ID PartsSourceB SUPPLIER SUPPLIER ID Criteria Editors Attribute Constant Function Attribute Constant Value is unchanged Value is unchanged 7 PartsSourceA SUPPLIER PARTS wv PartsSourceA SUPPLIER FA PartsSourceA SUPPL E PartsSourceA SUPPL E PartsSourceA SUPPL FA PartsSourceA SUPPL PartsSourceB SUPPLIER PartsSourceB E PartsSourceB SUPPL PartsSourceB SUPPL EJ PartsSourceA SUPPLIER PARTS PART ID PartsSourceA SUPPLIER_PARTS QUANTITY FA PartsSourceA SUPPLIE
220. on of the defined Teiid server always takes precedence over the 2 1 3 Server Version Status Panel Whether the server version preference has been modified or a server defined the server and server target version will be updated in the default server status panel This will always reflect the current server version being targeted and the server being used to preview or deploy against 21 Chapter 2 Dive Right In Default Server Server No default server defined Targeted Version 7 7 X Figure 2 3 Default Server Status Panel 2 2 Guide Example In this section the Guides View is demonstrated in detail 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 1 Define Teiid Model Project 2 Create JDBC connection 3 Create source model for JDBC data source 4 Preview Data 5 Define VDB 6 Execute VDB 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 2 2 1 Model a JDBC Source This section shows how to Model a JDBC Source using the Guide View action set We will connect to a MySQ
221. onal Tables CSV Format 115 4 7 3 Import Relational View Tables CSV Format 117 4 8 Import WSDL into Relational Models 119 4 8 1 Circular References in WSDL Schemas 125 4 9 Import WSDL Into Web Service ssssesesseesn menn 126 4 9 1 Import WSDL From Workspace Location 127 4 9 2 Import WSDL From File System Location 133 2 559 Import WSDE From URE 139 4 10 XSD SShema FIG uses 145 5 Creating and Editing Model Objects 151 5 1 Creating New Model Objects 151 GTT New Child Wie 151 5 1 2 INOW SIDING ACUON 154 5 1 3 New Association Action iius ansias ubi eer dob evi wl ba wi xi ow d veo nb edd 156 5 2 New Model Object Wizards 158 5 2 1 Create Relational Table Wizard 158 5 2 2 Create Relational Procedure Wizard 159 5 2 3 Create Relational Index Wizard
222. opied into your newly cloned project including VDBs If one or more editors that require save are open the user will be asked to save them before continuing with the cloning process e To clone a model project 246 Clone Project e Step 1 Select an existing model project in the Section D 2 1 Model Explorer View Step 2 Right click then select Model Project gt Clone in the context menu Otherwise you can select the Project gt Clone Project action located in Teiid Designer s main menu bar Metadata Run Window Help Close Project Build Working Set gt Clean Build Automatically Clone Project h Build Project Imports h Build All Imports Properties Figure 7 8 Clone Project In Project Menu Step 3 On the Clone Project wizard page provide a name for your new project 247 Chapter 7 Editing Models and Clone Model Project Clone Model Project Specify cloned model project name Project name ValuedCustomerAccaunts Use default location Figure 7 9 Clone Project In Project Menu Step 4 Optional If you wish to create your cloned project in a location other than your default workspace location uncheck the Use default location check box and specify type in or browse to a new directory location on your local file system Step 5 Click Finish to generate your new project 248 Chapter 8 Managing VDBs As stated in the introduction the critical artifa
223. or postion and text length values in the upper left panel to determine what your column widths are in your data file 93 Chapter 4 Importers When finished with this page click Next gt 94 Import From Flat File Source m Import From Flat File Source Flat File Fixed Columns Width Parser Settings Press the Next gt button to continue Selected Data Hle EmpDataFixedWidth t t Format Options File Contents Preview Data line if Department string WIDTH 5 Annua Sa Lary integer WI if MgrId integer WIDTH 8 Street string WIDTH 14 Cit Cursor Position 24 ce 2 Ki sselmeyer Abbieg ale Ti k vica 9000059 G 64000 Glore Diodie Vojvoda 5000060 G 71000 Dawson Pinckney Ostoja 9000061 G 71000 Waldrip Trixie Curic 9000062 57000 Kitchen Zilpha Buic 9000063 G 60000 Wakeman Gerard Vlahovic 9000 Batff Koareds gt 064 Column Name Datatype firstName string FY middleName string DOWN DELETE Tetid TEXTTABLE Function Options Include HEADER Include SKIP Include QUOTE Include ESCAPE Generated SQL Statement SELECT A firstName A middleName column_3 FROM EXEC ssss getTextHlesl EmpDataFRxedWidth txt AS f TEXTTABLE file COLUMNS firstName string width 12 middleName string width 12 column 3 string width 12 AS A lt Back Cancel Anish Figure 4 19 Flat File Fixed Columns Width Options Page 95
224. ore Defaults OK C Figure 2 1 Default Server Version Preference 2 1 2 Defining a Teiid Server The defining of a Teiid Server is encouraged since it allows for models to be previewed and their deployment tested There is no limit to the number of servers that can be defined However the default server will always be used for previewing and deployment unless using the context menu actions in the Section D 2 3 Server View The Guides View provides the following Teiid Server actions 20 Server Version Status Panel Guides i Status Modeling Actions Action Sets Telid Server v Al New Teiid Server Edit Teiid Server Properties A Set the Default Telid Server Description no action selected gt 1 Figure 2 2 Teiid Server Category in the Guides View The New Server action will display the wizard outlined in Figure D 14 New Instance Dialog and steps through the process of creating both the Teiid Server and its parent JBoss server in the Server View Should more than one Teiid Server be defined in the Server View then the Set the Default Server action allows for the default server to be changed appropriately If a Teiid Server is currently selected in the Server View then this will be selected as the default server However should nothing be selected then a dialog will be displayed inviting the user to choose which server they wish to select i Note The versi
225. ormation Diagram 7 Table Editor Via Ezd Cursor at 3 6 Supports Update Hn 5 SELECT A LastName A FirstName A MiddleName A Ermpld A Department A AnnualSalary A Title A Hom A Mgrld A Street A City A State A ZipCode FROM EXEC EmplayeeSource getTextFiles EmployeeData txt AS f TEXTTABLE f file COLUMNS LastNam FirstName string MiddleName string Empld string Department string AnnualSalary string Title string HomePhone string string Street string City string State string ZipCode string HEADER AS A Figure 4 22 Generated Flat File View Table 4 5 Import From XML Data File Source e supports XML Files as data sources You can import from these data sources and create the metamodels required to query your data in minutes Using the steps below you will define your flat file data source configure your parsing paramaters for the xml data file generate a source model containing the required Teiid procedure and create a view table containing the SQL defining the column data in your xml data file As with Designer s JDBC Salesforce and WSDL importers the XML File importer is based on utilizing a specific Data Tools Connection Profile 98 Import From XML Data File Source The results of the importer will include a source model containing the getTextFiles procedure or invokeHTTP procedure which are both supported by Teiid The importer will also create a new view model
226. ource Model 259 9 1 3 Remove Connection Profile from Source Model 261 9 2 Previewing Data For Model eret ente deor 261 9 2 1 Preview Relational Table or View 262 9 2 2 Preview Relational Table With Access Pattern 263 9 2 3 Preview Relational Procedure 264 9 2 4 Preview Web Service Operation 265 9 2 5 Sample SQL Results for Preview Data 266 S EAS Eeun RT 267 9 3 Testing With YOUR VDB vanes E YR EUR Sa niiit 268 9 9 1 Creating 268 9 3 2 Execute VDB from Model Explorer 270 9 3 3 Deploy VDB from Model Explorer 271 9 3 4 Executing a Deployed VDB 272 SO AN GANG BN m TT 279 10 1 PINGING Model ODJECIS 279 10 2 Search Transformation 281 10 3 Search Models Via Metadata Properties 283 F
227. p 3 Use the Criteria Builder to create the conditions for which the Teiid Server will test to determine whether to choose this option in the XML instance document e Step 4 Click OK The criteria you set displays both in the table and in the summary tab You must set a criterion for each option in your document unless you have selected to exclude that option or specify that option will be the default option 5 3 3 4 Setting Choice Element Order To edit the criteria for a choice element 189 Chapter 5 Creating and Editi The Teiid Server evaluates the choice criteria in the order in which they appear and when one choice criteria is met the Teiid Server populates the XML instance document with that option The Teiid Server might not test all criteria for all options so their order matters a great deal Therefore the order in which your options appear within the choice criteria often determines what information appears ultimately in your XML instance documents You can reorder the option list within the choice to set the order in which the Teiid Server tests the criteria To set this order select element in the table use the or m button to move it into a new position in the table The new order displays both in the table and in the Choice Criteria box and reorders the XML document as well 5 3 3 5 Setting a Default Choice Action The default action represents the course the Teiid Server should take if no
228. pecific error message Those error message relate to the error messages shown in the common header section Here is an example of the error decoration 3 3 Appendix D Teiid Designer Ui Namespace URI a Figure D 66 Text Field With Error The MED sub editors share a header section The header is composed of the following e Status Image an image indicating the most severe validation message error warning or info If there are no validation messages the model extension image is shown Title the title of the sub editor being shown Menu a drop down menu containing actions for 1 adding to and updating the MED in the registry and 2 for showing the Model Extension Registry View Validation Message this area will display an OK message or an error summary message When a summary message is shown the tooltip for that message will enumerate all the messages Toolbar contains the same actions as the drop down menu Below is an example of the shared header section which includes an error message tooltip 69 Overview 1 error detected Namespace URI The namespace URI cannot be empty __ Figure D 67 Shared Header Example D 4 Teiid Designer Main Menu There are 8 categories of actions on Teiid Designer s main menu bar These categories include e Section 0 4 1 File Menu Resource management actions Section D 4 2 Edit Menu Standard edit actions including undo redo Sec
229. 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 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 227 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 37 7 Appendix D Teiid Designer Ui D 4 2 Edit Menu The Edit menu provides act
230. present represents the contents of container objects i e Model Category Schema etc its context menus are limited to adding removing objects from diagram and basic diagram related display options D 3 1 1 3 Transformation Diagram The Transformation Diagram represents a view of the relationships defined by the source inputs described in a view table s SQL transformation 352 Model Editor SOURCES Ej SUPPLIER ID string E SUPPLIER NAME str SUPPLIER STATUS s E SUPPLIER CITY strir SUPPLIER STATE str Ej SUPPLIER ID string 10 PART ID string 50 QUANTITY short SHIPPER ID short 4 Ea bu Bu ba ba F FK SPLIER STATS P PK SUPPLIER ea Transformation Diagram Table Editor Figure D 45 Transformation Diagram Example Transformation Diagram toolbar actions include Refresh Diagram Re draws diagram 3 Show Parent Diagram Navigates to diagram for parent object if available Preview Data Executes a simple preview query SELECT FROM Add Transformation Sources Add selected sources to transformation 353 Appendix D Teiid Designer Ui iB Add Union Transformation Sources Add selected sources as union sources 124 en Remove Transformation Sources Removed sources selected in diagram from transformation Ha Clear Transformation Remove all sources from transformation gt Open Transformation Reconciler dialog Sa
231. pter 4 Importers KML Schema Url Enter XML schema URL http ns hr xml o rg 2 4 HR XML 2 4 StandAlone Resume xsd Optional User Name Password Verify Hostname HTTPS Cancel Figure 4 67 Add XSD Schema URLs The schema URL is now displayed in the XML Schema URLs list 148 XSD Schema File Import XML Schema Files Add XML Schemas URLs wt folder Jptions Figure 4 68 Add XSD Schema URLs e Step 6 Click Finish 149 150 Chapter 5 Creating and Editing Model Objects This section summarizes Teiid Designer features for creating and editing existing model objects contained in your models 5 1 Creating New Model Objects As discussed in the introduction Section 1 1 What is Telid 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 You can create new model objects directly in the Section D 2 1 Model Explorer View view Section D 3 1 1 Diagram Editor or Section D 3 1 2 Table Editor using the following actions New Child Action New Sib
232. ptions Template Options SELECT Queries PROCEDURES Simple SELECT SELECT with Join Criteria UNION Query Flat File Local Source XML File Local Source 5 XML File URL Source SQL Template SELECT FROM TABLEA Figure 6 17 SQL Templates Dialog 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 6 3 XML Document Modeling 6 3 1 Create XML View Documents from schema You can create XML View Documents by selecting an element in the Model Explorer and selecting the Modeling gt Create XML View Documents action 222 LAS NE et AE Sg i7 ima mm oci a un ll al G3 Books xsd 7 5 Books xsd Create XML View Documents from schema 4_ BookDatatypes xsd gt MetaMatrix Data Set Library Version 1 1 Boo O bibliography RooksNS Ribliooranhw bookListing New Child bookCallectic New Sibling bookSetMixe New Association Create Scalar To XML Tr BookSetFlat BookFlat Bibliography Modeling Bilbiagraphyt eJ T BookSetNest Cut BookNested _ Create XML To Scalar Tr ndo Set org ecli to count Accountid Y ndo Set org ecli
233. r The Column Foreign Key Primary Key reference properties are properly set on the selected columns new primary key and new foreign key 5 2 New Model Object Wizards In addition to the simple new object actions Teiid Designer provides New Child and New Sibling wizards for children of view and source relational models Namely tables views procedures and indexes The menu now looks like New j New Child Base Table Modeling Procedure Open 8 View Open With TI Index Copy fi Custom Diagram Figure 5 11 Relational Model New Child Menu 5 2 1 Create Relational Table Wizard The Create Relational Table Wizard shown below allows creating a complete relational table including columns unique keys foreign keys definition and indexes 158 Create Relational Procedure Wizard Create Relational Table Provide unique name properties and desired columns This wizard allows creating a complete relational table including columns unique keys and Foreign keys definition Model File PartsO xmi Mame Name In Source B Properties 8 Columns Primary Key ll Unique Constraint F Foreign Keys Indexes Mative Query Cardinaliby 1 Supports Update Is System Table Description Cancel Figure 5 12 Create Relational Table Dialog Note that the relational view wizard Is identical to the relational table wizard 5 2 2 Create Relational Procedure Wizard The Create Relational Procedure Wi
234. r confirming the attempt to modify the model and asking the user to confirm or cancel This dialog can be prevented by checking the preference Always open editor without prompting You can re set uncheck this property via Teiid Designer s main preference page Open Model Editor You must open this model in an editor tab before you can modify the property value Do you want to open the editor Always open editor without prompting Cancel Figure D 21 Open Model Editor Dialog Properties can also be edited via a right click menu presented below 328 Description View Value nt ila 1 i See CA SUPPLIER NA 99 zl 1 1 Copy f Paste string Path Delete varchar Cut T x Select All m Input Methods gt MEZ Insert Unicode Control Character gt ink be true p ni Figure D 22 Open Model Editor Dialog The Properties toolbar contains the following actions Show Categories toggles between categorized properties and flat alphabetical properties list e l A Show Advanced Properties shows hide advanced properties if available Restore Default Value for a selected property this action will reset the current to a default value if available D 2 5 Description View The Description View provides a means to display and edit add change or remove a description for any model or model object To show the Description View c
235. ransformation 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 D 3 1 Model Editor 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 D 3 1 1 Diagram Editor 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 E Package Diagram 346 Model Editor Bi Custom Diagram Transformation Diagram Mapping Diagram Mapping Transformation Diagram 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
236. ration in the Section D 2 1 Model Explorer View or diagram The operation can be in a view model as well as a source model Step 2 Right click select the Preview Data action You also select the same action in the tool bar of either the Section 0 2 1 Model Explorer View or diagram Step 3 An input parameter input dialog is presented Enter a valid value for each parameter i Note If data entered does not match the parameter datatype String integer etc an error message will be displayed in the dialog header When all required values are entered click the OK button to execute the query 265 Chapter 9 Testing Your Models Preview Data To execute this procedure input values are required All parameter values are set Select OK to continue Input Parameters AUTHOR ID xsilong 10022 OK Cancel Figure 9 7 Procedure Parameter Input Dialog Step 4 Your query results will be displayed in the Section 9 2 5 Sample SQL Results for Preview Data view The view will automatically open or get focus if not visible in your perspective 9 2 5 Sample SQL Results for Preview Data Preview Data results are displayed in the Eclipse Datatools SQL Results view as shown below i Note There are a number of display preference and filter options for this view via toolbar buttons and the dropdown menu 266 Execution Plans Problems O Message Log SQL Results 5
237. reate Relational Model from Web Service WSDL Operations Selection Press the Finish button to finish Select the desired WSDL Operations ia Share BSM Real Time Data Design Server RealTimeData portService 2 Share BSM Real Time Data Design Server RealTimeData_portPortd 7 Share BSM Real Time Data Design Server RealTimeData portBinding Selection Details OnHand Operation id http dladb 7 Share BSM Real Time Data Design Server RealTimeData p input message _OnHandIinput output message OnHandOutput fault names none Next gt Cancel Finish Figure 4 55 Namespace Resolution Dialog e Step 6 The next page is titled Schema Workspace Location Selection This page lists all schemas imported by the WSDL along with any dependent schemas referenced within schemas as well as schemas embedded in the WSDL and indicates whether or not they are resolvable All resolved schemas will be created in a separate file and added to the workspace The editor panel allows you to change the default file name of the new schema file s If no errors select Next to proceed or Finish to complete with default option 136 Import WSDL From File System Location Fe a Create Web Service from WSDL File Schema Workspace Location Selection All workspace schema locations are valid Click Next to continue Schema Location Information e Namespace Source Hle http flocalhast Share BSM Real Time Data Design Serv
238. reate 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 Consume a SOAP Web Service a Cheat Sheets 8 Avra Consume a SOAP Web Service v Introduction v amp Create New Teiid Model Project gt Create SOAP Web Service Connection Follow the steps below to create an ODA Web Service connection profile using the WSDL URL and end point information v Launch Create SOAP Connection Profile gt 3 Wizard v 1 Specify unique name and optional Iv description and click Next gt 2 Enter a WSDL URL or Browse to select a local WSDL 3 Click Test Connection to verify connectivity 4 Click Finish Create Models From SOAP Connection t Create VDE t Test VDE Figure 2 23 Create SOAP Connection Profile next to Launch Create SOAP Connection Profile Wizard to launch the wizard The first page of the wizard is shown below 41 Chapter 2 Dive Right In New Connection Profile New Connection Profile gt Create a SOAP Web Service connection profile in filter text Connection Profile Types Web Services Data Source SOAP CountryInfoConr Description optional n 0 T Figure 2 24 Create SOAP Connection Profile
239. reating New Relational View Model A 3 3 Creating XML Document View Model x 3 4 Creating XML Schema Model e Section 3 5 Creating Web Service View Model Use one of the following options to launch the New Model Wizard New Model Wizard e Choose the File gt New gt Metadata Model action Select a project or folder in the Section D 2 1 Model Explorer View and choose the same action in the right click menu e Select the New button on the main toolbar EX select the Metadata Model action At tm b P 51 Chapter 3 New Model Wizards New Model Wizard New Model Wizard Specify model name and options then create model file Location osei cas Hotel Boe Select a model builder optional E Generate Hle Translator Procedures a Generate Web Service Translator Procedures E Copy from an existing model of the same model class Figure 3 1 Import Wizard Selection Dialog 3 1 Creating New Relational Source Model Create New Relational Source Model Tocreate a new empty relational source model Step 1 Launch the New Model Wizard 52 Generate File Translator Procedures e Step 2 Specify a unique model name Step 3 Select Relational option from Model Class drop down menu Step 4 Select Source Model from Model Type drop down menu e Step 5 Click Finish G Note You can change the target location i e p
240. 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 i 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 D 2 13 Guides View To open Teiid Designer s Guides view select the main menu s Window Show View Other and select the Teiid Designer Guides view in the dialog 340 Guides View The Guides view provides assistance for many common modeling tasks The view includes categorized Modeling Actions and also links to Cheat Sheets for common processes Cheat Sheets are an eclipse concept for which Teiid Designer has provided contributions see Section D 2 15 Cheat Sheets View The Guides view is shown below a Guides BN wa Status mL Modeling Actions Action Sets Model J
241. rence Warning XML Root Element mapped to Mapping Class Warning lt lt Mapped XML Elements Attributes Nillable Ignore Incompatible Datatypes for Column to Element Attribute Mappings Warning Restore Defaults Apply Figure C 11 XML Document Model Validation Preferences Panel Validation a Specify the Severity Level for validation related occurrences Core Relational XSD XML XSD Validation Control Settings XML Schema Document walidation problems Ignore Figure C 12 XSD Schema Model Validation Preferences Panel 308 Validation Preferences 309 310 Appendix D Teiid Designer Ui Reference D 1 Teiid Designer Perspectives Designer utilizes the Eclipse http www eclipse org 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 D 1 1 Teiid Designer Perspective The Teiid Designer perspective provides access to fundamental model
242. rences Care Relational XSD Relational validation Central Settings Missing length property on columns with string or character types Warning Missing precision property on colurnns with numeric types Warning Columns of built in type integer Warning Table missing name In source value Nvarning Mame in source conflicts with sibling entitles Ignore Indexes with columns from more than ane table Warning w Empty transformations Error Restrict name characters to valid unquoted SOL identifiers Ignore w Groups are not joined either directly or transitively to other groups possible crass join lanore w Figure C 10 Relational Model Validation Preferences Panel 307 Appendix C User Preferences Validation ae Specify the Severity Level for validation related occurrences Core Relational XML Validation Control Settings XML Document Elements Attributes not referencing an XML Schema component Warning Excluded Element from XML Document required by XML Schema Warning lt lt P XML document entity violates max occurs specified by its schema component Warning 5 Unmapped required XML element or attribute Error Mapped XML Element Attribute has fixed or default value Warning 5 Excluded Elements Attributes from XML Document are mapped Warning 5 Mapped XML Elements Attributes with zero minimum accurrences Ignore Mapped XML Elements Attributes with one maximum accur
243. replacing specific existing objects creating new same named objects or cancel import entirely 114 Import Relational Tables CSV Format Duplicate Objects Exist Options Replace Existing Objects See list below Create New Objects Anyway results in validation errors 3 Cancel Import Duplicate Objects getProductinfo ProductData ProductiDIndex ProductSymbals Productview Select All Deselect All Cancel Figure 4 36 Duplicate Objects Dialog 4 7 2 Import Relational Tables CSV Format e To create relational tables from imported text file metadata Perform Steps 1 through 3 above and select the Relational Tables CSV Format import type then click Next 115 Chapter 4 Importers Import Metadata From Text File Select Import Type Select the type of text file import desired Import Type Relational Tables Purpose Imports relational schema catalogs tables columns and indexes from a CSV text file into a relatio 1 Schema Catalog and Table data is expected to be of the form gt gt SCHEMA CATALOG or TABLE Mame Description Optional Location Optional gt gt Locations are of the form ProjectName FolderName ModelName SchemaName gt gt f the project folder model or schema catalog containers do not exist they will be created 2 The Calumn data is expected to be in the form gt gt COLUMN ColumnMame JDBCType Length Descriptio
244. returning a result set Lastly enter or unique source model name in the Source Model Definition section at the bottom of the page or select an existing source model using the browse button When finished with this page click Next gt 87 Chapter 4 Importers m Import From Flat File Source Data File Source Selection Next gt button to continue Press the Data File Source EmployeeData Available Data Files Folder location fhome blafond TestDesignerFolder FlathleData employee data Data Ale Name EmpDataFixedWidth txt EmplayeeData txt PlayerD ata tet Selected Data Ale PlayerData txt Source Model Definition Location TestXxmllmpart Mame TextHleProcedures xmi EXISTING MODEL Source model TextHleProcedures xmi already contains required Model Status getTextHles procedure Figure 4 15 Data File Source Selection Page e Step 4 The next page titled Flat File Column Format Definition requires defining the format of your column data in the file The options are Character delimited and Fixed width 88 Import From Flat File Source This page contains a preview of the contents of your file to aid in determining the format The wizard defaults to displaying the first 20 lines but you can change that value if you wish When finished with this page click Next gt 89 Chapter 4 Importers 90 m Import From Flat File Source
245. reveals object in Model Explorer wu Additional Actions The additional actions are shown in the following figure gt gt Show all file types Show Imports Select Working Set Deselect Working Set ink with Editor L Figure D 8 Additional Actions lf Show Model Imports is checked the imports will be displayed directly under a model resource as shown below Partsvirtual xmi w import declarations 4 4 fPartsProject PartsSourceA xmi 4 jPartsProject PartsSourceB xmi 4 http www w3 org 2001 XMLSchema 4 PartsProject SampleViews xmi Package Diagram gt 8 Supplierinfo Figure D 9 Show Model Imports Action 317 Appendix D Teiid Designer Ui D 2 1 1 Selection Based Action Menus Selecting specific objects in the Model Explorer provides a context from which Teiid Designer presents a customized menu of available actions Selecting a view model for instance results in a number of high level options to manage edit model content perform various operations and provides quick access to other important actions available in Teiid Designer These may include specialized actions based on model type PartsSourceB xmi New Child Base Table iE B M Catalog 1 Index gt tes Open With L Logical Relationship Procedure E Copy i Schema Paste Ctrl V Wz E View we LLLI Custom Diagram Delete Refa
246. rion to the tree view at the top of the dialog box 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 179 Chapter 5 Creating and Editi 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 5 3 1 4 Using the Expression Builder The Transformation Editor s Expression Builder offers you a quick graphical means to build expressions in your transformations This Expression Builder lets you create Attributes by selecting an attribute Constants by selecting the datatype and value Functions from both the standard Teiid Designer SQL functions and your enterprise s custom user defined functions If you select a function before you launch the Expression Builder you can use the Expression Builder to edit the selected function otherwise you can create a new function from scrat
247. rmation Find and replace a string within your transformation e Validate the transformation to ensure its content contains no errors 168 Transformation Editor 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 C 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 Fe 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 at 1 1 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 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
248. roduction User Defined MED these are created by the user These MEDs can be updated registered and unregistered by the user The MED Registry state is persisted and is restored each time a new session is started 18 Chapter 2 Dive Right In 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 Targeting the Teiid Server The Teiid Server is the destination for Designer s modelling It is essential to define the correct server version that models will be deployed to This is achieved either by setting the server version preference or defining a teiid server in the Servers View 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 se
249. roject or folder by selecting the Browse button and selecting a project or folder within your workspace e 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 3 1 1 Generate File Translator Procedures 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 To create a new relational model containing file translator procedures complete Create New Relational Source Model above and continue with these additional steps e Step 5 Select the model builder labeled Generate File Translator Procedures and click Next gt The Generate File Translator Procedures dialog will be displayed Step 6 Check one ore more of the Available File Translator Procedures then Click Finish 53 Chapter 3 New Model Wizards New Model Wizard Generate File Translator Procedures Generate default relational procedures compatible with Teiid Ale translator Select desired procedures and RHnish Available File Translator Procedures getHlesi path ext return blob getTextHlesi path ext return clob saveHlel path value return void Cancel Figure 3 2 Generate File Translator Procedures Dialog 3 1 2 Generate Web Service Translator Procedures This builder option allows construction of a relational model
250. roperties e Step 3 Click Search The search will be performed and the results will be displayed in the Section D 2 7 Search Results View If the view is not yet open it will be opened automatically 284 Appendix A Supported Data Sources 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 G Note The DDL Import option is an available option to build a source model for any data source lts only indicated below when there s no specific importer created for that specific data source type Table A 1 Teiid Designer Supported Data Sources Data Source Translator Type Designer Import Option Apache Derby JDBC derby JDBC Importer Files file File Importer General JDBC JDBC jdbc simple JDBC Importer HDFS Hadoop hive use DDL importer or perform modeling manually HSQL JDBC hsql JDBC Importer H2 JDBC h2 JDBC Importer Ingres JDBC ingres Ingres 2006 JDBC Importer or later JDBC ingres93 Ingres 9 3 or later IBM DB2 JDBC db2 JDBC Importer Informix JDBC informix JDBC Importer LDAP ActiveDirectory ldap use DDL importer or perform modeling manually LoopBack JDBC loopback use DDL importer or perform modeling manually MetaMatrix JDBC metamatrix JDBC Importer ModeShape JCR JDBC modeshape JDBC Importer Mondrian olap use DDL importer or perform
251. roperty to TRUE and setting the Materialized Table reference as shown in the figure below 214 Create Materialized Views E Properties 23 Description Ed r4 Property Value Info Misc Cardinality Logical Relationships Materialized Ir true If true the table can be materialized cached Only applies to Materialized Table E PARTS Path PartsTest MaterializedParts xmi MaterializedParts Name PARTS Name In Source in PARTS Supports Update Ir false System 17 false m BaseTable PartsViewModel PARTS Figure 6 11 Materialized Table Properties Designer includes a feature to assist in quickly creating materialized relational tables based on your existing view tables To create materialized views e Step 1 Right click on one or more view tables in the Section D 2 1 Model Explorer View and select the Modeling Create Materialized Views action Lm y i n LE LS E amp PARTS New Child New Sibling New Association Modeling Create Materialized Views Create Web Service uin ta Add To New Custom Diagram Pie of Cut Ctrl X um Add to Metadata Favorites B Copy Ctrl C Paste Ctrl V 215 Chapter 6 Metadata specific Step 2 In the Create Materialized View Model dialog specify or select a target relational model for your generated relational tables Create Materialized View Model Create Materialized View Model All inputs OK Pres
252. rties 6 2 2 Create Relational View Table Wizard In Teiid Designer 7 7 a new action New Child gt 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 gt Base Table action which only created the empty table Custom Diagram Ctrl V Figure 6 13 New View Table Wizard Action 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 218 Create Relational View Table Wizard 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 The first tab labeled Properties contains the input for the simple table properties including name and supports update Create Relational View Table S x Create Relational View Table i No columns defined for table This wizard allows creating a complete relational view table including SQL definition gl Properties 4 Columns myTable Supports Update Figure 6 14 Properties Tab The Columns tab allows
253. rver lt Back Figure D 15 New Server Dialog e On the JBoss Runtime page click the top Browse button to select the installation folder of your JBoss AS 7 1 server 323 Appendix D Teiid Designer Ui o New Server JBoss Runtime m JBoss eg oy Red dat A JBoss Server runtime references a JBoss installation directory It can be used to set up classpaths For projects which depend on this runtime as well as by a server which will be able bo start and stop instances of JBoss Name JBoss 7 1 Runtime Home Directory Download and install runtime Fond T esting Servers AS 7 1 1 8 2 Betaz 10 19 2012 Browse JRE Corr LE LE Configuration file standalone teiid xml Browse lt Back Net Cancel Finish Figure D 16 JBoss Runtime Definition hen click the bottom Browse button to select the standalone teiid xml configuration file located under the standalone configuration folder on your file system Then click Finish to return the the New Teiid Instance dialog 324 Server View Name Size Modified EJ standalone xml history 11 14 _ application roles properties 634 bytes 03 10 2012 application users properties B12 bytes 03 10 2012 _ legging properties 20KB 03 10 2012 Lj mgmt users properties B36 bytes 03 10 2012 standalone xml 14 6KB 11 11 standalone Full xml 20 3KB 03 10 2012 I standalone Full ha xml 26
254. s Procedures Operations 2 SQL matches for Parts Supplierlnfo Table PartsProject PartsVirtual xmi az SELECT WF partsByColor lt Procedure gt PartsProject PartsVirtual xmi E SELECT SELECT SQL String CREATE VIRTUAL PROCEDURE BEGIN SELECT FROM PartsSourceB PARTS WHERE PartsSourceB PARTS PART COLOR PARTSVIRTUAL PARTSBYCOLOR colorln END Figure 10 4 Search Transformations Dialog Step 2 Specify a string segment in the Find field and specify change your case sensitive preference Step3 Select Perform Search button Any transformation object containing SQL text which contains occurances of your string will be displayed in the results section You can select individual objects and view the SQL If a table or view supports updates and there is insert update or delete SQL present you can expand the object and select the individual SQL type as shown below 282 Search Models Via Metadata Properties Search Tranformations Perform Search Search Status Search complete for Parts Virtual Tables Views Procedures Operations 3 SQL matches for Parts gt E Supplierlnfo Table PartsProject PartsVirtual xmi gt partsByColor lt Procedure gt PartsProject PartsVirtual xmi v E PARTS Table PartsProject PartsVirtual xmi E SELECT E INSERT gt UPDATE DELETE JMSERT SQL String CREATE PROCEDURE BEGIN ROWS UPDATED INSERT INTO PartsSourceB PARTS PartsSourceB PAR
255. s Anish to create materialized view tables Materialization Model materializedParts Target Location MaterializedParts Selected Virtual Tables PARTS SHIP VIA Step 2 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 216 Create Materialized Views Relational Model Selector Dialog within a model in which the entities will be created PartsTest Northwind xmi gt sr PartsSupplier xmi PartsTestVDB vdb gt 5 PartsViewModel xmi Og ProductsOracle xmi New Model Name MaterializedParts Click OK to create the relational model Properties Cancel 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 217 Chapter 6 Metadata specific GA PartsViewModel xmi MaterializedParts xmi 3 PART ID string 50 Ej SHIPPER ID bigdecimal Ej PART string 255 SHIPPER NAME string 30 PART COLOR string 30 PART WEIGHT string 255 e E d Figure 6 12 Materialized Table Prope
256. s 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 A fixed number of results to the query e A SQL based criteria limit condition A combination of both To open the Recursion Editor click Recursion Editor button o on the displayed mapping class 191 Chapter 5 Creating and Editi Figure 5 38 Open Recursion Editor Button 192 Recursion Editor Supervisor Count Limit 5 Action When Count Limit Exceeded THROW Recursion Editor XML Limit Condition Edit ErnployveeDocs EmployeeDocument MappingClasses Supervisor name Joe Smith Figure 5 39 Recursion Editor To edit recursion properties i Note e Step 1 Click the Enable Recursion check box if you want the Teiid Server to perform the query you specify to generate the nested tags within the XML document Step 2 Click the arrows beside the Count Limit box to limit the number of times to recursively perform the query If you do not set a Limit Condition in the text area the recursion finishes when the query reaches this limit You can only set this limit to a maximum supported by your Teiid Server For more information about this limit contact your system administrator Step 3 Click the Action When Count Limit Exceeded drop down menu to instruct the Teiid Server what
257. s exist for a VDB the Show Import VDBs button will be enabled and allow viewing the names of the imported VDBs as shown below 256 Next gt Reusing VDBs Parts AAA VDB vdb PartsLayered VDB vdb Z3 a Guides M di Ste Modeling Actions meee Other Files Import VDBs See ste YDB Properties Translator Ove Data Role Description 25 208 Chapter 9 Testing Your Models As described briefly in Section 1 3 8 Testing Your Models can test your models 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 9 1 Manage 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 profile
258. s or edit existing connection profiles without leaving the wizard The 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 9 1 1 Set Connection Profile for Source Model Teiid Designer integrates Data Tools Connection Profiles by persisting pertinent connection information in each source model This can occur through Importing process or through the Modeling Set Connection Profile action 9 1 2 View Connection Profile for Source Model In addition to setting the connection profile on a source model you can also view a source model s connection profile information via the Modeling View Connection Info action which displays the detailed properties of the connection 259 Chapter 9 Testing Your Models m Model Connection Info I Mda The properties in the table below are currently stored in the source model PartsSourceB xmi operties name value connectionProfileDescription connectionPrafileCategory org eclipse datatools connectivity db category connectionProfileProviderld org clipse datatools enablement oracle connectionProfile connection url jdbc oracle thin engledbs11 mm atl redhat com 1521 ORCL connectionProfileName PartsOraclell connectionProfilelnstancelD org eclipse datatools enablement orac
259. s the Next gt button to continue Selected Data Ale EmplayeeData txt Format Options File Contents Preview Column names in header LastName FirstName MiddleName EmpId De Header line 1 Kisselmeyer Abbiegale Tikvica S00005c p 2 i Glore Diodie Vojvoda 9000060 G 71000 Farse Selected Row Waldrip Trixie Curic 9000062 G 57000 Edit Delimiter Character Kitchen zZilpha Buic 9000063 G 60000 gt Edit function options Column Information Column Name Datatype LastName string FirstName string MiddleName string EY Empld string ADD DELETE UP DOWN Generated SQL Statement SELECT LastName A ArstName A MiddleName A Empld A Department A AnnualSalary A Title A HomePhone A Marld Street A City A State A ZipCode FROM EXEC AAAA getTextHles i EmployeeData t xt AS f TEXTTABLE file COLUMNS LastName string ArstName string MiddleName string Empld string Department string AnnualSalary string Title string HomePhone string Mgrld string Street string City string State string ZipCode string HEADER AS A 2 Next gt Cancel Finish Figure 4 17 Flat File Delimited Columns Options Page 92 Import From Flat File Source To aid in determining if your parser settings are correct you can select a data row in your File Contents Preview section and cli
260. ss Insert new mapping class referenced to the selected XML document element or attribute New Staging Table Insert new staging table referenced to the selected XML document element or attribute ES Merge Mapping Classes Merge selected mapping classes Split Mapping Class Split selected mapping class Display All Mapping Classes Show Mapping Class Columns l A Filter Displayed Mapping Classes with Selection Context menus for Mapping Diagrams provide Edit capability to the mapping class in addition to mapping class manipulation actions i e Merge Mapping Classes Split Mapping Class etc D 3 1 1 5 Mapping Transformation Diagram The Mapping Transformation Diagram is identical to a Transformation Diagram except for displaying an Input Set and possibly Staging Tables as sources for the Mapping Class s transformation 356 Model Editor BuoksXML xmi ME NI ns i VIEW SOURCES T i book pe isbn a Subs gt ht a uenrte Input Set im titio im subtil i if edition od authore E sequence ME author t irj suthor 7 ae publishing rifarmaden gt i Sequence bz 2 gubligshar As 9 publishDate 4 ig ISBN stringi 255 EA TITLE stringl255l SUBTITLE string 255 Ej PUBLISHER Int PUBLISH YEAR inl E EDITION int Ed stringiz551 E ______
261. st We want to generate the View Metadata model Address Pool from these enterprise information systems Address Book Vendor Text Database File Figure 1 2 Data Flow for View Transformations 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 Source and View Metadata 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
262. sted e bibliography BooksNS Bibliography L baokSetMixed BooksNS BookSetMixed Keep sorted alphabetically Document Options Build full virtual documents from the schema 5 Build only first level of each document for large schema where many portions will not be used Mapping Options Build Mapping Classes pes Locate at XML Schema iteration nodes max occurs gt 1 for schema compliant data access 3 Locate at XML Schema complex types for semantic use only Mapping Class Column Datatypes Use XML types from the document 3 Use String mapping type disables automatic output conversion to closest built in XSD type g Cancel Anis Figure 6 20 Build XML Documents From XML Schema Dialog 225 Chapter 6 Metadata specific 6 4 Web Services Modeling 6 4 1 Create Web Service Action This method is recommended for experienced users for consistent and rapid deployment of Web services designed to query relational sources It provides detailed control of all Web service interfaces operations and required transformations from XML Views To create a Web service model from relational models or objects e Step 1 Select any combination of relational models tables and or procedures in the Section D 2 1 Model Explorer View tree i Note It is recommended that the user selects single source models which enables auto naming of input output schema and Web service models i
263. structural and data characteristics of the information contained in data sources These are referred to as source models represented by 10 How is a Model Defined Cg 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 LA E cat re Entity Metadata relationship imported ai ERN Entity Metadata Figure 1 5 Model Internals 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 e XML Web services e 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 1 3 2 How is a Model Defined Models are defined using Teiid Designer in various ways 11 Chapter 1 Introduction Created via importing source data characteristics see Chapter 4 Importers e Manual creation via Chapter 3 New Model Wi
264. supports the Teiid feature of defining relational source models and binding them to multiple data sources Multi source models can be used to quickly access data in multiple sources with homogeneous metadata When you have multiple instances using identical schema Teiid can help you gather data across all the instances using multi source models In this scenario instead of creating importing a model for every data source one source model is defined to represents the schema and is configured with multiple data sources underneath it During runtime when a query issued against this model the query engine analyzes the information and gathers the required data from all sources configured and gathers the results and provides in a single result Since all sources utilize the same physical metadata this feature is most appropriate for accessing the same source type with multiple instances The VDB editors Models tab now contains a simplified model table on the left and a new tabbed panel on the right containing Model Details and Source Binding Definition tabs Click the Multi source check box if you wish to add additional source bindings Note that each binding must be defined with a unique Source Name as well as unique JNDI Name representing a deployed data source you your server 250 UDF support 2 Productsss VDB vdb 3 CAR UDF Jars Other Files Model Details Source Binding Definition Model 09 P Cig ProductsS
265. surance 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 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 5 3 3 3 Editing Choice Criteria To edit the criteria for a choice element e Step 1 In the table on the Choice Editor panel select the element you want to edit e Step 2 Click Edit Criteria button to launch the Criteria Builder dialog Ste
266. t Ctrl Z Show In Shift Alt W gt c3 Back Alt Left gt Figure D 73 Navigate Menu D 4 5 Search Menu The Search menu presents several specific search options 380 Search Menu Search Cirl H El Bie EP Remote Pointcut Matches SP Beans Text gt Teiid Designer gt Figure D 74 Search Menu Teiid Designer contributes a sub menu i e Teiid Designer gt to the main search menu as shown above Text gt Teiid Designer 2 Transformations oF E Metadata Find Model Object Figure D 75 Search Menu he individual actions in the Teiid Designer sub menu are described below Transformations Launches the Transformation Search dialog User can search models in the workspace for matching SQL text Search results appear in the dialog and user can select and view SQL as well as open desired transformations for editing Metadata Launches the Search dialog User can search for models in the workspace by specifying an Object Type and or a Data Type and or a property value Search results appear in the Section D 2 7 Search Hesults View view and double clicking a result will open that model in the appropriate editor Find Model Object Launches the Find Model Object dialog which can be used to find an object in the workspace by specifying all or part of its name Selecting the object will open itin the appropriate editor 381 Appendix D Te
267. t from vModel myTbl Execution Plan Text Plan Tree lt xml version 1 0 encoding UTF 8 standalone yes gt lt node name AccessNode gt lt property name Output Columns gt lt Value gt INTKEY integer lt value gt lt Value gt STRINGKEY string lt value gt lt Value gt INTNUM integer lt value gt lt vValue gt STRINGNUM string lt value gt lt Value gt FLOATNUM double lt value gt lt Value gt LONGNUM long lt value gt lt Value gt DOUBLENUM double lt value gt aston IRA hai erred e ME Shri Figure 9 9 Teiid Execution Plan View 9 3 Testing With Your VDB In Teiid Designer you can execute a VDB to test query actual data The requirements for VDB execution are A deployed VDB backed by valid deployed Data Sources e An instance of a Teiid Connection Profile configured for the deployed VDB 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 9 3 1 Creating Data Sources The mechanism by which VDBs are able to query actual data sources is the Data Source These are deployed configurations backed by database or source connection jars Each source model referenced within a VDB requiries a JNDI name representing
268. t 5 Found 7 match Cg Found 10 matcl Remove Selected Matches Delete te Remove All Matches E Search Again Edit C Figure D 29 Search Results Context Menu D 2 8 Datatype Hierarchy View To open Teiid Designer s Datatype Hierarchy view select the main menu s Window Show View Other and select the Teiid Designer Datatypes view in the dialog 335 Appendix D Teiid Designer Ui Model Expl Ts Datatype M lt OF anySimpleType xs anyType B anyURI gt n base64Binary E BookEdition xs nonNegativelnteger n Outline O boolean O date O dateTime OF decimal bigdecimal xs decimal biginteger xs decimal B integer xs decimal 5 xs integer gt B nonNegativelnteger xs integer gt Datatype details Property Defined by whiteSpace collapse decimal fractionDigits 0 integer maxinclusive 9223372036854775807 minlnclusive 9223372036854775808 pattern 0 9 integer Figure D 30 Datatype Hierarchy View D 2 9 Teiid Model Classes View The Model Classes View provides a hiearchical EMF centric view of the various metamodel classes available within Teiid Designer This view is primarily for informational purposes but can be used as a reference if creating relationships or searching your workspace for specific metamodel constructs 336 System Catalog View iat Model Explorer 5 Outline Teiid Model Classes
269. t Set Connection Profile action first then retry Figure 9 3 Preview Not Available 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 Missing Password Required Preview data requires password for model gt gt Products xmi Referenced connection profile gt gt AllProducts Figure 9 4 Missing Password Testing Your Transformations When editing transformation SQL in the Transformation Editor a special SQL Results data action is provided in editor tool bar X 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 9 2 1 Preview Relational Table or View 262 Preview Relational Table With Access Pattern To preview a relational table relational view or staging table Step 1 Select a relational table or view in the Section D 2 1 Model Explorer View or diagram The table or view can be in a view model as well as a source model Staging tables are not visible in the Section D 2 1 Model Explorer View so you need to open the mapping diagram and select it there e Step 2 Right click select the Preview Data action DU You can also select the same action in the tool bar of either the Section D 2 1 Model Explorer View or diagram Step 3
270. t Source Model Salesforce gt gt Source Model 3E Teiid Connection gt gt Source Model p WSDL File or URL gt gt Source and View Model SOAP p WSDL File or URL gt gt Web Service Model Ps Figure 4 1 Import Wizard 4 1 Import DDL Source relational models can be created by importing DDL You can create relational source models from your DDL using the steps below Chapter 4 Importers e Step 1 In Model Explorer choose the File gt action py in the toolbar or select a project folder or model in the tree and choose Import Step 2 Select the import option Teiid Designer gt DDL File gt gt Source or View Model and click Next gt Step 3 Select existing DDL from either Choose from file system or Choose from workspace set the Model folder location enter or select valid model name set Model type Source Model or View Model set desired options and click NEXT gt or Finish if enabled 72 Import DBL Provide DDL source Update relational source model PraductsSQLServer using DDL file hame Import DDL 3 blafond Runtime Workspaces test 7 1 0 E DDLTest ProductsSQLServer ddl fhome blafond Runtime Workspaces test lt Model folder DDLTest Model name ProductsSQLServer Model type Source Model Choose from file system Choose from workspace Choose Create model entities for DDL defined by unsupported DML e g Views
271. t 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 Chapter 1 Introduction 1 2 2 Business and Technical Metadata Metadata can include different types of information about a piece of data Technical metadata describes the information required to access the data such as where the data resides or the structure of the data in its native environment Business metadata details other information about the data such as keywords related to the meta object or notes about the meta object 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
272. tation examples and how to starting points Help Contents Launches the Help Window All of Designer s online documentation is accessible from there as well Search Launches the Help Search view which can be used to search for phrases in the documentation Dynamic Help Opens the docked dynamic help view Key Assist Ctrl Shift L Launches a dialog describing existing key assist bindings Tips and Tricks Launches a dialog to select one of any contributed Tips and Tricks help pages Cheat Sheets Launches a dialog to select one of any contributed Eclipse cheat sheets Project Examples A JBoss contributed action which provides quick access to import various project examples into your workspace Report Problem A JBoss contributed action which provides simple problem reporting Check for Updates provides access to retrieve updates to installed Eclipse software Install New Software provides access to install new software into your workbench About JBoss Developer Studio Launches the About dialog
273. the drop down list Use Connection Profile Info option or check the Use Model Info option and select an existing source model containing connection info 269 Chapter 9 Testing Your Models After creating your new data source it should now be shown in the Data Sources folder of the corresponding Teiid server 9 3 2 Execute VDB from Model Explorer f you have a Teiid instance defined and connected in your Servers view you can e Step 1 Right click a VDB in your Model Explorer select Modeling gt Execute VDB action This action will insure your selected VDB is deployed to Teiid create a Teiid Connection Profile specific for that VDB open the Database Development perspective and create a connection to your VDB a PartsProject gt data gt E readme Modeling Cis PartsSi b E Open Sample Open With SE Figure 9 11 Execute VDB Action e Step 2 Select your new Teiid connection profile and right click select Open SQL Scrapbook enter your designer SQL i e SELECT FROM TableXXXX select all text and right click select Execute Selected Text Scrap book 0 x B arts vdb Connection profile Type Teiid 7 1 Parts Teiid Connecti Z Database Stat SELECT FROM PartsSourceA Parts Figure 9 12 SQL Scrapbook Editor e Step 3 Results of query should be displayed in the SQL Results view on the Result1 tab 270 Deploy V
274. the import option Teiid Designer gt File Source XML gt gt Source and View Model and click Next gt 99 Chapter 4 Importers Select Choose import source Select an import source type filter text gt 2 Plug in Development D Run Debug gt Team Teiid Designer p DDL Ale gt gt Source or View Model Designer Text Ale gt gt Source or View Models En Hle Source Flat gt gt Source and View Model En Hle Source XML gt gt Source and View Model BJ JDBC Database gt gt Source Model M Salesforce gt gt Source Model a WSDL Ale or URL gt gt Source Model pf WSDL Ale or URL gt gt Web Service Model S XML Schemas 2 XML Cancel Anish Figure 4 23 Import from XML File Source Step 3 The next page of the wizard allows selection of the XML Import mode that specifies whether the XML file is local or remote The description at the top describes what operations this wizard will perform Select either the XML file on local file system or XML file via remote URL and click Next gt 100 Import From XML Data File Source ca ax Import From XML File Source XML Import File Options Select the desired XML Import Mode Description This wizard automates the construction of the source procedure and view table transformation necessary to que from your XML file source The view table transformation will contain a getTextHlest or invokeHTTPl procedure utilize the XMLTABLE function
275. the main WSDL file imports other WSDL files that cannot be resolved If no errors select Next to proceed or Finish if enabled to complete with default options 128 Import WSDL From Workspace Location Create Web Service from WSDL File Namespace Resolution All namespaces are resolved Select Next to modify the created schemas workspace locations Namespaces e Namespace Path http dladb07 fhome blafond Test Designer Folder example files wsdls OnHand wsdl Cancel Finish Figure 4 48 Namespace Resolution Dialog e Step 5 The next page WSDL Operations Selection allows customizing the resulting content of your Web Service model by selecting deselecting various operations and interfaces in the following dialog 129 Chapter 4 Importers T 1 DEA Create Relational Model from Web Service WSDL Operations Selection Press the Finish button to finish Select the desired WSDL Operations ia Share BSM Real Time Data Design Server RealTimeData portService 2 Share BSM Real Time Data Design Server RealTimeData_portPortd 7 Share BSM Real Time Data Design Server RealTimeData portBinding Selection Details OnHand Operation id http dladb 7 Share BSM Real Time Data Design Server RealTimeData p input message _OnHandIinput output message OnHandOutput fault names none Next gt Cancel Finish Figure 4 49 Namespace Resolution Dialog e Step 6 The next page is t
276. 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 Address Book vendor Text Database File Address XML Figure 1 3 Data Flow for XML Transformations 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 Chapter 1 Introduction 1 3 It s all in the Modeling 1 3 1 What Are Models 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 t
277. time metadata you created or imported This snapshot is in the form of a Virtual Database definition or VDB Chapter 1 Introduction As you create this runtime metadata Teiid Designer derives the runtime metadata from a consistent set of metadata models creates a subset of design time metadata focusing on the technical metadata that describes the access to underlying enterprise information systems Optimizes runtime metadata for data access performance You can continue to work with the design time metadata but once you have created a runtime metadata model it remains static 1 2 4 Source and View Metadata 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
278. ting BooksNS BookSetFlat Im bookSetMixed BooksNS BookSetMixed ra Document Options Build full virtual documents from the schema 5 Build only first level of each document for large schema where many portions will not be used Mapping Options Build Mapping Classes Cancel sak Figure 3 7 Select XML Schema Dialog e Step 7 Move the available schema root elements you want to become virtual documents in the new model over to the Virtual Documents list by using the arrow button gt for selected elements or the 62 Build XML Documents From XML Schema button to move all elements Step 8 Select the appropriate document options and mapping options Click Finish e Step 9 Click Finish to create a model of all selected document entities or optional click Next to view Selected Documents Statistics page which shows document entity statistics and gives you an idea the size of the model being created New Model Wizard Selected Documents Statistics This is an overview of the documents to be generated Select Next to preview and edit the document or Anish to create the document with default settings Documents 1 Elements Recursive Elements Complex Subtype Elements Attributes Total entiby count lt Back E Next Cancel Finish Figure 3 8 Selected Documents Statistics Dialog Step 10 Optional Click Finish to create a mod
279. ting model to a newly defined model You can choose a full copy or select individual model components for copy o create a new relational model by copying contents from another relational view model complete Create New Relational View Model above and continue with these additional steps e Step 5 Select the model builder labeled Copy from existing model of the same model class and click Next The Copy Existing Model dialog will be displayed Step 6 Select an existing relational model from the workspace using the browse button P e Step 7 Check the Copy all descriptions option if desired Click Finish New Model Wizard Copy an Existing Model Select an existing model to be copied Existing Model USCustomers xmi Model Contents gt 8 ACCOUNT gt ACCOUNTHOLDINGS gt E CUSTOMER gt O Data Source Copy all descriptions Cancel Figure 3 5 Copy An Existing Model Dialog 58 Transform From Existing Model 3 2 2 Transform From Existing Model This option is only applicable for creating a relational view model from a relational source model with the added feature of creating default transformations SELECT FROM SourceModel Table_X for each source table The steps are the same as for the Section 3 2 f Copy From Existing Model described above There is an additional option in the second page of the wizard which can automatically set the relational t
280. tion D 4 3 Refactor Menu Resource actions i e Rename Move etc Section D 4 5 Search Menu Find data within your workspace e Section D 4 6 Project Menu Model level actions Section D 4 7 Metadata Menu Custom metadata related actions Section D 4 9 Window Menu Change perspectives or add remove views to your perspective 3 4 File Menu e Section D 4 10 Helo Menu Access available Teiid Designer help documents Teiid Designer SQL Support Guide and Eclipse Overview information Designer PartsProject ParntsSourceA xmi jBoss Developer Studio Hle Edit Refactor Navigate Search Project Metadata Run Window Help Figure D 68 Application Main Menu D 4 1 File Menu The File menu provides actions to manage your workspace resources Designer PartsProject Parts Edit Refactor Navigate Search Project Me New Shiftt Alt N gt Open Ale Close Ctrl W Close All Shift Ctrl W Ctrl 5 Save A Shift Ctrl 5 Refr esh Conwert Line Delimiters To Print Switch Workspace Restart psg Import gg Export Properties Alt Enter 1 PartsSourceA xmi PartsProject 2 Parts vdb PartsProject 3 PartsSourceB xmi PartsProject Exit Figure D 69 File Menu 3 5 Appendix D Teiid Designer Ui The New gt sub menu provides specific actions to create various generic workspace resources as well as Telid Designer models and VDBs E
281. to do if it encounters more results for the query than the count limit before it reaches the limit condition Step 4 Click the Edit button to launch the SQL Section 5 3 1 3 Using the Criteria Builder to build a limiting condition for this recursion The Teiid Server will evaluate this condition each time it recursively performs this query If this criteria clause evaluates false the Teiid Server performs the query recursively again unless it has reached the Count Limit If the criteria evaluates true the Teiid Server performs the mapping for the current level and ends its recursive loop 193 Chapter 5 Creating and Editi When you have created the criteria it displays in the Limit Condition box When the Teiid Server dynamically populates your XML documents at runtime it will use the recursion specifications you entered here 5 3 5 Operation Editor Editing of Web Service Operation transformations is simplified via the Operation Editor When editing a Web Service model an additional editor tab labeled Operation Editor is available This editor shown below is comprised of e Operations section showing a tree view of Interfaces and Operations contained within the Web Service model Input Variables section providing editing of desired Input Variable declarations Procedure section providing SQL editing of the procedure Cg baokCollection WS xmi 24 Pi BooksXML xmi Operation Editor Cursor at 1 1
282. tor file rar Edit copy MySGLAccts mysql connector java 5 1 5 ar 15 MySGLAcctsDS mysql connector java 5 1 5 jar GreenPlumDS postgresqi 8 3 604 jdbc4 Jar Data Source Properties Name Value cannection url jdbe mysql localhost 3306 accounts user name root password kk a required property e lt Back Cancal Finish Figure 4 10 Select Deployed Data Source e Step 4 On the next page select the appropriate translator for your data source type as well as defined the target relational model that you wish to create or update Click NEXT gt to continue 81 Chapter 4 Importers 82 Import using a Telid Connection Select the translator and target model for the import Press the Next gt button to continue Server mm Localhost 98999 DataSource MySGLAccts Driver mysql connector java 5 1 5 Translator mysql Target Model Definition Location IMyPraj Model Status Model is selected MyModel Figure 4 11 Translator and Model Definition e Step 5 When you move to next page of the wizard a temporary dynamic vdb is actually deployed to you rserver and the schema your data source is retrieved in DDL form This DDL is displayed and can also be exported if desired Click NEXT to continue Ila Import From Teiid Data Source Connection lm port using Teld Connection Get DDL for the Import Press the Next gt butto
283. tput View xmi created New Model PartsVirtual WS xmi created Figure 6 23 Generation Completed Dialog 6 4 2 Web Services War Generation 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 6 4 2 1 Generating a JBossWS CXF War 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 To generate a new JBossWS CXF war using the VDB e Step 1 Right click on the VDB containing your web service model s and select the Modeling Generate JBossWS CXF War action gt MarketDataView Ws New n T mic d Po 1 gt fj MarketDataView x Portfoli PortfolioVDB vdb Deploy Execute VDB t RestfulBooks Open Generate JBossW5 CXF War ZElindateahleRankewe Open With e Step 2 Fill in missing properties in Web Service War Generation Wizard shown below 228 Web Services War Generation Create Web service WAR f Create a WAR file to deploy as a Web Service Enter the required information then click OK to
284. u wish to extend Relational Web Service XML Document and Function and a description Note that version number is available but is not currently being used After entering the basic MED info you can now switch to the Properties tab and begin creating your extended property definitions for specific model objects supported by selected model class 197 Chapter 5 Creating and Editi i5 mymxd mxd Z3 b Pro perties 7 Extended Model Objects Manage the extended model objects that have extension properties BaseTable zi Extension Properties 4 B Manage extension properties for each extended model object ID Runtime Type Required Modifiable Advanced Masked Indexed Default Value Fi copyable boolean Overview Properties Source Figure 5 43 MED Editor Properties Tab Start by selecting the Add Extended Model Object toolbar button to display the Model Object Name selection dialog Select an object and press OK 198 Edit MED Add Model Object Name Model Object Name Choose the Model object that will have extension properties BaseTable Column PrimaryKey Foreignkey AccessPattern UniqueConstraint View Column AccessPattern Procedure 7 ProcedureResult Column ProcedureParameter Index ad ncel Figure 5 44 Select Model Object Name Dialog Next select the model object in the Extended Model Objects secti
285. ueKeyName PK ProductiD uniqueKeyTableN lt column name ProductiD gt lt column gt lt foreign key gt lt table gt procedure name getProductinfo namelnSource dbo products getProductinfo funct lt parameter name ID direction IN datatype numeric length L0 radix 10 p lt parameter gt parameter name productinfo direction RETURN datatype varchar length 98 lt parameter gt lt resultset name InfoResult calumn name ProductiD datatype varchar length 10 nullable NO NULLS column name ProductName datatype varchar length 60 nullable NULLAE column name PraductType datatype varchar length 15 nullable NULLABL lt resultset gt lt procedure gt index name ProductiDIndex autoupdate false nullable false unique true gt column name ProductiD tableName ProductData gt Cancel Figure 4 34 Select Import Type Relational Model XML Format Perform Steps 4 On the next page select the XML file on your local file system via the Browse button Select a target model to which the imported relational objects will be added 112 Import Relational Model XML Format via the second Browse button The dialog allows selecting an existing relational model or creating a new model Click Finish to create your new model 113 Chapter 4 Importers Import Metadata From Text File Select Source Text File and Target Relational Model Select Met
286. urceA BOOK AUTHORS AUTHOR ID Cancel Figure 5 31 New Blank Function Created You need to set an attribute or constant value for each sub node in the tree to specify the arguments this function needs You can also nest another function in the tree using the Function editor 184 Input Set Editor XML Expression Builder Initial Expression X undefined SOL View CONCAT Books SourceB AUTHORS FIRSTMAME Books SeurceB AUTHORS LASTMAME Books SourceB AUTHORS FIRSTNAME Books SourceB AUTHORS LASTNAME 5 Attribute Constant Function Value is unchanged Category STRING Function Argument Value STRINGI Books SourceB AUTHORS FIRSTNAME STRING2 Books SourceB AUTHORS LASTNAME Cancel Figure 5 32 Nested Function Example Step 8 Click each sub node in the tree and use the editors at the bottom of the dialog box to apply an attribute constant or function value to it Step 9 When you have added values to all nodes as shown below click OK to add this expression to your query or Cancel to close the dialog box without inserting the expression If the OK button does not enable you have not added a value to all nodes in the tree You can also nest functions within your expressions by selecting an argument and selecting a function for that argument The nested function displays in the tree beneath your root function and its arguments display as well Using the Expression Bui
287. ure 4 40 Select Source Text File and Target Virtual Relational Model Step 5 Select an existing relational virtual model as the target location for your new model components using the Browse button to open the Virtual Model Selector Dialog Select a virtual relational model from your workspace or specify a unique name to create a new model e Step 6 Select Finish 4 8 Import WSDL into Relational Models 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 To create relational models from WSDL use the steps below 119 Chapter 4 Importers e Step 1 In Model Explorer choose the File gt Import action in the toolbar or select a project folder or model in the tree and choose Import Step2 Select the import option Teiid Designer WSDL File or URL Source and View Model SOAP and
288. usiness 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 1 2 3 Design Time and Runtime Metadata Teiid Designer software distinguishes between design time metadata and run time metadata This distinction becomes important if you use 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 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 Teiid Designer Server you take a snapshot of a metadata model for Teiid Designer Server to use when resolving queries from your client applications This run time metadata represents a static version of design
289. using VDBs Import Database via JDBC Select Database Objects e Can only select a single schema for VDB source model import gt AllParts mw PartsSupplierA e Step 5 The final page shows the name of the resulting VDB source model and the name is NOT editable All other options are disabled The target Into Folder must NOT contain a model with the same name or the Finish button will be disabled 255 Chapter 8 Managing VDBs Press the Next gt button to continue or the Finish button to finish Relational Model Definition Model Name AllParts Into Folder TeiidProject A Make target a view model Update if existing model selected Include Catalog For Fully Qualified Names Model Object Mames Tables Procedures Columns etc Use Fully Qualified Names Example partssupplier dbo PARTS Change Case Far All Characters Case Options Make All Upper Case Example Suppliers gt SUPPLIERS Make All Lower Case Example SUPPLIERS gt suppliers You can use your VDB source model like any other source model in your project VDB source model tables can be used in your transformation queries and the view models will contain model imports to your VDB source models However when your view model is added to a VDB any referenced VDB source models do NOT get added to your VDB Instead an lt import vdb gt element described above reference is added in it s place If VDB import
290. utton to continue or the Anish button to finish Select Target Relational Model Model Name BillingSourceModel Browse Location SalesforcelmportTest Browse Select Import Options Model audit fields Selecting this option will cause the importer to model the Audit Relds for each SalesForce object Created By Created Date Last Modified By Last Modified Date System Modification Timestamp Da not gather Cardinalities Selecting this option will stop the importer from calculating and setting the cardinalities metadata For large salesforce applications this can become a long running operation Gather Column Distinct Value Count Selecting this option will cause the importer to calculate and set the distinct value count metadate This will scan the data in each field individually and can be a very long running operation Set name to SalesForce label Selecting this option will cause the importer to set the name metadata in the generated model 1 By default the importer uses the internal data name because SalesForce labels are often invalid f Create a procedure for the GetUpdated operation Selecting this option will cause the importer to create a procedure for the getUpdated operation ir Create a procedure for the GetDeleted operation Selecting this option will cause the importer to create a procedure for the getDeleted operation in lt o gt lt Back Next gt Cancel Anish
291. ve Diagram as Image Save the diagram image to file in JPG or BMP format Modify Diagram Printing Preferences Modify page layout information for printing diagrams Includes margins orientation etc Show Hide Page Grid Show current page boundaries as grid in diagram Context menus for the D 3 1 1 4 Mapping Diagram The Mapping Diagram represents a view of the mapping between virtual mapping class columns and XML document elements This mapping defines how source data is transformed from row based results into XML formatted text 354 Model Editor co 7 je bookCollection 7 Li sequence MC book wv je book i isbn 11 sequence ier title ie subtitle fe edition de authors sequence MC author author wv je publishinglnformation E EE E JE w A sequence ie publisher ie publishDate Mapping Diagram 7 Table Editor Figure D 46 Mapping Diagram Example Mapping Diagram toolbar actions include e Refresh Diagram Re draws diagram Show Parent Diagram Navigates to diagram for parent object if available Show Mapping Transformation Diagram Show detailed mapping transformation diagram for selected mapping class Preview Data Executes a simple preview query SELECT FROM Generate Mapping Classes Generate mapping classes for the selected XML document root element 355 Appendix D Teiid Designer Ui El New Mapping Cla
292. view table or procedure e A transformation node in a transformation diagram or mapping transformation diagram e A mapping class in a mapping diagram or mapping transformation diagram 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 Transformation Editor Srl Ez Cursor at 1 1 Supports Update Ha F SELECT PartsSourceA SUPPLIER PARTS SUPPLIER ID PART ID QUANTITY SHIPPER ID SUPPLIER_NAME SU SUPPLIER CITY SUPPLIER STATE FROM PartsSourceA SUPPLIER PARTS PartsSourceB SUPPLIER WHERE PartsSourceA SUPPLIER PARTS SUPPLIER ID PartsSourceB SUPPLIER SUPPLIER ID Figure 5 21 Editing String Property 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 e 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 transfo
293. will be generated for you which will validate that the username password values you specified in the war generator dialog are passed in This is meant to be a testing mechanism for your WS Security enabled web service and your own security mechanism should be implemented in this class All source code is included in the generated war along with the compiled class files Target namespace This is the target namespace that will be used in the generated WSDL and subsequent generated web service classes MTOM Message Transmission If selected MTOM will be enabled for the Optimization Mechanism web service endpoint s You will also need to update your output schema accordingly 230 Web Services War Generation Field Name Description by adding the xmins xmime http www w3 org 2005 05 xmimim schema and adding type xs base64Binary xmime expectedContentT ypes application octet stream to the output element you wish to optimize War File Save Location The folder where the generated WAR file should be saved Step 3 Click OK to generate the web service war When war generation is complete a confirmation dialog should appear Click OK Web Service WAR File Created Figure 6 25 Generation Completed Dialog 6 4 2 2 Generating a RESTEasy War In Teiid Designer itis also possible to expose your VDBs over REST using a generated RESTEasy war Also if your target virtual model has update i
294. will open the appropriate editor and select the affected referenced object e Show In Navigator Opens the Basic gt Navigator view if not open and expands file system tree and reveals applicable resource Copy Copies the problem information to the system clipboard Paste Pastes the problem information located in the system clipboard if applicable into the curor location for a specified text editor Delete Deletes the selected problem rows if applicable Select All selects all problems in the table Quick Fix Not yet implemented in Teiid Designer Properties displays a dialog containing additional information D 2 7 Search Results View Below is an example set of search results The view contains rows representing matches for your search parameters You can double click a entry and the object will be opened and selected in an editor and or the Model Explorer if applicable 333 Appendix D Teiid Designer Ui LH Problems Ay Search x N TL an oe IE E EY v r4 e feature Name pattern Part 26 matches gt Found 1 match PartsProject SampleViews xmi Found 7 matches PartsProject PartsVirtual xmi El PART PART NAME http www metamatrix com metamodels Relational Column FY PART COLOR PART COLOR http www metamatrix com metamodels Relational Column FA PART ID PART ID http www metamatrix com metamodels Relational Column
295. zard shown below allows creating a complete relational Procedure including columns unique keys foreign keys definition and indexes The relational procedure object can represent different types of procedures including a standard procedure a source function or a user defined function When the New Child Procedure action is launched the first dialog gives you the option of selecting the procedure type 159 Chapter 5 Creating and Editi oO Select Procedure Type Select desired relational procedure type and click OK amp Procedure Simple relational procedure containing parameters and an optional result set Source Function Represents a source Function assumed to be present on a database This Function can be used in your virtual queries and the Function call will be pushed down to your source in the Final query execution plan User Defined Function You represent custom or user defined Function and use it in a similar was as source Functions This Function must be backed by java code and is defined by specifying java class and method properties oe Figure 5 13 Select Procedure Type Dialog The second dialog customizes the Create Relational Procedure dialog based on your selected type The following dialog provides standard procedure data input 160 Create Relational Procedure Wizard Create Relational Procedure Provide unique name properties and desired parameters and result set
296. zards Transforming or copying from one model into another see Chapter 3 New Model Wizards options Various custom actions 1 3 3 Guiding through the process To make the process of using Teiid Designer to build models more as easy as posssible a guides view Section D 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 e 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 1 3 4 Targeting the Right Teiid Server Like Teiid Designer the Teiid server is under continuous development and as such multiple versions have been and are being released Due to changes in both its code and the underlying JBoss server the versions are not always backward compatible To mitigate this situation Teiid Designer now provides multiple implementations of Teiid s runtime client allowing connection to different versions of the server New models must be compatible with their targeted server version hence the correct server version must be selected prior to creating them To aid with selection of the correct server version two changes have been made to Tei

Teiid Designer User Guide 1

Contents

Download Pdf Manuals

Related Search

Related Contents