ETL Testing and Informatica Training

Based on the interest and request from all the students and learners we are going to start online classes and offline/classroom classes for ETL Testing and Informatica Training

ETL Testing and Informatica Training
@ Real Time with good success rate call +91-8237320101 and +91-9885320101


Online Trainings
-All working days and weekends


Pune
- Monday to Friday
                                         -  Vishrantwadi, Pune,Maharashtra - 411 015

Pune
- Weekends
                                         -  Hinjewadi, Pune,Maharashtra - 411 057

Bangalore

- Weekends
                                         -  Kasturi Nagar,Nr Tin Factory,Bangalore - 43

Hyderabad
- Weekends
                                         -  Ameerpet,Hyderabad

Data Transformation Test

Data transformation test:

1.It’s a process of converting,cleansing,scrubbing,merging the data into required business format.

2.Validating that the data is transformed correctly or not,based on the business rules/business requirements,

Business rules/requirements validating can be the most complex and important part of ETL testing. 

3.An ETL application(i.e.Informatica,Datastage..etc) with significant transformation logic between source and target,where test should be make sure that the datatype of each column of each table is as per the functional and mapping specifications,If no specific details are mentioned in functional and mapping specifications about the tables/schema's then test should be make sure on the below concerns:

a)The datatype of source column and destination column(Target column) are same or not.

b)The destination column length is equal to or greater than the source column length.

c)Validation should be done that all the data specified gets extracted.

d)Test should include the check to see that the transformation and cleansing process are working correctly.

e)Make sure that all the types of data transformations are working and meeting the FS/MS and Business requirements/rules.

The following types of data transformation makes place in staging

1. Data Cleansing 

2. Data Scrubbing  

3. Data Aggregation  

4. Data Merging 

Data Completeness Test

1.Data completeness test are designed to verify that all the expected data loads into the DWH.

2.It includes running detailed tests to verify that all records,all fields and full contents of each field are loaded correctly or not.

3.Strategies to consider includes:

a)Record counts must compared between source and the target data.

b)Comparing record counts between source and data loaded to the warehouse (Target) and also rejected records in warehouse (Target).

c)Comparing unique values of key fields between source data and data loaded to the warehouse (Target) column mapping from the source or stage.

4.Populating the full contents of each field to validate that have no truncation occurs at any step in the process for example if the source data fields is having string(30) and make sure that to test it with 30 characters.

Test Strategy for ETL Testing / Standard Tests for ETL Testing

There will be some standard tests for DWH that should be carried out as part of testing for every DWH Project.

These are Strategies for testing ETL Applications are Identified as below:

1)      Data Completeness Testing

2)      Data Transformation Testing

3)      Data Quality Testing

4)      Initial Load / Full Load Testing

5)      Incremental Load Testing

6)      Presentation Layer Testing /  BI Testing / Report Testing

7)      Integration Testing / System Integration Testing / SIT

8)      Load and Performance Testing

9)      UAT Testing / User Acceptance Testing

10)   Regression Testing

                     High Level Description

    Data Completeness Testing             -  

Ensures that all expected data is loaded

       Data Transformation Testing            - 

 Ensures that all data is transformed correctly according to business rules

Data Quality Testing -

Ensure that the etl applications correctly rejects,substitutes the default values and reports invalid data

 Initial Load / Full Load Testing -

       Ensures that all the very first time data loaded correctly and als ensure the truncating process

Incremental Load Testing -

       Ensures that after first load all data is getting updating maintaining versioning and inserting new records  properly

  Presentation Layer Testing /  BI Testing / Report Testing  -

Testing BI Reports in DWH testing and comparing data correctness from DWH data and Reports

Integration Testing / System Integration Testing / SIT  -

Ensure that the ETL process functions with other upstream and downstream process

 Load and Performance Testing -

Ensure that the data loads and queries perform within expected Timeframes

 UAT Testing / User Acceptance Testing  -

Ensure that solution  and current expectations and anticipates full expectations

Regression Testing -

Ensures the new data updates have not broken any existing functionality or process.

ETL Testing Online Training & Course Content

ETL Testing Course Content by

Sandeep Manem  @ +91-9885320101, +91-8237320101

DataWare Housing Concepts:

·           What is Data Ware House?

·           Difference between OLTP and DataWare Housing

Data Acquisition

·           Data Extraction

·           Data Transformation

·           Data Loading

Data Marts

·           Dependent Data Mart

·           Independent Data Mart

Data Base Design

·           Star Schema

·           Snow Flake Schema

·           Fact constellation Schema

SCD(slowly changing dimension)

·           Type-1 SCD

·           Type-2 SCD

·           Type-3 SCD

Basic Concepts in SQL

·         Overview of ETL Tool Architecture

·         White Box and Black BOX Testing Functionality on Different Transformation Rules

Data Ware House Life Cycle

Different Types of Testing Techniques in ETL

·           Minus Queing

·           Count  Queing

  ETL Testing Concepts

1.Introduction

·            What is use of testing

·            What is quality & standards

·            Responsibilities of a ETL Tester

2.Software development life cycle

·            Waterfall model

·            V-model

·            Agile model & methodology

·            Prototype model

·            Spiral model

3.Testing methodologies

·            White box testing

·            Black box testing

·            Grey box testing

·            ETL Testing Work Flow Process

·            How to Prepare the  ETL Test Plan

·            How to design the Test cases in ETL Testing.

·            How to reporting the Bugs in ETL Testing ?

·            ETL Testing Responsibilities in DataStage, Informatica, Abinitio etc;

·            How to detect the bugs through database queries

·            ETL Performing Testing & Performing Tuning

Projects

Projects on Different Domains(Banking , Health Care, Telecom , Insurance)

OR

ETL Testing Course Content by

Sandeep Manem  @ +91-9885320101, +91-8237320101

Oracle Basics & Concepts

1. DBMS

2. RDBMS

3. DBMS vs RDBMS

4. Why Database Required

5. Different types of databases and difference

6. ASCII vs UNICODE

7. PL/SQL Basics

8. Oracle Architecture

9. Diff B/W Database & Files

10. OLTP

11. OLAP, ROLAP, MOLAP

12. METADATA

13. DDL,DML,DCL

14. BASIC ADMIN ACTIVITIES

15. DATATYPES

16. TABLES

17. SQL ,SUB QUERIES,CORELATED SUB QUERY

18. INNER QUERY,OUTER QUERY

19. FUNCTIONS AND TYPES AND IMPORTANCE

20. JOINS AND DIFFERENT TYPES

21. VIEWS n MATERIALIZED VIEWS

22. INDEX

23. CONSTRAINTS

24. REFERENTIAL INTEGRITY

25. PARTITIONING

26. PERFORMANCE TUNING

27. DIFFERENT TYPES OF TECHNIQES

28. DATABASE SCHEMA

DWH Concepts

1. WHAT IS DWH ?WHY WE REQUIRE THAT ?

2. DWH ARCHITECTURE

3. DATA MART and TYPES

4. DM vs DWH

5. DATA CLEANZING

6. DATA SCRUBING

7. DATA MASKING

8. NORMALIZATION

9. ODS

10. STG AREA

11. DSS

12. Diff B/w OLTP vs ODS,OLAP vs DSS

13. DIMENTION MODELING

14. DIMENSIONS

15. FACTS

16. AGGREGATES

17. DWH SCHEMA designing

18. STAR SCHEMA,SNOWFLAKE SCHEMA,GALAXY SCHEMA,FCS

19. SLOWLY CHANGING DIMENSIONS

20. SCD TYPE1,TYPE2,TYPE3

21. INITIAL LOAD

22. INCREMENTAL LOAD

23. FULL LOAD

24. CDC- change data capture

25. FAQ’S

ETL Testing Concepts

1. Introduction of ETL-Extract,Transform,Load

2. ETL TOOLS and Diff Types of ETL Tools

3. ETL ARCHITECTURE

4. ETL TESTING AND WHY WE REQUIRE

5. DIFFERENT ETL TOOLS ARCHITECTURES

6. SDLC and Methods/Models

7. STLC and Methods/Models

8. SDLC vs STLC

9. Reverse Engineering

10. QC(Quality Center and BugZilla)

11. Roles and Responsibilities

12. Minus,Duplicate,Count,Intersection,etc…

13. Detect Defects

14. Defects Logging and Reporting

15. How to prepare Queries very quickly with the help of mapping

16. Performance Tuning and Performance Testing,Report Testing,UI Testing

17. Quality and different standards that tester should follow,Why?

18. Testplan Preparation

19. Testcases Preparation

20. Preparation of Test data

21. Process Of ETL Testing

Testing Concepts

1. Whitebox Testing

2. Blackbox Testing

3. Gray Box Testing

4. Regression Testing

5. Smoke Testing vs Sanity Testing

6. User Testing

7. Unit testing

8. Intigration testing

9. Module testing

10. System testing

11. UAT

ETL Tool and Testing

1. Data Extract

2. Data Transform

3. Data Load

4. Import Source

5. Import Target

6. Mappings,Maplets

7. Workflows,Worklets

8. Transformations,Functionalities,Rules and Techniques

9. Import and Export

10. Coping and Rules

11. Queries Preparation based on Transformations

12. Importance of ETL Testing

13. Creating of Mappings,Sessions,Workflows

14. Running of Mappings,Sessions,Workflows

15. Analyzing of Mappings,Sessions,Workflows

16. Tasks and Types

Practice:

a. Testing scenarios, creation of test cases and scripts

b. Test case execution and defect tracking and reporting

c. Preparation of Test data

d. Practice ETL Testing with Real Time Scenarios and FAQ’s

e. Resume preparation.

ETL Testing Use Cases & Benefits

ETL Testing

ETL Testing in Less Time, With Greater Coverage, to Deliver Trusted Data

Much ETL testing today is done by SQL scripting or “eyeballing” of data on spreadsheets. These approaches to ETL testing are very time-consuming, error-prone, and seldom provide complete test coverage. Informatica Data Validation Option provides an ETL testing tool that can accelerate and automate ETL testing in both production environments and development & test. This means that you can deliver complete, repeatable and auditable test coverage in less time with no programming skills required. 

ETL Testing Use Cases

• Production Validation Testing (testing data before moving into production). Sometimes called “table balancing” or “production reconciliation,” this type of ETL testing is done on data as it is being moved into production systems. The data in your production systems has to be right in order to support your business decision making.  Informatica Data Validation Option provides the ETL testing automation and management capabilities to ensure that your production systems are not compromised by the data update process.
• Source to Target Testing (data is transformed). This type of ETL testing validates that the data values after a transformation are the expected data values. The Informatica Data Validation Option has a large set of pre-built operators to build this type of ETL testing with no programming skills required.
• Application Upgrades (same-to-same ETL testing). This type of ETL testing validates that the data coming from an older application or repository is exactly the same as the data in the new application or repository. Must of this type of ETL testing can be automatically generated, saving substantial test development time.

Benefits of ETL Testing with Data Validation Option

• Production Reconciliation. Informatica Data Validation Option provides automation and visibility for ETL testing, to ensure that you deliver trusted data in your production system updates.
• IT Developer Productivity.  50% to 90% less time and resources required to do ETL testing
• Data Integrity.  Comprehensive ETL testing coverage means lower business risk and greater confidence in the data.

DBMS

A database is a collection of occurrence of multiple record types containing the relationship between records, data aggregate and data items. A database may be defined as
      A database is a collection of interrelated data store together without harmful and unnecessary redundancy (duplicate data) to serve multiple applications
      The data is stored so that they are independent of programs, which use the data. A common and control approach is used in adding the new data, modifying and retrieving existing data or deletion of data within the database A running database has function in a corporation, factory, government department and other organization. Database is used for searching the data to answer some queries. A database may be design for batch processing, real time processing or on line processing.

DATABASE SYSTEM
      Database System is an integrated collection of related files along with the detail about their definition, interpretation, manipulation and maintenance. It is a system, which satisfied the data need for various applications in an organization without unnecessary redundancy. A database system is based on the data. Also a database system can be run or executed by using software called DBMS (Database Management System). A database system controls the data from unauthorized access.

Foundation Data Concept:
A hierarchy of several levels of data has been devised that differentiates between different groupings, or elements, of data. Data are logically organized into:

Character
      It is the most basic logical data element. It consists of a single alphabetic, numeric, or other symbol.

Field
      It consists of a grouping of characters. A data field represents an attribute (a characteristic or quality) of some entity (object, person, place, or event).

Record
      The related fields of data are grouped to form a record. Thus, a record represents a collection of attributes that describe an entity. Fixed-length records contain, a fixed number of fixed-length data fields. Variable-length records contain a variable number of fields and field lengths.

File
      A group of related records is known as a data file, or table. Files are frequently classified by the application for which they ar primarily used, such as a payroll file or an inventory file, or the type of data they contain, such as a document file or a graphical image file. Files are also classified by their permanence, for example, a master file versus a transaction file. A transaction file would contain records of all transactions occurring during a period, whereas a master file contains all the permanent records. A history file is an obsolete transaction or master file retained for backup purposes or for long-term historical storage called archival storage.

Database

      It is an integrated collection of logically related records or objects. A database consolidates records previously stored in separate files into a common pool of data records that provides data for many applications. The data stored in a database is independent of the application programs using it and o the ‘type of secondary storage devices on which it is stored.

ETL Testing Online Training Course Content

We need to aware of below topics

Oracle Basics & Concepts
1. DBMS
2. RDBMS
3. DBMS vs RDBMS
4. Why Database Required
5. Different types of databases and difference
6. ASCII vs UNICODE
7. PL/SQL Basics
8. Oracle Architecture
9. Diff B/W Database & Files
10. OLTP
11. OLAP, ROLAP, MOLAP
12. METADATA
13. DDL,DML,DCL
14. BASIC ADMIN ACTIVITIES
15. DATATYPES
16. TABLES
17. SQL ,SUB QUERIES,CORELATED SUB QUERY
18. INNER QUERY,OUTER QUERY
19. FUNCTIONS AND TYPES AND IMPORTANCE
20. JOINS AND DIFFERENT TYPES
21. VIEWS n MATERIALIZED VIEWS
22. INDEX
23. CONSTRAINTS
24. REFERENTIAL INTEGRITY
25. PARTITIONING
26. PERFORMANCE TUNING
27. DIFFERENT TYPES OF TECHNIQES
28. DATABASE SCHEMA

DWH Concepts
1. WHAT IS DWH ?WHY WE REQUIRE THAT ?
2. DWH ARCHITECTURE
3. DATA MART and TYPES
4. DM vs DWH
5. DATA CLEANZING
6. DATA SCRUBING
7. DATA MASKING
8. NORMALIZATION
9. ODS
10. STG AREA
11. DSS

ETL Testing Course Contents

12. Diff B/w OLTP vs ODS,OLAP vs DSS
13. DIMENTION MODELING
14. DIMENSIONS
15. FACTS
16. AGGREGATES
17. DWH SCHEMA designing
18. STAR SCHEMA,SNOWFLAKE SCHEMA,GALAXY SCHEMA,FCS
19. SLOWLY CHANGING DIMENSIONS
20. SCD TYPE1,TYPE2,TYPE3
21. INITIAL LOAD
22. INCREMENTAL LOAD
23. FULL LOAD
24. CDC- change data capture
25. FAQ’S

ETL Testing Concepts
1. Introduction of ETL-Extract,Transform,Load
2. ETL TOOLS and Diff Types of ETL Tools
3. ETL ARCHITECTURE
4. ETL TESTING AND WHY WE REQUIRE
5. DIFFERENT ETL TOOLS ARCHITECTURES
6. SDLC and Methods/Models
7. STLC and Methods/Models
8. SDLC vs STLC
9. Reverse Engineering
10. QC(Quality Center and BugZilla)
11. Roles and Responsibilities
12. Minus,Duplicate,Count,Intersection,etc…
13. Detect Defects
14. Defects Logging and Reporting
15. How to prepare Queries very quickly with the help of mapping
16. Performance Tuning and Performance Testing,Report Testing,UI Testing
17. Quality and different standards that tester should follow,Why?
18. Testplan Preparation
19. Testcases Preparation
20. Preparation of Test data
21. Process Of ETL Testing

Testing Concepts
1. Whitebox Testing
2. Blackbox Testing
3. Gray Box Testing
4. Regression Testing
5. Smoke Testing vs Sanity Testing
6. User Testing
7. Unit testing
8. Intigration testing
9. Module testing
10. System testing
11. UAT

ETL Tool and Testing

1. Data Extract
2. Data Transform
3. Data Load
4. Import Source
5. Import Target
6. Mappings,Maplets
7. Workflows,Worklets
8. Transformations,Functionalities,Rules and Techniques
9. Import and Export
10. Coping and Rules
11. Queries Preparation based on Transformations
12. Importance of ETL Testing
13. Creating of Mappings,Sessions,Workflows
14. Running of Mappings,Sessions,Workflows
15. Analyzing of Mappings,Sessions,Workflows
16. Tasks and Types

Practice:

Testing scenarios, creation of test cases and scripts
Test case execution and defect tracking and reporting
Preparation of Test data
Practice ETL Testing with Real Time Scenarios and FAQ’s

Check the below link

The best Online Training for ETL Testing is as follows

 Call:  Sandeep Manem
          +91-8237320101
          +91-9885320101
Trained around 100+ students and placed around 90+ in Top MNC's with 90% success rate.

ETL Testing Challenges

ETL testing is quite different from conventional testing. There are many challenges we faced while performing data warehouse testing. Here is the list of few ETL testing challenges I experienced on my project:
- Incompatible and duplicate data.
- Loss of data during ETL process.
- Unavailability of inclusive test bed.
- 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.
- Missing business flow information.

Data is important for businesses to make the critical business decisions. ETL testing plays a significant role validating and ensuring that the business information is exact, consistent and reliable. Also, it minimizes hazard of data loss in production.

Hope these tips will help ensure your ETL process is accurate and the data warehouse build by this is a competitive advantage for your business.

Difference between Database and Data Warehouse Testing

There is a popular misunderstanding that database testing and data warehouse is similar while the fact is that both hold different direction in testing.

•  Database testing is done using smaller scale of data normally with OLTP (Online transaction processing) type of databases while data warehouse testing is done with large volume with data involving OLAP (online analytical processing) databases.
•  In database testing normally data is consistently injected from uniform sources while in data warehouse testing most of the data comes from different kind of data sources which are sequentially inconsistent.
• We generally perform only CRUD (Create, read, update and delete) operation in database testing while in data warehouse testing we use read-only (Select) operation.
• Normalized databases are used in DB testing while demoralized DB is used in data warehouse testing.

There are number of universal verifications that have to be carried out for any kind of data warehouse testing. Below is the list of objects that are treated as essential for validation in ETL testing:
- Verify that data transformation from source to destination works as expected
- Verify that expected data is added in target system
- Verify that all DB fields and field data is loaded without any truncation
- Verify data checksum for record count match
- Verify that for rejected data proper error logs are generated with all details
- Verify NULL value fields
- Verify that duplicate data is not loaded
- Verify data integrity

ETL Testing Process

ETL Testing Process:

Similar to any other testing that lies under Independent Verification and Validation, ETL also go through the same phase.

• Business and requirement understanding
• Validating
• Test Estimation
• Test planning based on the inputs from test estimation and business requirement
• Designing test cases and test scenarios from all the available inputs
• Once all the test cases are ready and are approved, testing team proceed to perform pre-execution check and test data preparation for testing
• Lastly execution is performed till exit criteria are met
• Upon successful completion summary report is prepared and closure process is done.

It is necessary to define test strategy which should be mutually accepted by stakeholders before starting actual testing. A well defined test strategy will make sure that correct approach has been followed meeting the testing aspiration. ETL testing might require writing SQL statements extensively by testing team or may be tailoring the SQL provided by development team. In any case testing team must be aware of the results they are trying to get using those SQL statements.

ETL Testing Techniques

ETL Testing Techniques:

1) Verify that data is transformed correctly according to various business requirements and rules.
2) Make sure that all projected data is loaded into the data warehouse without any data loss and truncation.
3) Make sure that ETL application appropriately rejects, replaces with default values and reports invalid data.
4) Make sure that data is loaded in data warehouse within prescribed and expected time frames to confirm improved performance and scalability.

Apart from these 4 main ETL testing methods other testing methods like integration testing and user acceptance testing is also carried out to make sure everything is smooth and reliable.

why ETL Testing require for Data Warehouse

Why do organizations need Data Warehouse?
Organizations with organized IT practices are looking forward to create a next level of technology transformation. They are now trying to make themselves much more operational with easy-to-interoperate data. Having said that data is most important part of any organization, it may be everyday data or historical data. Data is backbone of any report and reports are the baseline on which all the vital management decisions are taken.

Most of the companies are taking a step forward for constructing their data warehouse to store and monitor real time data as well as historical data. Crafting an efficient data warehouse is not an easy job. Many organizations have distributed departments with different applications running on distributed technology. ETL tool is employed in order to make a flawless integration between different data sources from different departments. ETL tool will work as an integrator, extracting data from different sources; transforming it in preferred format based on the business transformation rules and loading it in cohesive DB known are Data Warehouse.

Well planned, well defined and effective testing scope guarantees smooth conversion of the project to the production. A business gains the real buoyancy once the ETL processes are verified and validated by independent group of experts to make sure that data warehouse is concrete and robust.

ETL or Data warehouse testing is categorized into four different engagements irrespective of technology or ETL tools used:

• New Data Warehouse Testing – New DW is built and verified from scratch. Data input is taken from customer requirements and different data sources and new data warehouse is build and verified with the help of ETL tools.
• Migration Testing – In this type of project customer will have an existing DW and ETL performing the job but they are looking to bag new tool in order to improve efficiency.
• Change Request – In this type of project new data is added from different sources to an existing DW. Also, there might be a condition where customer needs to change their existing business rule or they might integrate the new rule.
• Report Testing – Report are the end result of any Data Warehouse and the basic propose for which DW is build. Report must be tested by validating layout, data in the report and calculation.