Transformation Graph Video Tutorial

This tutorial use a simple example of adding customer personal data as their names into a flat file containing commissions paid by external partner to show a basic functionality and philosophy the clover.GUI tool. 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 clover.GUI 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 the clover.GUI. play the video

Source Components

	Database connections clover.ETL enging 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. play the video
	Database reader setup We use the database connection that was previously defined and create an SQL statement that loads needed data from the database. Then metadata is assigned to the component's output port. 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. play the video
	Flat file reader setup A path to the source file has to be defined as well as metadata has to be assigned to the component's output port. 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 have to define metadata for the component's output port before parameterizing the component itself. The metadata will consist of fields: customer ID, last name, first name, commission and contract number. play the video

Writer Setup

	Writers store transformed data into files or databases. We use Delimited data writer 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 clover.ETL engine outputs information about running components and results. play the video