Duration

3 days

OVERVIEW

ETL tools are used to Extract data from homogeneous or heterogeneous data sources, Transform data for storing it in proper format or structure for querying and analysis purpose and Load data into the final target (database, more specifically, operational data store, data mart, or data warehouse). Usually in ETL tools, all the three phases execute in parallel since the data extraction takes time, so while the data is being pulled another transformation process executes, processing the already received data and prepares the data for loading and as soon as there is some data ready to be loaded into the target, the data loading kicks off without waiting for the completion of the previous phases.

There are a few popular open source ETL tools in market such as Talend, Scriptella, KETL, Pentaho and etc. Talend provides multiple solutions for data integration, both open source and commercial editions. Talend offers an Eclipse-based interface, drag-and-drop design flow, and broad connectivity with more than 400 pre-configured application connectors to bridge between databases, mainframes, file systems, web services, packaged enterprise applications, data warehouses, Software-as-a-Service, Cloud-based applications, and more. Scriptella is an open source ETL and script execution tool written in Java. Its primary focus is simplicity. You don't have to study yet another complex XML-based language - use SQL to perform required transformations. Scriptella is licensed under the Apache License. KETL is a premier, open source ETL tool. The data integration platform is built with portable, java-based architecture and open, XML-based configuration and job language. KETL features successfully compete with major commercial products available today. Pentaho Data Integration is an interpreter of procedures written in XML format. It also provides a JavaScript engine as well as a Java engine to fine tune the data manipulation process.

At the end of this course, participants will be able to deploy and automate process to extract data from various sources, transform them so that the data can be queried, and finally load them into a warehouse. Participants would be able to apply these concepts in their day to day projects, where there might be a need to move data across systems and store it in a friendly query-able format for reporting and analysis

OBJECTIVES

Understand the key terms in Data Quality
Understand how to install and create Data profiling and cleansing jobs
Understand how to automate key phases of Data Quality Management framework
Understand the basics of Pentaho Data Integration and Data cleaner tool
Create ETL jobs that connect to almost any data source
Build standalone jobs that run on a schedule or based on an event
Filter, Modify, Unite data
Make jobs more user-friendly for non-technical users
Understand how to Integrate Data cleaner tool with PDI
Several hands on sessions to understand how to implement data quality jobs, data profiling jobs and how to schedule these jobs

PREREQUISITES

There is no pre-requisite to this course. We go through each step in detail.

TARGET AUDIENCE

Anyone who is interested in Data Integration ant ETL
Everyone who wants to make their job easier

Introduction

What is Data Integration
What is ETL
Data Acquisition
Data Transformation
ETL explained
ETL vs ELT
ETL Phases
Introduction to Talend
Overview of Talend
Talend fundamentals
Architecture of Talend
Installation
Talend UI
Repository Tab
Context and Metadata
Data Mapping
Mapping Data
Data Generation
Filtering Mapped Data
Mapping Database Data
Conversion, Extraction, Join
Regex Expression
XML to CSV Conversion
CSV to XML Conversion
Advanced XML
Joining Data
Splitting Data
Performing Advanced Data Lookups
Uniting Data
Databases
Databases in Talend
Working with Hyper SQL (HSQL)
Looking at Table Schema’s
Importing Table Schema’s
Reading Data from Database
Writing Data to a Database

Day 2

Introduction to Pentaho Data Integration
Introduction to Pentaho
Objectives & Class Logistics
What is Pentaho Data Integration (PDI)?
Introduction to DataCleaner
Installation of PDI
Installation of DataCleaner in stand-alone mode
Installation of DataCleaner as Plug-in with PDI
Integrating DataCleaner with PDI
Creating a Job with PDI
Creating a Transformation with PDI
Executing a DataCleaner Job in PDI
Scheduling a PDI Job
Transformation Basics
Learning the PDI User Interface
Creating Transformations
Error Handling & Logging Introduction
Introduction to Repositories
Reading & Writing Files
Input & Output Steps
Parameters & kettle.properties
Reading a simple file and several files at the same time
Reading semi-structured files
Reading files having fields and rows
Reading an Excel file
Writing an Excel file with several sheets
Writing an Excel file with a dynamic number of sheets
Working with Databases

10.1. Connecting to & Exploring a Database

10.2. Table Input & Output

10.3. Insert, Update, & Delete Steps

10.4. Data Cleansing

10.5. Using Parameters & Arguments in SQL

Day 3

Data Flows & Lookups

11.1. Copying and Distributing Data

11.2. Splitting a stream into two or more streams based on a condition

11.3. Merging rows of two streams with the same or different structure

11.4. Comparing two streams and generating differences

11.5. Generating all possible pairs formed from two datasets

11.6. Joining two or more streams based on given conditions

11.7. Interspersing new rows between existent rows

11.8. Executing steps even when your stream is empty

11.9. Processing rows differently based on the row number

Calculations

12.1. Using the Group By Step

12.2. Calculator

12.3. Regular Expression

12.4. User Defined Java Expression

12.5. JavaScript

Job Orchestration

13.1. Introduction to Jobs

13.2. Sending Alerts

13.3. Looping & Conditions

13.4. Executing Jobs from a Terminal Window (Kitchen)

13.5. Creating jobs

13.6. Executing transformation using jobs

13.7. Passing and retrieving data to/from the transformations.

13.8. Conditional execution of jobs

13.9. Pre-execution Validation with job.

13.10. File management through Jobs

Scheduling

14.1. Setting up the Scheduler

14.2. Monitoring Scheduled Tasks

14.3. Scheduling on the Command Line

Exploring Data Integration Repositories

15.1. The Pentaho Data Integration Repository

Detailed Logging

16.1. Setting up a Dedicated DB Schema

16.2. Setting up Built-in Logging

16.3. Detailed Logging throughout Execution

Programme scope, delivery arrangements and suitability are confirmed during enquiry.