19 December 2017

Informatica PowerCenter as an ETL tool

In its essence, Informatica PowerCenter is a software whose main function is to extract data from different systems, data cleaning and transformation according to business rules, and then be loaded in a data mart or a data warehouse.

One of the main advantages of PowerCenter is its portability. There is no need for a specific database to make the tool work, because it can access almost any distributor, including mainframes.

The PowerCenter framework supports both batch and real-time data processing. The tool platform allows real-time access to the transactional base for the data to be updated with the speed and demand required by the business.


                                           Figure 1 - PowerCenter's Overview

With that said, the goal of this article is to frame some features of the tool using a practical example as guide line.

The PowerCenter is divided in four components that together allow the extraction, treatment and loading of the data:


The PowerCenter Repository allows you to share Metadata in different repositories to create a Data Mart. In a Data Mart, we can create a single global repository for storing company’s metadata, or we can create a series of local repositories to share global metadata as needed.

1. Add Repository



2. Define Repository name and its Username.




The Designer is used to create and configure the features and definitions of our sources, targets and the transformations that the data will suffer to later create the mappings.

To start the Designer, it is necessary to connect to the previously created repository. Double click on the name of the repository and the following window will appear:



After we access the repository, these are the main options available:



• Source Analyzer: To import or create sources definitions;
• Target Designer: To import or create targets definitions;
• Transformation Developer: To develop the transformations that will be used on the mappings;
• Mapplet Designer: The set of transformations (mapplet) used on mapping;
• Mapping Designer: To create the mapping that the integration service uses to extract, transform and load the information.




This component allows, as the name itself indicates, to monitor the existent workflows and tasks, allowing that way to see the extraction details, transformations and loadings using a Gantt chart or a list of logs.

In summary, we can:

• See the details of a specific workflow: using a Gantt chart or the option task view;
• Run, stop, abort and see the resume of workflows or tasks;
• See the last loaded workflows;
• Follow the history because the Monitor tool receives information from the Repository.




Allows creating or changing an ordained group from one or more sessions and other tasks, projected with a general operation objective. Executes a series of mappings (as sessions - can or not be reusable if needed by the development in hand) and other tasks. In general, allows to construct and processing workflows.

It is composed by:

• Task Developer: Creates the tasks that we need to realize in the workflow;
• Worklet Designer: Creates a worklet, which is an object that puts together a group of tasks. It is the same as a workflow but without the scheduling information;
• Workflow Designer: Creates a flow that connects the tasks between themselves. (Note: The tasks can also be created at the same time as the workflow).