SQL

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

Advertisements

Chapter 9 – Understanding SQL by Selecting Indian Cricket team for South Africa tour using SQL

Posted on Updated on

Hi All,

Hope you have understood concepts what I discussed so far. Thanks for reading my blog !!!

Today I wanted to explain how we can write SQL – When I was new to Data warehouse , I asked Google about SQL. She gave so many links about SQLs. Hope you guys also have done the same. Now I will explain in my way about all the techniques in SQL using our Indian Cricket team.

Anyone wants to know What is Structured Query Language (SQL) then please ask Google, she will explain better than me 🙂

I will explain how we can easily understand and use SQL effectively

In SQL you will have 3 Segments

Select Clause –

1.You can mention the columns from the tables

2.You can set Conditional statements , Case When, IF and Else, Counters etc.,

3.You can use SUB STRING, CASTE Functions, TRIM functions etc.,

From Clause –

1. Here you need to mention your Database.Table Name (alias name can be given for the tables)

Where Clause – 

1. Here you need to mention what all records you need from your tables , using the relational operators (=,<>,>=,<= ,and, or)

Now you got the result using Select From Where clause – do you want to join this result with you neighbor table then use the Joins (Inner Join, Left Join, Right Join, Cross Joins)

Indian board of directors wants to see the eligible players from different states:

Image

In India we have 28 States and 7 Union Territories,each state has their own Cricket Club and connected to Indian Cricket board.

Each State team details will be kept in their own Tables and Director of Indian Cricket team will join all the tables and pull the final list of the Players.

To make it simple I will use 3 States – Tamil Nadu, Bangalore, Delhi.

Team Tamil Nadu:

In Tamil Nadu team Ian and Arya are retried so the director should not see them in his final team list

Image

In this team director wants players are

1. Aged below 40

2. Minimum 10 Matches

3.All rounders

4. Only active players

The SQL :

SEL Team,Team_ID,Player_ID,Player_Name, 

CASE All_Rounder WHEN ‘Y’ THEN ‘SELECTED’ ELSE ‘NOT SELECTED’,

END AS SELECTION,AGE,No_of_Matches, Hundreds, Fifty’s, Bating, Bowling, All_Rounder,Effective_from,Expired_On,

FROM

TEAM.TABLE_TAMIL_NADU as Tamil_Nadu

WHERE

AGE<=’40’ AND All_Rounder=’Y’ AND and Expired_On=Cast(’31-12-999′ as Date Format ‘YYYYMMDD’)

Just think using the rules and query what all are the records you will get from Tamil Nadu team!!!

Team Bangalore:

In Bangalore team Mickey and Raghav are retried so the director should not see them in his final team list

Image

In this team director wants players are

1. Aged below 30

2. Minimum 20 Matches

3.All Bates man

4. Only active players

The SQL :

SEL Team,Team_ID,Player_ID,Player_Name, 

CASE Bating WHEN ‘Y’ THEN ‘SELECTED’ ELSE ‘NOT SELECTED’,All_Rounder ,

END AS SELECTION,AGE,No_of_Matches, Hundreds, Fifty’s, Bowling, All_Rounder,Effective_from,Expired_On,

FROM

TEAM.TABLE_Bangalore as Bangalore

WHERE

AGE<=’30’ AND All_Rounder=’Y’ AND and Expired_On=Cast(’31-12-999′ as Date Format ‘YYYYMMDD’) and Matches <=’20’

Team Delhi:

In Tamil Nadu team Sachin and Agarker are retried so the director should not see them in his final team list

Image

In this team director wants players are

1. Aged below 25

2. Minimum 30 Matches

3.All bowlers

4. Only active players

The SQL :

SEL Team,Team_ID,Player_ID,Player_Name, 

CASE Bowling WHEN ‘Y’ THEN ‘SELECTED’ ELSE ‘NOT SELECTED’,All_Rounder ,

END AS SELECTION,AGE,No_of_Matches, Hundreds, Fifty’s, Bating, All_Rounder,Effective_from,Expired_On,

FROM

TEAM.TABLE_Delhi as Delhi

WHERE

AGE<=’25’ AND All_Rounder=’Y’ AND and Expired_On=Cast(’31-12-999′ as Date Format ‘YYYYMMDD’) and Matches <=’30’

Now the director got the results from each of the Tables. Now he wants to join all them and load it into a Selection table.

Its very simple , all alias names used to get the columns from their tables, and the column Team is key for all the three tables and used for joining the tables.

Select Final_List*

from

(

(Copy the Tamil Nadu Query) as team_1

Left join

(Copy the Bangalore Query) as team_2

on team_1.Team=team_2. Team

Left join 

(Copy the Delhi Query) as team_3

on team_2.Team=team_3. Team

) Final_List

Now the Director got the team he wants !!! India team is now ready for South Africa trip 🙂

Hope you guys understood the scenario, use this as base scenarios and play your cases with the help of our friend Miss Google.

Cheers

Asik

Chapter 3 – Setting up Test Environment for BI Projects

Posted on Updated on

Test Environment:

Image

In general BI Projects will have 3 environments

Production – where our tested code will function in real time

Development – where developers develops and Unit tests the code

Test Environment (System Test / SIT / E2E / UAT) – where the developed code will be deployed for testing

Setting up a Test Environment for Business Intelligence project is critical because the data for different level of testing is different. Developers will develop the ETL code in DEV (development) environment and when the testing phase kicks of they will point their codes to Testing environments.  

Why we need a separate environment? 

1. ACCESS and Test Environment

Because Developers are the one who designs the code and they will keep on changing the code until it works. We don’t have the version control in Data Stage or in Teradata. The development environment is the open space for all the Developers involved and there are high possibility of irregular updates on the code so the DEV environment is loosely controlled. Developers will have INSERT / UPDATE / DELETE access to all the designs in DEV and Test Environments. But testers will have only VIEW access on VIEWS (will explain what is Table and View in my next blog). Tester can only verify the data is as per ETL Code , they cant update any record to make them correct as per requirement. And developers also should agree what ever defects found in our environment should be open in DEV environments.

2. Data and Test Environments 

Data that used for Developing the code should be different to the data is used for testing why because developers used to create Test Data for UNIT testing, they are happy when the functionality is working fine as expected. So they are not interested source data quality. If any data causing trouble to them , they simply delete it and load the rest of the data. But testers should be very conscious about the data because data is the key for us to proceed the testing. So testers should have their own data in their own Environment. They should not depend on the source data that relies on Development environment.

 What all components needs access for a Tester ?

1. File Landing Directory – If your project is File to Table load then Source system will send the Files to a specified location. This location is different for both Development and Testing. Testers should get the access to this Directory.

2. Select ACCESS to Source Views – Tester can easily check whether they have Select access to the tables by simply querying the tables.If you are not having access to the tables then please create a Grants statement and send to your DBA.

[GRANT SEL ON DB_Name to User_ID;]

Good practice availing the access:

  1. A tester should run his queries in Views not in Tables, so Leads should make sure that the testers should not have Select access to tables before they start execution.
  2. Developers are creating Views over the tables, and when they deploy the code into PRODUCTION their code will points to the VIEWs not TABLEs.
  3. Developers might have introduced few filter statements in Views in order to stop duplicates, which might cause errors. Suppose if testers are tested the Target data using Tables then the errors related to Views could not be identified.
  4. Testers should not have Insert, Update, Delete access to Source and Target tables.
  5. Testers should have Create table access on Test Database for manufacturing data (will be explained in Test Data Management Blog)

Availability of the Source Data in Test Environment:

If the data load is into Existing warehouse tables then check the historical data is available in the Test environment by simply selecting the table. If there no records in test environment then request developer to copy sample records from PRODUCTION data into Test Environment.

[INSERT INTO TEST_DB.TABLE SEL * FROM PROD_DB.TABLE SAMPLE 1000;]

Q: Why we should have existing data into Test Environment if it is load into existing table?

A: Because our load into existing should not be delta records for the existing records. This can be verified using the record count       before and after the load – the count existing count should not be disturbed.
If the data load is into new warehouse tables then the tables should not contain any data in the Test Environment.

Q: Why we should not have any data  New target tables?

A: Because these tables do not exist in PRODUCTION and we are the one going to load the data into these tables.
So it should not contain any data.

Reference Tables in Test Environment 

MAP and TYPE tables are used for Referential Integrity. A project can use the existing MAP or TYPE tables or they
can create their own based on the project requirement

  1. Testers should verify all the MAP and TYPE tables mentioned in the S2T (even if it is not used in S2T transformation) are created in the Test Environment
  2. If the MAP or TYPE table exists then the testers should verify the data in Test Environment and PROD environment should be same.
  3. If the MAP or TYPE tables are created project specific then testers should verify the data is inserted correctly as per the Insert statements provide in S2T
  4. Tester should cross the values inserted into MAP and TYPE tables are matching with DDS (refer Appendix section for Reference data). If you find any data missing or added in the table please raise defect to Data designer.

Now we have everything in our Environment 🙂  Now we are going for Validation of Specification on next Blog.

See you @ my next Blog.

Regards – Asik