EXTRACTION, CLEANING AND TRANSFORMATION TOOLS Prepared By

EXTRACTION, CLEANING AND TRANSFORMATION TOOLS Prepared By Aakanksha Agrawal & Richa Pandey

MAIN FUNCTION: Data Extraction - Involves gathering data from multiple heterogeneous sources. Data Cleaning - Involves finding and correcting the errors in data. Data Transformation - Involves converting the data from legacy format to warehouse format. EXTRACTION, CLEANING AND TRANSFORMATION TOOLS 2

EXTRACT AND LOAD PROCESS Data extraction takes data from the source systems. Data load takes the extracted data and loads it into the data warehouse. Note: Before loading the data into the data warehouse, the information extracted from the external sources must be reconstructed. EXTRACTION, CLEANING AND TRANSFORMATION TOOLS 3

A) CONTROLLING THE PROCESS Controlling the process involves determining when to start data extraction and the consistency check on data. Controlling process ensures that the tools, the logic modules, and the programs are executed in correct sequence and at correct time. B) WHEN TO INITIATE EXTRACT Data needs to be in a consistent state when it is extracted, i.e., the data warehouse should represent a single, consistent version of the information EXTRACTION, CLEANING AND TRANSFORMATION to the TOOLS 4

C) LOADING THE DATA After extracting the data, it is loaded into a temporary data store where it is cleaned up and made consistent. Note: Consistency checks are executed only when all the data sources have been loaded into the temporary data store. EXTRACTION, CLEANING AND TRANSFORMATION TOOLS 5

CLEAN AND TRANSFORM PROCESS Once the data is extracted and loaded into the temporary data store, it is time to perform Cleaning and Transforming. Steps involved in Cleaning and Transforming: A) Clean and transform the loaded data into a structure B) Partition the data C) Aggregation EXTRACTION, CLEANING AND TRANSFORMATION TOOLS 6

A) CLEAN AND TRANSFORM THE LOADED DATA INTO A STRUCTURE Cleaning and transforming the loaded data helps speed up the queries. It can be done by making the data consistent: within itself with other data within the same data source with the data in other source systems with the existing data present in the warehouse EXTRACTION, CLEANING AND TRANSFORMATION TOOLS 7

A) CLEAN AND TRANSFORM THE LOADED DATA INTO A STRUCTURE Transforming involves converting the source data into a structure. Structuring the data increases the query performance and decreases the operational cost. The data contained in a data warehouse must be transformed to support performance requirements and control the ongoing operational costs. EXTRACTION, CLEANING AND TRANSFORMATION TOOLS 8

B) PARTITION THE DATA It will optimize the hardware performance and simplify the management of data warehouse. Here we partition each fact table into multiple separate partitions. C) AGGREGATION Aggregation is required to speed up common queries. Aggregation relies on the fact that most common queries will analyze a subset or an aggregation of the detailed data. EXTRACTION, CLEANING AND TRANSFORMATION TOOLS 9

EXTRACTION, CLEANING AND TRANSFORMATION Tasks of capturing data from source systems, cleansing and transforming it, and loading the results into a target system can be carried out either by separate products, or by a single integrated solution. Integrated solutions can fall into one of the categories below: Code Generators Database Data Replication Tools Dynamic Transformation Engines EXTRACTION, CLEANING AND TRANSFORMATION TOOLS 10

Thankyou 11