Data Generation & Transfer
The basic principle is that we:
- fetch data from the EPIC system/team
- format the data in flat files desired by Plexus' team
- transfer csv files from EPIC1 network share
- zip the files,
- PGP encrypt the zip file
- uploading the encrypted zip file to an SFTP location.
Windows Server Based Approach (current approach)
- Sparc automation script that runs bi-weekly on the Window Server (
sparc_automation_script.ps1) - Uses automated tasks on remote Windows virtual machine (10/08/2024 1PM CST)
- wuit-s-11413.accounts.ad.wustl.edu
- i2-svc_sparc_user
- Move password to key vault
- Task runs a powershell script (sparc_automation_script.ps1)
- Connects to hipfs03.bjc-net.bjc.org\shared\IIG_SPARC\fromepic
- Upload to plexus via sftp
- DevOps repo Data Warehousing/ics_sparc_automation
Data Lake Based Approach (future approach)
Still working on the details. Coming soon!
Execution Details & Requirements
Data Generation Requirements
The Plexus team currently expects 20 text files broken down into the following sections:
LZ_STD_ENCOUNTER
LZ_STD_ENCOUNTER_NOTES
LZ_STD_ENC_DIAGNOSIS
LZ_STD_ENC_LABS
LZ_STD_ENC_LABS_NOTES
LZ_STD_ENC_MEDICATION
LZ_STD_ENC_PROCEDURE
LZ_STD_ENC_PROCEDURE_NOTES
LZ_STD_ENC_PROFESSIONAL
LZ_STD_ENC_PROVIDER
LZ_STD_PATIENT
LZ_STD_PATIENT_ALLERGY
LZ_STD_PATIENT_HISTORY
LZ_STD_PATIENT_HISTORY_SOCIAL
LZ_STD_PATIENT_INSURANCE
LZ_STD_PATIENT_OBSERVATION
LZ_STD_PATIENT_PROBLEM
LZ_STD_PATIENT_VACCINATION
LZ_STD_PROVIDER
LZ_STD_SMARTFORMTransfer Submission Requirements
The Plexus team, expects 2 files in the /uploads directory on the SFTP server:
-
WUSTL_extracts_{mmddyyyy}.zip.gpgThis file contains the EMR data files zipped and encrypted.
-
COMPLETE_WUSTL_extracts_{mmddyyyy}.txtThis is a blank or empty file which act as trigger file for Plexus' automation system. The presence of this file indicates the uploads are completed. On receiving the trigger file, their automated process searches for the extracts to process.
Testing
When uploading a test extract file to the uploads directory, please add prefix ‘TEST’ so we can differentiate between production and test extract. For example, TEST_WUSTL_extracts_{mmddyyyy}.zip.gpg.
Windows (winsrvr) to Databricks Transition Considerations
(discussion points from a 2025-01-09 group meeting)
- how do we transition from the old windows approach to the new databricks approach?
- have a "soak-in" period.
- ensure we've transferred any necessary old stuff archival
- decomissioning the of the VM and notification with Epic1 to remove storage