Blue Elm Eventts

September 2017
S M T W T F S
« Apr    
 12
3456789
10111213141516
17181920212223
24252627282930

Does Your Data Repository Have Extra/Erroneous Data?

Home/Uncategorized/Does Your Data Repository Have Extra/Erroneous Data?

Does Your Data Repository Have Extra/Erroneous Data?

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.

Orphaned

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:

  1. There are additional SQL rows existing in the [LabSpecimenTests] table that should not be in that table, or;
  2. 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.

Non-deleted

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.

Ex:

 

This link provides more information and outlines configuring the SQL only check with DrAuditor:

https://www.blueelm.com/wp-content/uploads/2017/01/Using-DrAuditor-to-Check-Data-Repository-for-additional-rows-of-data-not-in-MEDITECH-orphaned-or-non-deleted.pdf

By | 2017-04-28T15:08:13+00:00 April 28th, 2017|Uncategorized|0 Comments

About the Author:

Leave A Comment