List of Figures

1.1. Family of CloverETL Products

2.1. CloverETL Server Project Displayed after Opening CloverETL Designer

2.2. Prompt to Open CloverETL Server Project

2.3. Opening CloverETL Server Project

2.4. Network connections window

5.1. CloverETL Designer Splash Screen

5.2. Workspace Selection Dialog

5.3. CloverETL Designer Introductory Screen

6.1. Available Software

7.1. Giving a Name to a CloverETL Project

7.2. CloverETL Server Project Wizard

7.3. Giving a Name to the New CloverETL Server Project

7.4. CloverETL Examples Project Wizard

7.5. Renaming CloverETL Examples Projects

8.1. CloverETL Perspective with Highlighted Navigator Pane and the Project Folder Structure

8.2. Opening the Workspace.prm File

8.3. Workspace.prm File

8.4. Basic Eclipse Perspective

8.5. Selecting CloverETL Perspective

8.6. CloverETL Perspective

9.1. CloverETL Perspective

9.2. Graph Editor with an Opened Palette of Components

9.3. Closing the Graphs

9.4. Rulers in the Graph Editor

9.5. Grid in the Graph Editor

9.6. A Graph before Selecting Auto-Layout.

9.7. A Graph after Selecting Auto-Layout.

9.8. Six New Buttons in the Tool Bar Appear Highlighted (Align Middle is shown)

9.9. Alignments from the Context Menu

9.10. Navigator Pane

9.11. Outline Pane

9.12. Another Representation of the Outline Pane

9.13. Locking a graph element - you can add any text you like to describe the lock.

9.14. Properties Tab

9.15. Console Tab

9.16. Problems Tab

9.17. Clover - Regex Tester Tab

9.18. Clover - Graph Tracking Tab

9.19. Clover - Log Tab

10.1. Creating a New Graph

10.2. Giving a Name to a New CloverETL Graph

10.3. Selecting the Parent Folder for the Graph

10.4. CloverETL Perspective with Highlighted Graph Editor

10.5. Graph Editor with a New Graph and the Palette of Components

10.6. Components Selected from the Palette

10.7. Components are Connected by Edges

10.8. Creating an Input File

10.9. Creating the Contents of the Input File

10.10. Metadata Editor with Default Names of the Fields

10.11. Metadata Editor with New Names of the Fields

10.12. Edge Has Been Assigned Metadata

10.13. Metadata Have Been Propagated through the Component

10.14. Opening the Attribute Row

10.15. Selecting the Input File

10.16. Input File URL Attribute Has Been Set

10.17. Output File URL without a File

10.18. Output File URL with a File

10.19. Defining a Sort Key

10.20. Sort Key Has Been Defined

10.21. Running the Graph

10.22. Result of Successful Run of the Graph

10.23. Contents of the Output File

11.1. Running a Graph from the Main Menu

11.2. Running a Graph from the Context Menu

11.3. Running a Graph from the Upper Tool Bar

11.4. Successful Graph Execution

11.5. Console Tab with an Overview of the Graph Execution

11.6. Counting Parsed Data

11.7. Run Configurations Dialog

12.1. Selecting Cheat Sheets

12.2. The Cheat Sheet Selection Wizard

12.3. CloverETL and Standard Eclipse Commands (Collapsed)

12.4. CloverETL and Standard Eclipse Commands (Expanded)

12.5. CloverETL Designer Reference Cheat Sheet

12.6. Locating a Custom Cheat Sheet

13.1. URL File Dialog

13.2. Edit Value Dialog

13.3. Find Wizard

13.4. Go to Line Wizard

13.5. Open Type Dialog

14.1. Import (Main Menu)

14.2. Import (Context Menu)

14.3. Import Options

14.4. Import Projects

14.5. Import from CloverETL Server Sandbox Wizard (Connect to CloverETL Server)

14.6. Import from CloverETL Server Sandbox Wizard (List of Files)

14.7. Import Graphs

14.8. Import Metadata from XSD

14.9. Import Metadata from DDL

15.1. Export Options

15.2. Export Graphs

15.3. Export Graphs to HTML

15.4. Export metadata to XSD

15.5. Export to CloverETL Server Sandbox

15.6. Export Image

16.1. Setting Up Memory Size

16.2. Custom Clover Settings

16.3. Enlarging the Font of Numbers

16.4. Setting the Font Size

16.5. Setting The Java Runtime Environment

16.6. Preferences Wizard

16.7. Installed JREs Wizard

16.8. Adding a Java Development Kit

16.9. Searching for JDK Jars

16.10. Adding JDK Jars

18.1. Selecting the Edge Type

18.2. Creating Metadata on an empty Edge

18.3. Assigning Metadata to an Edge

18.4. Metadata in the Tooltip

18.5. Properties of an Edge

18.6. Filter Editor Wizard

18.7. Debug Properties Wizard

18.8. View Data Dialog

18.9. Viewing Data

18.10. Hide/Show Columns when Viewing Data

18.11. View Record Dialog

18.12. Find Dialog

18.13. Copy Dialog

19.1. Creating Internal Metadata in the Outline Pane

19.2. Creating Internal Metadata in the Graph Editor

19.3. Externalizing and/or Exporting Internal Metadata

19.4. Selecting a Location for a New Externalized and/or Exported Internal Metadata

19.5. Creating External (Shared) Metadata in the Main Menu and/or in the Navigator Pane

19.6. Internalizing External (Shared) Metadata

19.7. Extracting Metadata from Delimited Flat File

19.8. Extracting Metadata from Fixed Length Flat File

19.9. Setting Up Delimited Metadata

19.10. Setting Up Fixed Length Metadata

19.11. Extract Metadata from Excel Spreadsheet Wizard

19.12. Format Extracted from Spreadsheet Cell

19.13. Extracting Internal Metadata from a Database

19.14. Database Connection Wizard

19.15. Selecting Columns for Metadata

19.16. Generating a Query

19.17. DBF Metadata Editor

19.18. Specifying Lotus Notes connection for metadata extraction

19.19. Lotus Notes metadata extraction wizard, page 2

19.20. Merging two metadata - conflicts can be resolved in one of the three ways (notice radio buttons at the bottom).

19.21. Creating Database Table from Metadata and Database Connection

19.22. Metadata Editor for a Delimited File

19.23. Metadata Editor for a Fixed Length File

20.1. Creating Internal Database Connection

20.2. Externalizing Internal Database Connection

20.3. Internalizing External (Shared) Database Connection

20.4. Database Connection Wizard

20.5. Adding a New JDBC Driver into the List of Available Drivers

20.6. Running a Graph with the Password Encrypted

20.7. Connecting to MS SQL with Windows authentication. Setting-up a database connection like this is not sufficient. Additional steps explained below this figure need to be performed.

20.8. Adding path to the native dll to VM arguments.

21.1. Edit JMS Connection Wizard

22.1. QuickBase Connection Dialog

23.1. Lotus Notes Connection Dialog

24.1. Creating Internal Lookup Table

24.2. Externalizing Wizard

24.3. Selecting Lookup Table Item

24.4. Lookup Table Internalization Wizard

24.5. Lookup Table Wizard

24.6. Simple Lookup Table Wizard

24.7. Edit Key Wizard

24.8. Simple Lookup Table Wizard with File URL

24.9. Simple Lookup Table Wizard with Data

24.10. Changing Data

24.11. Database Lookup Table Wizard

24.12. Appropriate Data for Range Lookup Table

24.13. Range Lookup Table Wizard

24.14. Persistent Lookup Table Wizard

24.15. Aspell Lookup Table Wizard

25.1. Creating a Sequence

25.2. Editing a Sequence

25.3. A New Run of the Graph with the Previous Start Value of the Sequence

26.1. Creating Internal Parameters

26.2. Externalizing Internal Parameters

26.3. Internalizing External (Shared) Parameter

26.4. Example of a Parameter-Value Pairs

28.1. Dictionary Dialog with Defined Entries

29.1. Pasting a Note to the Graph Editor Pane

29.2. Enlarging the Note

29.3. Highlighted Margins of the Note Have Disappeared

29.4. Changing the Note Label

29.5. Writing a New Description in the Note

29.6. A New Note with a New Description

29.7. Folding the Note

29.8. Properties of a Note

30.1. CloverETL Search Tab

30.2. Search Results

32.1. Valid selections

32.2. How to select the FTL wizard

32.3. Select a wizard (new wizard selection window)

32.4. Deselection

32.5. New Graph Name Page

32.6. Output Page

32.7. File Selection Page

32.8. URL Dialog

32.9. Database Connection Page

32.10. Fact Table Selection Page

32.11. Dimension Table Selection Page

32.12. Order Table Page

32.13. Mapping Page

32.14. Fact Mapping Page

32.15. Summary Page

32.16. Created Graph

32.17. Graph Parameters

34.1. Selecting Components

34.2. Components in Palette

34.3. Removing Components from the Palette

35.1. Find Components dialog - the searched text is higlighted both in component names and description.

35.2. Add Components dialog - finding a sorter.

36.1. Edit Component Dialog (Properties Tab)

36.2. Edit Component Dialog (Ports Tab)

36.3. Simple Renaming Components

36.4. Running a Graph with Various Phases

36.5. Setting the Phases for More Components

36.6. Running a Graph with Disabled Component

36.7. Running a Graph with Component in PassThrough Mode

37.1. Defining Group Key

37.2. Defining Sort Key and Sort Order

37.3. Define Error Actions Dialog

37.4. Transformations Tab of the Transform Editor

37.5. Copying the Input Field to the Output

37.6. Transformation Definition in CTL (Transformations Tab)

37.7. Mapping of Inputs to Outputs (Connecting Lines)

37.8. Editor with Fields and Functions

37.9. Input Record Mapped to Output Record Using Wildcards

37.10. Transformation Definition in CTL (Source Tab)

37.11. Java Transform Wizard Dialog

37.12. Confirmation Message

37.13. Transformation Definition in CTL (Transform Tab of the Graph Editor)

37.14. Outline Pane Displaying Variables and Functions

37.15. Content Assist (Record and Field Names)

37.16. Content Assist (List of CTL Functions)

37.17. Error in Transformation

37.18. Converting Transformation to Java

37.19. Transformation Definition in Java

38.1. Viewing Data in Components

38.2. Viewing Data as Plain Text

38.3. Viewing Data as Grid

38.4. Plain Text Data Viewing

38.5. Grid Data Viewing

38.6. XML Features Dialog

39.1. Viewing Data on Components

39.2. Viewing Data as Plain Text

39.3. Viewing Data as Grid

39.4. Plain Text Data Viewing

39.5. Grid Data Viewing

41.1. Source Tab of the Transform Editor in Joiners

45.1. Configuring prefix selector in ComplexDataReader. Rules are defined in the Selector properties pane. Notice the two extra attributes for regular expressions.

45.2. Sequences Dialog

45.3. A Sequence Assigned

45.4. Edit Key Dialog

45.5. Source Tab of the Transform Editor in DataGenerator

45.6. Generated Query with Question Marks

45.7. Generated Query with Output Fields

45.8. Mapping to Clover fields in EmailReader

45.9. Example mapping of nested arrays - the result.

45.10. SpreadsheetDataReader Mapping Editor

45.11. Basic Mapping – notice leading cells and dashed borders marking the area data will be taken from

45.12. The difference between global data offsets set to 1 (default) and 3. In the right hand figure, reading would start at row 4 (ignoring data in rows 2 and 3).

45.13. Global data offset is set to 1 to all columns. In the third column, it is locally changed to 3.

45.14. Rows per record is set to 4. This makes SpredsheetDataReader take 4 Excel rows and create one record out of their cells. Cells actually becoming fields of a record are marked by a dashed border, therefore the record is not populated by all data. Which cells populate a record is also determined by the data offsets setting, see the following bullet point.

45.15. Rows per record is set to 3. The first and third columns 'contribute' to the record by their first row (because of the global data offset being 1). The second and fourth columns have (local) data offsets 2 and 4, respectively. The first record will, thus, be formed by 'zig-zagged' cells (the yellow ones – follow them to make sure you understand this concept clearly).

45.16. Retreiving format from a date field. Format Field was set to the "Special" field as target.

45.17. Reading mixed data using two leading cells per column. Rows per record is 2, Data offset needed to be raised to 2 – looking at the first leading cell which has to start reading on the third row.

45.18. XLS Mapping Dialog

45.19. XLS Fields Mapped to Clover Fields

45.20. The Mapping Dialog for XMLExtract

45.21. Parent Elements Visible in XML Fields

45.22. Editing Namespace Bindings in XMLExtract

46.1. Generated Query with Question Marks

46.2. Generated Query with Input Fields

46.3. Generated Query with Returned Fields

46.4. EmailSender Message Wizard

46.5. Edit Attachments Wizard

46.6. Attachment Wizard

46.7. Defining bean structure - click the Select combo box to start.

46.8. Mapping editor in JavaBeanWriter after first open. Metadata on the input edge(s) are displayed on the left hand side. The right hand pane is where you design the desired output tree - it is pre-defined by your bean's structure (note: in the example, the bean contains employees and projects they are working on). Mapping is then performed by dragging metadata from left to right (and performing additional tasks described below).

46.9. Example mapping in JavaBeanWriter - employees are joined with projects they work on. Fields in bold (their content) will be printed to the output dictionary, i.e. they are used in the mapping.

46.10. Mapping editor in JavaMapWriter after first open. Metadata on the input edge(s) are displayed on the left hand side. The right hand pane is where you design the desired output tree. Mapping is then performed by dragging metadata from left to right (and performing additional tasks described below).

46.11. Example mapping in JavaMapWriter - employees are joined with projects they work on. Fields in bold (their content) will be printed to the output dictionary.

46.12. Mapping arrays in JavaMapWriter - notice the array contains a dummy element 'State' which you bind the input field to.

46.13. Mapping editor in JSONWriter after first open. Metadata on the input edge(s) are displayed on the left hand side. The right hand pane is where you design the desired JSON tree. Mapping is then performed by dragging metadata from left to right (and performing additional tasks described below).

46.14. Example mapping in JSONWriter - employees are joined with projects they work on. Fields in bold (their content) will be printed to the output file - see below.

46.15. Mapping arrays in JSONWriter - notice the array contains a dummy element 'State' which you bind the input field to.

46.16. Spreadsheet Mapping Editor

46.17. Explicit mapping of the whole record

46.18. The difference between global data offsets set to 1 (default) and 3. In the right hand figure, writing would start at row 4 with no data written to rows 2 and 3.

46.19. Global data offsets is set to 1. In the last column, it is locally changed to 4. In the output file, the initial rows of this column would be blank, data would start at D5.

46.20. With Rows per record set to 2 in leading cells Name and Adress, the component always writes one data row, skips one and then writes again. This way various data does not get mixed (overwritten by the other one). For a successful output, make sure Data offsets is set to 2.

46.21. Rows per record is set to 3. Data in the first and third column will start in their first row (because of their data offsets being 1). The second and fourth columns have data offsets 2 and 4, respectively. The output will, thus, be formed by 'zig-zagged' cells (the dashed ones - follow them to make sure you understand this concept clearly).

46.22. Writing into a template. Its original content will not be affected, your data will be written into Name, Surname and Age fields.

46.23. Partitioning by one data field

46.24. Mapping summary

46.25. Create Mask Dialog

46.26. Mapping Editor

46.27. Adding Child to Root Element.

46.28. Wildcard attribute and its properties.

46.29. Attribute and its properties.

46.30. Element and its properties.

46.31. Mapping editor toolbar.

46.32. Binding of Port and Element.

46.33. Generating XML from XSD root element.

46.34. Source tab in Mapping editor.

46.35. Content Assist inside element.

46.36. Content Assist for ports and fields.

47.1. DataBase Configuration

47.2. Input mapping

47.3. Output mapping

47.4. Source Tab of the Transform Editor in the Denormalizer Component (I)

47.5. Source Tab of the Transform Editor in the Denormalizer Component (II)

47.6. Example MetaPivot Input

47.7. Example MetaPivot Output

47.8. Source Tab of the Transform Editor in the Normalizer Component (I)

47.9. Source Tab of the Transform Editor in the Normalizer Component (II)

47.10. Source Tab of the Transform Editor in the Partitioning Component

47.11. Source Tab of the Transform Editor in the Rollup Component (I)

47.12. Source Tab of the Transform Editor in the Rollup Component (II)

47.13. Source Tab of the Transform Editor in the Rollup Component (III)

47.14. XSLT Mapping

47.15. An Example of Mapping

48.1. Matching Key Wizard (Master Key Tab)

48.2. Matching Key Wizard (Slave Key Tab)

48.3. Join Key Wizard (Master Key Tab)

48.4. Join Key Wizard (Slave Key Tab)

48.5. An Example of the Join Key Attribute in ApproximativeJoin Component

48.6. An Example of the Join Key Attribute in ExtHashJoin Component

48.7. Hash Join Key Wizard

48.8. An Example of the Join Key Attribute in ExtMergeJoin Component

48.9. Join Key Wizard (Master Key Tab)

48.10. Join Key Wizard (Slave Key Tab)

48.11. Edit Key Wizard

48.12. An Example of the Join Key Attribute in the RelationalJoin Component

48.13. Join Key Wizard (Master Key Tab)

48.14. Join Key Wizard (Slave Key Tab)

50.1. Foreign Key Definition Wizard (Foreign Key Tab)

50.2. Foreign Key Definition Wizard (Primary Key Tab)

50.3. Foreign Key Definition Wizard (Foreign and Primary Keys Assigned)