data quality testing generic (

Microsoft Confidential 12/27/06 1

“Virtually everything in business today is an undifferentiated commodity, except how a company manages its information. How you manage information determines whether you win or lose.” Bill Gates

[email protected]

-Narendra Parihar- Bhoomika Goyal

- Raj Kamal (rajkamal)

Data Quality Testing


Data Quality Overview

Testing :: DQ Categories / Checks

Testing :: DQ Case Study

DQ Test Management

DQ Benefits & Challenges

Q & A

Data Quality Overview

Testing :: DQ Categories / Checks


DQ Test Management

DQ Benefits & Challenges

Q & A

Agenda

2

Overview DQ Testing Case Study CloseDQ

Management


Data are of high quality "if they are fit for their intended uses in operations, decision making and planning" (J.M. Juran).

The state of completeness, validity, consistency, timeliness and accuracy that makes data appropriate for a specific use.

DQ Impact: Organizations with poor data quality spend time working with conflicting reports and flawed business plans, resulting in erroneous decisions that are made with outdated, inconsistent, and invalid data

Data are of high quality "if they are fit for their intended uses in operations, decision making and planning" (J.M. Juran).

The state of completeness, validity, consistency, timeliness and accuracy that makes data appropriate for a specific use.

DQ Impact: Organizations with poor data quality spend time working with conflicting reports and flawed business plans, resulting in erroneous decisions that are made with outdated, inconsistent, and invalid data

Overview: DQ Definition

3


Management


“End users spend as much as 40-50% of a typical IT budget reworking data in one application to make it work with another”. The high cost of low data quality.

The Data Warehouse Institute estimates that bad customer data costs American companies upwards of $600billion dollars per year By Wayne W. Eckerson

POOR Data Quality can kill your POOR Data Quality can kill your business!!!!business!!!!

“End users spend as much as 40-50% of a typical IT budget reworking data in one application to make it work with another”. The high cost of low data quality.

The Data Warehouse Institute estimates that bad customer data costs American companies upwards of $600billion dollars per year By Wayne W. Eckerson

POOR Data Quality can kill your POOR Data Quality can kill your business!!!!business!!!!

Overview: DQ Stats

4


Management


Testing :: DQ CheatSheet

5


Management


Count of records at Source and Target should be same at a given point of time.

Count of records at Source and Target should be same at a given point of time.

Rule #1: Row Counts

6


Management

Missing Records

ExtraRecords


# Example 1

7


Management

DeptID DeptName DeptStartDate

1 HR 22-Aug-2007

2 Finance 12-June-1988

4 Admin 1-May-1999

5 IT 2-June-1997

Source_Dept


1 Human Resource 22-Aug-2007


3 Operations 11-May-1752

Target_Dept


Missing Records: Records which are only present at Source

Extra Records: Records which are only present at Target



Rule #1: Row Counts

8


Management


4 Admin 1-May-1999

5 IT 2-June-1997




All the data under consideration at the Source and Target should be same at a given point of time satisfying the business rules.

All the data under consideration at the Source and Target should be same at a given point of time satisfying the business rules.

Rule #2: Completeness

9


Management

SourceTable

TargetTable




Mismatched Records: Which contain at least one different value for the same record between Source and Target



Mismatched Records: Which contain at least one different value for the same record between Source and Target

Rule #2: Completeness

10


Management


4 Admin 1-May-1999

5 IT 2-June-1997



DeptID DeptName DeptStartDate DifferenceType

2 Finance 12-June-1988 At Source

2 Finance 12-June-1978 At Target


This ensures that each user observes a consistent view of the data, including changes made by transactions

There is data inconsistency between the Source & Target if the same data is stored in different formats or contain different values at different places.

This ensures that each user observes a consistent view of the data, including changes made by transactions

There is data inconsistency between the Source & Target if the same data is stored in different formats or contain different values at different places.

Rule #3: Consistency

11


Management


# Example 2

12


Management

DeptID DeptName Revenue ($)

DeptStartDate

1 HR 100 22-Aug-2007

2 Finance 200 12-June-1988

Source_Dept

DeptID DeptName Revenue (Euro)

DeptStartDate

1 HR 70 22/08/2007

2 Finance 140 12/06/1978

Warehouse_Dept

Data Mart_Dept


DeptStartDate

1 Human Resource

70 22/08/2007

2 Finance 999999 12/06/1978


Example #1: Zip code / Date / Currency formatsa)

b)

Example #1: Zip code / Date / Currency formatsa)

b)


13


Management

DeptID DeptName Revenue ($ or Euro ) DeptStartDate Difference Point

1 HR 100 22-Aug-2007 Same data, Inconsistent due to Revenue & Currency format

1 HR 70 22/08/2007 Same data, Inconsistent due to Revenue & Currency format


1 HR 100 22-Aug-2007 Same data, Inconsistent due to different format of Department name

1 Human Resource

70 22/08/2007 Same data, Inconsistent due to different format for department name


Example #2: Regional Setting e.g. Language

Example #3: Different values at different points

Example #2: Regional Setting e.g. Language

Example #3: Different values at different points


14


Management


1 Human Resource

100 22/08/2007 Same data, Inconsistent due to different language used

1 人的資源 100 22/08/2007 Same data, Inconsistent due to different language used


2 Finance 140 12/06/1978 Same data, Inconsistent value for Revenue between Warehouse & Mart

2 Finance 999999 12/06/1978 Same data, Inconsistent value for Revenue between Warehouse & Mart


Validity is the correctness and reasonableness of data.

A valid measure must be reliable, but a reliable measure need not be valid.

Questions:

-> Is Information Reliable?

-> How is Information measured ?

Validity is the correctness and reasonableness of data.

A valid measure must be reliable, but a reliable measure need not be valid.

Questions:

-> Is Information Reliable?

-> How is Information measured ?

Rule #4: Validity

15


Management


Example #1: Measuring “Unemployment” in a country

-> Statistics are collected reliably month-on-month

-> Definition of collecting “Unemployment” remains same.

e.g. Definition of “unemployment” has changed in past 25 years hence we can’t compare old data with current data as comparison is not valid

Example #2: Values falling outside a range

Example #1: Measuring “Unemployment” in a country

-> Statistics are collected reliably month-on-month

-> Definition of collecting “Unemployment” remains same.

e.g. Definition of “unemployment” has changed in past 25 years hence we can’t compare old data with current data as comparison is not valid

Example #2: Values falling outside a range

Rule #4: Validity

16


Management


DeptStartDate

1 Human Resource

70 22/08/2255

2 Finance 999999 12/06/1752


Example #3: Dates having valid MM, DD, YYYY

Example #4: Birth date > Death Date

Example #3: Dates having valid MM, DD, YYYY

Example #4: Birth date > Death Date

Rule #4: Validity

17


Management


DeptStartDate

1 Human Resource

70 13/13/2007

EmpId EmpName DOB DOE

1 Jack 13/01/2008 24/11/1996


Physical Duplicates: All the columns values repeating for at least 2 records in a table

Logical Duplicates: Business Key (list of column) values are repeating for at least 2 records in a table

Physical Duplicates: All the columns values repeating for at least 2 records in a table

Logical Duplicates: Business Key (list of column) values are repeating for at least 2 records in a table

Rule #5: Redundancy

18


Management

Logical Dups

Physical Dups


# Example 3

19


Management

EmpID EmpName EmpAddress Age DeptID

1 Jim #22, Jackson St., NY 23 1

2 Sam A302, Woodsvilla, WA 28 2

4 Samuel No. AA, Andrew Street, Redmond, WA 22 999



7 Jack #23, Jackson St., NY 41 NULL

Employee

Example #1: Physical Duplicates

Example #2: Logical Duplicates








If there are child records for which no corresponding parent records existing then they are called “Orphan Records”

Logical relationship rules between parent & child tables should be defined by business.

Rule #6: RI

20


Management


# Example 4

21


Management

EmpID EmpName EmpAddress Age DeptID (FK)






Child Table:: Employee

DeptID (PK) DeptName DeptStartDate

1 HR 22-Aug-2007



Parent Table:: Department




Orphan Records


Possible values that can be allowed in a data element.

Possible values that can be allowed in a data element.

Rule #7: Domain Integrity

22


Management


Example #1: Invalid Lookup Table Values (Valid:: HR, Finance, Operations)

Example #2: Truncation::Data Types, Data Length etc

Example #1: Invalid Lookup Table Values (Valid:: HR, Finance, Operations)

Example #2: Truncation::Data Types, Data Length etc


23


Management

DeptID (PK) DeptName

1 HR

2 Finance

3 Operations

4 Invalid Dept

DeptID (PK) DeptName (Varchar(50))

1 HR

2 Finance

3 Operations

DeptID (PK) DeptName (Varchar (2))

1 HR

2 Fi

3 Op

Source Table Target Table


Example #3: Constraints: NOT NULL, CHECK, PK, UK etc

Example #3: Constraints: NOT NULL, CHECK, PK, UK etc


24


Management

DeptID (PK) DeptName (NOT NULL)

1 HR

2 Finance

3 Operations

4 Invalid Dept

DeptID (PK) DeptName (NOT NULL)

1 HR

2 Finance

3 NULL

4 NULL

Source Table Target Table


Degree to which data reflects Real World objects

Accuracy is generally measured by comparing against something defined as “true” source of information

Degree to which data reflects Real World objects

Accuracy is generally measured by comparing against something defined as “true” source of information

Rule #8: Accuracy

25


Management

Accuracy


Describes the relevance and the meaning of data

Example #:

Denotes the ease with which data can be used

Describes the relevance and the meaning of data

Example #:

Denotes the ease with which data can be used

Rule #9: Usability

26


Management


1 HR

2 Fin

3 Ops


1 Human Resources

2 Finance

3 Operations

Represented As

Mart Table ReportingTable


Defines if data required is available when required as per SLA

Example #1: Data Freshness

If everyday data is pulled 24 times and target doesn’t get even for one cycle, “data freshness” get impacted and users see old data which can impact business decisions.

For decision making & mission critical system, timely availability of information is must.

Defines if data required is available when required as per SLA

Example #1: Data Freshness

If everyday data is pulled 24 times and target doesn’t get even for one cycle, “data freshness” get impacted and users see old data which can impact business decisions.

For decision making & mission critical system, timely availability of information is must.

Rule #10: Timeliness

27


Management


ADQC (Automated Data Quality Check) v2.0

ADQC (Automated Data Quality Check) v2.0


28


Management


DQ Test Management:DQ Test Management:

DQ Test Management

29


Management


DQ Test Management: Planning

1. Ensure DQ Requirement are covered in following documents:

BRDFSDTest Plan

2. Ensure DQ Requirements are clarified by Business / PDMs

DQ Test Management: Planning

1. Ensure DQ Requirement are covered in following documents:

BRDFSDTest Plan

2. Ensure DQ Requirements are clarified by Business / PDMs

DQTM: Test Planning

30


Management


DQ Test Management: Test Case Design

1. Ensure DQ Requirement are covered in Test Scenarios and Test Cases

2. Ensure DQ Test cases are automated.

DQ Test Management: Test Case Design

1. Ensure DQ Requirement are covered in Test Scenarios and Test Cases

2. Ensure DQ Test cases are automated.

DQTM: Test Design

31


Management


DQ Test Management: Test Execution

1. Ensure Test Cases related to DQ Requirements are executed in Test cycles

2. Ensure DQ Test results & DQ Bugs are

shared with the Business / PDM in the triage meeting to understand the correct priority based on the impact.

DQ Test Management: Test Execution

1. Ensure Test Cases related to DQ Requirements are executed in Test cycles

2. Ensure DQ Test results & DQ Bugs are

shared with the Business / PDM in the triage meeting to understand the correct priority based on the impact.

DQTM: Test Execution

32


Management


DQ Test Management: Test Monitoring

1. Regularly collect DQ Metrics to depict the trend

2. If DQ Issues Trend is upward, immediate

action need to be taken

DQ Test Management: Test Monitoring

1. Regularly collect DQ Metrics to depict the trend

2. If DQ Issues Trend is upward, immediate

action need to be taken

DQTM: Test Monitoring

33


Management


DQ Challenges

34


Management


DQ Best Practices Overview DQ Testing Case Study CloseDQ

Management


DQ Jargons

DATA GOVERNANCE

• Data governance (DG) refers to the overall management of the availability, usability, integrity, and security of the data employed in an enterprise

• Data governance program includes a governing body or council, a defined set of procedures, and a plan to execute those procedures

DATA STEWARDS

• Data Stewards are those individuals ultimately responsible for the definition, management, control, integrity or maintenance of Enterprise data.

DATA INTEGRITY

• Data integrity is the assurance that data is correct and consistent--that the data correctly reflects the "real" world.

DATA GOVERNANCE

• Data governance (DG) refers to the overall management of the availability, usability, integrity, and security of the data employed in an enterprise

• Data governance program includes a governing body or council, a defined set of procedures, and a plan to execute those procedures

DATA STEWARDS

• Data Stewards are those individuals ultimately responsible for the definition, management, control, integrity or maintenance of Enterprise data.

DATA INTEGRITY

• Data integrity is the assurance that data is correct and consistent--that the data correctly reflects the "real" world.


Management


References

• www.infoimpact.com• http://www.idma.org/valuePropositionGeneral.pdf• http://www.intelligententerprise.com/showArticle.jhtml?

articleID=17701630• http://www.sociology.org.uk/p1mc5n1a.htm• http://blogs.sun.com/emmyp/entry/ensuring_the_validity_of_your• http://www.dmreview.com/dmdirect/20021108/6019-1.html


Management


Questions & Answers

38


Management


Thank you


Management

data quality testing generic (

Technology

dq categories

dq impact

dq definition data

source extra records

dq stats end users

data appropriate

target mismatched records

completeness missing