Learning Informatica PowerCenter 10.x - Second Edition

Rahul Malewar

• Publisher: Packt Publishing
• Year: 2017
• ISBN:
  9781788474108
• Edition: 2
• Topic: Computer Science
• Subtopic: Data Processing
• Index: Computer Science

Harness the power and simplicity of Informatica PowerCenter 10.x to build and manage efficient data management solutionsAbout This Book• Master PowerCenter 10.x components to create, execute, monitor, and schedule ETL processes with a practical approach.• An ideal guide to building the necessary skills and competencies to become an expert Informatica PowerCenter developer.• A comprehensive guide to fetching/transforming and loading huge volumes of data in a very effective way, with reduced resource consumptionWho This Book Is ForIf you wish to deploy Informatica in enterprise environments and build a career in data warehousing, then this book is for you. Whether you are a software developer/analytic professional and are new to Informatica or an experienced user, you will learn all the features of Informatica 10.x. A basic knowledge of programming and data warehouse concepts is essential.What You Will Learn• Install or upgrade the components of the Informatica PowerCenter tool• Work on various aspects of administrative skills and on the various developer Informatica PowerCenter screens such as Designer, Workflow Manager, Workflow Monitor, and Repository Manager.• Get practical hands-on experience of various sections of Informatica PowerCenter, such as navigator, toolbar, workspace, control panel, and so on• Leverage basic and advanced utilities, such as the debugger, target load plan, and incremental aggregation to process data• Implement data warehousing concepts such as schemas and SCDs using Informatica• Migrate various components, such as sources and targets, to another region using the Designer and Repository Manager screens• Enhance code performance using tips such as pushdown optimization and partitioningIn DetailInformatica PowerCenter is an industry-leading ETL tool, known for its accelerated data extraction, transformation, and data management strategies. This book will be your quick guide to exploring Informatica PowerCenter's powerful features such as working on sources, targets, transformations, performance optimization, scheduling, deploying for processing, and managing your data at speed.First, you'll learn how to install and configure tools. You will learn to implement various data warehouse and ETL concepts, and use PowerCenter 10.x components to build mappings, tasks, workflows, and so on. You will come across features such as transformations, SCD, XML processing, partitioning, constraint-based loading, Incremental aggregation, and many more. Moreover, you'll also learn to deliver powerful visualizations for data profiling using the advanced monitoring dashboard functionality offered by the new version.Using data transformation technique, performance tuning, and the many new advanced features, this book will help you understand and process data for training or production purposes. The step-by-step approach and adoption of real-time scenarios will guide you through effectively accessing all core functionalities offered by Informatica PowerCenter version 10.x.Style and approachYou'll get hand-on with sources, targets, transformations, performance optimization, scheduling, deploying for processing, and managing your data, and learn everything you need to become a proficient Informatica PowerCenter developer.

The Lifeline of Informatica - Transformations

Transformations form the most important aspect of the Informatica PowerCenter tool. The functionality of any ETL tool lies in the transformations. Needless to say at this point, transformations are used to transform data. Informatica PowerCenter provides multiple transformations, each serving a particular functionality. Transformations can be created as reusable or non-reusable based on the requirement. The transformations created in workflow manager are non-reusable, and those created in task developer are reusable. You can create mapping with a single transformation or with multiple transformations.

When you run the workflow, integration service extracts the data in a row-wise manner from the source path/connection you defined in the session task and makes it flow from the mapping. The data reaches the target through the transformations you defined.

The data always flows in a row-wise manner in Informatica, no matter what your calculation or manipulation is. So, if you have 10 records in the source, there will be 10 source-to-target flows while the process is executed.

We will be talking about the following topics in this chapter:

• Adding transformation in mapping
• Ports of transformation
• Various transformations such as Expression, Aggregator, Sorter, Filter, Router, Lookup, Sequence Generator, Update Strategy, and so on
• Classification of transformation
• Tracing levels

Creating the transformation

There are various ways in which you can create the transformation in the designer tool. They are discussed in the following sections.

Mapping Designer

To create transformations using Mapping Designer, perform the following steps:

1. Open the Mapping in Mapping Designer. Then open the mapping in which you wish to add transformation, and click on Transformation | Create as shown:
   
2. From the drop-down list of transformations, select the transformation you wish to create, specify the name, and click on Create and then Done:
   

The transformation appears in the Mapping Designer workspace.

For our reference, we have created an Expression transformation in the preceding screenshot. You can create all other transformations in the same way.

The transformations you create in Mapping Designer are non-reusable, that is, you cannot use those transformations in other mappings. You will learn about reusable transformations in Chapter 5, Using the Designer Screen - Advanced Features.

Mapplet Designer

To create transformations in Mapplet Designer, perform the following steps:

1. Open the Mapplet in Mapplet Designer, and click on Transformation | Create as shown previously.
2. From the drop-down list of transformations, select the transformation you wish to create, and specify the name.

Transformation Developer

To create transformations in designer, perform the following steps:

1. Open the Transformation Developer, and go to Transformation | Create as shown previously under Mapping Designer.
2. From the drop-down list of transformations, select the transformation you wish to create, and specify the name as shown in the preceding section under Mapping Designer.

The transformations created in the Transformation Developer are reusable, that is, you can use them across multiple Mappings or Mapplets.

With this basic understanding, we are all set to jump into the most important aspect of Informatica PowerCenter tool--the transformations.

Expression transformation

Expression transformation is used for row-wise manipulation. For any type of manipulation you wish to do on an individual record, use expression transformation. Expression transformation accepts row-wise data, manipulates it, and passes to the target. The transformation receives the data from the input port and sends the data out from the output ports.

Use the expression transformation for any row-wise calculation, for instance, if you want to concatenate the names, calculate the total salary and convert in upper case, and so on. To understand functionality of expression transformation, let's consider the following scenario:

Scenario for expression transformation: Using the flat file we created in Chapter 3, Understanding Designer Screen and its Components as the source, concatenate FIRST_NAME and LAST_NAME to get FULL_NAME, and get TOTAL_SALARY from JAN_SALARY and FEB_SALARY of individual employee.

We are using expression transformation in this scenario because the value of FULL_NAME can be achieved by concatenating FIRST_NAME and LAST_NAME of an individual record. Similarly, we can get TOTAL_SALARY using JAN_SALARY and FEB_SALARY. In other words, the manipulation required is row wise.

We are going to learn some basic aspects about transformations such as ports of transformations, writing a function, and so on while we implement our first transformation using Expression.

Perform the following steps to achieve the functionality:

1. Create the source using the flat file in Source Analyzer and target in Target Designer. We will be using EMP_FILE as the source and TGT_EMP_FILE as the target.
2. Create a new mapping m_EXP_CONCAT_TOTAL in mapping designer, drag the source and target from Navigator to Workspace, and create the Expression Transformation with the name EXP_CONCAT_TOTAL.
3. Drag all the columns from source qualifier to expression transformation. At this point, the mapping will look like this:
   

We have connected EMPLOYEE_ID, AGE and LOCATION directly to target as there is no manipulation needed for those columns.

At this step, we need to understand how to use the different types of ports in the transformation.

Ports in transformations

The transformations receive the data from the input ports and send the data out using the output ports. Variable ports temporarily store the value while processing the data.

Every transformation with a fe...