In this Informatica tutorial, we will show you the step by step process to connect with different data sources. Next, extract data from data source, transform the data using transformations. Last section of this Informatica tutorial covers the creating session and workflow, and load data into the destination with screenshots.
Informatica is the Market Leader in ETL tools, and over 5800 enterprises depend on it. The Informatica is mainly used to build powerful business applications for extracting data from Source(s), transforming and loading data into the target(s). In this Informatica tutorial page, we explain everything about this ETL tool.
The Informatica PowerCenter Client contains different applications or tools that help to design Mappings and Mapplets. Use this PowerCenter client to create a Session and WorKflow.
This is an introduction to this Informatica tutorial, which covers, installation of Informatica, configuring Power center, Workflows etc.
Informatica PowerCenter application allows to load or extract data from multiple sources (Source Qualifier), transform the data as per the business requirements(transformation), and loads the processed data into the destination table (Target Designer).
Informatica PowerCenter accesses the information or data from different sources such as Flat files, XML, Relational Databases, SAP Hana, Teradata, Mainframes, Excel, and Access. Similarly, Informatica supports the same destinations to load data. This section of Informatica Tutorial covers the different types of Data Connections, and basic ETL operations.
This Informatica tutorial section explains the creation of Informatics Sessions, Mapping, and Workflow. This section of Informatica tutorial is the most important section because of its reusability in all the transformations.
A transformation is a part of Informatica Mapping, which transforms or modifies the data as per the selected transformation. This section of Informatica tutorial covers the list of available Transformations with practical examples.
Step 1: In order to download Informatica first Go to the Oracle website by clicking this link Download. Next, Click on the Sign In button to log in to your personal account
Step 2: Once you click on the sign in button, the following window will be opened. Please provide the username and password of your personal or organization account and click on Sign In button.
If you didn’t have oracle account then, create one by clicking the create Accountbutton and fill in the required details.
Step 3: Once you sign in, Please click on the Accept button as shown below
Step 4: Once you accepted the terms, the following screenshot will be displayed. Here, Product field is to type the required product(s) name(s) and Select Platform is to select the operating system(s).
Step 5: Here we want to download Informatica so within the Product field, Start typing Informatica and select Oracle Informatica PowerCenter and PowerConnect Adapters as shown below
Step 6: Selecting the Select Platform will show you the list of available platforms. Please select the checkbox next to the platform(s) you need, and click the Select button. Since we are running on windows 64 bit, we selected the same.
Step 7: once you selected the product and the platform, Please click on the continue button as shown below.
8th Step: Make sure you checkmark the listed software’s and click Continue button
Step 9: Once you click on the continue button, Terms and Conditions pop-up window will be displayed. Please click on the checkbox to accept the conditions, and then click on the Continue button
Step 10: Click on the Download All button as shown below. If you require any particular part(s) then, click on the part(s) link and download them.
Step 11: Next, Click on the Download the installer hyperlink to download the installer.
Step 12: Please click on Save File as shown below to save the installer.
Once the download is complete, install it.
Step 14: Once you complete installing the recommended installer, the following window will be displayed. As you can see, it is downloading four zip files
Step 15: From the below screenshot you can notice the four ZIP files. It means, we successfully downloaded informatica from the Oracle website.
Step 16: Please extract them
Step 17: Once you extract the four parts, Go to V76290-01_4of4 folder and select the dac_win_11g_infa_win_64bit_961 zip file and extract it.
Remember, While extracting the above RAR file, Win-zip will ask the file path of four parts. So,
TIP: Please refer Install Informatica article to understand the steps involved in installing Informatica
In this article, we will show you, How to install Informatica (irrespective of the version) with the screenshots. Installing the Informatica includes following steps so, Please follow the steps in the same order we mentioned below:
Before we start installing Informatica,
TIP: Please refer Download Informatica article to understand the steps involved in downloading Informatica 9.6.1
Once you extracted the dac_win_11g_infa_win_64bit_961 zip file, four ZIP files and one KEY file will be opened. Please select and extract all of them. The following screenshot will show you the ZIP files and extracted folders.
Please navigate your file system to the Server folder and click on the install.exe application as shown below
Step 1: Once you click on install application, Informatica installation wizard will be opened as shown below. Here we have two options:
Step 2: Here you can verify the Disk space, Software and Hardware requirements to install Informatica
Step 3: Here we have to select the Oracle licence key so click on the … button beside the first text box. This will open our file system to navigate and select the Oracle licence key. From the below screenshot you can observe that we selected the key from Inf Soft folder.
NOTE: Here you can also change the default installation directory
Step 4: Click Next button to start installing the Informatica server
5th Step: Please wait until it installs 100%
Step 5A: Here we have multiple options. If you are installing for the first time, Please leave the default settings and click the Next button:
Step 5B: Within the First section, we have to configure the database settings.
Within the second section we have a Database connection:
Please fill your database details. Before you click on Next button, Please test your connection setting by clicking on the Test Connection button.
Step 5C: Here we have to specify the encryption key details (which is a combination of upper case, lower case and a special character). This is just for domain security
Step 6: Please provide the Domain password and confirm it by retyping it.
TIPS:
Step 6: If you want the Informatica service under a different account then please checkmark the Run Informatica under a different user account option and provide the username and password.
Once you click on the next button, the following screenshot will be displayed. From the below screenshot you can observe that We successfully installed the Informatica Server into our local hard drive
Please click Informatica Administrator home page URL to open the Informatica Admin Console. Within the Home page, we have to configure the Informatica Repository Service and Informatica Integration Service. Please refer Informatica Admin Console article to understand the same.
Once you completed configuring the Informatica Repository Service and Integration Service. Please navigate your file system to Client folder and click on the install batch file as shown below
Step 1: Once you click on install batch file, Informatica installation wizard will be opened as shown below. Here we have two options:
Step 3: Please select all the check marks and click the Next button
Step 4: Here you can leave the default installation directory or change by clicking the … button as shown below
Step 4A: Here we have multiple options to choose from:
Click Next button to start installing Informatica client
Step 6: Please wait until it installs 100%
From the below screenshot you can observe that we successfully installed Informatica client
In this article, we will show you, How to use the Informatica Admin Console page to configure the Informatica Repository Service and Informatica Integration Service.
In order to open the Informatica Admin Console, Please Go to the Start menu -> Programs -> Informatica -> Server -> Informatica Administrator home page.
NOTE: Please refer How to install Informatica article to understand the steps involved in configuring the Informatica Server and Clients.
Once you click on the Administrator home page, the following page will be opened. Please provide the Username and password you specified while installing the Informatica Server. Please refer Step 6 in How to install Informatica article
Once you log in successfully, you will enter into the Informatica Admin Console. Here, you can see the Domain name and licence key that we used for the installation
In order to create Informatica Repository Service, Right Click on the domain name -> New -> Power Center Repository Service.. as shown in the below screenshot.
Once you select the Power Center Repository Service.. option following window will be opened to create New Power Center Repository Service. Here we have multiple options such as:
Once you fill the details, Please click on the Next button to go to Step 2
Within step 2 we have to specify the database properties:
Next, Please check to mark the version control option as shown below. This will enable us to change the Operating Mode from Exclusive to Normal mode
Next, Click on the Edit button beside the repository properties to edit or change the repository default properties.
By default Repository operating mode will be in exclusive. We have to change the Operating Mode from Exclusive to Normal mode.
NOTE: If you forgot to check to mark the version control option in the above step then, it will be impossible to change the operating mode.
Click OK button to change the Operating mode and then enable the Informatica Repository Service by clicking the below-shown button
In order to create Informatica Integration Service, Right Click on the domain name -> New -> Power Center Integration Service.. as shown in the below screenshot.
Once you select the Power Center integration Service.. option following window will be opened to create New Power Center Integration Service. Here we have multiple options such as:
Once you fill the details, Please click on the Next button to go to Step 2
Within step 2 we have to specify the credentials:
Once you fill the required details, Please click on the finish button to finish creating the New Power Center Integration Service
Once you finish creating the New Power Center Integration Service, you have to Enable the service by clicking the below-shown button
Well, we successfully configured the Informatica repository Service and Informatica Integration Service inside our Informatica Admin Console.
The Informatica Repository Manager is mainly used for two reasons:
In this article, we will show you how to Create New Domains, How to connect with Informatica Repository Services and Steps involved in creating new folders.
In order to open the Informatica Repository Manager, Please Go to the Start menu -> All Programs -> Informatica 9.6.1 -> PowerCenter Repository Manager. Once you click on that, following window will be opened
Within the Menu, Please navigate to Repository and then select Configure Domains.. option as shown below.
Once you select Configure Domains.. option, a new window opens up to configure the domains. If you closely observe the below screenshot, there are no Domains in our domains section because we haven’t added any. In order to add a new domain, Please click on the Add a new Domain button as shown below.
Once you click on the Add a new Domain button, a new window will be opened to add Domain. Here, we have to specify the Domain name, Hostname and the port number. If you don’t know your details then, Please refer Informatica Server installation Step 6 inside our How to install Informatica article
Please select the PowerCenter Repository Service and Click OK as shown below.
Once we finish the Domain configuration, Please select and right Click the newly added PowerCenter Repository Service (INF_Repository) and click on the Connectoption
In order to connect with the PowerCenter Repository service, we have to provide the Admin Console credentials. So, Please provide the Username and Password.
TIP: Here you have to provide the Admin Username and password that you specified while installing the Informatica Server.
Once you connected successfully, Let us create one folder to perform Extraction Load and transformations. In order to create a new folder, Please navigate to Folder Menu and select the Create.. option. Selecting that option open the following window to create a new folder. Here, you can leave all the properties to default except name and Description.
Clicking OK button opens a pop-up window. This displays the message saying that The folder has been successfully created as shown below
The Informatica PowerCenter Designer will help us to create or edit the PowerCenter objects such as: Source(s), Target(s), Transformation(s) and mapping(s). In this article we will explain you each and every component present in the PowerCenter Designer.
In order to open the Informatica PowerCenter Designer, Please Go to the Start menu -> All Programs -> Informatica 9.6.1 -> PowerCenter Designer. Once you click on that, following window will be opened.
From the below screenshot you can observe that We divided the Informatica PowerCenter Designer screen into five sections to explain each section in details. Let us see them individually,
Before we start designing we have to connect with the Repository Service so, Please select and Right Click the PowerCenter Repository Service (INF_Repository) under the repository section and select the Connect option
In order to connect with the PowerCenter Repository service we have to provide the Informatica Admin Console credentials. So, Please provide the Username and Password.
TIP: Here you have to provide the Admin Username and password that you specified while installing the Informatica Server.
Once you connected successfully, the following window will be displayed. If you observe the below screenshot, you can see the Subfolders inside our tutorials folder.
The following screenshot will show the list of available tools inside the Informatica PowerCenter Designer. These tools will help us to work with Source, Target and ETL operations.
Instead of using the Tools Menu, We can use the following Images to navigate between the Source, Target and mapping. Each image belongs to one component and they are:
Th
In order to perform Extraction, Transformation and Load (ETL operations) we require Source Data, Target tables and Transformations (if required). Informatica Source Analyzer allows us to create our own source definitions, Modify already existing source definitions and also allows us to Import source data from various sources includes Flat Files, Relational Databases, XML definitions, Excel Worksheets etc
In order to connect with the Informatica Repository service, we have to provide the Informatica Admin Console credentials. So, Please provide the appropriate Username and Password and click on Connect button as shown below.
TIP: Here you have to provide the Admin Username and password that you specified while installing the Informatica Server.
Once you connected successfully, Please navigate to Tools menu in Menu Bar. Following screenshot will show the list of available tools inside the Informatica PowerCenter Designer. In this article we are explaining about the informatica Source Analyzer so please select the Source Analyzer option.
Once you select the Source Analyzer option, we will be in the Source Analyzer screen.
TIP: Source Menu in the Menu bar will be available only when you are in the Source Analyzer section otherwise, it will be hidden.
Next, Please navigate to Sources menu in Menu Bar. From the following screenshot, you can observe the list of available sources inside the Informatica PowerCenter Designer. They are
The Informatica Workflow Manager is used to create a Workflow. A workflow is nothing but a set of instructions to execute the Mappings that we designed in the PowerCenter Designer. Generally, a workflow contains a Session Task, Command Task, Event Wait Task, Email Task etc. It also helps you to schedule the Mappings.
Informatica PowerCenter Workflow Manager has an import option called Worklet designer, Which is used to combine (group) one or more task to make a Powerful task.
In order to open the Informatica Workflow Manager, Please Go to the Start menu -> All Programs -> Informatica -> PowerCenter Workflow Manager. Once you click on that, following window will be opened.
Before we start designing we have to connect with the Repository Service so, Please select and Right Click the PowerCenter Repository Service (INF_Repository) under the repository section and select the Connect option
In order to connect with the PowerCenter Repository service we have to provide the Informatica Admin Console credentials so, Please provide the Username and Password.
TIP: Here you have to provide the Admin Username and password that you specified while installing the Informatica Server.
Once you connected successfully, the following window is displayed. If you observe the below screenshot, you can see the folders. In order to see the existing Tasks, Worklets, and Workflows or to create them please right-click on the folder and select Open from Context menu.
Clicking the Open will show the following window. The following screenshot will show the list of available tools inside the Informatica Workflow Manager.
Expand the Workflows folder to see the existing Workflows
The Informatica Workflow Monitor is used to monitor the execution of Workflows or Tasks assigned in the Workflow. Generally, Informatica PowerCenter helps you to track the Event Log information, list of executed Workflows, and their execution time in detail.
To open the Informatica Workflow Monitor, Please Go to the Start menu -> All Programs -> Informatica -> PowerCenter Workflow Monitor. Once you click on that, the following window opened.
From the below screenshot, you can observe that We divided the Informatica PowerCenter Workflow Monitor screen into four sections to explain each section in detail. Let us see them individually,
Before we start anything, we have to connect with the Repository Service. So, Please select and Right-click the PowerCenter Repository Service (INF_Repository) under the repository section and select the Connect option
To connect with the Informatica Workflow Monitor, provide the Admin Console credentials. So, Please provide the Username and Password.
TIP: Here, you have to provide the Admin Username and password that you specified while installing the Informatica Server.
Once you connected successfully, the following window will display. If you observe the below screenshot, you can see the Integration Service. To see the existing Workflows, right-click on the Integration Service and select Open from Context menu.
Now you can see the folders in Informatica Workflow Monitor. To see the existing Workflows or to run them, right-click on the folder and select Open from menu.
Expand the individual Workflows to see the previous execution details, i.e., Workflow Run start Time, Completion Time, and the Status of the run.
Informatica Workflow Monitor provides a Gantt Chart to see the execution in detail. To view, right-click on Workflow and select the Go to Gantt Chart from the context menu.
Now you can see the Informatica Workflow run in Gantt Chart
By simply selecting the Workflow you can see the information in the properties window
Let me remove the Output window to explore the Properties window information. Next, we are running the Event Wait Task workflow to show the Workflow run. From the below screenshot you can observe, Under the Task details tab you can see all the information about the task
Under the Source or Target Statistics, you can see the Row flow. I mean, the number of rows passed, failed, etc.
Under the partition tab, you can see the partition information (if any). And use the Performance tab to check the performance of any task
To see the details Event log of any task, right-click on the Task. Next, select the Get Session Log from the context menu.
You can see the detailed event log. Generally, we used this Session log to check the error position. I mean, at what position error throws and the reason for it.
The Informatica Workflow Manager is used to create a Workflow. A workflow is nothing but a set of instructions to execute the Mappings that we designed in the PowerCenter Designer. Generally, a workflow contains a Session Task, Command Task, Event Wait Task, Email Task etc. It also helps you to schedule the Mappings.
Informatica PowerCenter Workflow Manager has an import option called Worklet designer, Which is used to combine (group) one or more task to make a Powerful task.
In order to open the Informatica Workflow Manager, Please Go to the Start menu -> All Programs -> Informatica -> PowerCenter Workflow Manager. Once you click on that, following window will be opened.
Before we start designing we have to connect with the Repository Service so, Please select and Right Click the PowerCenter Repository Service (INF_Repository) under the repository section and select the Connect option
In order to connect with the PowerCenter Repository service we have to provide the Informatica Admin Console credentials so, Please provide the Username and Password.
TIP: Here you have to provide the Admin Username and password that you specified while installing the Informatica Server.
Once you connected successfully, the following window is displayed. If you observe the below screenshot, you can see the folders. In order to see the existing Tasks, Worklets, and Workflows or to create them please right-click on the folder and select Open from Context menu.
Clicking the Open will show the following window. The following screenshot will show the list of available tools inside the Informatica Workflow Manager.
Expand the Workflows folder to see the existing Workflows
In order to work with any of the workflow, Sessions, Tasks, Worklets, right-click on it and select the Open from the context menu.
The Informatica Workflow Monitor is used to monitor the execution of Workflows or task assigned in the Workflow. Generally, Informatica PowerCenter helps you to track the Event Log information, list of executed Workflows, and their execution time in detail.
In order to open the Informatica Workflow Monitor, Please Go to the Start menu -> All Programs -> Informatica -> PowerCenter Workflow Monitor. Once you click on that, following window will be opened.
From the below screenshot you can observe that We divided the Informatica PowerCenter Workflow Monitor screen into four sections to explain each section in details. Let us see them individually,
Before we start anything we have to connect with the Repository Service. So, Please select and Right Click the PowerCenter Repository Service (INF_Repository) under the repository section and select the Connect option
In order to connect with the PowerCenter Repository service, we have to provide the Informatica Admin Console credentials. So, Please provide the Username and Password.
TIP: Here you have to provide the Admin Username and password that you specified while installing the Informatica Server.
Once you connected successfully, the following window will be displayed. If you observe the below screenshot, you can see the Integration Service. In order to see the existing Workflows, please right-click on the Integration Service and select Open from Context menu.
Now you can see the folders. In order to see the existing Workflows or to run them please right-click on the folder and select Open from Context menu.
Expand the individual Workflows to see the previous execution details i.e., Workflow Run start Time, Completion Time, and the Status of the run.
Informatica Workflow Monitor provides Gantt Chart to see the execution in detail. In order to see, right-click on Workflow and select the Go to Gantt Chart from the context menu.
Now you can see the Workflow run in Gantt Chart
By simply selecting the Workflow you can see the information in the properties window
Let me remove the Output window to explore the Properties window information. Next, we are running the Event Wait Task workflow to show the Workflow run. From the below screenshot you can observe, Under the Task details tab you can see the all the information about the task
Under the Source or Target Statistics, you can see the Rows flow. I mean, the number of rows passed, failed etc.
Under the partition tab, you can see the partition information (if any). And use the Performance tab to check the performance of any task
In order to see the details Event log of any task, right-click on the Task and select the Get Session Log from the context menu.
You can see the detailed Event log. Generally, we used this Session log to check the error position. I mean, at what position error was thrown and the reason for it.
