ETL Testing the Data Warehouse

Testing the data warehouse and business intelligence system is critical to success.  Without testing, the data warehouse could produce incorrect answers and quickly lose the faith of the business intelligence users. Effective testing requires putting together the right processes, people and technology and deploying them in productive ways.

Data Warehouse Testing Responsibilities

Who should be involved with testing?  The right team is essential to success:

• Business Analysts gather and document requirements
• QA Testers develop and execute test plans and test scripts
• Infrastructure people set up test environments
• Developers perform unit tests of their deliverables
• DBAs test for performance and stress
• Business Users perform functional tests including User Acceptance Tests (UAT)

Business Requirements and Testing

When should your project begin to think about testing?  The answer is simple - at the beginning of the project.  Successful testing begins with the gathering and documentation of requirements.  Without requirements there is no measure of system correctness.

Expect to produce a Requirements Traceability Matrix (RTM) that cross references data warehouse and business intelligence features to business requirements.  The RTM is a primary input to the Test Plan.

Data Warehousing Test Plan

The Test Plan, typically prepared by the QA Testers, describes the tests that must be performed to validate the data warehousing and business intelligence system.  It describes the types of tests and the coverage of required system features.

Test Cases are details that enable implementation of the Test Plan.  The Test Case itemizes steps that must be taken to test the system along with expect results.  A Text Execution Log tracks each test along with the results (pass or fail) of each test item.

Testing Environments and Infrastructure

Multiple environments must typically be created and maintained to support the system during its lifecycle:

• Development
• QA
• Staging / Performance
• Production

These kinds of tools can facilitate testing and problem correction:

• Automated test tool
• Test data generator
• Test data masker
• Defect manager
• Automated test scripts

Unit Testing for the Data Warehouse

Developers perform tests on their deliverables during and after their development process.  The unit test is performed on individual components and is based on the developer's knowledge of what should be developed.

Unit testing should definitely be performed before deliverables are turned over to QA by developers.  Tested components are likely to have fewer bugs.

QA Testers Perform Many Types of Tests

QA Testers design and execute a number of tests:

Integration Test	Test the systems operation from beginning to end, focusing on how data flows through the system.  This is sometimes called "system testing" or "end-to-end testing".
Regression Test	Validate that the system continues to function correctly after being changed.  Avoid "breaking" the system.

Can the Data Warehouse Perform?

Tests can be designed and executed that show how well the system performs with heavy loads of data:

Extract Performance Test	Test the performance of the system when extracting a large amount of data.
Transform and Load Performance Test	Test the performance of the system when transforming and loading a large amount of data.  Testing with a high volume is sometimes called a "stress test".
Analytics Performance Test	Test the performance of the system when manipulating the data through calculations.

Business Users Test Business Intelligence

Does the system produce the results desired by business users?  The main concern is functionality, so business users perform functional tests to make sure that the system meets business requirements.  The testing is performed through the user interface (UI) which includes data exploration and reporting.

Correctness Test	The system must be produce correct results.  The measures and supporting context need to match numbers in other systems and be calculated correctly.
Usability Test	The system should be as easy to use as possible.  It involves a controlled experiment about how business users can use the business intelligence system to reach stated goals.
Performance Test	The system must be able to return results quickly without bogging down other resources.

Business Intelligence Must Be Believed

Quality must be baked into the data warehouse or users will quickly lose faith in the business intelligence produced.  It then becomes very difficult to get people back on board.

Putting the quality in requires both the testing described in this article and data quality at the source described in the article, Data Sources for Data Warehousing, to launch a successful data warehousing / business intelligence effort.

What Is Software Testing?

IEEE Terminology: An examination of the behavior of the program by executing on sample data sets.

Testing is executing a program with an intend of   finding Fault/Error and Failure.

Testing comprises of set of activities to detect defect in a software.

Testing Concepts

                1.   What is Software Testing

                2.   Why Software Testing

                3.   How exactly Testing is different from QA/QC

                4.   Why should we need an approach for Testing

                5.   Areas of Testing

                6.   Testing phases

                7.   All about Test Cases
                8.   Testing Methods – FURPSC Model

                9.   When should we start writing Test Cases / Testing

                10. Testing Limitations

                11. Test stop criteria

                12. Tester Responsibilities.

                13. How to prioritize Tests

                14. How can we improve the efficiency of Testing.

Testing Process

                1.   BSL Model of executing Testing projects.

                2.   BSL Test Life Cycle. 

Testing concepts :

Fault / Error / Failure

Fault is a condition that causes the software to fail to perform its required function.

Error refers to difference between Actual Output and Expected Output.

Failure is the inability of a system or component to perform required function according to its specification.

Failure is an event;

fault is a state of the software, caused by an error.

Why Software Testing?

•          To discover defects.

•          To avoid user detecting problems

•          To prove that the software has no faults

•          To learn about the reliability of the software.

•          To ensure that product works as user expected.

•          To stay in business

•          To avoid being sued by customers

•          To detect defects early, which helps in reducing the cost of

  defect fixing.

How exactly testing is different from QA/QC

 

Testing is often confused with the processes of quality control and quality assurance.

Testing is the process of creating, implementing and evaluating tests.

Testing measures software quality.

Testing can find faults; when they are removed , software quality is improved.

QC is the process of Inspections, Walk-troughs and Reviews.

QA involves in Monitoring and improving the entire SDLC process, making sure that any agreed-upon standards and procedures are followed.

Why do we need an approach for testing?

Yes, We definitely need an approach for testing.

To over come the following problems, we need a formal approach for Testing.

•          Incomplete functional coverage

•          No risk management

•          Too little emphasis on user tasks

•          Inefficient over the long term

3 Areas of Testing

•             Black box Testing

•             White box Testing

•             Grey Box Testing

Black box Testing

Black box testing is also called as Functionality Testing.

In this testing Tester will be asked to test the correctness of the functionality with the help of Inputs and Outputs.

Tester doesn’t require the knowledge of software code. 

Approach:

•          Equivalence Class

•          Boundary Value Analysis

•          Error Guessing

Equivalence Class:

•   For each piece of the specification, generate one or more equivalence Class.

•   Label the classes as “Valid” or “Invalid”.

•   Generate one test case for each Invalid Equivalence class.

•   Generate a test cases that Covers as many as for Equivalence class. 

Boundary Value Analysis:

•             Generate test cases for the boundary values.

•             Minimum Value , Minimum Value + 1, Minimum Value -1

•             Maximum Value, Maximum Value + 1, Maximum Value - 1

Error Guessing:

•            Generate Test cases against to the specification.

White box Testing

White box testing is also called as Structural testing.  Tester does require the knowledge of software code.

Structure = 1 Entry + 1 Exit with certain Constraints, Conditions and Loops.

Why do we go for white box testing when black box testing is used to find defects:

•           Logic Errors and incorrect assumptions are most likely to be made while coding for “special cases”. Need to ensure these execution paths are tested.

•           May find assumptions about execution paths incorrect, and so make design errors.

•           Typographical errors are random. Just as likely to be on an obscure logical path as on a mainstream path.

•          Approach

•          Basic Path testing:

•                          Cyclomatic Complexity.

•                          Mc Cabe Complexity.

•          Structure Testing:

•                          Conditions Testing.

•                          Data Flow Testing.

•                          Loop testing.

•          Grey Box Testing.

•          Grey box Testing is the new term, which evolved due to the different architectural usage of the system. This is just a combination of both Black box & White box testing. Tester should have the knowledge of both the internals and externals of the function.

•          Tester should have the good knowledge of White Box Testing and complete knowledge of Black Box Testing.

•          Grey box testing is especially important with Web and Internet applications, because the Internet is built around loosely integrated components that connect via relatively well-defined interfaces

4 Phases of Testing – The V Model

•             Unit Test

•             Integration Test

•             System Test

•             Acceptance Test

Unit Testing: In Unit testing Tester is supposed to check each and every micro function. All field level validations are expected to test at the stage of testing. In most of the cases Developer will do this.

Approach:

Equivalence Class:

•   For each piece of the specification, generate one or more equivalence Class.

•   Label the classes as “Valid” or “Invalid”.

•   Generate one test case for each Invalid Equivalence class.

•   Generate a test cases that Covers as many as for Equivalence class. 

Boundary Value Analysis:

•   Generate test cases for the boundary values.

Error Guessing:

•            Generate Test cases against to the specification.

Integration Testing:

•          The primary objective of integration testing is to discover errors in the interfaces between Modules / Sub-Systems.

•           

•          Approach:

•          Top-Down Approach

•          Bottom-Up Approach.

•          System Testing:

•          The primary objective of system testing is to discover errors when the system is tested as a whole. System testing is also called as End-To-End Testing. Tester is expected to test from Login-To-Logout by covering various business functionalities.

•           

•          Approach: IDO Model

•           

•          Identifying the End-To-End / Business Life Cycles.

•          Design the test and data.

•          Optimize the End-End / Business Life Cycles.

Acceptance Testing:

 The primary objective of acceptance testing is to get the acceptance from the client. Client will be using the system against the business requirements.

Pre-user acceptance testing will be conducted to ascertain the stability and to check whether the complete functionality of the system is checked during system testing. After the first round of system testing, test engineers will go through the test cases (Test Scripts) sent by the users.

Approach: BE Approach

•             Building a team with real-time users, functional users and         developers.

•             Execution of  business test cases.

5 points for developing Test Cases

 

•             What is a test case?

•             What are the items of test cases?

•             Can this test cases reused?

•             What are the characteristics of good test case?

•             Are there any issues to be considered?

•          What is a test case?

•          Test case is a description of what to be tested , what data to be given and what actions to be done to check the actual result against the expected result.

•          A test case is simply a test with formal steps and instructions

•          Test cases are valuable because they are repeatable, reproducible under the same / different environments, and easy to improve upon with feedback.

What are the items of test case?

Test case items are:

•             Test Case Number

•             Pre-Condition

•             Description

•             Expected Result

•             Actual Result

•             Status (Pass/Fail)

•             Remarks.

•          Can this test cases reused?

•          Yes, Test cases can be reused.

•           

•          Test cases developed for functionality testing can be used for Integration/System/Regression testing and performance testing with few modifications.

What are the characteristics of good test case?

A good test case should have the following:

•             TC should start with “what you are testing”.

•             TC should be independent.

•             TC should not contain “If” statements.

•             TC should be uniform.

                E.g.. <Action Buttons> , “Links”…

Are there any issues to be considered?

Yes there are few Issues:

 

All the TC’s should be traceable.

There should not be too many duplicate test cases.

Out dated test cases should be cleared off.

All the test cases should be executable.

 

Case 01:

In Insurance application, User can apply for different types of

insurances. When user applies for Type – B insurance system asks

To enter the age of the customer. In functional specifications it is

mentioned that user age should be greater then 18 years and less

than 60.

Questions:

1. what should be the approach of Test Engineer to validate the

                respective constraint is taken care.

2.            How can you give the coverage for the respective functionality.

Case 02:

 In most of the applications Authorization and Authentication is mandatory module.  Test Engineer got the following sheet to test the roles and rights of the users.

Questions:

1. what should be the approach of Test Engineer to validate the roles and responsibilities are taken care.
2. How can you give the coverage for the respective functionality.
3. Case 03:
4. In most of the Applications,  entering the Login ID / Password to enter into the application is common. In functional specification it is mentioned that Password fields will accept only Alpha Numeric and up to 8 digits only.
5.  Questions:
6.  1.           what should be the approach of Test Engineer to validate the respective constraint is taken care.
7. 2.            How can test Engineer ensure that application accepts only Alpha Numeric characters.
8.  

Case 04:

XGEN is an Insurance Application. This Application has 4 modules, they are Underwriting, Re-Insurance, Claims and Accounts. All the 4 modules functionalities are working fine when they have tested individually. Few more modules may be added to the existing functionalities.

Questions :

1. What type of testing do you introduce to show all the modules work fine when the output of Underwriting module will become Input parameters to Re-Insurance.
2. What will be the approach

Case 05:

Credit Platform (CP) is a Credit Approval Application. Credit Platform will take care of credit process of an application, to complete the process it has to interact with Citi Safe System, Client Wise System, Hogan System and Collateral management System.

Questions :

1. What type of testing do you introduce to show the systems work fine as a whole.

What will be the approach

Case 06:

In Product Development functionalities will be added in each and every release. Testing team has completed the thorough testing of version 01. Development Team is decided to release the Version 02.

Questions :

1. What type of testing does Testing Team should introduce to show the both the new functionalities and Version 01 functionalities  are working fine.
2. What will be the approach

Functionality Testing

Objective:

1. Test against system requirements.
2. To confirm that all the requirements are covered.

Approach:

Equivalence Class

Boundary Value Analysis

Error Guessing.

Usability Testing:

To test the Easiness and User-friendliness of the system.

Approach:

Qualitative & Quantitative

Heuristic Checklist

Classification of Checking:   Accessibility

                                                                Clarity of communication.

                                                                Consistency

                                                                Navigation

                                                                Design & Maintenance

                                                                Visual Representation.

Qualitative Approach:

 

1. Each and every function should available from all the pages of the site.
2. User should able to submit each and every request with in 4-5 actions.
3. Confirmation message should be displayed for each and every submit.

 Quantitative Approach:

Heuristic Checklist should be prepared with all the general test cases that fall under the classification of checking.

This generic test cases should be given to 10 different people and ask to execute the system to mark the pass/fail status.

The average of 10 different people should be considered as the final result.

Example: Some people may feel system is more users friendly, If the submit is button on the left side of the screen. At the same time some other may feel its better if the submit button is placed on the right side.

Reliability Testing:

RT is property, which defines how well the software meets its requirements.

Objective is to find Mean Time between failure/time available under specific load pattern and Mean time for recovery.

Approach: RRT for continuous hours of operation.

More then 85% of the stability is must.

Reliability Testing helps you to confirm:

Business-logic performs as expected

Active buttons are really active

Correct menu options are available

Reliable hyper links

Regression Testing:

Objective is to check the new functionalities has been incorporated correctly with out failing the existing functionalities.

Approach: Automation tools.

 RAD – In case of Rapid Application development Regression Test plays a vital role as the total development happens in bits and pieces.

The term "regression testing" in simple words. When a code problem has been fixed, a regression test runs tests to verify that the defect is fixed.

Performance Testing

Primary objective of the performance testing is “to demonstrate the system functions to specifications with acceptable response times while processing the required transaction volumes on a production database.

Objectives:

Assessing the system capacity for growth.

Identifying weak points in the architecture

Detect obscure bugs in software

Tuning the system

Verify resilience & reliability

Performance Parameters:

•             Request-Response Time

•             Transactions Per Second

•             Turn Around time

•             Page down load time

•             Through Put

 Approach: Usage of Tool

Classification of Performance Testing:

                                Load Test

                                Volume Test

                                Stress Test

Load Testing

Approach: Load Profile

 

Volume Testing

Approach: Data Profile

Stress Testing

Approach: RCQE Approach

                Repeatedly working on the same functionality

                Critical Query Execution (Join Queries)

                To Emulate peak load.

Load Vs Stress:  

With the Simple Scenario (Functional Query), N number of people working on it will not enforce stress on the server. 

A complex scenario with even one user will stress the server.

Scalability Testing:

Objective is to find the maximum number of user system can handle.

 

Classification:

                                Network Scalability

                                Server Scalability

                                Application Scalability

 

 

Approach: Performance Tools

Compatibility Testing:

Compatibility testing provides a basic understanding of how a product will perform over a wide range of hardware, software & network configuration and to isolate the specific problems.

Approach: ET Approach

Environment Selection:

                understanding the end users application environment.

                Importance of selecting both old browser and new browsers.

                Selection of the Operating System.

Test Bed Creation:

                Partition of the hard disk.

1         Creation of Base Image.

2         When Should we start writing Test Cases/ Testing

3         V Model is the most suitable way to follow for deciding when to start writing Test Cases and conduct Testing

Testing Limitations

•          We can only test against system requirements

·        May not detect errors in the requirements.

·       Incomplete or ambiguous requirements may lead to                              inadequate or    incorrect testing. 

•          Exhaustive (total) testing is impossible in present scenario.

•          Time and budget constraints normally require very careful planning of the testing effort.

·        Compromise between thoroughness and budget.

·       Test results are used to make business decisions for release dates.

1 Test Stop Criteria

 

Minimum number of test cases successfully executed.

Uncover minimum number of defects (16/1000 stm)

Statement coverage

Testing uneconomical

Reliability model

1 Tester Responsibilities

 

Follow the test plans, scripts etc. as documented

Report faults objectively and factually

Check tests are correct before reporting s/w faults

Assess risk objectively

Prioritize what you report

Communicate the truth.

1 How to Prioritize Tests

 

We can’t test every thing. There is never enough time to do all testing you would like, so what testing should you do?

Prioritize Tests. So that, whenever you stop testing, you have done the best testing in the time available.

Tips

Possible ranking criteria ( all risk based)

Test where a failure would be most serve.

Test where failures would be most visible.

Take the help of customer in understanding what is most important to him.

What is most critical to the customers business.

Areas changed most often.

Areas with most problems in the past.

Most complex areas, or technically critical.

How can we improve the efficiency in testing

In the recent year it has show lot of outsourcing in testing area. Its right time to think and create process to improve the efficiency of testing projects.

The best team will result in the efficient deliverables. The team should contain 55% hard core test engineers, 30 domain knowledge engineers and 15% technology engineers.

How did we arrive to this figures? The past projects has shown 50-60 percent of the test cases are written on the basis of testing techniques, 28-33% of test cases are resulted to cover the domain oriented business rules and 15-18% technology oriented test cases.

BSL Modeof  Executing  Testing Projects

BSL Model Of executing Testing Projects

•             Project Startup and Planning

•             Analysis & Knowledge Transfer

•             Infrastructure Set-Up & Training

•             Testing

•             Project Closure

•             Communication Protocol

•             Regular Activities

   Support Activities

BSL Test Life Cycle

                Prepare Phase Plan

                Analyze the Application

                Creation of Test Plan

                Creation of Test Cases

                Review of Test Cases

                Test Execution

                Error Tracking & Reporting

                Data Collection and Analysis

                Verification of Defects

                Configuration management

                SQAR to conduct Final Inspection

                Final Deliverables

Analyze The Application

Find the domain of the application  

Find the environment of the application

Collect the number of Use Cases/ Functional Points

Select the Core/Associated/Maintenance modules and list the findings

Try to unserstand the system as a whole and trace down the logical flows

Collect the total number of business transactions

Understand the system from the end user point of view.

Classify all the attributes of the project.

Test Plan Items:               

                Introduction of company and client company

                Reference documents for the preparation of test plan

                Overview of the application

                Architecture of the system

                Scope of testing

                Types of  Testing

                                Definition

                                Approach

                                Start criteria

                                Stop criteria

                                Deliverable

                Training needs

                Schedules

                Resources

                Risk  Management

                Final Deliverables

Test Execution

Execution and execution results plays a vital role in the testing. Each and every activity should have proof.

 The following activities should be taken care:

                1.Number of test cases executed.

                2.Number of defects found

                3.Screen shoots of successful and failure executions should be taken in word document.

                4.Time taken to execute.

                5.Time wasted due to the unavailability of the system.