Informatica Tutorials & Interview Questions

Informatica Powercenter Tutorial

Welcome to the Tekslate Informatica Tutorials. The intent of these tutorials is to provide you in depth understanding of Informatica ETL Tool. In these tutorials, we will cover topics such as Informatica basics, Informatica Architecture, Router Transformations, Source Qualifier Transformations and Informatica PowerCenter Installation etc.

In addition to Informatica Tutorials, we will look at common interview questions, how to tutorials, issues and their resolutions.

If you want to enrich your career and become a professional in Informatica, then visit Tekslate - a global online training platform: "Informatica Training" This course will help you to achieve excellence in this domain.

Informatica Introduction

Knowledge Base

• Informatica Concepts
• Informatica in Data Warehouse
• ETL Project Architecture in Informatica

1.What is Informatica Tool?

Ans: Informatica is a data integration/ETL tool. It gathers data from various sources and loads it into distinctive targets. It just moves data from one place to another, but does not store anything. Informatica utilizes a service oriented architecture, that carries the ability to promote services and resources over various machines.

2.What is Informatica?

Ans: Imagine going through thousands of lines of stored procedures when visually debugging a few mappings is handy.

To overcome the problem, Informatica technology and its tools provide monitoring features and a UI for building a logic and out of the box connectivity to multiple sources and targets.

Another advantage is when you make complex aggregations or joins, you can use the power of the ETL server to buffer the data rather than the burdening your database that can be utilized for reporting/Analytics.

3.Who owns Informatica?

Ans: Informatica Corporation is the world's leading Independent provider of Enterprise Data Integration software and services. Around the world, enterprises rely on Informatica to acquire potential information and manage top business imperatives.

Informatica’s competent features made it go private in a $5.3 billion buyout owned by equity firms Permira Funds and Canada Pension Plan Investment Board.

4.What are ETL tools used for?

Ans: ETL stands for "Extract/Transform/Load".

ETL tools are unique and incomparable. The action of Extracting, Transforming, and Loading significant data into the data warehouse is organized and evaluated for substantial business intelligence that governs the success of business policies.

Informatica Tutorials

[caption id="attachment_27145" align="aligncenter" width="638"] ETL Workflow[/caption]

ETL - Extract Transform Load Tutorials

ETL is all about simply getting data, altering it, then sending it somewhere else.

1. Extraction - get data from any Source viz. files, database tables, etc.
2. Transformation – transform the data obtained from source.
3. Load – This transformed data is ready to load anywhere known as Target.

Almost any type of data store, including web services, Teradata or any SAP solutions can be termed as a Source and Target.

[button url="http://tekslate.com/informatica-training/" class="blue" bg="" hover_bg="" size="0px" color="" radius="0px" width="0px" height="0px" target="_self"] Informatica Training [/button]

Informatica Architecture

   

Informatica Components

The following of the various business components which are placing a key role in developing data warehouse.

• Informatics PowerCenter
• Informatica Power Connect
• Informatica Power Mart
• Informatica Power Exchange
• Informatica Power Analyses
• Informatica Power Quality

Differences between PowerCenter and Power Mart

Informatica PowerCenter

• It suppose to process high volume of data
• It support ERP source such as SAP, PeopleSoft etc...
• It supports both global and local repositories
• It can convert a local repository into global repository

Informatica Power Mart

• It is recommended to process low volumes of data
• It doesn’t support ERP sources such as SAP, people soft etc
• It supports local repository
• There is no specialty to convert local to global repository

Note:- An Informatica power center is a gui based ETL product from Informatica corporation.

Mapping Components

A mapping logically defines extraction transformation and loading

A mapping is design with the following components

Source definition

It is the structure of the source table (or) file from which data will be extracted.

Target definition

It is the structure of the target tables into which data loads.

Transformation logic

It defines data transformation

Extra:-

• A mapping is nothing but a ETL plan processing
• Mapping design with source definition, target definition and business logic

Odbc (Open data base connectivity)

An  odbc is a middle ware (or) an interface which allows you to access databases

Informatica Tutorials For Beginners With Examples

Informatica PowerCenter Components

When we install Informatica PowerCenter, the following component get install.

• Power center clients
• Power center repository
• Power center domain
• Integration service
• Repository service
• Web service hub
• Power center administration console

Informatica Power Center Clients

The following are the power center clients are

• Power center designer
• Power center workflow manager
• Power center work flow monitor
• Power center repository manger

Informatica Power Center Designer

The designer is GUI based client component which allows you to design a plane of ETL process known as mapping.

The designer allows you to create following power center objects.

• Source definition
• Target definition
• Design mapping with or without transformation Rule or logic

Checkout Our  Frequently asked Informatica Interview Questions & Answers

Informatica Power Center Workflow Manager

It is a  GUI based client component which allows you to create following power center objects.

• Create a session for each mapping
• Create work flow to start sessions

Session

• A session is a power center object which runs mapping.
• A session which makes mapping avail i.e. for extraction, transformation and loading
• A session is the pointer to map

What is Informatica Workflow

A workflow is known as “St task” which can run one or more sessions. The sessions can sequential (or) parallels.

Workflow can execute only no. ________ in sequentially

The top objects in the power center development hierarchy is workflow.

Informatica PowerCenter Workflow Monitor

It is a GUI based client component which allows you to monitor the session and workflow running on integration service.

• It allows you to view session and work flow and session states
• It allows you to fetch the session log from repository
• Integration services is an ETL engine it perform ETL

Steps involving implementing ETL Process:-

Step 1. :- Creation of source definition

Step 2 :- Creation of target definition

Step 3:- design a mapping with (or) without transformation rule  Workflow manager

Step 4:- Create a session for each mapping

Step 5:- Create work flow

Step 6:- Start Work flow.

Informatica Power Center Repository Managers

It is a GUI based administrative client which allows you to perform the following tasks.

• Create, edit, delete folders
• Assign, permission to the users or user groups

Informatica Power Center Repository

The  power center repository is a relational database, a system databases which contains meta data which is required to perform extraction, transformation and loading

The repository database consist of following type of meta data objects.

• Source definition
• Target definition
• Transformation rules
• Mappings
• Session
• Odbc connection
• Work flows
• Session logs

The Repository is the brain of ETL system which contains “instructions” required to perform ETL. There are two types of repository can be created.

• Local repository
• Global repository

Local Repository

The metadata an be shared with in the repository

Global Repository

 [button url="https://tekslate.com/informatica-interview-questions-answers/" class="green" bg="" hover_bg="" size="0px" color="" radius="0px" width="0px" height="0px" target="_self"] Informatica Interview Questions [/button]

Informatica Repository Service

The Repository service is a “multithreaded” process which inserts, updates, delete and retrieve metadata from repository.

The power center client applications access the repository through repository service.

The integration service access the repository through repository service to read the metadata such as mapping and sessions.

The integration service also stores metadata such as session log, session and workflow status in the repository through repository service

Session log are created integration service.

Informatica Integration Service

The Integration service is an ETL engine which performs extraction transformation and loading

The integration service reads session and mappings from repository.

It extract the data from mapping sources, stores in a temporary memory called staging where it apply the transformation rules to process the data.

It loads transformed data into mapping targets

Success and  failures and session logs are created integration service

• Integration service ETL Process engine
• Repository collection of tables that contains power center metadata
• Repository services A centralized management of repository connection
• Repository manager Performs repository security
• Designer Create mapping objects
• Work flow manger Create and start work flows
• Work flow monitor and controls work flows
• Repository services process Multi threaded process that retrieves, update, inserts, repository metadata.

Informatica Power Center Architecture

Sources

It defines  the extraction, it can be relational tables, flat files, XML files, cobal files, SAP, Oracle application etc.

Targets

It defines the data loading, it can be relational tables (or) files.

Integration services:-

The engine that performs extraction, transformation and loading.

Informatica Repository Service

It manager connected  to the metadata repository that contains mappings session and workflow definition.

Repository Services Process

A multi-threaded process that retrieves, inserts and updates repository metadata.

Repository

It contains all of the metadata need to run ETL proc

Client Tools

Desktop tools used to populate the repository with metadata execute workflows and integrating service and monitory service and manage the repository.

• Populate metadata means extraction transformation and loading mapping source workflow

Integration Service – Component

An Integration service is created with three components.

Reader

It extract  the data from various types of sources the following are the types of readers

• Relational reader
• File reader
• XML Reader

DTM (Data transformation manager)

It transforms the data according to the business logic that you configure in the mapping.

Writer

It inserts the data into a target system.

The following are the types of writers

• Relational writer
• File writer
• XML writer

Setup Source and Target Database

Start Programs oracle  Application development

Client on SQL Plus.

Enter the following Logon details

Username : System

Password : NiPUNA

Host string : ORCL

SQL > Create user batch 4:00 PM identified by target:

User created

SQL> Grant DBA to Batch 4:00PM;

SQL> Connect batch4:00PM / Target@ORCL;

Connected

SQL> Show user

Create table dim- employee (EMPNO Number(5),

Ename Varchar2(15)

Sal Number (7,2),

Deptno Number (5));

Table created

SQL> conn scott

Enter Password: *****

Creation of dob connections

Source odbc:- Procedures

Start settings control panel Administrative tools Datasources (odbc)

• Select system DSN tab click on add
• Select the driver oracle in ORADDBLOG-home click on finish

Enter the following details

Data source name: Batch 4:00 PM _ source_oracle

TNS Service Name : ORCL

User ID : SCOTT

Click on Test connection

Enter password: Tiger click ok

Target odbc:- procedure:

From system DSN Tab click on Add

Select the driver oracle in oraDblog-home1

Click on finish.

Data source name : Batch 4:00PM – target –oracle

TNS service Name :ORCL

Userid : Batch 4:00PM

Click on test connection

Enter password: Target click ok

Informatica Services

Start settings control panel administrative tools services

Start the following services

• Informatica orchestration service
• Informatica services

Creation of folder

A folder  is a power center object which allows you to organize the metadata store in the repository.