18-12-2012, 06:51 PM
ETL PROCESS
1ETL PROCESS.ppt (Size: 587.5 KB / Downloads: 27)
ETL BACKGROUND
Data lies in all sorts of heterogeneous systems, and in all sorts of formats
For instance, a CRM system may define a customer in one way, while a back-end accounting system may define the same customer differently
ETL BACKGROUND
To solve problem, companies use
Extract, transform and load (ETL)
software, which includes :
Extracting data from its source,
cleaning it up
formatting it uniformly,
and then writing it to the target repository to be exploited.
ETL TOOLS
The ETL tools make the life of ETL developers easy.
Some of the ETL tools:-
Teradata Warehouse Builder from Teradata
DataStage from Ascential Software
Power Mart/Power Center from Informatica
Sagent Solution from Sagent Software
Hummingbird Genio Suite from Hummingbird Communications
MULTI- STAGE DATA TRANSFORMATION
Data transformations are complex and most costly part, in terms of processing time in ETL process
Simple data conversions, complex data scrubbing techniques
The data can be transform in the Multistage Data Transformation as follows
BASIC TRANSFORMATIONS
Format revision : Change to the data type , length of individual attribute
Decoding of fields : Different types of values for field but makes the same meaning
Example : Source system 1 and 2 contains a field called Gender and it’s values are given by M/F or 1/2 format
LOAD METHODS
Destructive Merge : Primary key of incoming record matches with key of existing record, UPDATE the target record. Record is new then add incoming record
Constructive Merge : Primary key of incoming record matches with key of existing record, REMOVE existing record and target record. Record is new then add incoming record