JasperSoft ETL

Lets First discuss about what is ETL.ETL stands for Extract Transform and Load.These terms refers to processes in Database and Data Warehousing that are combined into one tool to pull data from one database transform it and load it to another database .

• Extract: Extracts data from homogeneous or heterogeneous data sources or databases

• Transform:It is the process of converting extracted into desired form.Transformation occurs by using rules or look-up tables or by combining the data with other data.

• Load :It means loading data into target database.

 

We need to install JasperSoft Etl (One of the tool to design jobs and run ETL).You could download  JasperSoft ETL from  the JasperSoft Community site http://community.jaspersoft.com/project/jaspersoft-etl/releases.

After you open JasperSoft ETL and create a project name , Here I have chosen project name as TestETLProcess .Window that would show-up is

Below are the steps to create ETL Process:

1.) First we need to create a job under Job Designs 

      a) Right click on Job Designs 

      b) Select Create Job

      c) Write Job Name and click Finish .

2.) Create DB connection of source db from where tables needs to be processed.

  

   a) Expand MetaData

   b) Then Click on DbConnection 

   c) Create the name of Db Connection and click on next.

3.) Select DB type For e.g I have selected mysql and fill rest of the requirements.

4.) Click on check connection to check if connection is successful or not . If the connection is successful then click finish.

5.) Make another Db connection  of the target database where we want to transfer database.

6.) Now we would retrieve schema of Source Db .
 
      a) Right click on source db (In my Case it is CouponDb ) select retrieve schema.
      b) After you click on retrieve schema the following window would pop-up.

    c) Click on Next.
    d) After you click on Next following window would popup in which click on Select All .
    

 

    e) Click on Next and then Click Finish.

7.)  Under CouponDb click on Table schemas and drag the tables which you want to transform and load on Target Database.

8.) I have selected Coupon and CouponCode tables and dragged them to Job window and selected tMySqlInput option.

    

  9.) In Palette on Right under Processing select tMap and drag it in job window.

 10.) Right Click on Coupon and CouponCode and select Row->Main and drag the line to tMap_1.
    

 11.) From Palette on right click on Databases->Mysql select tMysqlOutput .

 12.) Right click on tMap select Row->Main drag it to tMySqlOutput_1 .Select the name of row which you want (I have selected transform) .

  

13.) Double click tMap component.Drag coupon_code_id from coupon table to id column in couponcode table ,to make sure that it is a unique match.

14.) Now we will select columns we want to get and drag it to the right at transform table.

15.) Click Ok and close the component.

16.) Double click tMySqlOutput and fill the credentials of database and table name you want to set .

        In the action select Create Table if not exists and in Action on Data select Insert or Update.

    17.)Double click on Edit Schema to check the schema .

   18.) Click Ok and close the window.Now we will run the ETL and check Dataflow .

   19.) Press F6 to run ETL process and check dataflow .

                

Hope the article would be helpful .I would be looking forward to your comments suggestion and feedback.  ......Thanks :)