Business intelligence

ETL Testing Roll Back post – Ask yourself and get ready to move to ETL Testing world

Posted on Updated on

Hello Folks

It had been long time I posted something through my Blog L , I’m really sorry for that 😦

jobinterview

I have met a person unknowingly in a common place, we were discussing about ETL testing and other stuffs and I told him I’m writing blogs about ETL Testing.  He was surprised and said that he is one visitor of my Blog. I am happy that I have met a unknown person who reads and benefited with my blog.

I have got few requests ‘Can you please share some of Interview Q & A?’

I replied them

“If you have hands on experience and if you would have mentioned in your CV about you skills and responsibilities then why you wanted to know about Q&A in ETL Testing”

Guys who are trying in ETL Testing domain, are thinking that if they know how to write SQL can clear the Interview and can get the job. Folks that’s not possible. I request you to think that ETL testing is not a different entity; your job is testing on whatever technology it is.

If you are taking an interview, please go through your CV and be strong on what you have mentioned. If you are asked other than what you have mentioned then please raise your hand ‘I’m sorry, I haven’t worked in it’

Ok let me come subject what I wanted to discuss here!!!

Here I’m going ask questions to myself to test my technical capability, if I win myself then I can win anywhere in the Globe, right guys?

So try to ask as much questions as possible to yourself!!!!

Q1

INTRV: Asik, Can you please let us know your roles and responsibilities in your previous project?

CAND: Hello, I’m from here and blaw, blaw, blaw………

Q2

INTRV: Asik, How you can scale yourself in writing in SQL from 1-5?

CAND:  [—-Fill your Best Answer here——]

Q3

INTRV: Asik, What all are the tools that you used in your ETL Projects?

CAND: [—-Fill your Best Answer here——]

Q4

INTRV: Asik, Can you please explain me the ETL architecture?

CAND: [—-Fill your Best Answer here——]

Q5

INTRV: What all are the Joins that you have worked so far? And can you please write down joins using two tables T1 and T2?

CAND: [—-Fill your Best Answer here——]

Q6

INTRV: Asik, what is the difference between Inner join and Outer Join?

CAND: [—-Fill your Best Answer here——]

Q7

INTRV: Asik, can you please explain me about the significance of Data Types in ETL Testing?

CAND: [—-Fill your Best Answer here——]

Q8

INTRV: Asik, I have a column as VARCHAR from source but corresponding target column is INTERGET, how you can write a query to check the target column?

CAND: [—-Fill your Best Answer here——]

Q9

INTRV: Asik, what is the significance of the Staging tables in the ETL loads?

CAND: [—-Fill your Best Answer here——]

Q10

INTRV: Asik, what is the difference between, copy records and duplicate records?

CAND: [—-Fill your Best Answer here——]

Q11

INTRV: Asik, what is the significance of Oracle Control tables in ETL Testing?

CAND: [—-Fill your Best Answer here——]

Q12

INTRV: Asik, what does it mean if you get a NULL, Blank, Space or 0 from Source?

CAND: [—-Fill your Best Answer here——]

Q13

INTRV: Asik, I have a VARCHAR column from source, this column can allow 0, suppose if source is sending 0000 instead of 0 , and the rule is we should not allow this record into target table. Does our SQL will stop this record if we write the condition as [Column <> 0]

CAND: [—-Fill your Best Answer here——]

Q14

INTRV: Asik, what is the difference between the Primary Index and Primary Key?

CAND: [—-Fill your Best Answer here——]

Q15

INTRV: Asik, what could be consequence if the reconciliation failed in the Day_01 load or Day_02 load?

CAND: [—-Fill your Best Answer here——]

Q16

INTRV: Asik, Why you wanted to test the DDL of any table?

CAND: [—-Fill your Best Answer here——]

Q17

INTRV: Asik, what is the difference between Data Definition Language and View Definition?

CAND: [—-Fill your Best Answer here——]

Q18

INTRV: Asik, as a tester what all are the access you required? And do you know the SQL query syntax to get the access?

CAND: [—-Fill your Best Answer here——]

 Q19

INTRV: Asik, what is the difference between DB level access, Table level access and view level access?

CAND: [—-Fill your Best Answer here——]

Q20

INTRV: Asik, What all are the steps you take to validate the Source Files? What is significance of Source File Names, Header, Trailer and Detailed Records?

CAND: [—-Fill your Best Answer here——]

Q21

INTRV: Asik, if you could not able to open a file manually by double click due to the file size? How you will access the file and look at the records?

CAND: [—-Fill your Best Answer here——]

Q22

INTRV: Asik, What is the High Severity defect that you raised so far in any ETL project and why you have classified as Sev-01?

CAND: [—-Fill your Best Answer here——]

Q22

INTRV: Asik, What is the High Priority defect that you raised so far in any ETL project and why you have classified as Sev-01?

CAND: [—-Fill your Best Answer here——]

Q23

INTRV: Asik, Explain about Inserts, Updates, Logical Deletes in any given ETL Projects?

CAND: [—-Fill your Best Answer here——]

Q24

INTRV: Asik, Why and what is necessity of Reference tables in any ETL Projects?

CAND: [—-Fill your Best Answer here——]

 Q25

INTRV: Asik, Can you modify Reference table data if you have Update access to the reference tables?

CAND: [—-Fill your Best Answer here——]

Q26

INTRV: Asik, What all are the Steps by which you can achieve the test data for a given scenarios?

CAND: [—-Fill your Best Answer here——]

Q27

INTRV: Asik, What is the difference between the Time Variant and Non time variant tables in any ETL Testing projects and how you will test it?

CAND: [—-Fill your Best Answer here——]

Q28

INTRV: Asik, Can you delete a table or Drop a table? What happened if we Drop a tables?

CAND: [—-Fill your Best Answer here——]

Q29

INTRV: Asik, What is the significance of Views in any ETL testing projects?

CAND: [—-Fill your Best Answer here——]

Q30

INTRV: Asik, Does a table contain more than one Primary Key? And Does a table contain more than one Primary Index?

CAND: [—-Fill your Best Answer here——]

Q31

INTRV: Asik, DBA has granted Select access to DB, does it mean that you have select access to all the tables sits in that DB?

CAND: [—-Fill your Best Answer here——]

 Q31

INTRV: Asik, if the target column is Integer (30) can you load a Varchar (30) into it?

CAND: [—-Fill your Best Answer here——]

Q32

INTRV: Asik, target Date column is constrained as DDMMYYYY but from source we are getting it as MMDDYYYY? What will happen to load job?

CAND: [—-Fill your Best Answer here——]

Q34

INTRV: Asik, What does it mean by Full-Snap shot and historical records?

CAND: [—-Fill your Best Answer here——]

Q35

INTRV: Asik, What does it mean Catch up logic involved in loads and how you will test it?

CAND: [—-Fill your Best Answer here——]

Q36

INTRV: Asik, What is the difference between Load projects and Extract projects?

CAND: [—-Fill your Best Answer here——]

Q37

INTRV: Asik, if the target column is defined as Decimal (4, 3), can you load the values as Decimal (4, 5)? Or can you load the values as Decimal (4, 3)

CAND: [—-Fill your Best Answer here——]

Q37

INTRV: Asik, What is the difference between Union and Union All? And where we want to use Union and where we want to use Union All?

CAND: [—-Fill your Best Answer here——]

 Q38

INTRV: Asik, What is the use of Cross Join? And what circumstances you use the Cross Join?

CAND: [—-Fill your Best Answer here——]

Q39

INTRV: Asik, What is the AutoSys comment to trigger the job, abort the job, hold the job?

CAND: [—-Fill your Best Answer here——]

Q40

INTRV: Asik, What is the significance of Group by functions in SQL and where we will use the Group by functions?

CAND: [—-Fill your Best Answer here——]

Q41

INTRV: Asik, How you can find the duplicates in the Staging tables?

CAND: [—-Fill your Best Answer here——]

Q42

INTRV: Asik, What is the difference between SET table and Multi Set tables? From where we can get these details?

CAND: [—-Fill your Best Answer here——]

Q43

INTRV: Asik, Can one View table refer another view table to create a new view table?

CAND: [—-Fill your Best Answer here——]

Q44

INTRV: Asik, Can one View table refer another view table to create a new view table?

CAND: [—-Fill your Best Answer here——]

 Q45

INTRV: Asik, What all are the common SQL Syntax errors you encountered and how you are resolved it?

CAND: [—-Fill your Best Answer here——]

Q46

INTRV: Asik, Do you have any idea what is Data Modeling?

CAND: [—-Fill your Best Answer here——]

Q47

INTRV: Asik, What is the difference between the Physical Design and Logical Design?

CAND: [—-Fill your Best Answer here——]

Q48

INTRV: Asik, What all are the Items available in a generic S2T?

CAND: [—-Fill your Best Answer here——]

Q49

INTRV: Asik, what does it mean if a column is mentioned as Value column in the S2T?

CAND: [—-Fill your Best Answer here——]

Q50

INTRV: Asik, Have I answered all of my basic questions to go to advanced questions?

CAND: I am still thinking and trying to answer few questions J

What is Quality Assurance & Quality Control? and Who is PQA, CC, DPA in QA ???

Posted on

Hi All,

Quality Assurance and Quality Control

quality-blocks1

 

Being a Test Professionals we should know about QA and QC. What we are doing is actually Quality Control related stuff and what people involved in bringing up CMMI, ISO standards are Quality Assurance. Quality Assurance is more likely related to maintain the process in any given Project or Programme. Quality Control is nothing but giving the right product by verifying the requirements are covered and working as expected.

We know there are multiple levels of testing methods are spoken in the testing world and we have the methodology by which we need to execute them like System Test, Integration Test etc., and Methodology like Water fall, Iterative etc.,

Quality Assurance:

Let me explain what I know and aware of Quality Assurance:

There 3 different roles who will be responsible for assuring the process in any projects.

  1. PQA – Project Quality Analyst
  2. CC – Configuration Controller
  3. DPA – Defect Prevention Analyst

Project Quality Analyst – PQA role

A person who involved in this role needs to know the pre-defined industry standards of that Organization.

PQA’s Roles and Responsibilities

  1. Documents Naming Convention met as per the Industry Standard
  2. Names of who prepared , reviewed , approved the deliverables
  3. Reviews happened across all the Customer facing documents
  4. Review defects are found, fixed, verified and captured for Metrics
  5. Checking whether all the deliverables are kept in a Common place where the stake holders can access
  6. Checking all the necessary deliverables are prepared by the project team
  7. Checking the actual project delivery date and the date mentioned in the documents are same
  8. Checking the Stake Holders names, document owner names are mentioned correctly in all customer deliverables
  9. Differentiating the Customer facing deliverables and Internal audit specific deliverables are as per the industry standards
  10. Verifying the Entry Criteria and Exit Criteria of any Levels in SDLC are met and collecting the proofs for the same
  11. PQA’s will be involved in all levels of SDLC

Business Analyst Teams will have separate sets of Deliverables like Business Requirement documents, Business Understanding Documents, Requirement Traceability documents etc.,

  1. Development teams will have separate sets of Deliverables like High Level Design, Low Level Design , Functional Specifications etc.,
  2. Testing teams will have separate sets of documents like Test Plans, Test Conditions

The PQA should validate all the documents that supposed to be delivered to the Clients and maintain for internal audits

CC – Configuration Controller

Configuration Controller who controls the versions and the placement of the documents in tools like VSS – Microsoft Virtual Source Safe or Documentum etc.,

Configuration Controller Roles and Responsibilities

  1. CC’s are responsible of Creating the folder structures in VSS or Documentum

Like, in any Projects the following folders will be created to maintain the projects deliverables

  1. Project Kick off
  2. Minutes of Meeting
  3. Requirements
  4. Review Log
  5. Development

1.1.  High Level design

1.2. Low Level Design

1.3. Issue Log

1.4. Emails

6. Testing

1.1   Unit Testing

1.2   System Testing

1.3   System Integration Testing

1.4   User Acceptance Testing

6.Production Support

CC’s will have the Admin rights to Grant and Revoke access to folders.

Developers should not have access to the folders related to Testing and vice versa

  1. CC’s will maintain the Check in and Check out of the documents that goes into VSS
  2. CC’s will maintain the relevant documents are kept in corresponding folders in VSS

DPA – Defect Prevention Analyst

Defect Prevent Analysts will maintain the defects across the SDLC. For any raised defects the work flow should be maintained. Proper comments for those defects should be given when they are created. All high severity defects should be fixed from one Phase to next phase to being with.

As testers when we raise defects we need to concentrate on applying Defect Cause and Defect Type in any Defect Management tool. This will help DPA’s to classify the defects and comes up prevention tips.

Defect Cause – What is the root cause of the defect that is,

  • Is the defect caused because of the Upstream Source Data or Test Data
  • Is the defect caused because of Incomplete of Missing requirements
  • Is the defect caused because the Inconsistent Requirement
  • Is the defect caused because of the Code discrepancy
  • If you find any anomalies in any Documents then raise the defects to Artefacts
  • If any of your defects leads to Changes in the Requirement then please raise them as Change Request – CR can be on the Actual Business Requirement or on Design Changes.

Defect Type – Classifications of the Defects that is,

  • Is the defect related to Data Error
  • Is the defect related to Application Code
  • Is the defect related to Work Request
  • Is the defect related to Change Request
  • Is the defect related to Deployment
  • Is the defect related to Specification
  • Is the defect related to Artefact
  • Is the defect related to Production
  • Is the defect related to Process
  • Is the defect related to Environment
  • Is the defect related to Specification
  • Is the defect related to Requirements
  • Is the defect related to Reviews

DPA’s most prominent work is to prepare the CAPA – “Corrective Analysis and Preventive Actions”

DPA roles and Responsibilities

  1. DPA’s will collect the Metrics related to Defects in a periodic manner – Weekly, Monthly or Ad-hoc
  2. DPA’s will collect the defects by Defect classifications like in a given period of time how many defects are raised in Reviews, Code, Requirement Change and collects the cause of the defects
  3. Then using the metrics that was retrieved from any Defect Management tools, they design the Fish Bone diagram by filling the necessary details
  4. Using the Statistical tools like Minitab, they calculate the Defect Density of the defects from each phase
  5. Then they will create the Prevention actions on the areas where the Defect Density is above to the threshold limits.

Suppose if your Organization has the Defect Density threshold limit as 0.5 and your Defects under Review defect type is more than 0.5 then they will ask the Reviewers to do their work better to minimize the review defects at any levels of SDLC.

Cheers – Asik

Application Life Cycle Management Tool – HP Quality Centre 11.0 for Beginners

Posted on Updated on

Hi All,

HP Quality Center is quality management software offered from the HP Software Division of Hewlett-Packard with many capabilities acquired from Mercury Interactive Corporation.[1] HP Quality Center offers software quality assurance, including requirements management, test management and business process testing for IT and application environments. HP Quality Center is a component of the HP Application Life cycle Management software solution set. (Taken from Wiki)

Qualitycenter

I have been working in QC right from version 9.0 to 11.0. There are so many key functionalities added to the new version.
I hope most of them worked in Quality Centre as a Management Tool. Here I will explain for those who want to learn QC and its modules.

QC is a web tool, user can access from anywhere but only in INTRANET (specific to your company we). You cannot access outside of your company. Projects will be created and by QC Admins and the access to the projects will be given by QC Admins as per your Role. Quality Center has so many functionalities and a user cannot access all of them and can use most of them based on the access given.

  1. If you are a Test Analyst, you will not have access to delete any entity that’s created folder level.
  2. If you are a Test Analyst you will not have access to move the defects from Analysis status to retest status.
  3. If you are a Developer you will only have access to move the defects from Analysis status to retest status.
  4. If you are a Test Lead / Dev Lead , you will have most of the access
  5. If you are a Test Manager you will have access to all the functionalities existing in Dashboard module

Logging into Quality Center:

  • Use the URL (Web Link)
  • Login Name – Enter your User Id (Mostly Windows Login user Name)
  • Password – Enter your password (You will be entitled to a Default password @ the time of user id creation and you can change the password by Tools à Customize à Change Password.
  • After entering User Name and Password and Check the Authentication of your user name and password by clicking Authentication button.
  • After Clicking it, it will show the Domain and Project Name that is assigned to you, If you are assigned to more than one project the use the drop down and select the Project that you wanted to work.
  • You can change the project from the QC Home Screen (Top Left Corner) Change project.

Module 1à Dashboard Module:

In Dashboard Module user can see two types of View, Analysis and Dashboard View. Analysis view can be used to get the reports for the particular Test Sets, Defects from particular Test Sets and Reports from particular project. Dashboard views will be used by the Test Managers, and they can create a report across programme level. In each View you will have two folders, Public and Private. If you create any Reports under Private then it’s visible to the creator only but if you create a report in Public then it’s visible to all the users who are accessed to the Domain and the Project.

1. Analysis View

In this user can create a Graphical Report or Standard Report. There is a button (+), if you click you can see

Graph Wizard, New Graph, New project Report, New Excel Report and New Standard Report.  Click on the type of the report that you wanted and choose where you wanted to post the report either in Private or in Public.

Now I am using New Graph functionality.

  1. Click on New Graph (Under Private section)
  2. Upon clicking you will get a New Window where you need to select1.
    1. Entity (Defects, Tests etc.,)
    2. Graph Type (Progress , Age, Summary, Trend)
    3. Graph Name (Project Name  – User Specific)
    4. After creating the Test Report, you can see the Project Name that you have created under the Private Section. (Left Side of the tool)
    5. Click on the Project then check your right hand side, you can see 3 tabs, Details, Configuration and View.
    6. Details tab – the use can’t edit anything, its auto populates the values that we had given when created the project.
    7. Configuration tab – this is the place that we need set up what we need in the report.
      1. X- Axis – Choose from the drop down what you need to see in the X- Axis
      2. Y- Axis – Choose from the drop down what you need to see in the Y- Axis
      3. Grouped by – Choose the entity by which the X-Axis and Y-Axis needs to be grouped by
      4. And you can see a button Filter (Funnel Symbol) click on that then you will get a Pop up window where you need to set the target folder from where you get the data for the Graph.
      5. In that window you will have Filter and Cross filter tabs. Choose Cross Filter tab and you can see different sections like Defects, Test Sets, Requirements etc,
      6. Choose the Entity that you need, right now I want to show to my lead how many test cases that I have executed over a period of time so I will choose Test Set section.
      7. Under Test Set section you will have 3 radio button, click on the radio button that shows next to None radio button.
      8. At the end of the text box you will have (…) 3 dots. Click on it. It will take you to the Test Lab where you can see all the Test Sets created under a given domain.
      9. Select the Test Set that you want from the Test Set tree.
      10. And Click Ok now Click on View you can see the Graphical representation of the data that you have in your Test Lab.
      11. By Clicking Data Grid on View tab you can see the data in Numbers.

Module 2 > Management Module:

Management module will allow the Test Managers or Test Leads to set up the Cycle Start and Cycle End date to the given Test Set in Test Lab. A user can’t access a Test Set after the Cycle End date. This module can give the data to drive the Test Metrics related to Effort.

Module 3 > Requirements Module:

Business requirements are captured in this module. You can add the actual business scenarios or you can add the test conditions as requirements. You can add requirements by simple clicking the Add New Requirement button or you can upload using the Excel upload add in manager.

By capturing the Testable requirements we can achieve the requirement traceability. Suppose if you are testing a log in screen then you will set up a test condition as

‘Verify the Login Screen is working as expected by giving valid user name and password’

This test conditions can be furthermore explained in detail steps under Test Plan module.

The key points that we need to maintain when setting up the Test Conditions are

  1. We need to set the Priority to each test conditions
  2. We need to write the meaning full test condition name
  3. We need to choose the correct requirement type from the drop down box (Right top most corner)
  4. Author name will be auto populated from your Login credential (if I logged in then my name will be populated as Author name)
  5. You can’t delete a created Test Requirement whereas you can Cut and Paste into Recycle Bin folder. (Requirement, Test Plan and Test Lab modules will have the Recycle Bin to keep the rubbish contents)
  6. The created Test Conditions will be mapped to Test Cases from the Test Plan module. If you have not mapped then you can see Direct Cover Status as Not Covered, if you have mapped but not executed then you can see it as Not Completed, you have executed the Test Case if the test case is Passed or Failed then you can see it in Direct Cover Status.

Module 4 > Testing

Under Testing module we will have below mentioned tabs

Test Resources – This tab will be used for QTP to keep the Scripts

Test Plan – Marinating all detailed steps involved in particular Test Conditions

Test Lab – We will pull the test cases under Test Lab for Execution

Test Plan

We can simply add a Test Case using New Test button or we can write the test cases in spread sheet and upload it into QC using QC Excel Add in Manager.

The key points that we need to maintain when setting up the Test Plan are:

  1. Click on the New Test button , you can see a window opened
  2. Enter a Valid test name, mostly it should match with the name that we had given for test conditions
  3. Select the type of the test that we are performing (right top most corner)
  4. If you see any sections are marked as Red then it’s a mandatory column and you need to enter the value.
  5. Select the SDLC phase from drop down box like System Test, E2E Test, Regression Test etc
  6. Select the Priority from the drop down box, this priority should match with the priority that we had given for Test Conditions.
  7. Select the Test Type from drop down , what kind of testing that you are doing, like Functional or Non Functional etc.,
  8. Capability mostly Do Not Know
  9. Select the Application that you are going to work in and this will be pre-defined by the QC Admin team
  10. When you are creating the Test Case please keep the Reviewed question as Not Reviewed and assign the Reviewer name.
  11. Once you save the Test Case the Auto Email will be sent to the Reviewer.
  12. Now reviewer will be reviewing the test case and will set the status as Reviewed.
  13. Under the Description Section we need to enter the following details, these details will be pre-defined by the QC Admin team and will be populated to all the resources associated to the domain.

Objective:

Description:

Pre-Requisites/Data Setup:

Expected Results:

  1. We have set up the Test Case, now we need to map this Test Case to the Test Conditions. To do that please Click on the Test Case name from the left side pan so that you can see the below mentioned tabs on your right side
    1. Summary – All the details related to that test cases (what we had given when creating) will be populated
    2. Design Steps –  will have the Details steps that will be performed on the Test Conditions
    3. Parameters – the data that needs to be passed for Automation Frameworks
    4. Test Configuration – auto populates the values
    5. Attachments – if you like to add any documents that related to this particular scenario you can add under this tab
    6.  Requirement Coverage – from this tab we can search the Test Requirement (test conditions) that related to the test case and drag and drop using Select Requirement button
    7. Linked Defects – Defects can be attached to Test Case or to Test Steps. So the attached Defects will be shown under this tab
    8. Dependency – This tab is used for Automation tests, where you need to create dependency between different modules.

PS: After mapping the Test Conditions to Test Cases please go to Requirement module and check the Direct Cover Status, if the Status is Covered then leave it else Refresh it reflect the status.

Test Lab

We have set up the Test Conditions and Test Cases and linked them now we need to pull them into Test Lab and make them available to Test Execution.

Before pulling the Test Cases into Test Lab, please create a Test Set folder using New Test Set button. By Clicking it you will be getting a window where we need to enter the logical test set name.

Now we have created the Test Set and pull the test cases into test lab by going to Execution Grid tab in the Test Lab module and Click Select Tests this will take you to Test Plan module where you browse the test cases that you wanted to move.

Pull all the test cases that you wanted. All the modules in QC you will have the Select Columns button to customize the details that you wanted to display in the Screen, so play across the needed columns and make them available in your screen.

The key points that we need to maintain when setting up the Test Lab are:

  • Running the Test Case
  1. You have Run button to execute the test case. In Run we will have Run With Manual Runner and Continue Manual Run.
  2. By simply clicking Run will lead you to Run with Manual Runner option. Now you can see the test steps that are written in Test Plan module.
  3. To pass the Test Case, we can click CTL+P to fail the Test Case CTL+F. Please attach a Test Evidence for each Test Case that you are executing.
  4. Do not execute the test cases more than one, if you do then it will create an Instance for each time that you are executing this will annoy your test reports
  5. If you kept a Test Step as Not Completed for any reason @ first time of execution and you wanted to execute that particular test step then choose Continue Manual Run under RUN tab.
  6. If you are failing a Step then you can create a Defect from Test Plan module.
  7. After failing the Defect, go to Linked Defects tab and Click Add (+) button it will take you to defect module from there you can create defect and it automatically linked to that particular step.
  8. If you are keeping a Test Case as Not Applicable then you need to attach a Evidence why this test cases are chosen as Not Applicable
  9. If you are keeping a Test Case as Deferred  then you need to attach a Evidence why this test cases are chosen as Deferred

Module 5 > Defects

Defects can be added from Test Plan module or from Defect Module. If a Defect is attached to a test cases then please do link the test case to this defect. You can raise an Orphan defects without linking them to any test cases.

Go to defect module and click Add Defect button then you will get a window where we need to input the defect details.

  1. Summary – Brief description about defects
  2. There are few columns will be auto populated as per the QC configuration
  3. When we raise the Defects it will be in New Status as per the Defect Life Cycle
  4. Defect Type – We need to select the defect type from the drop down like Application Code, Requirement Defect etc.,
  5. Discovery Phase – We need to select on which phase the defect is Injected like System Test, UAT, E2E etc.,

And there will be more than 10 mandatory fields that we need to enter as per the project specific details.

  1. Description – Testers needs to give the detailed description about the defect in Description section

Test User Name/ID
Pre-requisite
Steps to Replicate
Expected Result
Actual Result
Test Data Reference
Test Case Reference [Test Case Name (Step number)]

 Defect Life Cycle in QC

  • New – (When the defect is created)
  • Analysis – (When the defect is moved to Developers)
  • Fix – (When the fix is given by the developers)
  • Deploy –(when the developers deployed the defect fixed code into test environment)
  • Retest – (When the code is ready for retesting)
  • Closed – (when the defect is retested and closed by the testers – once you closed the defect we can’t modify the defect.

Note: User can’t jump from one status to another status by by-passing one status in between.

Setting up the Priority and Severity to the Defects:

Do you remember we have kept Priority to a Test Requirement when we have created in Requirement module? I have raised a Defect that related to the Test Case which was set to Priority Low then keep Defect priority as Low.

Cheers – Asik

Importance of Non Functional Testing in Data warehouse

Posted on Updated on

Hi All

In this post I would like to share my knowledge in Non Functional Testing in Data warehouse testing.

car

There are different types non-functional testing that we do in testing world, some of them is

  1. Baseline testing
  2. Compatibility testing
  3. Compliance testing
  4. Documentation testing
  5. Endurance testing
  6. Load testing
  7. Localization testing and Internationalization testing
  8. Performance testing
  9. Recovery testing
  10. Resilience testing
  11. Security testing
  12. Scalability testing
  13. Stress testing
  14. Usability testing
  15. Volume testing

To me Non Functional testing is something like which will not give any business values; It’s something like dealing with the environment. When we extract the data from heterogeneous source system, we might need to think of handling

Verifying the volume of the data

Any business can’t ensure what could be the volume of the data that they will send. They can say approximately, Our Code should have the capability of pulling the maximum number of data that they source system can send at any point of the time. To manage the Volume of the data, Teradata has the feature called M-Load and T-Pump. When developers designs the system they fix a limit by which data will be loaded into Warehouse.

Example:

  • M-Load – If we get a data file with 100 records then the records will be loaded by M-Load functionality
  • T-Pump – If we get a data file with less than 100 records then the records will be loaded by T-Pump

What we need to test here is, send a file with 100 records and check records are loaded by M-Load. This can be verified using the Load Job Names.

Verifying Date and Time of the Data file arrival to the Unix Landing directory

Most of the Companies will not function on Week Ends, Public Holidays so our source systems will not send any transactional data on those days. Because of the phenomenon developers will design their jobs to archive any files coming on these days.

Normally, Monday’s transactional data will come to us for loading on Tuesday early morning and it will end on Fridays transactional data will hit us on Saturday early morning.

We as testers need to verify these schedules are working as per the specification. This can be achieved

  • sending a file on Week End and check this file is archived
  • Sending a file on Public Holiday and check this file is archived
  • Verifying Mondays transactional data received on Tuesday morning until on Saturday morning

Verifying Purging and Truncate Loads

I have already mentioned about Purging and Truncate loads in my earlier blogs.

Purging –  The AutoSys jobs will Purge the data leaving the required data in staging table. Suppose if I have loaded 10th,11th ,12th of January data into staging table and when I load 13th of January data, the 10th of January data will be purged.

Truncate –  Simple load day_01 data and when you load day_02 data  they Day_01 data will be deleted

We as testers need to verify the Truncate and Purging is happening as per design requirement.

Verifying File Watcher Script

There will be File Watched Script that will look for files until it arrives the Unix Landing directory. Source system is promising us that they will send Day_01 file on 10-01-2013. So we have set the Date in File watcher Script. Source System sent the records on 10-01-2013 , now our File watcher Script will look the date from the file header, if both are matching then it will process the file into Staging table. Source system failed to send the data on 11-01-2013, our file watcher job will look for the file on 11-01-2013 for given time interval if its not arrived then automated Email will be sent to the concern source system saying the file is not arrived

So we as testers needs to verify the File watched job is working as expected.

Cheers – Asik.

My Imagination – How Scientists and Software Testers are related to each other?

Posted on

Hi All,

Hope you guys had  wonderful X-Mas holidays :-).

Image

As we all know all the Inventions are made by Asking more and more questions.

If Newton would have not asked ‘Why Apple fallen down from the Tree’ then we would have not have Gravity Theory.

If Archimedes would have not asked ‘Why waters spilled out of the Tub when he was bathing?’ then we would have not have Archimedes principle.

Likewise if testers asks questions on applications like Why or How? if he/She finds the answer that is not matching with what it supposed to be then IT IS A Discovery, So we are Scientists 🙂

As far as I consider testing the application is asking questions about the application that I am testing – is perfect way of finding software anomalies.

Each functional defect that we stopping by our testing is just saving the production fixes but if we stop any Business Gaps then it saves the whole Business Need. So before kick of the testing make sure we know the Business Needs.

You will get a Functional Defect – If you ask How things are working? then it will be already mentioned in all the functional specifications ! if some functionality is missed out in the developed code can be found.

You will get a Req/ Specification / Design / Defect If you ask why things are working ? then you will need to check the Business Requirements, if you feel something is wrong then you will encounter few anomalies that may be from all the specifications related to that particular work request.

In this post I wanted to explain how Important the Domain knowledge is required for a Testers.

Domains like Banking, Health Care, Manufacturing, Insurance etc., All these domains are closely related to us.

To modernize these functional areas

> Business peoples will write Specs to cater the business needs as Business Requirement Documents.

> Considering the Business Specifications Solution Designers will prepare Functional Specification Documents.

> We testers and developers will refer the above documents and Develop and Test the application.

How you can learn Business easily ?

If you are working in a Banking Domain, you got Loan functionality to be tested in your Client website, then please create a Loan application in real time with your bank or with some other bank’s on-line application ,

If you are working in a Health Care Domain, you got Inventory functionality to be tested then go to nearby Chemist (Medical Shop) pick some medicines from here and there and go for billing and check how the shop keeper handling your goods.

Like above two examples, what ever business that you are testing, please do imagine that YOU ARE ALSO GOING TO USE THIS PRODUCT. Would you accept a Defective product from your manufacturer, ‘No’ right then your testing will be perfect.

I hope all the readers know about Validation and Verification ?

Let me tell you what I think about it,

Validation means, we need to verify all the documents that related to the given functionality are acceptable and valid.

Verification means, by validated specifications, the Code is written and its verified by us.

In Data warehouse world, the Specification documents are needs to be validated, because even a simple mistake will create a huge problem @ the end.

For an Example,

In warehouse we keep amount columns are in Negative (Bank Owe’s to us) as well as in Positive numbers (We Owe to bank).

Business Need – All the transactions of the day to be extracted

For extracts If specification documents asks us to pull the records where Balance > 0 then you will get the customers who are owing money to the bank.

So even a Single Symbol matters a lot !!! Before we start the Verification we need to Validate first!!!

Lets Discover along with Finding defects 🙂

Cheers – Asik

Chapter 7 – Data warehouse and Test Condition and Test Case design- Reuse or Automate using EXCEL

Posted on Updated on

Hi All,

In this blog I am going to explain how to write Test Conditions (What you are going to do?) Test Cases (How you are going to do?).

Image

I am using a Excel Macro that will generate the TCN and TCA easily, the only thing is you need to configure.

I am not able to attach here – if you need it please drop me an EmailTo – aashiqamrita@gmail.com (please find the screen shots at the end of this blog)

One who doesn’t like when he asked to write test cases 😦 Let me explain you how to set up Test Conditions (TCN) and Test Cases (TCA) .

As I said already in my earlier blogs we have below types of DWH testing

1. Loads from file to staging tables to IM tables
2. Loads from table to staging tables to IM tables
3. DIMN/FACT projects
4. Extract projects
5. Reports

You need to know how to reuse your TCN and TCA ! write it for one project and use FIND and REPLACE for other projects !! confusing?

For the Loads from file to staging tables to IM tables we will set up TCN and TCA to validate Source file, verification of data load into Staging tables and verification of data transformation of load into target tables. Suppose you have prepared for PROJECT_A where Source is file is File_1.DAT and Staging table is Staging_Table1 and target tables is Target_Table then use the same test cases by replacing File_2.DAT and Staging table is Staging_Table2 and target tables is Target_Table2 for Project_B.

Don’t Understand ??

For project A I designed the steps and for Project B I replaced with attributes related to Project B

Source File Validations (Steps) :

Image

Staging Loads :

Verification of Staging is very easy, It will be one to one mapping, loading all the data from Source file or system into Intermediate tables.

Image

Staging Data into Target Tables:

Staging (more than one STG) data will be loaded into one or more than one Target tables. But we need to write test cases for each tables.

Please cover below scenarios:

1. Reconciliation check – record count between the STG tables and target tables are same after applying filter rules

2. Inserting a record which is not loaded into target table for given key combination

3.Copy records , sending same records (same key ) which is already loaded into target tables – should not be loaded

4. Updating a record for a Key when Value columns changed on Day_02 loads

5. Logically Delete the records in the Target tables

6. values loaded by ETL Code

7. Values loaded by Process Tables

8. Values loaded by Reference Tables

(If you get any special scenarios please add them )

Write test cases for Target Table_1 and reuse them for other tables.

Image

In the Excel sheet , just configure the rules as you require feed the Source and Target column, and Click Create button it will give you the Test Case or Test Conditions as you configured.

Configuring :

Image

Click on Create then 🙂

Image

Hope you guys understood How to write test case and test conditions for BI Projects.

See you guys in my Next blog.

Cheers

Asik

Chapter 6 – Test Data Fabricating in Business Intelligence

Posted on Updated on

Hi All,

Thanks for viewing my blog so far ! I wanted to keep sharing my knowledge as much as I can – Happy Reading 🙂

In this blog I am going to explain about Test Data. In other testing practices you don’t need predefined test data, as we are the user who updated the values either in the Front End applications or some other applications. But in Data warehouse we are the ones who are completely depend on our Upstream (front end application) applications. We will test what all are the data that used in the Upstream guys.

Image

We testers are the intermediate guys who buys the data from one team and validate , transform and sell it to other guys 🙂

Image

Let me explain you in normal terms, you are doing a business , you need to buy the goods from your buyer and sell to u r customer!

What all you need to do?

– Need to buy quality products

– Need to check the count what you ordered

– Need to check the specification what you asked for?

– Need their support in fixing the issues

– if they cant fix it, then you need to repair it and sell it to your customer

Now come to our Business Intelligence scenarios,

As a tester what you need to do with the data ?

– Check the quality of the data (is as per the specifications)

– Check the count is correct using Trailer records (refer my Source Data validation Blog)

– Check if the data is not as per requirement then ask upstream guys (the people who gave the data to you) to fix the data issues

– Check you have got all the data that you required, if not ask your upstream to provide more data, suppose if the deny, please manufacture your data 😦

As I said already in my earlier blogs we have below types of DWH testing

1. Loads from file to staging tables to IM tables
2. Loads from table to staging tables to IM tables
3. DIMN/FACT projects
4. Extract projects
5. Reports
Test Data fabricating for Staging table to Target tables are quite easy, but for Extract Testing and Reports testing are bit difficult 😦

For most of the positive scenarios Upstream will provide the data for us !! but we tester wants to break the code so we will have few negative scenarios for which we will not get data from upstream. And for the scenarios which will happen once in a while we will not get the data so we need to create the data.

For Extracts, the Teradata SQL codes will be written by joining more than 10 tables , each tables will have its own WHERE clauses. After extracting the records using the SQL ,  you found that few scenarios are missing then you need to set up a data that needs to flow across all the tables.( Will be explained in my Know about Extracts Blog)

As I said in my Test Environment blog about the access, the testers should have Create table access in Test Environment and Select access to View Data Base.

So using Create Table access we can create our own test data as follows:

Please read the image very carefully, if you don’t understand please send me an EmailTo : aashiqamrita@gmail.com

Image

1 Create a TEMP table in you test Environment using Create Table Qry.

2. Insert sample records into your TEMP tables using INSERT INTO Qry.

3. Modify the data as per your requirement

4.Insert back the modified data into your actual table.

5.Your test data is ready to use 🙂

Hope you guys understood the test data manufacturing technique . If you guys need more support please comment in this blog 🙂

Thanks –

Happy Reading

Asik