DATA ACQUISITION –DATA EXTRACTION - Part 2

Data Extraction:

          It is a process of reading the data from various types of sourcesSuch as relational sources, ERP sources, Mainframe sources, XML file and Flat files.

Relation –  Oracle, MSSQL

ERP –  SAP,PeopleSoft

Mainframe – Cobol, DB2

File-Flat File (Text File), XML file

Data Transformation:

   It is a process of cleaning the data and transforming the data into .A required business format.

   The following data transformation activities take place in staging

Area.

Ø   Data Merging

Ø   Data Cleansing

Ø   Data Scrubbing

Ø   Data Aggregation

Data Merging:

             It is a process of combining the data from multiple inputs andLoad into a single output. There are two types of Data Merging Activities.

1. Join

2. Union

Data Cleansing:

          It is a process of removing unwanted data from Staging

                                               OR

          It is a process of changing inconsistencies and inaccuracies

Example : Init Cap() and Round() functions

Data Scrubbing:

  It is a process of deriving new data definitions using existing data.

Example:  Concat (First Name+ Last Name), Sal Amount=QTY*Price

Data Aggregation:

    It’s process of calculating the summaries for a group of recordsUsing aggregate functions.

Example : Average, Max, Min etc….

Data Loading:

  It is a process of inserting the data into a target system. There are 2 types of Data Loads.

Ø   Initial or Full Load

Ø   Incremental or Delta Load

1. Initial or Full Load

       It is a process of loading all the required data at very first load.

2. Incremental or Delta Load

       It is a process of loading only new records after initial load.

Data Mart

A data mart is a simple form of a data warehouse that is focused on a single subject (or functional area), such as Sales, Finance, or Marketing. Data marts are often built and controlled by a single department within an organization.

There are 2 types of DM

1. Dependent DM

2. Independent DM