ChkDB |
Open Source Rule-Based Data Checking |
|
Home Contact Download Releases Open Source License Project Contributing The PL/SQL Using ChkDB Installation Read-Me Rule-Sets ChkDB Tables E-R Diagram
Tiny Sample External Links Data Quality and Data Profiling (a glossary) |
Sample ResultsThe results below were generated by executing sample_queries.sql using Oracle's SQL*Plus. The tiny sample database contains a little bit of bad data, which the rules are designed to detect.
====================================================
ChkDB Sample Queries
= = = = = = = = = = = = = = = = = = = = = = = = = =
Summary of Active Rules in a Ruleset
TABLE_NAME RULE_ID CLASS
------------------------------ -------------------- -----
DESCRIPTION
------------------------------------------------------------
CHKDB_SAMP_HR_DEPT BUDGET_AMT_APPRV_NEQ 1
Budget amount different than approved budget
CHKDB_SAMP_HR_DEPT BUDGET_EST_OFF 1
Budget estimate too far from budgeted amount
CHKDB_SAMP_HR_DEPT DEPT_EMPTY 1
Department has no employees
CHKDB_SAMP_HR_DEPT FLOOR_BAD 3
Floor is invalid or not set
CHKDB_SAMP_HR_DEPT FLOOR_NOT_NUM 3
Floor is not a number
CHKDB_SAMP_HR_DEPT MANAGER_BAD 3
Manager is invalid or not set
CHKDB_SAMP_HR_DEPT MNGR_DEPT_BAD 2
Manager is not in this department
CHKDB_SAMP_HR_EMP DEPT_GROUP_BAD 4
Dept_id|Group_id is invalid or Dept_id is not set
CHKDB_SAMP_HR_EMP HIRE_DT_BAD 3
Hire-date is invalid or not set
CHKDB_SAMP_HR_EMP LAST_REVIEW_BAD 2
Date of last review is invalid or before hire
CHKDB_SAMP_HR_EMP POSITION_BADCHAR 3
Position contains illegal character (,)
CHKDB_SAMP_HR_EMP POSITION_LONG 3
Position is too long
CHKDB_SAMP_HR_EMP POSITION_NULL 3
Position is not set
CHKDB_SAMP_HR_EMP PROBATION_OVER 1
Probation period is over
CHKDB_SAMP_HR_EMP REVIEW_DT_ISSUE 2
Date of last review is hire date or in future
CHKDB_SAMP_HR_EMP VACN_DAYS_TOT_BAD 3
Vacn_days_tot is invalid or not set
CHKDB_SAMP_HR_EMP VACN_DAYS_USED_HIGH 1
Vacn_days_used is too high
CHKDB_SAMP_HR_GROUP ACTIVE_YN_BAD 4
Active_YN must be Y or N
CHKDB_SAMP_HR_GROUP DEPT_BAD 4
Department is invalid or not set
19 rows selected.
= = = = = = = = = = = = = = = = = = = = = = = = = =
Summary of In-Active Rules in a Ruleset
TABLE_NAME RULE_ID CLASS
------------------------------ -------------------- -----
DESCRIPTION
------------------------------------------------------------
CHKDB_SAMP_HR_DEPT BUDGET_AMT_HIGH 1
Budget amount greater than $125,000
= = = = = = = = = = = = = = = = = = = = = = = = = =
Result count by table and rule...
(raw data for Data Quality Profiling)
TABLE_NAME RULE_ID CLASS OCCURS
------------------------------ -------------------- ----- ------
CHKDB_SAMP_HR_DEPT BUDGET_AMT_APPRV_NEQ 1 1
CHKDB_SAMP_HR_DEPT BUDGET_EST_OFF 1 2
CHKDB_SAMP_HR_DEPT DEPT_EMPTY 1 1
CHKDB_SAMP_HR_DEPT FLOOR_BAD 3 3
CHKDB_SAMP_HR_DEPT FLOOR_NOT_NUM 3 1
CHKDB_SAMP_HR_DEPT MANAGER_BAD 3 1
CHKDB_SAMP_HR_DEPT MNGR_DEPT_BAD 2 1
CHKDB_SAMP_HR_EMP DEPT_GROUP_BAD 4 2
CHKDB_SAMP_HR_EMP HIRE_DT_BAD 3 1
CHKDB_SAMP_HR_EMP LAST_REVIEW_BAD 2 2
CHKDB_SAMP_HR_EMP POSITION_BADCHAR 3 1
CHKDB_SAMP_HR_EMP POSITION_LONG 3 1
CHKDB_SAMP_HR_EMP POSITION_NULL 3 1
CHKDB_SAMP_HR_EMP PROBATION_OVER 1 1
CHKDB_SAMP_HR_EMP REVIEW_DT_ISSUE 2 2
CHKDB_SAMP_HR_EMP VACN_DAYS_TOT_BAD 3 1
CHKDB_SAMP_HR_EMP VACN_DAYS_USED_HIGH 1 1
CHKDB_SAMP_HR_GROUP ACTIVE_YN_BAD 4 1
CHKDB_SAMP_HR_GROUP DEPT_BAD 4 1
19 rows selected.
= = = = = = = = = = = = = = = = = = = = = = = = = =
Results by table and rule, ordered by number
of occurrences, with description...
(crude data quality profiling report)
TABLE_NAME CLASS OCCURS
------------------------------ ----- ------
DESCRIPTION
------------------------------------------------------------
CHKDB_SAMP_HR_DEPT 3 3
Floor is invalid or not set
CHKDB_SAMP_HR_DEPT 1 2
Budget estimate too far from budgeted amount
CHKDB_SAMP_HR_DEPT 1 1
Department has no employees
CHKDB_SAMP_HR_DEPT 2 1
Manager is not in this department
CHKDB_SAMP_HR_DEPT 1 1
Budget amount different than approved budget
CHKDB_SAMP_HR_DEPT 3 1
Manager is invalid or not set
CHKDB_SAMP_HR_DEPT 3 1
Floor is not a number
CHKDB_SAMP_HR_EMP 2 2
Date of last review is hire date or in future
CHKDB_SAMP_HR_EMP 2 2
Date of last review is invalid or before hire
CHKDB_SAMP_HR_EMP 4 2
Dept_id|Group_id is invalid or Dept_id is not set
CHKDB_SAMP_HR_EMP 1 1
Probation period is over
CHKDB_SAMP_HR_EMP 3 1
Position is too long
CHKDB_SAMP_HR_EMP 3 1
Vacn_days_tot is invalid or not set
CHKDB_SAMP_HR_EMP 3 1
Position contains illegal character (,)
CHKDB_SAMP_HR_EMP 3 1
Hire-date is invalid or not set
CHKDB_SAMP_HR_EMP 3 1
Position is not set
CHKDB_SAMP_HR_EMP 1 1
Vacn_days_used is too high
CHKDB_SAMP_HR_GROUP 4 1
Active_YN must be Y or N
CHKDB_SAMP_HR_GROUP 4 1
Department is invalid or not set
19 rows selected.
= = = = = = = = = = = = = = = = = = = = = = = = = =
Check-Result Details (Max 100)
TABLE_NAME DATA_DOMAIN
------------------------------ --------------------
RULESET_ID RULE_ID CHECK_DATE CLASS
-------------------- -------------------- ---------- -----
DESCRIPTION
------------------------------------------------------------
SELECTED_ROWID
------------------
PRIMARY_KEY
------------------------------------------------------------
ROW_OUT_DESC
----------------------------------------------------------------------
ROW_OUT_DATA
----------------------------------------------------------------------
CHKDB_SAMP_HR_DEPT CHKDB_SAMP_HR
SAMP_HR_RULES FLOOR_BAD 27-JAN-04 3
Floor is invalid or not set
AAAHgfAALAAAAB6AAB
DEVELOPMENT
FLOOR
@
CHKDB_SAMP_HR_DEPT CHKDB_SAMP_HR
SAMP_HR_RULES FLOOR_BAD 27-JAN-04 3
Floor is invalid or not set
AAAHgfAALAAAAB6AAC
SALES
FLOOR
CHKDB_SAMP_HR_DEPT CHKDB_SAMP_HR
SAMP_HR_RULES FLOOR_NOT_NUM 27-JAN-04 3
Floor is not a number
AAAHgfAALAAAAB6AAB
DEVELOPMENT
FLOOR
@
CHKDB_SAMP_HR_DEPT CHKDB_SAMP_HR
SAMP_HR_RULES MANAGER_BAD 27-JAN-04 3
Manager is invalid or not set
AAAHgfAALAAAAB6AAD
DEPT_999
MANAGER
CHKDB_SAMP_HR_DEPT CHKDB_SAMP_HR
SAMP_HR_RULES FLOOR_BAD 27-JAN-04 3
Floor is invalid or not set
AAAHgfAALAAAAB6AAD
DEPT_999
FLOOR
444
CHKDB_SAMP_HR_DEPT CHKDB_SAMP_HR
SAMP_HR_RULES MNGR_DEPT_BAD 27-JAN-04 2
Manager is not in this department
AAAHgfAALAAAAB6AAC
SALES
MANAGER
E3
CHKDB_SAMP_HR_DEPT CHKDB_SAMP_HR
SAMP_HR_RULES BUDGET_AMT_APPRV_NEQ 27-JAN-04 1
Budget amount different than approved budget
AAAHgfAALAAAAB6AAC
SALES
BUDGET_AMT|BUDGET_APPRV
170000|160000
CHKDB_SAMP_HR_DEPT CHKDB_SAMP_HR
SAMP_HR_RULES BUDGET_EST_OFF 27-JAN-04 1
Budget estimate too far from budgeted amount
AAAHgfAALAAAAB6AAA
MANAGEMENT
BUDGET_EST|BUDGET_AMT
280000|265000
CHKDB_SAMP_HR_DEPT CHKDB_SAMP_HR
SAMP_HR_RULES BUDGET_EST_OFF 27-JAN-04 1
Budget estimate too far from budgeted amount
AAAHgfAALAAAAB6AAC
SALES
BUDGET_EST|BUDGET_AMT
17400|170000
CHKDB_SAMP_HR_DEPT CHKDB_SAMP_HR
SAMP_HR_RULES DEPT_EMPTY 27-JAN-04 1
Department has no employees
AAAHgfAALAAAAB6AAD
DEPT_999
DEPT_ID
DEPT_999
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES DEPT_GROUP_BAD 27-JAN-04 4
Dept_id|Group_id is invalid or Dept_id is not set
AAAHgjAALAAAACaAAD
E4
DEPT_ID|GROUP_ID
DEVELOPMENT|APP_TEAM
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES DEPT_GROUP_BAD 27-JAN-04 4
Dept_id|Group_id is invalid or Dept_id is not set
AAAHgjAALAAAACaAAE
E5
DEPT_ID|GROUP_ID
|WEB_TEAM
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES HIRE_DT_BAD 27-JAN-04 3
Hire-date is invalid or not set
AAAHgjAALAAAACaAAG
E7
HIRE_DT
01-11-1901
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES POSITION_NULL 27-JAN-04 3
Position is not set
AAAHgjAALAAAACaAAG
E7
POSITION
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES POSITION_LONG 27-JAN-04 3
Position is too long
AAAHgjAALAAAACaAAF
E6
POSITION
Sales Representativeee
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES VACN_DAYS_TOT_BAD 27-JAN-04 3
Vacn_days_tot is invalid or not set
AAAHgjAALAAAACaAAF
E6
VACN_DAYS_TOT
5
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES POSITION_BADCHAR 27-JAN-04 3
Position contains illegal character (,)
AAAHgjAALAAAACaAAB
E2
POSITION
Manager, Development
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES LAST_REVIEW_BAD 27-JAN-04 2
Date of last review is invalid or before hire
AAAHgjAALAAAACaAAD
E4
LAST_REVIEW_DT|HIRE_DT
27-01-2303|01-11-1998
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES REVIEW_DT_ISSUE 27-JAN-04 2
Date of last review is hire date or in future
AAAHgjAALAAAACaAAA
E1
LAST_REVIEW_DT|HIRE_DT
12-04-1998|12-04-1998
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES REVIEW_DT_ISSUE 27-JAN-04 2
Date of last review is hire date or in future
AAAHgjAALAAAACaAAD
E4
LAST_REVIEW_DT|HIRE_DT
27-01-2303|01-11-1998
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES LAST_REVIEW_BAD 27-JAN-04 2
Date of last review is invalid or before hire
AAAHgjAALAAAACaAAF
E6
LAST_REVIEW_DT|HIRE_DT
27-01-1903|01-02-2001
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES PROBATION_OVER 27-JAN-04 1
Probation period is over
AAAHgjAALAAAACaAAE
E5
PROBATION_END_DT
01-06-1903
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES VACN_DAYS_USED_HIGH 27-JAN-04 1
Vacn_days_used is too high
AAAHgjAALAAAACaAAE
E5
VACN_DAYS_USED|VACN_DAYS_TOT
33|15
CHKDB_SAMP_HR_GROUP CHKDB_SAMP_HR
SAMP_HR_RULES ACTIVE_YN_BAD 27-JAN-04 4
Active_YN must be Y or N
AAAHghAALAAAACKAAB
DEVELOPMENT|WEB_TEAM
ACTIVE_YN
U
CHKDB_SAMP_HR_GROUP CHKDB_SAMP_HR
SAMP_HR_RULES DEPT_BAD 27-JAN-04 4
Department is invalid or not set
AAAHghAALAAAACKAAA
DEVELOPMENTTT|APP_TEAM
DEPT_ID
DEVELOPMENTTT
25 rows selected.
= = = = = = = = = = = = = = = = = = = = = = = = = =
Various Other Sample Queries
= = = = = = = = = = = = = = = = = = = = = = = = = =
All Active Rules in a Domain
TABLE_NAME DATA_DOMAIN
------------------------------ --------------------
RULESET_ID RULE_ID CLASS
-------------------- -------------------- -----
DESCRIPTION
------------------------------------------------------------
CHKDB_SAMP_HR_DEPT CHKDB_SAMP_HR
SAMP_HR_RULES BUDGET_AMT_APPRV_NEQ 1
Budget amount different than approved budget
CHKDB_SAMP_HR_DEPT CHKDB_SAMP_HR
SAMP_HR_RULES BUDGET_EST_OFF 1
Budget estimate too far from budgeted amount
CHKDB_SAMP_HR_DEPT CHKDB_SAMP_HR
SAMP_HR_RULES DEPT_EMPTY 1
Department has no employees
CHKDB_SAMP_HR_DEPT CHKDB_SAMP_HR
SAMP_HR_RULES FLOOR_BAD 3
Floor is invalid or not set
CHKDB_SAMP_HR_DEPT CHKDB_SAMP_HR
SAMP_HR_RULES FLOOR_NOT_NUM 3
Floor is not a number
CHKDB_SAMP_HR_DEPT CHKDB_SAMP_HR
SAMP_HR_RULES MANAGER_BAD 3
Manager is invalid or not set
CHKDB_SAMP_HR_DEPT CHKDB_SAMP_HR
SAMP_HR_RULES MNGR_DEPT_BAD 2
Manager is not in this department
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES REVIEW_DT_ISSUE 2
Date of last review is hire date or in future
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES LAST_REVIEW_BAD 2
Date of last review is invalid or before hire
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES DEPT_GROUP_BAD 4
Dept_id|Group_id is invalid or Dept_id is not set
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES HIRE_DT_BAD 3
Hire-date is invalid or not set
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES POSITION_BADCHAR 3
Position contains illegal character (,)
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES POSITION_NULL 3
Position is not set
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES POSITION_LONG 3
Position is too long
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES PROBATION_OVER 1
Probation period is over
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES VACN_DAYS_TOT_BAD 3
Vacn_days_tot is invalid or not set
CHKDB_SAMP_HR_EMP CHKDB_SAMP_HR
SAMP_HR_RULES VACN_DAYS_USED_HIGH 1
Vacn_days_used is too high
CHKDB_SAMP_HR_GROUP CHKDB_SAMP_HR
SAMP_HR_RULES ACTIVE_YN_BAD 4
Active_YN must be Y or N
CHKDB_SAMP_HR_GROUP CHKDB_SAMP_HR
SAMP_HR_RULES DEPT_BAD 4
Department is invalid or not set
19 rows selected.
= = = = = = = = = = = = = = = = = = = = = = = = = =
Tables and Primary Keys in the CHKDB_SAMP_HR Domain
(only 1st 28 chars of table and column names)
TABLE_NAME CHECKABLE TABLE_VIEW ACTIVE
------------------------------ --------- ---------- ------
PRIMARY_KEY_LIST
----------------------------------------------------------------------
CHKDB_SAMP_HR_DEPT Y View Y
DEPT_ID
CHKDB_SAMP_HR_EMP Y View Y
EMP_ID
CHKDB_SAMP_HR_GROUP Y View Y
DEPT_ID,GROUP_ID
CHKDB_SAMP_DEPT N Table Y
CHKDB_SAMP_EMPL N Table Y
CHKDB_SAMP_GROUP N Table Y
6 rows selected.
= = = = = = = = = = = = = = = = = = = = = = = = = =
To see which tables in a domain are active...
TABLE_NAME ? DATA_DOMAIN ?
------------------------------ - -------------------- -
CHKDB_SAMP_DEPT Y CHKDB_SAMP_HR Y
CHKDB_SAMP_EMPL Y CHKDB_SAMP_HR Y
CHKDB_SAMP_GROUP Y CHKDB_SAMP_HR Y
CHKDB_SAMP_HR_DEPT Y CHKDB_SAMP_HR Y
CHKDB_SAMP_HR_EMP Y CHKDB_SAMP_HR Y
CHKDB_SAMP_HR_GROUP Y CHKDB_SAMP_HR Y
6 rows selected.
= = = = = = = = = = = = = = = = = = = = = = = = = =
To see which rules in a table are active...
Rules for table CHKDB_SAMP_HR_DEPT
in domain CHKDB_SAMP_HR...
RULESET_ID ? RULE_ID ?
-------------------- - -------------------- -
SAMP_HR_RULES Y BUDGET_AMT_APPRV_NEQ Y
SAMP_HR_RULES Y BUDGET_AMT_HIGH N
SAMP_HR_RULES Y BUDGET_EST_OFF Y
SAMP_HR_RULES Y DEPT_EMPTY Y
SAMP_HR_RULES Y FLOOR_BAD Y
SAMP_HR_RULES Y FLOOR_NOT_NUM Y
SAMP_HR_RULES Y MANAGER_BAD Y
SAMP_HR_RULES Y MNGR_DEPT_BAD Y
8 rows selected.
= = = = = = = = = = = = = = = = = = = = = = = = = =
Count of Results by Table...
TABLE_NAME PROBLEMS
------------------------------ ----------
CHKDB_SAMP_HR_DEPT 10
CHKDB_SAMP_HR_EMP 13
CHKDB_SAMP_HR_GROUP 2
= = = = = = = = = = = = = = = = = = = = = = = = = =
Counts from each ChkDB table...
CHKDB_DOMAIN_COUNT
------------------
1
CHKDB_TABLE_COUNT
-----------------
6
CHKDB_RULESET_COUNT
-------------------
1
CHKDB_RULE_COUNT
----------------
20
CHKDB_RESULT_COUNT
------------------
25
CHKDB_RULE_SQL_COUNT
--------------------
19
CHKDB_RULE_ERR_COUNT
--------------------
0
====================================================
|