Transformation Graph Video Tutorial

This tutorial uses a simple example of adding both customer personal data and their names into a flat file containing commissions paid by external partner to show a basic functionality and philosophy of CloverETL Designer. Obviously, transformations would be more complex and elaborate in real situations. However, the example is sufficient for the purpose of this introductory video tutorial.

Project Definition

	Project is represented by a folder where all files related to the transformation are placed - files with source data, metadata files, database connections, graphs or files with transformed data. play the video

Graph Definition

	Transformation graph is a key part of the CloverETL Designer tool which makes data transformation almost “visible”. Graph contains single components (nodes) connected by “data pipelines” (edges). Each component does its own transformation on the data and then sends it to a following component. play the video

Components of Graph

Before designing a transformation graph, it is necessary to think about which transformation steps will be needed to complete a global transformation process. Then the designing process itself should be very simple. We will build the graph using a palette of components and interconnect these components by edges. In order to achieve a nice look we can use auto-layout functionality of CloverETL Designer. play the video

Source Components

	Database connections CloverETL Engine can process any structured data stored in files or tables of relational databases. Before defining source components – readers, a couple of other steps has to be accomplished. Our first source is database, so we have to define database connections at first – in this case as an external definition. Such connection can be reused in other graphs of the same project. play the video
	Metadata definition using source database Metadata describes structure of data record floating between nodes through edges. Metadata definition is created on a base of database data structure in this example. The definition is stored as an external definition in order to share among other graphs in the same project. The created metadata are assigned to the output edge. play the video
	Database reader setup We have selected DBInputTable as a database reader. We use the database connection that was previously defined and create an SQL statement that loads needed data from the database. play the video
	Metadata definition using source file Another way of defining metadata is based on a structure of records in the flat file through a special wizard designed for this purpose. The metadata definition is saved as an internal graph element in opposition to preceding cases – therefore its usage will be exclusively limited for this graph. Metadata must be assigned to the output edge. play the video
	Flat file reader setup We use UniversalDataReader to read the flat file. A path to the source file must be defined. play the video

Transformation Components Setup

	Sort component Transformation components provide basic transformations. The first transformation step in the example is sorting the data when it leaves the Reader components. A customer ID number is used as a sort key. play the video
	Merge join component Merge join component joins together all sorted records from both sources. Join is accomplished by a defined key – customer ID. We can define metadata for the component's output port during parameterizing the component itself. The metadata will consist of fields: customer ID, first name, last name, contract number and commission. play the video

Writer Setup

	Writers store transformed data into files or databases. We use UniversalDataWriter component for writing to a text file. play the video

Transformation Run

	Once a transformation graph is designed and configured, we can proceed to running it. During the transformation run-time the CloverETL Engine outputs information about running components and results. play the video