DWH: Data WareHousing Concepts
What is Data Warehouse?
Need of Data Warehouse
Introduction to OLTP, ETL and OLAP Systems
Difference between OLTP and OLAP
Data Warehouse Architecture
Data Marts
ODS [Operational Data Store]
Dimensional Modelling
Difference between relation and dimensional modelling
Star Schema and Snowflake Schema
What is fact table
hat is the Dimension table
Normalization and De-Normalization
ETL Testing
ETL architecture.
What is ETL and importance of ETL testing
How DWH ETL Testing is different from the Application Testing
SDLC/STLC in the ETL Projects (ex: V Model, Waterfall model)
Challenges in DWH ETL Testing compare to other testing
Incompatible and duplicate data.
oss of data during ETL process.
Testers have no privileges to execute ETL jobs by their own.
Volume and complexity of data is very huge.
Fault in business process and procedures.
Trouble acquiring and building test data.
ETL Testing Workflow activities involved
Analyze and interpret business requirements/ workflows to Create
estimations/li>
Approve requirements and prepare the Test plan for the system testing
Prepare the test cases with the help of design documents provided by the
developer team
Execute system testing and integration testing.
Best practices to Create quality documentations (Test plans, Test Scripts, and Test closure summaries)
>How to detect the bugs in the ETL testing
How to report the bugs in the ETL testing
How to coordinate with the developer team for resolving the defects
Types of ETL Testing
Data completeness.
Data transformation.
Data quality.
Performance and scalability.
Integration testing.
User-acceptance testing.
SQL Queries for ETL Testing
Incremental load testing
Initial Load / Full load testing
Different ETL tools available in the market
Informatica
Ab Initio
IBM Data stage
Power Center Components
Designer
Repository Manager
Workflow Manager
Workflow Monitor
Power Center Admin Console
Informatica Concepts and Overview
Informatica Architecture.
Sources
Working with relational Sources
Working with Flat Files
Targets
Working with Relational Targets
Working with Flat file Targets
Transformations Active and Passive Transformations
Expression
þÿLookup Different types of lookup Caches
Sequence Generator
Filter
Joi
Stored Procedure
Slowly Changing Dimension
SCD Type1
SCD Type2
Date, Flag and Version
SCD Type3
Workflow Manager
Creating Reusable tasks
Workflows, Worklets & Sessions
Tasks
Session
Decision task
Control Task
Event wait task
Timer task
Monitoring workflows and debugging errors
Indirect Loading
Constraint based load ordering
Target Load plan
Worklet
Migration ?ML migration and Folder Copy.
Scheduling Workflow
Parameter and variables
XML Source, Target, and Transformations
Performance Tuning
Pipeline Partition
Dynamic Partition
Pushdown optimization
Preparation of Test Cases
Executing Test case
Preparing Sample data
Data validation in Source and target
Load and performance testing
Unit testing Procedures.
Error handling procedures.