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
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.
- Extraction - get data from any Source viz. files, database tables, etc.
- Transformation – transform the data obtained from source.
- 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.