DrAuditor can check for extra rows of data in your Data Repository(DR). It can compare a DR table against itself or against another “parent” table in DR. The report results will display the primary keys of the additional rows of data. There are two kids of SQL only checking DrAuditor can perform: orphaned and non-deleted.
DrAuditor allows you to compare 2 related tables against each other checking for inconsistencies. For example, you can check [LabSpecimenTests] (the child table) against [LabSpecimens] (the parent table). The assumption is that every [LabSpecimenTests] row should have a corresponding row in [LabSpecimens]. If there were additional rows in [LabSpecimenTests] that do not exist in [LabSpecimens], these rows would be considered “orphaned” rows as their parent row is missing. This indicates one of two possible issues:
- There are additional SQL rows existing in the [LabSpecimenTests] table that should not be in that table, or;
- There are missing rows of data from the [LabSpecimens] table.
For example, say the [LabSpecimens] table consists of only 2 rows, (all rows can assume SourceID = “BEC”, so we’ll ignore that column):
SpecimenID = 100
SpecimenID = 200
If the [LabSpecimenTests] table consisted of these 5 rows:
SpecimenID = 100, TestID = 100.001
SpecimenID = 100, TestID = 100.002
SpecimenID = 150, TestID = 920.005
SpecimenID = 200, TestID =3400.120
You can see the highlighted row doesn’t have a corresponding SpecimenID (150) in the [LabSpecimens] table and is therefore an “orphaned” row.
DrAuditor also allows you to compare a DR SQL table against itself. When comparing a table against itself, like a dictionary table or a table that doesn’t purge from MEDITECH, the additional rows in SQL that are not in MEDITECH are referred to as non-deleted. . While data purging from MEDITECH will result in rows existing exclusively in SQL, the non-deleted feature of DrAuditor is intended to find rows of erroneous data that should not exist in DR. A common problem might be that your DR data validates clean but your report value totals differ between MEDITECH NPR/MAT reports and SQL queries/reports. This could be an indicator of extra rows of data in SQL. The SQL only check will display the extra/non-deleted rows from tables.
This link provides more information and outlines configuring the SQL only check with DrAuditor: