Data Quality Case Study Prepared by ORC Macro

Data Correction • Background – Data Correction • Tracking system • SAS AF query application • Guidelines – Profile Analysis • SSNs • Names 2

Profile Analysis—SSNs 3

Profile Analysis—SSNs Shared SSNs (n=7, 100) Different Names 27% Candidates for Collapse Candidates for Correction Same or Similar Names 73% 4

Profile Analysis—Names Possible Duplicates 23% n=79, 300 Unique Persons 77% n=267, 081 5

Profile Analysis—Names 6

OLTP—Commons Cases • Definition • Statistics • Status 7

Data Correction • • • Identifying the extent of the problem Investigating based on type of error Validating the investigation Implementing the change Tracking the identification, investigation, validation, and implementation 8

Data Correction—An Example PERSON_ID=3070908—PPRF record • Identification of problem – Two different middle initials found • Investigation of problem – TA module – Scripts run • Validation of information – Name, SSN, degree(s), grant(s) – Sources 9

Data Correction—An Example PERSON_ID=3070908—PPRF record • Implementation of correction – Grants report submitted to NIH OD • Tracking of correction – Internal tracking system • Post-correction – Loss of control of data 10

Developing a Data Quality Business Plan

Focus of Our Activities Examination of the Database, Procedures, and Interface Development of Modified Use Cases Unified Modeling Language Identification and Extraction of Business Rules Identification of Business Model 12

Data Quality Issues • • • Type-over of information Generation of duplicate persons Collapsing Changes in degree and address data Generation of orphans 13

Type-Over Practices • Intentions: – Assign a new principal investigator (PI) to a grant – Change the name of a PI on a grant – Correct a misspelled name • Consequences: – Inclusion of incorrect information in a person profile – Absence of linkages between PIs and grant applications – Creation of false linkages between PIs and grant applications 14

Factors Affecting Quality • Relatively easy access to person-related data elements • Lack of self-validation routines • Interface issues 15

Solutions • • Restricted access Quality control validation Interface simplification Self-validation algorithm 16

Data Quality Validation • Who does it? – ICs – A Quality Assurance group – Other • How is it done? – Staging areas – Manual and intelligent filtering – Architecture 17

GM Module Screen GM 1040 18

GM Module Screen COM 1100 19

Self Validation • Name-matching algorithm • Consistency checking 20

Higher-Level Analysis The following are being examined relative to their effect on quality: • • Commons interface with IMPAC II Database redundancy Business rules in the database Master person file Front-end design Human factors Ownership 21

Development of a Data Quality Model

Major Goals Quality improvements plan for personal identifiers • Evaluate the different identification algorithms currently in use for IMPAC II • Develop identification algorithm(s) and procedures • Serve as consultant and guarantor of efficacy of algorithm implementation 23

Moving Forward • Understanding the technical infrastructure • Identification of specific areas of concern • Development/proposal of data quality expectations • Development/proposal of appropriate, acceptable solutions 24

Data Quality White Paper Knowledge assets are very real and carry tremendous value. Outline • • • Definition Rules Risks and Costs NIH Expectations Process Measurements/Metrics Testing Continuous Improvements Conclusions 25

Conclusion Examination of the Database, Procedures, and Interface Development of Modified Use Cases Unified Modeling Language Identification and Extraction of Business Rules Identification of Business Model Development/Proposal of Appropriate, Acceptable Solutions Development/Proposal of Data Quality Expectations Identification of Specific Areas of Concern Understanding the Technical Infrastructure 26