Instructions for importing a batch of DSM Data: Difference between revisions

From CCMDB Wiki
Jump to navigation Jump to search
 
(70 intermediate revisions by 3 users not shown)
Line 1: Line 1:
Preliminary information, this is not active yet
'' see [[Instructions for requesting a batch of data from DSM]] for counterpart
'' see [[Instructions for requesting a batch of data from DSM]] for counterpart


Line 6: Line 4:


== Instructions ==
== Instructions ==
{{dT | Something is still not right with the code for reconnecting, Tina needs to look into. Ttenbergen 17:04, 2018 May 17 (CDT) }}


=== Prep ===
=== Prep ===
Line 16: Line 13:
#** [[DSM Labs data.accdb]]
#** [[DSM Labs data.accdb]]
#** '''PHI not needed''' - [[PHI.mdb]] is not needed during import because it matches by D_ID
#** '''PHI not needed''' - [[PHI.mdb]] is not needed during import because it matches by D_ID
# copy the newly received batch of data from [[DSM_Lab_Extract#File_Share]] to a '''different local directory that contains '''only those files'''
# copy the newly received batch of data from [[DSM_Lab_Extract#File_Share]] to a '''different local directory that contains '''only those files'''not the requestor file & '''''''''the csv with no(#) beside it.'''
# open [[DSM_Labs_Consistency_check.accdb]] and click the Re-link button


=== Check for new labs ===
=== Check for new labs ===
# Open [[DSM_Labs_Consistency_check.accdb]]  
# Open [[DSM_Labs_Consistency_check.accdb]]
# Make sure that you remove the combine copy of the csv file on your desk top copy.
# Click the "Check DSM .csv for new labs " button
# Click the "Check DSM .csv for new labs " button
# pick the directory with the new data files
# pick the directory with the new data files
# wait for the check to complete, about half an hour
# wait for the check to complete, about half an hour
# if the program tells you there are new labs, review them and ask Trish or Allan if any of them should be considered as part of our counts
# '''if the program tells you there are new labs''' click "no" and hold off on the rest of the import as you might have to re-do it if any of the new labs need to be included; follow the steps at [[Validating new types of DSM results]]; '''do this right away''' since some of the queries are based on labs imported within the last hour
#* open the following tables, and check labs added today
#* If no additional lab, just close it.
#** s_group, sort by added_dt
#** s_mapping_lab, sort by date_listed newest first
#* If Trish or Allan says any of them need to be added talk to Tina, we can document that process when it next happens. It involves editing table s_mapping_labs or s_group to accommodate the new data.


=== actual data import ===
=== actual data import ===
If the above listed no new labs, or the new labs were resolved, click the "Import DSM .csv" button and follow essentially the same steps as above to import the actual data.  
If the above listed no new labs, or the new labs were resolved, click the "Import DSM .csv" button and follow essentially the same steps as above to import the actual data.
Close the file
 
=== Run consistency checks ===
* Open [[DSM_Labs_Consistency_check.accdb]]
* Open [[query Import_request_matcher]]
* Get the Number of D_ID with no match lab DSM by following the steps below.
** Go to design view and add the Import DtTm
** SentDtTm column on criteria put the date you want to run and change the covered date (Ex Between Mar 27, 2019 and May 13, 2019)
** Go to External Data then click excel
** Go to S MED MED_CCMED CCMDB DSM Lab imports Archive_raw then to the folder you want to save
** Click Export data with formatting and layout then rename Ex. (No_Match_DSM_450.xlsx) then save.


=== Cleanup ===
=== Cleanup ===
# Move files back to their master directories
Move files to their permanent homes. '''Make sure you do all of these!'''
#** [[DSM_Labs_Consistency_check.accdb]] - only need to move if new labs were found
# Make a new directory in \\Hsc1msfp0001\med_ccmed\CCMDB\DSM Labs Imports\archive_processed and call it e.g. DSM_2019-03-27
#** [[DSM Labs data.accdb]]
## '''copy''' (not move) [[DSM_Labs_Consistency_check.accdb]] to that new directory
# enter import date in log below
## '''copy''' (not move)  [[DSM Labs data.accdb]] to that new directory
# '''move''' (not copy) [[DSM_Labs_Consistency_check.accdb]] and [[DSM Labs data.accdb]] from your computer to \\Hsc1msfp0001\med_ccmed\CCMDB\DSM Labs Imports\ (not the archive) - this is the master version.
# move (not copy) the folder of data you processed from the directory it arrived where we share with DSM to the '''archive_raw''' directory in the same folder (these are big so they really should be moved, not copied and left in two places)
 
=== Log import ===
Update the log at [[Instructions for requesting a batch of data from DSM#Log]].


== Integrity checks after import ==
== Integrity checks after import ==
Please note here if any are needed.
{{Data Integrity Check List}}


=== D_IDs in CFE for which no DSM labs exist ===
=== queries for which we have no page yet ===
This could happen if
* we have a bad MRN / PHIN
* we change/fix a D_ID after we request data


{{discuss | who = Julie | question =  
=== D_ID Related Issues - Duplicate RequestNo For Incomplete Profiles sent more than once ===
* This could also be true where no labs were sent for, eg a patient who dies shortly after arrival. In the past we would have entered a "no labs" for these. Do we want to do something similar? It would have to be Pagasa that does it. Might be a lot of extra work. Need to review. (ex. wrong D_ID when exported but found it error and so fixed it before the data for import comes back). '''For now we do not have an entry like that. And it might not be worth it - what would Pagasa do to check that the no-labs are legit?''' }}
This is caused by patients having more than one D_ID because
 
# The location portion of the D_ID has been changed after the requestor list has been sent to DSM. 
=== D_IDs in DSM data for which no CFE record exists ===
# The D_ID format has been changed to include Laptop Identifier.
Could happen if we change/fix a D_ID after requesting. Problem is, we can find if one doesn’t exist, but not if one was changed, but the one that was in the export is actually in present now. Not sure how we would catch that.  
Every time the batch of DSM data is uploaded, matches maybe be found for same patient but labeled with diff D_IDs.
 
=== D_ID Related Issues - Missing Lab data for Completes ===
=== Duplicate RequestNo ===
This is also caused by the change in the D_ID after the requestor list has been sent to DSM.
Should never happen due to how import works, but can check anyway.
# The location portion of the D_ID has been changed.  
# The D_ID format has been changed to include Laptop Identifier.
When uploading the DSM data, the new D_ID is being used for finding matches while the dumped Lab data is identified by the old D_ID. While present in DSM dumped file, no match was found resulting to no lab data for that profile.
=== Missing some Lab Dates ===
Found cases where Lab dates between Accept and Arrived are missing.
# This may be due to the changes done in the dates.
=== Duplicates due to Inconsistent Lab labels ===
Found three labs that are not labeled the same
# 'Arterial Lactat ' vs 'Arterial Lactate'  
# 'Blood Gas Gluco ' vs 'Blood Gas Glucos'
# 'Blood Gas Sodiu ' vs ’Blood Gas Sodium'
=== Re-do the DSM matching : 29Oct2020 ===
Julie went back to the dumped DSM data file starting from '''batches 2019-01-23 to 2020-10-14'''. She did the following steps using SAS program
# For each batch of data, identify the profiles with D_ID changes by comparing the requestor list and CCMDB.
## Make a new column adding the new D_ID.  The old and new D_ID will be saved in CFE [[L_Problems_D_ID table]].
# Get the Accept DtTm and Dispo DtTm for all records in requestor List from CCMDB L_log.
# Get from the dumped DSM datafile only the needed Lab tests. 
# Match the subset dumped DSM data file and the requestor list by old D_ID.
# Get the records where Lab_DtTm is within the (Accept_DtTm or Arrived_DtTm) and Dispo_DtTm.
# Replaced those old D_ID (found in step1) with the New D_ID.
# Check duplicates due to lab labels or changed D_IDs and remove them.
# Compare D_ID of the matched DSM dataset with L_log to determine the unmatched cases (and %).
# Get the lab data from the DSM Database '''before the batch 2019-1-23''' and combine it with the new matched dataset.
## check again for any duplication due to lab labels or changed D_IDs and remove duplicates.
## Make sure the lab data is unique by D_ID, registry no, Lab DtTm, Lab Test and result.
# Import the New_Lab_DSM to the DSM database.
Julie is still thinking to re-send those profiles found with no lab data to DSM. Julie will give Pagasa some more list of changed D_IDs to be added to the CFE [[L_Problems_D_ID table]].
=== Re-do the importing: Sept 8-14, 2022 ===
*Tina updated the import program in ACCESS because matches are not completely done in the previous import program.  It was agreed to re-do the importing from Allun's DSM dumped files using the new import program in ACCESS.
*Pagasa  did the following steps:
# First, remove from the L_DSM_Data all data imported after Oct 2020.
# Using the new import program, upload the DSM data one batch at a time starting from batch  Dec 3, 2020 to batch Aug 31, 2022.


== Known issues ==
== Known issues ==
Line 63: Line 101:


== Log ==
== Log ==
eg copy/paste and fill in the following:
Update the log at [[Instructions for requesting a batch of data from DSM#Log]].
<nowiki>* ~~~~~ - imported batch</nowiki>
 
* 11:34, 2018 May 18 (CDT) - imported batch 2018-04-05
* 17:47, 2018 April 9 (CDT) - imported batch 2017-10-26 and 2018-01-25


==Related articles ==
==Related articles ==

Latest revision as of 17:42, 2023 May 17

see Instructions for requesting a batch of data from DSM for counterpart

We receive data from DSM that needs to be imported into our main repository.

Instructions

Prep

  1. copy files to local, they are large and won't work well on a share.
  2. copy the newly received batch of data from DSM_Lab_Extract#File_Share to a different local directory that contains only those files'not the requestor file & ''''the csv with no(#) beside it.

Check for new labs

  1. Open DSM_Labs_Consistency_check.accdb
  2. Make sure that you remove the combine copy of the csv file on your desk top copy.
  3. Click the "Check DSM .csv for new labs " button
  4. pick the directory with the new data files
  5. wait for the check to complete, about half an hour
  6. if the program tells you there are new labs click "no" and hold off on the rest of the import as you might have to re-do it if any of the new labs need to be included; follow the steps at Validating new types of DSM results; do this right away since some of the queries are based on labs imported within the last hour
    • If no additional lab, just close it.

actual data import

If the above listed no new labs, or the new labs were resolved, click the "Import DSM .csv" button and follow essentially the same steps as above to import the actual data. Close the file

Run consistency checks

  • Open DSM_Labs_Consistency_check.accdb
  • Open query Import_request_matcher
  • Get the Number of D_ID with no match lab DSM by following the steps below.
    • Go to design view and add the Import DtTm
    • SentDtTm column on criteria put the date you want to run and change the covered date (Ex Between Mar 27, 2019 and May 13, 2019)
    • Go to External Data then click excel
    • Go to S MED MED_CCMED CCMDB DSM Lab imports Archive_raw then to the folder you want to save
    • Click Export data with formatting and layout then rename Ex. (No_Match_DSM_450.xlsx) then save.

Cleanup

Move files to their permanent homes. Make sure you do all of these!

  1. Make a new directory in \\Hsc1msfp0001\med_ccmed\CCMDB\DSM Labs Imports\archive_processed and call it e.g. DSM_2019-03-27
    1. copy (not move) DSM_Labs_Consistency_check.accdb to that new directory
    2. copy (not move) DSM Labs data.accdb to that new directory
  2. move (not copy) DSM_Labs_Consistency_check.accdb and DSM Labs data.accdb from your computer to \\Hsc1msfp0001\med_ccmed\CCMDB\DSM Labs Imports\ (not the archive) - this is the master version.
  3. move (not copy) the folder of data you processed from the directory it arrived where we share with DSM to the archive_raw directory in the same folder (these are big so they really should be moved, not copied and left in two places)

Log import

Update the log at Instructions for requesting a batch of data from DSM#Log.

Integrity checks after import

Data Integrity Checks (automatic list)

 AppStatus
Query Import request matcherDSM Labs Consistency check.accdbneeds review

queries for which we have no page yet

D_ID Related Issues - Duplicate RequestNo For Incomplete Profiles sent more than once

This is caused by patients having more than one D_ID because

  1. The location portion of the D_ID has been changed after the requestor list has been sent to DSM.
  2. The D_ID format has been changed to include Laptop Identifier.

Every time the batch of DSM data is uploaded, matches maybe be found for same patient but labeled with diff D_IDs.

D_ID Related Issues - Missing Lab data for Completes

This is also caused by the change in the D_ID after the requestor list has been sent to DSM.

  1. The location portion of the D_ID has been changed.
  2. The D_ID format has been changed to include Laptop Identifier.

When uploading the DSM data, the new D_ID is being used for finding matches while the dumped Lab data is identified by the old D_ID. While present in DSM dumped file, no match was found resulting to no lab data for that profile.

Missing some Lab Dates

Found cases where Lab dates between Accept and Arrived are missing.

  1. This may be due to the changes done in the dates.

Duplicates due to Inconsistent Lab labels

Found three labs that are not labeled the same

  1. 'Arterial Lactat ' vs 'Arterial Lactate'
  2. 'Blood Gas Gluco ' vs 'Blood Gas Glucos'
  3. 'Blood Gas Sodiu ' vs ’Blood Gas Sodium'

Re-do the DSM matching : 29Oct2020

Julie went back to the dumped DSM data file starting from batches 2019-01-23 to 2020-10-14. She did the following steps using SAS program

  1. For each batch of data, identify the profiles with D_ID changes by comparing the requestor list and CCMDB.
    1. Make a new column adding the new D_ID. The old and new D_ID will be saved in CFE L_Problems_D_ID table.
  2. Get the Accept DtTm and Dispo DtTm for all records in requestor List from CCMDB L_log.
  3. Get from the dumped DSM datafile only the needed Lab tests.
  4. Match the subset dumped DSM data file and the requestor list by old D_ID.
  5. Get the records where Lab_DtTm is within the (Accept_DtTm or Arrived_DtTm) and Dispo_DtTm.
  6. Replaced those old D_ID (found in step1) with the New D_ID.
  7. Check duplicates due to lab labels or changed D_IDs and remove them.
  8. Compare D_ID of the matched DSM dataset with L_log to determine the unmatched cases (and %).
  9. Get the lab data from the DSM Database before the batch 2019-1-23 and combine it with the new matched dataset.
    1. check again for any duplication due to lab labels or changed D_IDs and remove duplicates.
    2. Make sure the lab data is unique by D_ID, registry no, Lab DtTm, Lab Test and result.
  10. Import the New_Lab_DSM to the DSM database.

Julie is still thinking to re-send those profiles found with no lab data to DSM. Julie will give Pagasa some more list of changed D_IDs to be added to the CFE L_Problems_D_ID table.

Re-do the importing: Sept 8-14, 2022

  • Tina updated the import program in ACCESS because matches are not completely done in the previous import program. It was agreed to re-do the importing from Allun's DSM dumped files using the new import program in ACCESS.
  • Pagasa did the following steps:
  1. First, remove from the L_DSM_Data all data imported after Oct 2020.
  2. Using the new import program, upload the DSM data one batch at a time starting from batch Dec 3, 2020 to batch Aug 31, 2022.

Known issues

None at this time

How does it work

Log

Update the log at Instructions for requesting a batch of data from DSM#Log.

Related articles

Related articles: