Introduction to Informatica | Oracle Database | Databases

I n t r o d u c t i o nt oI n f o r ma t i c a Informatica is a Software development company, which offers data integration integratio n products. If offers products for ETL, data masking, data Quality, data replica, data virtualization, master data management, etc. Informatica owercenter owercenter ETL!"ata Integration tool is a most widely used tool and in the common term when we say Informatica, it refers to the Informatica ower#enter ower#enter tool for ETL. Informatica owercenter owercenter is used for "ata integration. It offers the capa$ility to connect % fetch data from different heterogeneous source and processing of data. &or e'ample, you can connect to an SQL SQL Server  Server "ata$ase and (racle "ata$ase $oth and can integrate the data into a third system. The latest version of Informatica ower#enter availa$le is ).*.+. The different editions for the ower#enter are Standard edition dvanced edition remium edition The popular clients using Informatica owercenter owercenter as a data integration tool are -.S ir &orce, llianz, &annie ae, I/0, Samsung, etc. The popular tools availa$le in the market in competition to Informatica are I1 "atastage, (racle (21, icrosoft SSIS and $ Initio.

• • •

Typical use cases for Informatica can $e •

•

•

•

n organization migrating from e'isting legacy system like mainframe to a new data$ase system. So the migration of its e'isting data into a system can $e performed. Enterprises setting up their "ata 2arehouse would 2arehouse would re3uire an ETL tool to move data from the roduction system to 2arehouse. Integration of data from various heterogeneous systems like multiple data$ases and file4$ased systems can $e done using Informatica. Informatica can $e used as a data cleansing tool.

Informatica is $etter than its competitors as it offers a wide range of product editions. So the user can opt for a specific edition $ased upon the re3uirement. Informatica Informatica is constantly featured as "ata Integration product leader in the 0artner agic Quadrant listing. Informatica is availa$le for all the popular platforms. It offers cloud4$ased services so that with minimal setup an industry can use this tool. Informatica offers real4time data integration, we$ services integration, 1usiness to $usiness data integration 51617, 1ig data edition, data edition, aster "ata anagement and connectors for social mediaand mediaand Salesforce. &or$es has 3uoted Informatica as the ne't icrosoft, this itself reflects the market share Informatica is having over its competitors.

Why do we need Informatica? Informatica comes to the picture wherever we have a data system availa$le and at the $ackend we want to perform certain operations on the data. It can $e like cleaning up of data, modifying the data, etc. $ased on certain set of rules or simply loading of $ulk data from one system to another. Informatica offers a rich set of features like operations at row level on data, integration integratio n of data from multiple structured, semi4structured or unstructured systems, systems, scheduling of data operation. It also has the feature of  metadata, so the information a$out the process and data operations are also preserved.

Informatica rchitecture Tutorial 1efore we learn how to use Informatica Informatica,, we need to understand what are the important components of Informatica and how it works. Informatica tool consists of following services % components 8. 9eposi 9eposito tory ry Se Servi rvice ce : 9esponsi$le for maintaining Informatica metadata % providing access of same to other services.

Informatica is $etter than its competitors as it offers a wide range of product editions. So the user can opt for a specific edition $ased upon the re3uirement. Informatica Informatica is constantly featured as "ata Integration product leader in the 0artner agic Quadrant listing. Informatica is availa$le for all the popular platforms. It offers cloud4$ased services so that with minimal setup an industry can use this tool. Informatica offers real4time data integration, we$ services integration, 1usiness to $usiness data integration 51617, 1ig data edition, data edition, aster "ata anagement and connectors for social mediaand mediaand Salesforce. &or$es has 3uoted Informatica as the ne't icrosoft, this itself reflects the market share Informatica is having over its competitors.

Why do we need Informatica? Informatica comes to the picture wherever we have a data system availa$le and at the $ackend we want to perform certain operations on the data. It can $e like cleaning up of data, modifying the data, etc. $ased on certain set of rules or simply loading of $ulk data from one system to another. Informatica offers a rich set of features like operations at row level on data, integration integratio n of data from multiple structured, semi4structured or unstructured systems, systems, scheduling of data operation. It also has the feature of  metadata, so the information a$out the process and data operations are also preserved.

Informatica rchitecture Tutorial 1efore we learn how to use Informatica Informatica,, we need to understand what are the important components of Informatica and how it works. Informatica tool consists of following services % components 8. 9eposi 9eposito tory ry Se Servi rvice ce : 9esponsi$le for maintaining Informatica metadata % providing access of same to other services.

6. Integr Integrati ation on Serv Service ice : 9esponsi$le for the movement of data from sources to targets ;. 9eport 9eporting ing Serv Service ice 4 Ena$ Ena$les les the generation of reports .

2or orkf kflo low w on onit itor or : -se -sed d to to mon monit itor or th the e e'ec e'ecut utio ion n of wo work rkfl flow owss

?.

9epo 9e posi sito tory ry a ana nage gerr : -s -sed ed to ma mana nage ge o$ o$@e @ect ctss in in rep repos osit itor oryy

In this tutorial4 you will learn •

Informatica "omain

•

ower#enter 9epository

•

"omain #onfiguration

•

roperties of the domain

•

owercenter client % Server #onnectivity

•

9epository Service

•

Integration Service

•

Sources % Targets

Informatica Domain The overall architecture of Informatica is Service (riented rchitecture 5S(7. •

•

Informatica "omain is the fundamental administrative unit in Informatica tool It is a collection of nodes and services. &urther, this nodes and services can $e categorized into folders and su$4folders $ased on the administration re3uirement.

&or e'ample, in the $elow screenshot, you can see under domain window the folder A"omainB9a@eshA is created under which we have created a node name Anode+8Bra@eshA and services as Aguru)) integration servicesA.

/ode is a logical representation of a machine inside the domain. /ode is re3uired to run services and processes for Informatica. Cou can have multiple nodes in a domain. In a domain, you will also find a gateway node. The gateway node is responsi$le for receiving re3uests from different client tools and routing those re3uests to different nodes and services. There are two types of services in "omain •

•

Service ManagerD Service manager manages domain operations like authentication, authorization, and logging. It also runs application services on the nodes as well as manages users and groups. Application ServicesD pplication service represents the server specific services like integration service, repository service, and reporting service. These services run on different nodes $ased upon the configuration.

PowerCenter epository ower#enter repository is a relational data$ase like (racle, Sy$ase, SQL server and it is managed $y repository service. It consists of data$ase ta$les that store metadata. There are three Informatica #lient tools availa$le in Informatica owercenter. They are Informatica • • •

"esigner 2orkflow onitor 2orkflow anager

These clients can access to the repository using repository service only. To manage a repository there e'ists an Informatica service called 9epository Service.  single repository service handles e'clusively only one repository. lso, a repository service can e'ecute on multiple nodes to increase the performance. The repository services use locks on the o$@ects, so multiple users cannot modify the same o$@ect same time. Cou can ena$le version control in the repository. 2ith the version control feature, you can maintain different versions of the same o$@ect. ($@ects created in the repository can have following three state •

•

•

!alidD alid o$@ects are those o$@ects whose synta' is correct according to Informatica. These o$@ects can $e used in the e'ecution of workflows. InvalidD Invalid o$@ects are those who does not adhere to the standard or rules specified. 2hen any o$@ect is saved in Informatica, it is checked whether its synta' and properties are valid or not, and the o$@ect is marked with the status accordingly. ImpactedD Impacted o$@ects are those whose child o$@ects are invalid. &or e'ample in a mapping if you are using a reusa$le transformation, and this transformation o$@ect $ecomes invalid then the mapping will $e marked as impacted.

Domain Configuration s mentioned earlier, domain is the $asic administrative control in Informatica. It is the parent entity which consists of other services like integration service, repository service, and various nodes. The domain configuration can $e done using the Informatica admin console. The console can $e launched using we$ $rowsers.

(nce open in a we$ $rowser it prompts for administrator login. The password is set during the Informatica installation.

fter login into the Informatica domain, the home page looks something like this.

In the left pane it shows the e'isting nodes, repository services, integration services under the domain.

(n the main window, it shows the status of those services, whether those are up or down.

Properties of the domain #lick on the properties menu in the admin page to view the properties of the domain.

Fey properties of the domain are 9esilience timeout : If any of the integration service or repository services goes down then resilience timeout is the no of seconds the application service tries to connect to those services. 9estart eriod : It is the ma'imum num$er of seconds the domain spends to restart a service.

"ispatch ode : It is the policy used $y the load $alancer to dispatch tasks to various nodes. "ata$ase type : The type of data$ase on which domain is configured. "ata$ase host : Gostname of the machine on which domain is configured. "ata$ase port % name : It is the data$ase port and the data$ase instance name for the domain. These properties can $e modified $ased upon re3uirement.

Powercenter client " Server Connectivity ower#enter client tools are development tools which are installed on the client machines. owercenter designer, workflow manager, a repository manager, and workflow monitor are the main client tools. The mappings and o$@ects that we create in these client tools are saved in the Informatica repository which resides on the Informatica server. So the client tools must have network connectivity to the server. (n the other hand, ower#enter client connects to the sources and targets to import the metadata and source!target structure definitions. So it also must have connectivity to the source!target systems. •

•

To connect to the integration service and repository service, ower#enter client uses T#!I protocols and To connect to the sources!targets ower#enter client uses ("1# drivers.

epository Service The repository service maintains the connections from owercenter clients to the ower#enter repository. It is a separate multi4threaded process, and it fetches, inserts and updates the metadata inside the repository. It is also responsi$le for maintaining consistency inside the repository metadata.

Integration Service Integration service is the e'ecuting engine for the Informatica, in other words, this is the entity which e'ecutes the tasks that we create in Informatica. This is how it works • • • • •

•

• •

 user e'ecutes a workflow Informatica instructs the integration service to e'ecute the workflow The integration service reads workflow details from the repository Integration service starts e'ecution of the tasks inside the workflow (nce e'ecution is complete, the status of the task is updated i.e. failed, succeeded or a$orted. fter completion of e'ecution, session log and workflow log is generated. This service is responsi$le for loading data into the target systems The integration service also com$ines data from different sources

&or e'ample, it can com$ine data from an oracle ta$le and a flat file source.

So, in summary, Informatica integration service is a process residing on the Informatica server waiting for tasks to $e assigned for the e'ecution. 2hen we e'ecute a workflow, the integration service receives a notification to e'ecute the workflow. Then the integration service reads the workflow to know the details like which tasks it has to e'ecute like mappings % at what timings. Then the service reads the task details from the repository and proceeds with the e'ecution.

Sources " #argets Informatica $eing an ETL and "ata integration tool, you would $e always handling and transforming some form of data. The input to our mappings in Informatica is called source system. 2e import source definitions from the source and then connect to it to fetch the source data in our mappings. There can $e different types of sources and can $e located at multiple locations. 1ased upon your re3uirement the target system can $e a relational or flat file system. &lat file targets are generated on the Informatica server machine, which can $e transferred later on using ftp. elational: these types of sources are data$ase system ta$les. These data$ase systems are generally owned $y other applications which create and maintain this data. It can $e a #ustomer 9elationship anagement "ata$ase, Guman 9esource "ata$ase, etc. for using such sources in Informatica we either get a replica of these datasets, or we get select privileges on these systems. $lat $iles % &lat files are most common data sources after relational data$ases in Informatica.  flat file can $e a comma separated file, a ta$ delimited file or fi'ed width file. Informatica supports any of the code pages like ascii or -nicode. To use the flat file in Informatica, its definitions must $e imported similar to as we do for relational ta$les. "ata integration for small as well as $ig market $usiness, Informatica ower#enter leverage one in all solution for ETL testing. To download and install Informatica, you have to visit the link given here.

Steps to Download Informatica The link will open Informatica download page, where you will see, •

•

ower#enter download link for ;6 $it and for *< $it version (ur system is *< $it system so we will install *< $it version

(nce the downloading is complete on your system follow the following steps to install ower#enter to your system.

Steps for Installing Informatica Step &D Cou will receive a file in zip format, to unzip the file right click on the file and then click on option A'(tract )ereA

Step *D  window will appear showing the e'traction of file in progress

Step +D Installation will $egin soon the file is e'tracted, and you can see the installation in progress

Step ,D ccept the terms and condition for Informatica and move to the ne't step

Step -D #lick on option AInstall Informatica ower#enter E'pressA and click ne't.

Step D It will open another window, where you have to provide the license key

Step /D Cou have to download the license key from the main page of Informatica. 9emem$er that license key is generated specifically for the user or customer that is registered with Informatica with their e4mail address

Step 0D (nce you download the license key, give the path of the license key $y $rowsing the ta$

8.

1rowse the ta$ to provide the path for license key

6.

(pen the file for license key

;.

#lick /e't

Step 1D fter giving the path for license key click on (F $utton

8.

0ive the path for license key

6.

#lick on ne't $utton to proceed

Step &2D In the ne't window, fill the detail a$out the domain. It will take some time to save the details.

8.

Enter the detail a$out • • • •

6.

"omain username "omain assword #onfirm assword 9epository /ame

#lick on /e't $utton

Step &&D This window will show the post4installation summary a$out Informatica, make sure you mark the check$o' for Launch Informatica "eveloper at the $ottom and then click done $utton

Step &*D The Informatica developer will launch

Step &+D (nce the installation of ower#enter is done it will direct you to the home4page

Step &,D In your window, you can see Informatica "eveloper is launched

Gow to #onfigure #lients and 9epositories in Informatica

fter installing Informatica server and client, Informatica server needs to $e configured. The Informatica architecture is a Service (riented rchitecture 5S(7. Therefore, $efore using Informatica tools, server and client components needs to $e installed and configured. rere3uisite to configure clients, integrati integration on service and repository service. Informatica server and client should $e installed. Informatica service should $e up and running. (racle or other compati$le data$ases must $e installed, and data$ase service must $e up. "ata$ase users must $e created for repository service. "omain administrator console credentials must $e availa$le 5it is configured during server installation7 •

•

•

• •

The following components needs to $e set up $efore you can start working in Informatica ower#enter. 8.

Informatica domain

6.

creating repository service

;.

creating integration service