Duration: 40 days

Online  Fee: 15,000

Classroom Fee: 6000

 

Data Warehousing Concepts

Chapter 1

 

  • Introduction
  • What is a Database?
  • What is a Data warehouse?
  • Why we need a Data Warehouse?
  • Who will use Data Warehouse?
  • Purpose of Analysis
  • Evolution of Reporting
  • Difference between -Data vs. Information vs. Knowledge
  • What is OLTP?
  • Importance and Examples of OLTP
  • Characteristics of OLTP
  • What is OLAP?
  • Importance and Examples of OLAP
  • Characteristics of OLAP
  • Why OLAP for Data Warehouse?
  • OLTP vs. OLAP
  • Steps to Transform OLTP to OLAP

Chapter 2

 

  • Different type of Data Warehouse Architectures
  • Simple DWH architecture
  • DWH architecture with staging area
  • DWH architecture with staging area and Data marts
  • What is ODS
  • DWH architecture with ODS
  • Definitions of Data Warehouse
  • Ralph Kimball’s Data Warehouse definition
  • Bill Inmon’s Data Warehouse definition
  • What are BI and DSS
  • OLAP vs. DW vs. BI vs. DSS

Chapter 3

 

  • Components of Data Warehouse
  • Architecture
  • Different types of Data Warehouse
  • Approaches
  • Top down Approach
  • Bottom up Approach
  • Difference between Top down Approach
  • Bottom up Approach
  • Characteristics of Data Warehouse

DWH

 

  • Definition of Data Mart
  • Different types of Data Marts
  • Dependant Data Marts
  • Independent Data Marts
  • Difference between Dependant Data Martand Independent Data Marts
  • Difference between Data Mart and DataWarehouse

Chapter 4

 

  • Data modeling
  • Different types of Data modeling’s
  • E-R modeling
  • Dimensional Modeling
  • Different types of Dimensional Modeling
  • Star schema
  • Snow flake schema
  • Galaxy schema
  • What is dimension?
  • What is Fact?
  • What is OLAP/ROLAP/MOLAP/HOLAP.
  • What is Drill Through/Drill Across/Drill Down/Drill Up

Chapter 5

 

  • Different Types of Dimensions based on
  • the data changes in the dimensions
  • Rapidly Changed Dimension
  • Slowly Changed Dimension
  • Unchanged/Static Dimension
  • Slowly Changing Dimensions (SCD)
  • Definition of SCD Type 1
  • Definition of SCD Type 2
  • Definition of SCD Type 3
  • Implementation of SCD Type 1
  • Implementation of SCD Type 2
  • Implementation of SCD Type 3
  • Importance of Surrogate key in SCDs
  • Implementation of Fact table

Chapter 6

 

  • Types of Dimensions and Fact Tables
  • Conformed Dimension
  • Degenerated Dimension
  • Rapidly Changing Dimension
  • Static Dimension
  • Incremental Dimension
  • Time Dimension
  • Types of Facts
  • Additive Fact
  • Semi Additive Fact
  • Non Additive Fact
  • Factless Fact Table
  • What is Fact Granularity?

Chapter 7

 

  • Extraction Transformation and Loading(ETL Process)
  • Extraction
  • Different types of Sources
  • Methods of Extraction
  • Full extraction
  • Incremental extraction
  • Transformation
  • Transformations categories
  • Different types of LoadsInitial/Bulk/First loadIncremental loads

Advanced Oracle SQL Queries

 

  • Different Types of queries and explanation with an examples
  • Simple queries
  • Sub queries
  • Inline queries
  • Co Related sub queries
  • Importance of ROWNUM, ROWID
  • Explanation of advanced queries
  • Performance Tuning of Oracle at Informatica LevelJoins, SET operators, Indexes etc.
  • How to display nth record or range of records, or last record
  • How to find out duplicate records and eliminate duplicate records from table
  • How to deploy Nth maximum salary with 3 different queries Like this
  • How to deploy top N salaries, like this many queries will discuss in this chapter in the

Interview point of view

Informatica 9.x Basic Concepts

 

Chapter 1

 

  • Introduction to Informatica
  • Architecture of Informatica – Power
  • Center 9.x
  • Difference between 7.x/8.x/9.x
  • Informatica Design Process
  • Informatica Client Tools
  • Informatica Power Center Designer
  • Informatica Power Center WorkflowDesigner
  • Informatica Power Center WorkflowMonitor
  • Informatica Power Center RepositoryManager
  • Creation of user, grant the permissions tothe userCreation of Folder

Chapter 2

 

  • Introduction of Power Center Designer
  • Source Analyzer
  • Target Designer
  • Transformation Developer
  • Mapplet Designer
  • Mapping Designer
  • Creation of ODBC Connections
  • Import Source definition by using sourceanalyzer
  • Import Target definition by using TargetDesigner
  • Development of Simple ETL plan
  • Creating ETL Specification document
  • Creating the first Mapping
  • Introduction of Power Center WorkflowManager
  • Creating the Connection Objects
  • Creating and configuring Session
  • Creating the first Workflow
  • Executing the workflow.
  • Learning the post Load activities- gathering statistics, Validating the target data
  • Viewing the session log, workflow log Viewing Error/Reject files.
  • Reconciling the Data.
  • Transformations Overview & their Types
  • Active Transformation
  • Passive Transformation
  • Connected transformation
  • Unconnected transformation
  • Single group Transformation
  • Multiple group Transformation
  • Reusable Transformation
  • Non Reusable Transformation

Chapter 3

 

  • List of Tools in Informatica Power Center
  • Workflow Designer
  • Task developer
  • Worklet designer
  • Workflow designer
  • Definition of Session
  • Creating different type of Sessions-Non

Reusable and Reusable session

 

  • Creation of Session for Particular Mapping
  • Creation of workflow with reusable and Non_reusable sessions.
  • Explanation of different session and Workflow properties.

Chapter 4

  • List of Transformations
  • Filter Transformation
  • Expression Transformation
  • Aggregator Transformations
  • Union Transformation
  • Joiner Transformation
  • Router Transformation
  • Sorter Transformations
  • Sequence Generator Transformation
  • Source Qualifier Transformation
  • Rank Transformation

Lookup Transformation

  • Connected
  • Unconnected
  • Active
  • Update Strategy Transformation
  • Normalizer Transformation
  • Transaction Control Transformation

Chapter 5

 

  • Explanation of Filter Transformation
  • Implementation of Data clinging by usingFilter Transformation
  • Explanation of Expression Transformation
  • Implementation of Data scrubbing byusing Expression Transformation
  • Explanation of Union Transformation
  • Implementation of Vertical Merging byusing Union Transformation
  • Explanation of Joiner Transformation
  • Implementation of Horizontal Merging by using Joiner Transformation
  • Explanation of Aggregator Transformation
  • Implementation of data aggregation by Aggregator Transformation

Chapter 6

 

  • Transformations Contd
  • Explanation of Router Transformation
  • Loading the data to Multiple Targets by using Router Transformation
  • Explanation of Sorter Transformation
  • Sorting the data and eliminating the duplicate by using Sorter transformation.
  • Explanation of Sequence GeneratorTransformation
  • Generating Surrogate keys or sequence numbers by using Sequence generator.
  • Difference between Joiner and Lookup.

Chapter 7

  • Transformations Contd
  • Explanation of Source Qualifier
  • Transformation
  • Implementation of Data cleansing, Data Scrubbing, Data Merging, Data aggregation using Source Qualifier
  • Transformation
  • Explanation of Rank Transformation
  • Loading Top ranks or Bottom Ranks byusing Rank Transformation
  • Explanation of Lookup Transformation
  • Uses of lookup transformation
  • Explanation of Update Strategy
  • Transformation
  • Explanation of Normalizer Transformation
  • Explanation of Stored Procedure
  • Transformation

Informatica 9.x Advanced Concepts

 

Chapter 1

 

  • Java Transformation
  • Stored Procedure Transformation
  • XML Transformation
  • Active Lookup
  • Replacing Joiner with Active lookup
  • Debugging the Mapping with Debugger

Files Handling

 

  • Delimited
  • Fixed Width
  • Excel Files

Create Simple Mappings with Flat files

 

    • Flat File Analysis
    • Indirect loading for Flat files
    • Dynamic generation of Flat files
    • SCD Type 1 mapping Implementation
    • SCD Type 2 mapping Implementation
    • considering performance Measures
    • SCD Type 2 mapping Implementation
    • considering Performance Measures
    • SCD Type 2 mapping Implementation
    • considering Performance Measures
    • SCD Type2 mapping Implementation in
    • Dynamic Cache Method
    • SCD Type 3 mapping Implementation.
    • Re-keying the dimension tables Error handling Mechanism in Informatica.
    • Migrating the Code from one Environment to the Other Environment
    • Comparing the code post migration
    • New Informatica Power Center FeaturesUDF,Versioning, Checking Dependencies,Using search tool and many more.

Chapter 2

 

      • Different Types of Caches in -Aggregator Transformation.
      • Rank Transformation
      • Lookup Transformation
      • Joiner Transformation
      • Sorter Transformation
      • Unconnected Lookup Transformation
      • Cache in Active Lookup

Chapter 3

 

      • Types of caches in Lookup transformation
      • When to Cache/Uncache?
      • Static/Dynamic/Persistent lookup cache
      • Shared cache
      • Named /Un Named/Rebuild cache.
      • Mapplets & Limitation of MappletsExplanation of shot cut
      • Difference between shot cut and reusabletransformation.

Chapter 4

 

      • Mapping Parameters
      • Mapping Variables
      • Session Variables
      • Workflow Variables
      • Creation of Parameter Files
      • Significance of Sorted Input in Joiner and
      • Aggregator transformations
      • Implementing Incremental Loading usingmapping variables.

Chapter 5

 

      • Batch processing
      • Sequential and parallel execution
      • Workflow Tasks
      • Scheduling the workflows
      • Concurrent workflows
      • Types of TASKSWorklet
      • Identifying the session/workflow failuresand fixing them

Chapter 6

 

      • Target load Plan.
      • Pushdown Optimization
      • High Availability
      • Importance of Code Page
      • Constraint based load ordering.
      • Explanation of all the session Properties

Chapter 7

 

      • ETL Testing Strategies.
      • Different types of testing
      • Unit testing, Integration test, Performance
      • Testing and UAT.
      • Basic UNIX required in Informatica
      • Execute workflows usingPMCMD, PMREP.

Chapter 8

 

Repository Administration

 

      • Creating Repository Users and Groups.
      • Connecting to the Repository.
      • Creating a Group
      • Assigning Privileges to a Group.
      • Creating a User
      • Creating a Folder
      • Folder Permissions

Informatica Real Time Scenarios

 

      • Update the data without using the Update strategy transformation.
      • Converting Rows to Columns
      • Splitting the file dynamically.
      • Dynamic generation of Flat file
      • Removing duplicate records from source

How to Load Multiple Flat Files using one mapping

 

      • Loading duplicate records in one target and unique records into another target
      • Loading first record into one target and last record into second target
      • Loading header/trailer Record in one target and Detail records into another target
      • Loading the selected record into target
      • Loading even know of records into one target and odd no of records into second target.

Different ways of generating sequence numbers without sequence generator.

 

      • Finding sum of SAL without aggregator transformation
      • Find dense ranks without Rank transformation
      • Generating Fibonacci numbers
      • Applying tracing level in Informatica
      • Implementing different required patterns, many scenarios along with these.

Project Explanation Overview

 

    • Explanation of Different Type of Projects
    • Different phases in development Project (data warehouse life cycle)
    • Responsibilities of ETL Developer.
    • Loading strategies
    • SCDs implementation of Different Dimensions
    • Loading strategies of Dimensions
    • Loading strategies of Fact tables
    • Implementation of Star schema with dimension tables and fact table
    • Performance Tuning at Project Level…