Saturday, April 23, 2016

Data Integration Methodologies

Data integration solutions can be categorized into three main forms.
  •     ETL - Extract,Transform,Load
  •     ELT - Extract, Load, Transform
  •     EII - Enterprise Information Integration (Data Virtualization)
ETL (Extract, Transform, and Load)

  • Extract: All processing required to connect to various data sources, extract the data from these data sources, and make the data available to the subsequent processing steps.

  • Transform: Any function applied to the extracted data between the extraction from sources and loading into targets.
        Movement of data
        Validation of data against data quality rules
        Modification of the content or structure of the data
        Integration of the data with data from other sources
        Calculation of derived or aggregated values based on processed dat
  • Load: All processing required to load the data in a target system.

ELT (Extract, Load and Transform)
  • The data is first extracted from the source(s),loaded into the target database, and then transformed and integrated into the desired format.All the heavy data processing takes place inside the target database.
  • ELT tool needs to know how to use the target database platform and the specific SQL dialect being used.

EII (Enterprise Information Integration)

  • Instead of physically integrating data, it is virtually integrated, making the data accessible in real time when it is needed.
  • data is always up-to-date.
  • There is no extra storage layer and no extra data duplication.
  • EII tool takes care of all the translations and transformations in the background.