DW- ETL Informatica and Oracle Course Contents
Informatica is an ETL tool , which extracts data from different sources such as oracle,files,SAP etc , TRANSFORM the data such as removing duplicate values or removing null or extra character and load the data in oracle, flat files, Teradata etc. Informatica is one of the famous tool, which is used across the IT industry in the data warehouse, data analytics and data extraction projects.

Oracle 11G Concepts

  1. Oracle Objects like Tables, Views, Indexes etc
  2. DDL Statements
  3. DML Statements
  4. TCS Statements
  5. Joins like (Inner, Outer, Self)
  6. Sub Query
  7. Correlated Sub Query
  8. Oracle Analytical functions (RANK, DENSE_RANK, ROW_NUMBER, FIRST, LAST etc)
  9. Views, Materialized views
  10. PL/SQL – Procedure, Functions, Triggers etc

Datawarehouse Concepts

  1. Data Warehouse
  2. Data Mart
  3. OLAP VS OLTP
  4. Data Warehouse Architecture (Basic)
  5. Data Warehouse Architecture (with a Staging Area)
  6. Dimensional Modeling
  7. Star Schema
  8. Snowflake Schema
  9. Dimension
  10. Fact
  11. Slowly Changing Dimension type 1,2 and 3 (concepts and Informatica implementation)
  12. Concept of surrogate key in DW
  13. Hierarchy
  14. Confirmed Dimension
  15. Fact less Fact
  16. Summary / Aggregations table
  17. Change Data capture

Informatica Power center 8.6.1:

  1. Power Center Components
  2. Designer
  3. Repository Manager
  4. Workflow Manager
  5. Workflow Monitor

 

  1. Informatica Concepts and Overview
  2. Informatica Architecture.
  3. Source Qualifier
  4. Target Qualifier
  5. Transformations
  6. Mappings
  7. Mapplets
  8. Sessions
  9. Tasks

 

  1. Sources
  2. Working with relational Sources
  3. Working with Flat Files

 

  1. Targets
  2. Working with Relational Targets
  3. Working with Flat file Targets

 

  1. Transformations
  2. Expression
  3. Lookup
  4. Sequence Generator
  • Filter
  1. Joiner
  2. Sorter
  3. Procedural
  • Router
  1. Aggregator
  2. Active and Passive Transformations
  3. Update Strategy
  4. Rank Transformation
  5. SQL transformation
  • Union Transformation.
  1. Stored procedure transformation
  2. Java Transformation
  3. Normalizer transformation
  4. Data Concatenation
  5. Debugging Mappings

 

  1. Mappings & Mapplets
  2. Mapping Variables & Parameters
  3. Workflow level Parameters
  4. Session Level Parameters
  5. Working with Mapping Wizards

 

  1. Workflow Manager
  2. Server Architecture
  3. Workflows, Worklets & Sessions
  4. Tasks, Assignments, Events, Command etc,

 

  1. Workflow Variables and Tasks
  2. Link Conditions
  3. Workflow Variables
  4. Assignment Tasks
  5. Decision Task
  6. Email Task

 

  1. More Tasks and Reusability
  2. Event Raise Task
  3. Event Wait Task
  4. Command Task
  5. Reusable Tasks
  6. Reusable Session Task
  7. Reusable Session Configuration

 

  1. Workflow Monitor
  2. How to Run Workflow
  3. Workflow Monitoring
  4. Reading Workflow Logs

 

  1. Advance features and Performance Tuning in Oracle and Informatica
  2. Informatica Tuning (Source, target, mapping & Session)
  3. Informatica cache concepts such as Static, Dynamic, Persistent and shared cache
  4. Oracle Tuning (Indexes (B* Tree, Bit Map, Function Based Indexes etc), Materialized View, Explain plan, Tkprof & SQL Hints
  5. Data profiling with Informatica.
  6. Concept of Pushdown optimization
  7. Concepts of Deployment group in Informatica
  8. Concepts of partitioning in Informatica
  9. Object export and import for deployment of code across repository and folder

 

  1. Project – Case Study – This case study will include all the major Informatica Transformations.

Arrange LAB for:-

  1. Slowly Changing Dimension – 1 (SCD – 1)
  2. Slowly Changing Dimension – 2 (SCD – 2)
  3. Slowly Changing Dimension – 3 (SCD – 3)

 

  1. Discussion of Industry-based scenarios and their solutions.
  2. Preparing Industry based interview Questions and Answers for Oracle and Informatica