Course Code: informaticaetl
Duration: 21 hours
Prerequisites:
  • Basic knowledge of SQL is preferred

Audience

  • Business Intelligence, ETL, SQL and Database Professionals
  • Project Managers and graduates looking to make a career in BI
Course Outline:

Introduction

  • Introduction to data warehousing
  • What is ETL and overview of data cleansing, data
  • Scrubbing, Data aggregation
  • Learn what is Informatica PowerCenter ETL

 

Informatica Installation and Configuration

  • Overview of Informatica Configuration
  • Integration Services
  • Installation of Informatica (PowerCenter)
  • Operational Administration Activities

 

Transformation

  • Active and Passive Transformation
  • Expression Transformation
  • Passive Transformation and Calculating Value on a Single Row
  • Sorter, Sequence Generator, Filter transformation
  • Transform data using Filter technique
  • Use a Sequence Generator, Use a Sorter
  • Use Joiner Transformation to bring data from heterogeneous data sources
  • Understand and Perform Ranking and Union transformation
  • Understand and Perform rank and dense rank functions
  • Understand and Perform router transformation
  • Perform Lookup Transformation: Connected, Unconnected, Dynamic and Static

 

Changing Dimension in Informatica, Mapplet and performance tunning

  • What is SCD
  • Processing in XML
  • How to handle flat file, list and define various transformations
  • Implementing ‘for loop’ in power center
  • Concepts of pushdown optimization and partitioning
  • Constraint-based loading and incremental aggregation
  • Load data from a flat file, implement ‘for loop’ in power center, Use pushdown optimization and partitioning, Do constraint based data loading, Use incremental aggregation technique to aggregate data
  • Different types of the designer: Mapplet, Worklet, target load plan, loading to multiple targets, linking property
  • Create a mapplet and a worklet
  • Plan a target load, Load multiple targets
  • Objectives of performance tuning
  • Defining performance tuning
  • Learning the sequence for tuning
  • Manage tasks in repository manager and best practices

 

Workflow Informatica and Error handling

  • Common tasks in workflow manager
  • Creating dependencies
  • The scope of workflow monitor
  • Define variables and parameter in functions
  • Use parameter of mapping,
  • Use work and session parameter,
  • Use workflow and service variable
  • Session and workflow log, using debuggers
  • Error handling framework in Informatica
  • Failover and high availability

 

High Availability & Failover in Informatica, utilities and Java transformations

  • Configure recovery options, Check the health of PowerCenter environment
  • Using commands infacmd, pmrep, infasetup
  • Flat file processing (advance transformations)
  • Perform expression transformations- sequence numbers, dynamic targeting using transaction control
  • Dynamic target with the use of transaction control, indirect loading
  • Java transformations to extend Power Center capabilities, transforming data, active and passive mode
  • Use unconnected stored procedure in Informatica in different scenarios

 

Advanced Concepts in SCD, data profiling

  • Use of SQL transformation (active and passive)
  • Incremental Loading and aggregation
  • Working with database constraints using PowerCenter
  • Understanding constraint-based loading and target load order
  • Perform constraint-based loading in a given order
  • The various types of XML transformation in Informatica
  • Configuring a lookup as active
  • What is data profiling in Informatica
  • It's significance in validating content
  • Ensuring quality and structure of data as per business requirement

 

Database Connection

  • Create a new database connection in Informatica
  • Incremental Loading and aggregation
  • Relational database tables in Informatica
  • Mapping for loading data from flat files to relational database files
  • Deploy PowerCenter for seamless LinkedIn connectivity with Informatica PowerCenter
  • Connect Informatica PowerCenter with various data sources like Social media channels like Facebook, Twitter, etc.

 

Pushdown Optimization and Partitioning and cache management

  • Optimize using Push down technique for load-balancing on the server for better performance
  • Create various types of partitioning for optimizing performance
  • Understanding session cache
  • Importance of cache creation with
  • Implementing session cache, calculating cache requirement

 

Informatica Data Quality

  • Data Quality Management Process Cycle
  • Dimensions of Data Quality
  • Data Quality Processes
  • Developer and Analyst Roles and Tools
  • Data Quality Architecture
  • Developer Interface
  • Understanding Analyst projects, Data Objects, Profiles, Rules, Scorecards, Comments and Tags
  • Reference Tables and the Data Quality Process
  • Creating Reference Tables 
  • Tasks in the Developer Tool
  • Working with Physical and Logical Data Objects
  • Connecting to a table
  • Importing and flat file
  • Creating logical data objects

Informatica Data Quality

  • Developer Transformations
  • Mappings and mapplets
  • Content sets and their uses
  • Column Profiling
  • Mapplets and Scorecards
  • Profiling techniques to debug and improve development
  • Updating Scorecards with Rules
  • Standardizing, cleansing and enhancing data.
  • Mappings that cleanse, standardize and enhance data
  • Developing standardization mapplets
  • Configuring standardization transformations

Informatica Data Quality

  • The Parsing Process
  • Parsing techniques
  • Key parsing transformations
  • Match Data definition
  • The DQ matching process
  • The different stages of Matching
  • Grouping and its effect on matching
  • Grouping methods
  • Grouping results and refining a grouping strategy
  • Match algorithms
  • Workflows and Workflow Tasks
  • Human Tasks and Steps
  • Identifying exception and duplicate records
  • Deploying and executing workflows
  • Verifying Tasks in Informatica Analyst
  • Deployment options.
  • Mappings as applications
  • Scheduling mappings, profiles and Scorecards

Enterprise Data Catalog

  • Major Business Challenges
  • Scale of Challenges
  • EDC as a Solution
  • EDC Metadata Lifecycle
  • EDC Architecture
  • EDC Key Features

Informatica Axon

  • Axon's Data Governance Methodology
  • Introduction to Axon
  •  Axon Users and Dashboards
  • The Glossary
  • Change Requests
  • Systems, Datasets, Interfaces & Lineage             
  • Insight and Local Maps
  • Axons Data Governance Methodology 
  • The Glossary
  • Systems, Datasets, Interfaces & Lineage             
  • Leveraging Enterprise Data Catalog Content in Axon       
  • Policies           
  • Leveraging Secure@Source Dashboards in Axon               
  • Process          
  • Workflows and Change Requests          
  • Mandatory Approval Process  
  • Data Quality