ETL Informatica Syllabus
Definition of Datawarehouse
Learn Integral Processes of Datawarehousing
Concepts: Subject-Oriented Data, Integrated Data, Non-Volatile Data and Time-Variant Data
History of Datawarehousing
What is use of Datawarehosuing?
Learn Datawarehousing and Business Intelligence
Understanding Datawarehousing Schema in Real Life
Case Studies on Banking, Stock Market and Ecommerce
Facts and it types
Learn Additive, Semi-Additive, and Non-Additive Facts
Cumulative and Snapshot
Datawarehousing Architecture
Types of layer: Data Source Layer, Data Extraction Layer, Data Staging Layer, ETL Layer, Data Storage Layer, Data Logic Layer, Data Presentation Layer, Metadata Layer, System Operation Layer
Dimension table and Fact table
Dimensions types
Slowly Changing Dimensions (SCD) and SCD1, SCD2, and SCD3
Rapidly Changing Dimensions, Confirmed Dimensions and Degenerate Dimensions
Understanding Processing
Online Transactional Processing and Online Analytical Processing
Understanding Difference between OLAP and OLTP
Various Types of OLAP
Multi-Dimensional OLAP, elational OLAP and Hybrid OLAP
Attribute and Hierarchy
Types of Schema and Fact Constellation Schema
Star Schema and Snow Flake Schema
Introduction to Informatica PowerCenter 9.X
Informatica Core Components and Server (Informatica Administrator)
Understanding Services of informatica
Informatica Domain and Node
Learn Licensing and Configuration of Services
Studying Terminology of informatica
Client Tools
Power Center Designer and Working with the Designer
Study of Working with Sources and Targets
Tools GUI and Defining Mappings
Explaining Transformations of mappings
Understanding Sessions of mappings
Defining Workflows and Workflow Manager and Workflow Monitor
Learn how to Generate SQL Dynamically in Informatica?
Understanding of Transformation, Mapplets and Worklets
Informatica Services, Integration Service and Repository Service
Connections and Roles of informatica
Introduction to Informatica Administration
Installation of Virtual Machine in informatica
How to create Services and add Users?
Study of Connecting with Client
Adding and Configuring Demands
To Creating and Sharing Folders in Central Repository
How to create Connections for various types of Sources?
Database
Learn Data Loading and Unloading
Basic Queries and Joins of databases
Working with Semi-Structured Data and Structured Data
Importing Data from E-commerce Domain
Creating and Dropping Indexes and Compiling Stored Procedures
Create Truncate Update and Delete
How to Change Data Structure for Structured Data?
Defination of Extract, Transform, and Load
ETL Architecture
Source Analyzer and Target Developer Flat File and Relational Sources
Importing Relational Sources into Informatica, Flat File Sources in Informatica and data from Salesforce
Source Preview Techniques and Creating Targets in Informatica
How to add Targets of various types in Informatica?
Mapping Designer and Mappings and Workflow Designer and Workflows
Monitoring Workflow and PowerCenter Workflow Manager
To describe Flow within a Workflow
Workflow Manager
Workflow Monitor Components
Workflow Task and Job Handling and Studying PowerCenter Designer
Visualizing PowerCenter Designer and PowerCenterDesigner and Preview Target Data
How to create Mapping in Designer?
Transformations and their Usage
Filter transformation
Filter Cache and Internal Working
Filtering various Types of Data
Multiple Conditions Based Filter
Transformation Propertiesv
Performance Tuning Aspects
Sorter Transformations
Sorting Techniques
Sorting Internal Data
Caching Options: Single and Multisort
Performance Tuning Techniques for a Sorter
Router Transformation
Routing Mechanism
Pipeline Concepts with Grouping Data by Conditions
Usage of Default Group
Source Qualifier
Usage techniques
Why is it served as Default Transformation with every (except COBOL) source?
PreSQL and PostSQL Usage and Use cases
SQL Override
Data Sorting & Data Filtering from SQL
Joiner
Join types available in Informatica
Introduction to Master Table
Introduction to Detail Table
Normal Join
Detail Outer Join
Master Outer Join
Full Outer Join
Use case of joiner and Pipeline limitations of Joiner
Union Transformation
Constraints with Union and Usage
Learn Aggregator
Generic Aggregate Operations: min,max,sum,avg,count
Aggregator Grouping Mechanism
Constraints related to Cache
Limitations of Aggregator: Pipeline and Input Adherence
Working with Sorted Input
Performance Tuning Concepts
Sequence Generator
Sequencing and its Use
Generating various types of Keys
Using Sequence Generator as a Reusable Transformation
Working with Cycle
Reset Options with Change in Parameters
Expression Transformation
Industrial use cases of Expression and its Usage with Generic Expression
String Related Functions and their Usage
Advanced Expressions and their Usage
Learn Calling Unconnected Lookup from Expression Transformation
Study of Calling Unconnected Stored Procedures from Expression What is Transformation?
Lookup and its types
Learning of Persistent and Non-persistent Cache
What is Cache Overflow Problem?
Custom Expressions in Lookup and Dynamic Calls
Lookup SQL Override
Ports and Conditional Operators
Performance Considerations and use cases
Lookup on Flat Files
Update Strategy
Working with Updates: Mapping Level and Session Level
Usage of Keys to Update
Target Update Override and Update Conditions
Study of DD_UPDATE, DD_DELETE, DD_REJECT, DD_INSERT
Rank: Data Cache and Index Cache in Rank
Ranking with Priority (top and bottom)
Understanding Usage of Rank Index and Grouping with Rank
Stored Procedure
Connected and Unconnected Stored Procedure
Change Parameters
SP Load Options: Before/after source/target
Production Usage and Implementation Techniques
Normalizer
Normalizing COBOL Data
Working with Generic Options
Use Cases of Normalizer
Transaction Control
Types of Transactions in Informatica
Significance of Normalized Data in Production Environment
The Transformation Language
Constants and Operators
Variables, Dates, and Functions
Creating Custom Function
Understanding Domains
Nodes and Application Services
How to Use Administration Console?
How to Manage the Domain?
Managing Alerts, Folders, and Permissions
Managing Application Services and Nodes
How to Manage Users and Groups?
Managing Privileges and Roles
Domain Privileges
Repository and Reporting Services Privileges
Managing Roles
Create and Configure Integration Services
Enable and Disable Integration Services
Run in Normal and Safe Mode
Integration Services
Processes and Configuration
Architecture
Reporting Services
Create, Manage, and Configure Reporting Services
Understanding Workflow Manager
Workflow Manager Options
Navigate the Workspace
Defining Repository Objects
Copying and Comparing Repository Objects
Working with Worklets and Links
Workflow Wizard
Tasks: Create and Configure
Command Task and Decision Task
Event Task and Timer Task
Validation and its Significance
Validating Tasks, Worklets, Session, Workflows
Scheduling Workflows
Workflows: Running and Manual Starting
Monitoring Workflow: Customizing Workflow Monitor Options
Gantt Chart View and Task View
Properties: Integration Services
Properties: Workflow and Worklet Run
Session and Workflow Logs
Defining Log Events and Log Files
Understanding Partitioning
What is Pipeline Partitioning?
Attributes of Partitioning
Defining Dynamic Partitioning
Rules of Partitioning
Configure Partitioning
Defining Partitioning Points
Add and Delete Partitioning Points
Partitioning Relational Sources
Partitioning File Targets
Partitioning Transformations
Types of Partitioning
What is Real-Time Processing?
Commit Points
Defining Workflow Recovery
Stopping and Aborting
Error Handling
Learn Stopping and Aborting Workflows
Concurrent Workflows
The Concept of Load Balancer
Understanding Variables
Workflow Variables
Predefined Workflow Variables
User- Defined Workflow Variables
Using Worklet Variables
Studying Parameters and Variables in Sessions
Working with Session Parameters
Study of Assigning Parameter and Variables in a Session
Parameter File and Session Caches
Incremental Aggregation and Session Log Interface
Study Performance Tuning and Optimization
The Concept of Performance Tuning
Understanding Bottlenecks
Target and Source Bottlenecks
Mapping and Session Bottlenecks
Optimizing the Targets and Source
Optimizing Mapping and Transformations
Usage of Pipeline Partitions and Performance Counters
Working with Multiple Pipelines
Protocols for Creating Multiple Pipelines
Understanding the Data Flow
Target Load Order
Understanding Granularity of Data with respect to Load Order
Changing the Default Load Order to get Expected Data
Constraint-Based Load Ordering
Understanding Constraints and their Suitability to the given Load Order
Use cases where Constraint Based Loading can save time on Evaluation
Debugger
Understanding dependenciesr.t debugger
Row Status on Required State of the Mapping Limitations of Debugger
Types of Sessions used in Debugger
Slowly Changing Dimensions(implementation using two techniques)
Types of SCD
Manual Technique and Auto Generate Technique
Mapping Parameters
Types of Parameters in Informatica
Creating a Parameter File
Altering Session Properties
Mapping Variables
Types of Variables in Informatica
Setminvariable, setmaxvariable and setcountvariable
Advanced Session Properties
Configuration of Transformations
Sources and Target Properties at Session Level
Load Testing in Informatica
Working with Normal Loading and Bulk Loading
Setting Update Options
Setting Transaction Options
Setting up Connections at Session Level Manually
Tracing Level Types
Batch Processing
Types of Tasks
Session Task
Event Wait, Event Raise, and File Watcher Event
Timer task, Decision task, and Control task
STOP and ABORT
Assignment task
Version Control: Checking dependencies of an Object
Types of Objects in Informatica
Check in and Checkout
Loading Techniques: Direct and Indirect loading of Flat Files and Relational Sources
ETL and data-warehousing professionals
End Users
SQL and database Developers
Business Intelligence Professionals
Mainframe Professionals
Project Managers
Professionals planning to appear for the Powercenter Developer Certification
Basic SQL
Keen to learn most popular ETL tool in IT market