3.12 Data Management

   Database/Document Download

Demographic Export

This export is for transferring patient demographic, clinical and administrative data to another clinic, or to another compatible program.  Oscar will need to temporarily write to usr/local/temp for export to happen.

CMS Export

The Patient Demographic Export abstracts files and exports them in a standardized import export format

1. Click on the 'Demographic Export' (if you do not see this option contact your site support staff for privileges)
   
2. Choose a patient set from the drop down menu that you defined earlier in the Demographic Report Tool.  In this case we have selected the list of patient(s) that we labeled '>65'
   
3. What do you want to export?  Usually you want all options checked so 'Check All'
   
4. Click the Export button to export into either of the two XML formats supported  a) CMS spec 4.0 format (OntarioMD) or b)  E2E-DTC (BC Pito). 
   
5. The zip file can be used to import the patient(s) into another Oscar or other compatible software.  Alternately you can open it with a text editor or  Firefox  (the zip consists of a text file, a log file, and xml file) 

Diabetes Export

The Diabetes export  can be accessed here. 

1. Identify the patient set
2. Specify the date range
3. Export!
4. It will download a zip of XML files with certain data extracted from the chart as per OMD standard.

CIHI Export

• The CIHI export is another specific format (Canadian Institute for Health Information) 
• When this report completes the page will refresh with the generated file name appearing in the list.

Rourke 2009 Export

This export is of the older Rourke 2009 Forms on the system.  Most users have migrated to using the newer Rourke 2014 eForm.

Import New Demographic

This tool is to import patient(s) who have come from another clinic that uses Oscar, or other compatible software. 

1. Click on Import New Demographic
2. Browse to the file from the other system.  It has to be in CMS 4.0 spec format.  Leave the file zipped.
3. Options, if offered usually best to leave at default values
4. Import!

Merge Patient Records

The merge references one record as associated with the invalid record. The invalid record can be found using the search function of Oscar and when you open it up, it just loads the echart and demographic of the valid demo.

Technically the merge creates a pointer to the valid record and makes reading or writing to the invalid one difficult, by setting a role/object for provider "_all" with all rights denied for that demographic number.  Thus if there is information that you want kept, you should transfer it to the correct record before doing this procedure.

1. Search normally and then change the demographic name so that they are close and you know which chart to keep
2. Search again with the merge record facility so that both names are brought up
3. Check the check boxes for the ones that you want to merge. 
   
4. Click on the radio button for the main chart and
   
5. Then click the button that says 'Merge Selected Records'

Update Patient Provider

If a set of patients needs to change the Resident, Nurse or Midwife that cares for them, choose from the former and replacement provider, as well as the last name patient range, from the drop down menus.  Then, click "Go".

This example replaces the Resident 'doctor doe' with 'Peter Hutten-Czapski' for all of the patients with last names ending in A that were previously tagged as those for doctor doe

Document Actions

Product Dispensing

This is a new function available for those who need to maintain their own formulary from which they can dispense.

Fix notes with invalid role

• If you have some notes which have a reporter_caisi_role of 0
• This utility will fix them in a bulk way.
• Make sure you know what you are doing!

Audit Log Purge Tool

OSCAR 15 has much more complete logging than OSCAR 12. However after a few tens of millions of rows the log table may start slowing OSCAR down.

To improve performance, a new feature was added in OSCAR 15 build #722 to purge the log

• rows are backed up using mysqldump and saved as a sql file directly on the server so the data is not lost
• Allow a Gig of disk space for every 10 million log entries that you purge.
  
• The purge tool will log it's own action to the Log identifying the user who runs the tool as well as the purge range
  
• Identified rows will be removed from log table
• When complete the number of rows deleted will be displayed
  

NOTE THAT THIS TOOL IS QUITE RESOURCE INTENSIVE

run the utility when no one is using OSCAR as it will slow OSCAR to a crawl

 There are 2 modes

1. Automatically using a scheduler.
2. Manually using the Administration interface.

Note on limitations: This feature is designed for a local database.  Do not attempt to use on a remote database as the proper connection data will not be provided to mysqldump, and an error will about the function.

Manually purging the Audit Log

There's some configuration required to use this feature

1. In the properties file, you should be sure about your log.purge.minDays property. What this value tells the system, is to not allow the purging of any data which is more recent than the threshold set here. A default of 7 days (a week) is set but you may prefer a larger number to prevent users from the temptation of doing something nefarious and then making it hard(er) for the actions to be noted by deleting the log. If you set log.purge.minDays=30, the feature will never purge data collected within the last month. 
   
2. You must give the security object "_admin.auditLogPurge" to users who will be authorized to manually run this function from the administration panel. 
   
3. Set log.purge.mysqldump to the location of your mysqldump  executable. By default its set to /usr/bin/mysqldump
   
4. Set log.purge.outputdir to determine where the data to be purged will be written to (a mysqldump file). If not set, DOCUMENT_DIR will be used.  The file is uncompressed sql
   

You should be able to go to the Administration Panel, and under Data Management to access the wizard.

If you are purging a hundred million entries this will take a few hours to run.  Grab a book.

Automatically purging the Audit Log

Make sure you've setup the properties above accordingly. In addition, you will need to set log.purge.daysfromnowtopurge. This value determines how far into the past to purge the audit log records. If you set 365 here (the default), then every time this job runs, it will purge all audit log records older than 365 days.

Setup the job in the Oscar Job interface of the Administrative Panel.

The Job Type class is org.oscarehr.admin.job.AuditLogPurgeJob

Restoring a Purged Audit File

The Audit Purge file is written with INSERT INTO `log` VALUES syntax so its easy to undo the purge. 

Change directory to OscarDocuments/oscar, find the audit file in question, expand it and load it into your database.  Yes the file is called OSCAR_AUDIR_LOG_PURGE_FILE

NOTE: These files are huge so loading them into the schema will take MUCH LONGER than dumping the archive above (a 11G file can take several hours to load).  A file lock will be applied to the log table prevention other changes (from regular OSCAR use) being applied while the data are loaded.  For this reason it is suggested that you do this doing OSCAR downtime (We strongly suggest that you enforce downtime by stopping Tomcat)

$ cd /usr/share/oscar-emr/OscarDocument/oscar
$ ls *.sql.tar.gz
OSCAR_AUDIR_LOG_PURGE_FILE_20180101235959.sql.tar.gz
$ tar -xvzf OSCAR_AUDIR_LOG_PURGE_FILE_20180101235959.sql.tar.gz
$ mysql -uroot -p******* oscar_15 < OSCAR_AUDIR_LOG_PURGE_FILE_20180101235959.sql