Informatica Tutorial

In this Informatica tutorial, we will show you the step-by-step process of connecting with different data sources. Next, extract data from the data source, and transform the data using transformations. The last section of this Informatica tutorial covers the creating session and workflow and loads data into the destination with screenshots.

Informatica is the Market Leader in ETL tools, and over 5800 enterprises depend on this complete data integration solution. This Informatica business intelligence tool is mainly used to build powerful business applications for data extraction from Source(s), transforming and loading data into the target(s). This page explains everything about this data integration tool or ETL tool.

Use this Informatica tutorial page to learn all its features. The Power Center Client contains different applications or tools that help to design Mappings and Mapplets. Use this PowerCenter client to create a Session and WorKflow.

Informatica Tutorial Introduction

This Informatica data integration tool is a complete data integration solution provided by Oracle to perform data extraction from database management systems, perform data cleansing to process data, and load it into data warehousing or target database.

This is an introduction to this Informatica tutorial, which covers the installation of the data integration tool, configuring Powercenter, Workflows, etc.

As we all know, three steps are involved in Informatica or any ETL tool.

  • Step 1: Import data into the PowerCenter Designer to perform a transformation (cleaning) on the data (Chapter 2(a)). 
  • Step 2: Perform the data cleaning using the required transformation, please check Chapter 3.
  • Step 3: Load data into the destination table or file (Chapter 2(b)).
  • Step 4: To combine everything, we need Workflow, Session, and Mappings. So, please check the fourth Chapter.

Chapter 1: Software Configurations

The Informatica PowerCenter application allows to load or extract data from multiple sources (Source Qualifier) or data warehouses. Next, transform the data as per the business requirements (transformation). And loads the processed data into the destination table (Target Designer).

  1. How to Download the Software
  2. Step by Step approach to installing Software
  3. How to Configure the Repository Service & Integration Service in Informatica Admin Console.
  4. PowerCenter Repository Manager to create new domains and perform administrative tasks like deploying workflows, mappings, etc.
  5. Introduction to PowerCenter Designer is to design ETL. It is the place where we load data from sources, perform transformations, and send them to the destination or target tables.

Chapter 2: Informatica Tutorial on Data Connections

The Informatica PowerCenter accesses information or data from different sources. They are Flat files, XML, Relational Databases, SAP Hana, Teradata, Mainframes, Excel, and Access. Similarly, it supports the same destinations to load data.

This section of Informatica covers the different types of Data Connections and teaches basic ETL operations such as extraction, transformation, and load.

Chapter 2(a): Informatica Sources

The Source Analyzer is the page that helps to import data from various sources such as Text Files, Excel Files, Cobal Files, XML definitions, etc. Apart from the standard files, it supports all kinds of Databases. To connect with the databases, you have to create an ODBC connection, and the Microsoft Access Driver helps to connect with the MS Access database.

The following is the List of Informatica sources that we can use to import data into the PowerCenter Designer, please use the hyperlink to see the detailed tutorial information.

  1. PowerCenter Source Analyzer
  2. ODBC Connection
  3. Import from Relational Database
  4. How to Import from Flat File
  5. Import from Excel Workbook
  6. Move Data from One SQL Database to another Database
  7. Export from SQL Server to Flat File
  8. Load From Text File to SQL Server
  9. Load Multiple Text Files into SQL Server

Chapter 2(b): Informatica Targets

The Target Designer is the tab that helps to export or load data to various destinations such as Databases, Text Files, Cobal Files, Excel Files, XML Files, etc. Informatica provides various drivers to connect with all kinds of Databases and files. So, all you have to do is choose the right driver for your destination.

The following is the List of Informatica targets, please use the hyperlink to see the tutorial steps involved in creating a target table, loading data, etc.

  1. Target Designer
  2. Create a Target table using the Source Definition
  3. Create a New Target Table

Chapter 3: Informatica Transformations

This Informatica tutorial section covers the list of available Transformations.

  1. Aggregator to aggregate the source data using the available functions. The supporting operations include finding Average, Sum, Minimum, Maximum, Count records, Median, Variance, and Standard deviation.
  2. Expression helps to perform non-aggregate math calculations on the source data.
  3. Filter Transformation will filter the source data based on the specified condition or expression result (Boolean True or False).
  4. Java Transformation allows you to write a custom Java code inside the workflow to perform complex functionality.
  5. Joiner Transformation: It covers all the available ones, such as Normal Join, Master Outer Join, Detail Outer Join, and Full Outer Join.
  6. The Informatica Lookup Transformation is to perform lookup operations on Tables, Flat Files, Excel, and Views.
  7. Unconnected Lookup is a function with a parameter. You can perform a lookup at the table.
  8. Normalizer is to perform data normalization on the unnormalized source data.
  9. Rank Transformation and the Group By option. It helps to rank the data from top to bottom or bottom to top.
  10. Router Transformation is the same as the IF ELSE condition or CASE statement. Informatica checks the condition and routes the data based on the result.
  11. Sequence Generator to generate sequence numbers for the primary or foreign key columns.
  12. Sorter Transformation will sort the incoming source data into the Ascending or Descending order.
  13. Informatica Source Qualifier is the default one generated by the Power Center Designer.
  14. SQL Transformation allows to write the transact queries to truncate, insert, update, and delete in the middle of the workflow.
  15. Stored procedure Transformation allows to call or execute the stored procedure inside the workflow to perform complex calculations.
  16. Transaction Control helps to control the transaction such as committing the records or rollback to the original position.
  17. Union Transformation combines data from multiple data sources (or tables) without removing duplicates.
  18. Update Strategy performs Insert, Update, or Delete operations on the target table based on the data inside the Source. It compares and performs any of the three operations.
  19. Pre and Post SQL Transformation.

For all the remaining Informatica Transformations, such as XML Generator, XML Parser, and XML Source Qualifier, please refer to the List of Transformations article.

Chapter 4: Informatica Sessions & Mappings

This Informatica tutorial section explains the creation of Sessions, Mapping, and Workflow. These are very helpful in running the applications.

This section of the Informatica tutorial is the most important section for learning data management. Because of its reusability in all the transformations.

The Introduction to Workflow Manager contains the Task designer, worklet designer, and workflow designer. Another key point is that you can create the Informatica Session task, Event, Email, Command, etc.

  1. The Mapping defines the rules of the data transformation from the source file to the target table.
  2. Non-Reusable and Usable Session: A session is a set of instructions about when and how to move data from source to destination.
  3. Informatica Workflow is a set of instructions on how to run one or more tasks that are connected via links.
  4. Command Task allows you to run the Unix or shell commands within the workflow.
  5. Event Wait Task allows you to create events and this task will wait for a specified Event to occur.

There is one more item called Informatica Workflow Monitor allows you to monitor or track the tasks or workflow execution process.

Comments are closed.