Chapter 9 Data Transformations Basics

Data Transformations Overview

In most enterprises, information is stored in separate databases, data warehouses and applications causing more and more difficulties in the information management.

This compels the need to recombine data coming from diverse sources into one that satisfy your business requirements.

ETL (Extract Transform and Load) and EII (Enterprise Information Integration) are two technologies that fully address that need in two different ways:

ETL

ETL (Extract Transform and Load) is a process in data warehousing which refers to three separate functions combined into a single programming tool:

Extract function reads data from a specified source system, and extracts it or a subset of it
Transform function manipulates the extracted data to convert it in order to fit business needs
Load function writes the resulting data to a target that may or may not previously exists.

Note that ETL is not a standard procedure but multiple processes, which vary according to an enterprise needs.

EII

EII (Enterprise Information Integration) combines raw data by orchestrating and organizing queries to diverse source systems without any previous aggregation of the content. Note that data is not permanently moved into a new location; rather the source data remains where they are.

PowerDesigner Information Liquidity Model lets you design and document ETL and EII processes, together with providing a link to the other metadata, such as the PDM for example. This lets ETL and EII processes benefit from the traceability and impact analysis features of PowerDesigner.

Data combination high level view

The Information Liquidity Diagram lets you represent data combination functions in a high-level view as follows:

Data input. It can be a database (associated with a Physical Data Model), an XML document (associated with an XML model), a Business Process (associated with a Business process Model), and a flat file containing columns.
Transformation process. It lets you create data transformation tasks and transformation control flows with their respective diagrams: the Data Transformation Diagram and the Transformation Control Flow Diagram. See Transformation Processes.
Data output. It can be a database (associated with a Physical Data Model), an XML document (associated with an XML Model), and a flat file.

Transformation detailed view

The Information Liquidity Model lets you represent data transformation tasks and their workflow in detailed views as follows:

The Data Transformation Diagram lets you define a detailed view of a task. It shows how data is extracted from data inputs, transformed by elementary or complex actions and loaded into data outputs. Data inputs and outputs are linked to the source and target data stores defined in the high level view.

The Transformation Control Flow Diagram lets you define the sequence of execution of a series of tasks. Each task execution is an instance of a task.