4.27 out of 5
4.27
3244 reviews on Udemy

Informatica Tutorial: Beginner to Expert Level

Become an expert in world's top Data Integration tool.
Instructor:
Sid Inf
12,818 students enrolled
English [Auto-generated] More
Understand Data Warehouse Concepts and ETL Concepts
Describe Informatica PowerCenter architecture & its different components
Use PowerCenter 10x/9.x components to build Mappings, Tasks, Workflows
Describe the basic and advanced features functionalities of PowerCenter 10.0/9.6 transformations
Understand Workflow Task and job handling
Describe Mapping Parameter and Variables
Perform debugging, troubleshooting, error handling and recovery
Learn different types of cache available and how to calculate cache requirement and implement session cache
Execute performance tuning and Optimization
Identify and explain the functionalities of the Repository Manager tool
Identify how to handle services in the Administration Console
Understand techniques of SCD, XML Processing, Partitioning, Constraint based loading and Incremental Aggregation
Gain insight on ETL best practices using Informatica
Understand all the basic interview questions for all the transformations and real time scenarios

Informatica Power Center Data Integration tool is the top in the Gartner’s magic quadrant for the past ten years with high GO LIVE rate compared to any other existing ETL tools in the market.

Informatica Power Center tool supports all the steps of Extraction, Transformation and Load process/life cycle. There are lot of other (third party) products which are offered and created around the Power Center’s ability to connect to different technologies ranging from Legacy systems, Mainframes to CRM, SAP, TIBCO, HP VERTICA to BIG DATA.

Informatica Power Center Developer course, will introduce you to work with the Power Center version 10.x/9.6x to create, execute, as well as administer, monitor and schedule ETL processes and understand how these are used in data mining operations and enterprise data warehouse setup.

Informatica Power Center is an easy to use GUI based tool. It has a simple visual interface which is easy to understand and use. All the components are designed to be used by a simple drag and drop feature for different objects like source, targets, transformations, mapplets, mappings, sessions, worklets and workflows which contribute to the design process flow of the data extraction, transformation and load.

Once the objects are integrated into a package called as workflow, it can be scheduled to run as and when required with rich features to accommodate all the possibilities of a business requirement. 

The architecture of Informatica 10x/9x is created based on the SOA (Service Oriented Architecture) which takes care of the data fetch, execution of the transformation and load the data into the target systems in the desired formats like Relational, Flat File, XML, WSDL, PDF, Word (Structured, Semi-Structured, Un-Structured).

Informatica Power Center 10x/9x has the ability to communicate via ODBC sources/plugins/extensions with all major data sources like Mainframe, Big Data, traditional RDBMS (Oracle, SQL Server, Teradata, Netezza, Informix, DB2 etc), NoSQL (Mongo DB, Cassandra etc) Flat Files, XML, TIBCO, WSDL (Via web services) SAP, Salesforce etc) and move/transform data between them based on the need.

The tool’s ability to fetch/transform and load huge volumes of data in a very effective way, with less resource consumption is better than hand coded programs written for specific data movement using PL/SQL procedures, Java, Perl and other languages.

The course covers all topics starting from Data warehouse concepts, Roles and Responsibilities of an ETL developer, Installation & Configuration of Informatica Power Center 10x/9.X, in detailed explanation of transformations with practical examples, performance tuning tips for each transformation (clearly shown and explained), usually asked interview questions, quizzes for each section and assignments for your hands on and in-depth explanation of the Repository Service, Integration Service and other basic Administration Activities. 

Some basic information about the course before you start

1
Welcome and Thank you!!

Thank you and welcome to this course. 

In this lecture, I have tried to put in a brief perspective of what you are going to get into and what you will get out of this course. 

Data Warehouse Concepts : Back to Basics

1
Why do we need a Data Warehouse?

The concept of data warehousing is not hard to understand. The notion is to create a permanent storage space for the data needed to support reporting, analysis, and other BI functions. In this lecture we understand what are the main reasons behind creating a data warehouse and the benefits of it. 

This long list of benefits is what makes data warehousing an essential management tool for businesses that have reached a certain level of complexity.

2
What is a Data Warehouse?

A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. It usually contains historical data derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from several sources.

In addition to a relational database, a data warehouse environment includes an extraction, transportation, transformation, and loading (ETL) solution, an online analytical processing (OLAP) engine, client analysis tools, and other applications that manage the process of gathering data and delivering it to business users.

3
What is Business Intelligence?

Business intelligence (BI) is a technology-driven process for analyzing data and presenting actionable information to help corporate executives, business managers and other end users make more informed business decisions.

4
What is Business Intelligence? - Extended

Business intelligence (BI) is the use of computing technologies for the identification, discovery and analysis of business data - like sales revenue, products, costs and incomes.

BI technologies provide current, historical and predictive views of internally structured data for products and departments by establishing more effective decision-making and strategic operational insights through functions like online analytical processing (OLAP), reporting, predictive analytics, data/text mining, bench marking and Business Performance Management (BPM). These technologies and functions are often referred to as information management.

5
The Complete Master Data Warehouse Concepts Course

Data Warehouse Concepts play a critical role in all the Data Warehouse and ETL projects. This course is equipped with the content which is required for you to start. 

But, if you want in-depth knowledge on the foundations of the Data Warehouse Concepts, you can enroll to the course as mentioned in the lecture. 

6
Test your understanding on Data Warehouse & Business Intelligence

Let's answer few questions about the basic questions of Data Warehouse and Business Intelligence. 

Data Warehouse Architectures

1
Enterprise Architecture or Centralized Architecture

In this lecture we see how the Centralized architecture is set up, in which there exists only one data warehouse which stores all data necessary for the business analysis

2
Federated Architecture

In a Federated Architecture the data is logically consolidated but stored in separate physical database, at the same or at different physical sites. The local data marts store only the relevant information for a department. 

The amount of data is reduced in contrast to a central data warehouse. The level of detail is enhanced in this kind of model. 

3
Multi Tired Data Warehouse Architecture

Multi Tired architecture is a distributed data approach. This process cannot be done in a one step because many sources have to be integrated into a warehouse.

4
Components of a Data Warehouse Architecture

Different data warehousing systems have different structures. Some may have an ODS (operational data store), while some may have multiple data marts. Some may have a small number of data sources, while some may have dozens of data sources. In view of this, it is far more reasonable to present the different layers of a data warehouse architecture rather than discussing the specifics of any one system.

In general, all data warehouse systems have the following layers:

  • Data Source Layer
  • Data Extraction Layer
  • Staging Area
  • ETL Layer
  • Data Storage Layer
  • Data Logic Layer
  • Data Presentation Layer
  • Metadata Layer
  • System Operations Layer
5
Purpose of Staging Area in a Data Warehouse Architecture

This is where data is stored prior to being scrubbed and transformed into a data warehouse / data mart. Having one common area makes it easier for subsequent data processing / integration. Based on the business architecture and design there can be more than one staging area which can be termed with different naming conventions. 

6
Purpose of Staging Area in a Data Warehouse Architecture - part 2

This is where data is stored prior to being scrubbed and transformed into a data warehouse / data mart. Having one common area makes it easier for subsequent data processing / integration. Based on the business architecture and design there can be more than one staging area which can be termed with different naming conventions. 

7
Test your understanding on Data Warehouse Architectures

Let's review your understanding on the Data Warehouse Architectures

ODS VS OLTP Vs OLAP

1
What is ODS?

An ODS is designed for relatively simple queries on small amounts of data (such as finding the status of a customer order), rather than the complex queries on large amounts of data typical of the data warehouse. 

An ODS is similar to your short term memory in that it stores only very recent information; in comparison, the data warehouse is more like long term memory in that it stores relatively permanent information.

2
Features and Benefits of ODS

To understand the purpose of the ODS and when it is an appropriate solution, its characteristics must first be defined.

Characteristics of an Operational Data Store

Subject Oriented : The ODS contains specific data that is unique to a set of business functions. The data therefore represents a specific subject area.

Integrated : Data in the ODS is sourced from various legacy applications. The source data is taken through a set of ETL operations that includes cleansing and trans-formative processes. These processes are based on rules that        have been created through business requirements for data quality and standardization.

Current (non-historical) : The data in the ODS is up-to-date and is a current status of data from the sourcing applications.

Detail : Data in the ODS is primarily used to support operational business functions. This means that there is a      specific level of granularity based on business requirements that dictate the level of detail that data in the  ODS will have.

3
OLAP Overview

OLAP (Online Analytical Processing) is the technology behind many Business Intelligence (BI) applications. OLAP is a powerful technology for data discovery, including capabilities for limitless report viewing, complex analytical calculations, and predictive “what if” scenario (budget, forecast) planning.

4
OLTP Vs OLAP -Part 1

OLTP (On-line Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. In OLTP database there is detailed and current data, and schema used to store transnational databases is the entity model (usually 3NF). 

OLAP (On-line Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema).

5
OLTP Vs OLAP - Part 2

OLTP (On-line Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. In OLTP database there is detailed and current data, and schema used to store transnational databases is the entity model (usually 3NF). 

OLAP (On-line Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema).

6
Differences between ODS,DWH, OLTP, OLAP, DSS and Data Mart

Please refer to the additional resources of this section which contains the Info-graphic on the differences between the ODS, DWH, OLTP, OLAP, DSS and DM (Data Mart).

7
Test your understanding ODS, OLAP, OLTP, Data Warehouse

Test your understanding ODS, OLAP, OLTP, Data Warehouse

Data Mart

1
What is a Data Mart?

The data mart is a subset of the data warehouse that is usually oriented to a specific business line or team. Data marts are small slices of the data warehouse. Whereas data warehouses have an enterprise-wide depth, the information in data marts pertains to a single department.

2
Fundamental Difference between DWH and DM

Data Warehouse:

  • Holds multiple subject areas
  • Holds very detailed information
  • Works to integrate all data sources
  • Does not necessarily use a dimensional model but feeds dimensional models.


Data Mart:

  • Often holds only one subject area- for example, Finance, or Sales
  • May hold more summarized data (although many hold full detail)
  • Concentrates on integrating information from a given subject area or set of source systems
  • Is built focused on a dimensional model using a star schema.
3
Test your understanding on Data Marts

Test your understanding on Data Marts

Dimensional Modeling

1
What is Dimensional Modeling?

Dimensional Model is a database structure that is optimized for online queries and Data Warehousing tools. It is comprised of "fact" and "dimension" tables. A "fact" is a numeric value that a business wishes to count or sum. A "dimension" is essentially an entry point for getting at the facts.

2
What is a Dimension?

dimension is a structure that categorizes facts and measures in order to enable users to answer business questions. Commonly used dimensions are people, products, place and time. In a data warehousedimensions provide structured labeling information to otherwise un-ordered numeric measures.

3
What is a Fact?

In data warehousing, a fact table consists of the measurements, metrics or facts of a business process. It is often located at the center of a star schema, surrounded by dimension tables.

There are four types of facts. 

  • Additive - Measures that can be added across all dimensions.
  • Non Additive - Measures that cannot be added across all dimensions.
  • Semi Additive – Measures that can be added across few dimensions and not with others.
  • Fact less fact tables – The fact table does not have aggregate numeric values or information.
4
What is a Surrogate key?

surrogate key is any column or set of columns that can be declared as the primary key instead of a "real" or natural key. Sometimes there can be several natural keys that could be declared as the primary key, and these are all called candidate keys. So a surrogate is a candidate key.

5
Star Schema

A star schema is the simplest form of a dimensional model, in which data is organized into facts and dimensions

6
Snow flake Schema

 The snowflake schema is diagrammed with each fact surrounded by its associated dimensions (as in a star schema), and those dimensions are further related to other dimensions, branching out into a snowflake pattern.

7
SnowFlake Vs Star Schema

When choosing a database schema for a data warehouse, snowflake and star schema tend to be popular choices. This comparison discusses suitability of star vs. snowflake schema in different scenarios and their characteristics.

8
Conformed Dimension

conformed dimension is a dimension that has exactly the same meaning and content when being referred from different fact tables. A conformed dimension can refer to multiple tables in multiple data marts within the same organization.

9
Junk Dimension

 In a Junk dimension, we combine these indicator fields into a single dimension. This way, we'll only need to build a single dimension table, and the number of fields in the fact table, as well as the size of the fact table, can be decreased.

10
Degenerate Dimension

According to Ralph Kimball, in a data warehouse, a degenerate dimension is a dimension key in the fact table that does not have its own dimension table, because all the interesting attributes have been placed in analytic dimensions. The term "degenerate dimension" was originated by Ralph Kimball. 

11
Slowly Changing Dimensions - Intro and Example

We start with the basic definition of a Dimension, Fact and start with the Slowly Changing Dimensions.

12
Slowly Changing Dimensions - Type 1, 2 and 3

There are many approaches how to deal with SCD. The most popular are: 

  • Type 0 - The passive method
  • Type 1 - Overwriting the old value
  • Type 2 - Creating a new additional record
  • Type 3 - Adding a new column
  • Type 4 - Using historical table
  • Type 6 - Combine approaches of types 1,2,3 (1+2+3=6)
13
Slowly Changing Dimensions - Summary

Dimension, Fact and SCD Type 1, 2 and 3 are reviewed in this lecture. 

14
Test your understanding on Dimensional Modeling

Test your understanding on Dimensional Modeling

Data Warehouse Indexes

1
What is an Index?

Indexing the data warehouse can reduce the amount of time it takes to see query results. When indexing dimensions, you'll want to index on the dimension key. When indexing the fact table, you'll want to index on the date key or the combined data plus time.

2
Bit-Map Index

bitmap index is a special kind of database index that uses bitmaps.Bitmap indexes have traditionally been considered to work well for low-cardinality columns, which have a modest number of distinct values, either absolutely, or relative to the number of records that contain the data.

3
BTree Index

B-tree is a self-balancing tree data structure that keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic time.

4
BTree Vs BitMap

One of the common questions which come up in the interviews is which one is the better one to use, Is it Bitmap or B Tree?

In this lecture, we try to evaluate the differences and the best one to use.

5
Test your understanding on Data Warehouse Indexes

Test your understanding on Data Warehouse Indexes

ETL Vs ELT

1
What is Data Integration?

Data integration is the combination of technical and business processes used to combine data from disparate sources into meaningful and valuable information. A complete data integration solution delivers trusted data from a variety of sources.

2
ETL - Explained

ETL (Extract, Transform and Load) is a process in data warehousing responsible for pulling data out of the source systems and placing it into a data warehouse.

3
ELT - Explained

ELT is a variation of the Extract, Transform, Load (ETL), a data integration process in which transformation takes place on an intermediate server before it is loaded into the target. In contrast, ELT allows raw data to be loaded directly into the target and transformed there.

4
ETL Vs ELT

In ETL (extract, transform, load) operations, data are extracted from different sources, transformed separately, and loaded to a Data Warehouse database.

 In ELT, the extracts are fed into the single staging database that also handles the transformations.

5
Commonly used terms in an ETL project - Part 1

Though, its not limited to the below, here are some of the commonly used terms in any ETL project.

•Source Systems
•Mapping
•Metadata
•Staging Area
•Data Cleansing/Scrubbing
•MDM -  Golden Source
•Transformation
•Target Systems
•Reporting/BI
•Scheduling

6
Commonly used terms in an ETL project - Part 2

Though, its not limited to the below, here are some of the commonly used terms in any ETL project.


•Source Systems
•Mapping
•Metadata
•Staging Area
•Data Cleansing/Scrubbing
•MDM -  Golden Source
•Transformation
•Target Systems
•Reporting/BI
•Scheduling

7
MDM - Golden Source explained

This lecture is in response to the question below:

Could you please elaborate MDM - Golden Source. What does MDM store? Does MDM store dimension data and the data warehouse store fact data where MDM is implemented?

8
Test your understanding on ETL Vs ELT

Test your understanding on ETL Vs ELT

Different Tools/Technologies used in DWH/BI/ETL

1
Different Types of Enterprise Databases

In this lecture we talk about the different Enterprises Databases which can be used as a Data Warehouse. 

Please note, NoSQL databases are not discussed in this lecture.

2
Different ETL tools available in the Market

In this lecture we talk about the different popular ETL tools available in the market. 

3
Which ETL tool should you learn?

Based on the Gartner's magic quadrant we see which ETL tool is the leader in the ETL technologies and what is the best choice for you to learn. 

4
Test your understanding on different types of ETL Tools

Test your understanding on different types of ETL Tools

Roles and Responsibilities of ETL Developer

1
Roles and Responsibilities of an ETL Developer - Part 1

The daily activities and the roles and responsibilities of an ETL developer are mentioned. These are covered considering the involvement of the ETL developer at various phases of the Data Warehouse implementation life cycle. 

2
Roles and Responsibilities of an ETL Developer - Part 2

This is in continuation of the previous lecture (Part 2) and we talk about the different responsibilities of an ETL developer. 

3
Roles and Responsibilities of an ETL Developer - Part 3

This is in continuation of the previous lecture (Part 3) and we talk about the different responsibilities of an ETL developer. 

4
Test your understanding on Roles and Responsibilities of an ETL developer

Test your understanding on Roles and Responsibilities of an ETL developer

Informatica Power Center Architecture

1
Informatica Domain Architecture

Informatica Domain is the fundamental administrative unit in Informatica tool. In this lecture we talk about the overall architecture and how the domain is lined with the rest of the components in the architecture. 

2
Informatica Nodes/Core Services/Gateway Nodes

A node is a logical representation of a machine or a blade. Each node runs a Service Manager that performs domain operations on that node.

3
Types of Nodes, Common Issues and Troubleshooting

Different types of nodes are discussed in this lecture. 

  • Master Gateway Node
  • Primary Node
  • Backup node
  • Worker Node
4
Test your understanding on Informatica Power Center Architecture

Test your understanding on Informatica Power Center Architecture

Informatica Version 9.6 : Installation and Configuration

1
Pre Installation Steps & System Pre-Requisites

What do you need to get started is described here both for your personal PC and the how should it be done at work.

2
Quick Tip: Product Availability Matrix - PAM

PAM - Product Availability Matrix is the right place to start for all pre installations checks on what version is compatible for which version of Informatica.

3
Where to get the software and download it? Officially and Legally

This session shows the way to download the free software from Oracle eDelivery website for Informatica 9.6 and Oracle 11g or 12c.

4
Too many files!! Which one do I need to downlaod?

There are about 16 different files and 3 different versions if Informatica Adapters to download. This lecture shows which files to download.

5
Oracle 11g Installation and SQL Developer Configuration

Oracle 11g Installation and SQL Developer Configuration

6
File Extraction for Installation from Oracle eDelivery

This session shows how to extract the client and the server executable from the .ZIP and .gz files downloaded from eDelivery website of Oracle. 

7
Informatica PowerCenter 9.6 - Server Installation

Step by Step process on installing the Informatica Server. Informatica Service set up. Explanation of all the options available and the port numbers.

8
Informatica Power Center 9.6 - Client Installation

Step by Step process on completing the Client Installation for Power Center and other available client options for Informatica Data Quality and Transformation Studio.

Informatica Version 9.6: Administration Console

1
Administration Console Introduction

This session provides the overview of the Administration Console page layout and the tabs. Differences between the 8.6 version web page layout and the 9.x version layout. Log Management and the basic differences on what the Monitoring in Administration Console is all about and the Client Informatica Monitor Tool.

2
List of Services

This session explains the list of services available in the Administration Console and the purpose of them. The order in which the services should be created and the dependencies. Common Issues and fixes are also discussed.

3
Setting up Repository Service

Step by Step process on what properties to choose for creating the Repository Service.

4
Repository Service Properties - Part 2

This session explains the default properties when the service is created and what are the options which can be updated and to what value. What will be the implications if the changes are done and what are the scenarios in which it can be changed.

5
Setting up Integration Service

Integration service is created while installing the Power Center .After creation of Integration service we use Administration console to mange the Integration Services.

What is Integration service?

Integration service is used to read workflow information from the Informatica Repository. Integration services create one or more Integration services processes to manage Workflows. When we run a workflow, what the Integration service does is that it will locks the workflow, runs the workflow tasks, and sessions.

6
Integration Service properties

All the properties of Integration Service are covered in this lecture. 

7
Repository Configuration and Folder Creation

Post the services creation, how is the repository configured and how are the folders created is what is discussed in this lecture. 

8
Start and Stopping the services

In this lecture we see how to stop and restart the different services of Information from the Administration Console. 

9
Test your understanding on Informatica Power Center Administration Console

Test your understanding on Informatica Power Center Administration Console

Informatica Power Center Versions

1
Informatica 10 - New Features

New Features of  version 10 at a glance & the overall impact:

  • More features for Business Users on Data Analyst and Metadata management
  • Improved Administrator experience
  • Build in Intelligence to improve performance
  • Better designs with enforced best practices on code development, May reduce development effort drastically for common use cases
  • Code integration with external Software Configuration tools
  • Not much for Architects
  • Hardware upgrade is must for V10
  • Lot of new features are part of Data Integration and Data Governance area.
  • Traditional Users will not be impressed much since there are no new features in traditional products like PC, MDM, B2B

Informatica Version 10 Installation and Configuration

1
Informatica 10 - Server/Client Installation - Part 1

Server/Client installation steps for Informatica version 10. The steps are almost the same but there are new additions with couple of new features. 

2
Informatica 10 - Server/Client Installation - Part 2

Server/Client installation steps for Informatica version 10. The steps are almost the same but there are new additions with couple of new features. 

Informatica 10 - Administrator Console

1
Informatica 10 - Administration Console (New Features)

The new layout and the features are discussed in this lecture. 

You can view and review the lecture materials indefinitely, like an on-demand channel.
Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don't have an internet connection, some instructors also let their students download course lectures. That's up to the instructor though, so make sure you get on their good side!
4.3
4.3 out of 5
3244 Ratings

Detailed Rating

Stars 5
1339
Stars 4
1300
Stars 3
487
Stars 2
74
Stars 1
48
6a10d6dc9a14469151ac46cef9eb612a
30-Day Money-Back Guarantee

Includes

35 hours on-demand video
Full lifetime access
Access on mobile and TV
Certificate of Completion