26 September, 2020
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.
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.
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.
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.
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.
[caption id="attachment_27145" align="aligncenter" width="638"] ETL Workflow[/caption]
ETL is all about simply getting data, altering it, then sending it somewhere else.
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]
The following of the various business components which are placing a key role in developing data warehouse.
Note:- An Informatica power center is a gui based ETL product from Informatica corporation.
A mapping logically defines extraction transformation and loading
A mapping is design with the following components
It is the structure of the source table (or) file from which data will be extracted.
It is the structure of the target tables into which data loads.
It defines data transformation
An odbc is a middle ware (or) an interface which allows you to access databases
When we install Informatica PowerCenter, the following component get install.
The following are the power center clients are
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.
It is a GUI based client component which allows you to create following power center objects.
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.
It is a GUI based client component which allows you to monitor the session and workflow running on integration service.
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.
It is a GUI based administrative client which allows you to perform the following tasks.
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.
The Repository is the brain of ETL system which contains “instructions” required to perform ETL. There are two types of repository can be created.
The metadata an be shared with in the repository
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.
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
It defines the extraction, it can be relational tables, flat files, XML files, cobal files, SAP, Oracle application etc.
It defines the data loading, it can be relational tables (or) files.
The engine that performs extraction, transformation and loading.
It manager connected to the metadata repository that contains mappings session and workflow definition.
A multi-threaded process that retrieves, inserts and updates repository metadata.
It contains all of the metadata need to run ETL proc
Desktop tools used to populate the repository with metadata execute workflows and integrating service and monitory service and manage the repository.
An Integration service is created with three components.
It extract the data from various types of sources the following are the types of readers
DTM (Data transformation manager)
It transforms the data according to the business logic that you configure in the mapping.
It inserts the data into a target system.
The following are the types of writers
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:
SQL> Grant DBA to Batch 4:00PM;
SQL> Connect batch4:00PM / Target@ORCL;
SQL> Show user
Create table dim- employee (EMPNO Number(5),
Sal Number (7,2),
Deptno Number (5));
SQL> conn scott
Enter Password: *****
Creation of dob connections
Source odbc:- Procedures
Start settings control panel Administrative tools Datasources (odbc)
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
Start settings control panel administrative tools services
A folder is a power center object which allows you to organize the metadata store in the repository.
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills .