Building Data Transformation and Control Flow Diagrams

The PowerDesigner Information Liquidity Model (ILM) lets you model and document ETL and EII processes in a rich graphical environment, supported by sophisticated metadata. By modeling your transformations in PowerDesigner, you can benefit from its powerful traceability and impact analysis features.

In most enterprises, information is stored in multiple databases, data warehouses and applications. Such a situation requires the recombination and transformation of data coming from diverse sources into new formats for replication reporting or other consumption.

ETL (Extract Transform and Load) and EII (Enterprise Information Integration) are two technologies that address this need:

ETL is a process in data warehousing that refers to three separate functions combined into a single programming tool:
- Extract – reads data from a specified source system.
- Transform – manipulates the extracted data to convert it for particular specialized consumption.
- Load – writes the resulting data to a target.

EII combines raw data by orchestrating and organizing queries to diverse source systems without any previous aggregation of the content. The data is not permanently moved into a new location.

The ILM lets you model and analyze data transformations in high-level and detailed views as follows:

Information liquidity diagram – high-level diagram, which enables you to model a high-level view of your data transformations by specifying:
- Input sources – Databases (PDMs), XML documents (XSMs), business processes (BPMs), and flat files such as .CSV and .XLS
- Transformation process – where the transformations occur. It contains lower level detailed transformation diagrams (see Transformation Processes).
- Output sources – Databases (PDMs), XML documents (XSMs), and flat files such as .CSV and .XLS
Data transformation diagram – low level diagram, which enables you to model a transformation task by specifying how data is extracted from data inputs, transformed by actions and loaded into data outputs. Data inputs and outputs are linked to the input and output sources specified in the high-level diagram.
Transformation control flow diagram – low level diagram, which enables you to specify the sequence of execution of a series of tasks.

The following example shows how input and output sources can be linked to a transformation process at a high level, and how the transformation is modeled in the lower level diagrams:

Modeling Data Transformations
You can model a data transformation in PowerDesigner by:
Data Transformation Diagram Basics
A data transformation diagram provides a graphical view of the inputs, outputs, and steps involved in a data transformation task.
Transformation Control Flow Diagram Basics
A transformation control flow diagram provides a graphical view of the order in which a series of data transformation tasks is linked together in a control flow.
Transformation Processes (ILM)
A transformation process is a high-level process, which models the extraction of data from input sources, its transformation, and loading to output sources. Input and output sources are linked to databases, flat files, XML documents, or business processes.
Data Inputs (ILM)
A data input represents a source of data in a data transformation diagram, and is linked to a database, an XML document, a web service or a flat file.
Actions (ILM)
An action represents a transformation to execute on input flows in a data transformation diagram. Filtering, aggregating or duplicating data are examples of transformation you may need to perform in your activities.
Data Outputs (ILM)
A data output represents a target destination to load data in a data transformation diagram, and is linked to a database, an XML document, or a flat file.
Data Flows (ILM)
A data flow conveys data between steps in a data transformation diagram.
Data Structure Columns (ILM)
A data structure column represents a database table column, a flat file column, an XML element or attribute, or an output parameter of a web service operation at a particular point in the transformation.
Transformation Parameters (ILM)
A parameter is an input or output variable global to a transformation task that you can use to customize your data transformations. A parameter is available to all diagrams within a given task. For example, if you manipulate sales figures, you might require a parameter specifying the sales region you are interested in.
Transformation Starts (ILM)
A transformation start initiates the sequence of execution of a series of data transformation tasks in a transformation control flow diagram.
Transformation Task Executions (ILM)
A transformation task execution is an instance of one or more data transformation tasks in a transformation control flow diagram. Tasks can be executed serially or in parallel.
Transformation Synchronizations (ILM)
A transformation synchronization enables the synchronization of control flows between two or more concurrent actions.
Transformation Decisions (ILM)
A transformation decision lets you choose between different paths by evaluating guard conditions, which must be satisfied for an associated flow to execute some action.
Transformation Ends (ILM)
A transformation end terminates the sequence of execution of a series of tasks in a transformation control flow diagram, and specifies the result for the execution, which can be either Success or Error.
Control Flows (ILM)
A control flow connects transformation starts, task executions, decisions, synchronizations and ends.