How To

Manually Remove Data From Database

The DQA is an analysis tool and as such collects a great deal of information. Care must be taken to ensure that data that is no longer needed is removed. DQA v1.08 includes new functionality to automatically delete old data every week. The DQA supports several standard cleanup processes, which are documented here for those who wish to run the process manually using more efficient database scripts.

Data Fields

For every Message Received where the Received Date is older than the target date for deletion do the following:

• Delete every associated Next Of Kin
• Delete every associated Vaccinaton
  • Delete every associated Vaccination VIS
• Delete every associated Patient
  • Delete every associated Patient Id Number
  • Delete every associated Patient Phone
  • Delete every associated Patient Address
  • Delete every associated Patient Immunity

Message Text

For every Message Received where the Received Date is older than the target date for deletion set the Request Text and Response Text to null.

Message Analysis

For every Message Received where the Received Date is older than the target date for deletion do the following:

• Delete all associated Issue Found
• Delete all associated Receive Queue
• Delete Message Received

Submitted Batch Reports

For all Message Batch where the End Date is before the target date and where the Batch Type is S for Submission :

• Delete all associated Receive Queue
• Delete all associated Batch Issues
• Delete all associated Batch Code Received
• Delete all associated Batch Actions
• Delete all associated Batch Vaccine CVX
• Delete all associated Batch Report
• Delete Message Batch

Weekly Batch Reports

Same as Submission batch except for weekly reports.

Submissions

Delete all Submission Analysis and Submission where the Submission Status Date is before the target date.

Update Template Profile

Every application has a default Report Template that points to a base submitter profile which defines the defaults that are used when automatically creating a profile. For the DQA version 1.0 this is the primary way of configuring the DQA report for all the profiles. In future versions this process will become more fine-tuned to support different types of submitters.

How to Update Potential Issue Status for Template

1. Check out the the current ERD - Potential Issue spreadsheet from SVN and open in Open Office.
2. If working on a new version, re-save with the version number this is targeted for. Potential issues should not be added to previous versions, only to new versions yet to be finished.
3. Select 'Potential Issue' tab, navigate to columns G through I, and locate your systems default action code column.
4. Update that column's issues to reflect the correct issue status.
5. The SQL for inserting the values is listed in columns AH through AM. There are two basic profiles for each entity, one for reporting immunizations in general and one for reporting inventory data. (For the time being we are only interested in the reporting immunizations settings as this is the default used by DQA.) Make sure the database type is listed as 'Oracle' in cell AG2. This indicates to the formulas to make an Oracle version of the scripts.
6. Copy out both columns for entity you are updating and place in script file.
7. In script file add an initial line that deletes all the values from the dqa_potential_issue_status table. DELETE FROM dqa_potential_issue_status; This will remove all the potential issue status settings on all profiles in the entire project. (This will erase all customized settings for each profile but this should have no impact since no one is currently tweaking the potential issue status for each individual profile.) It is possible to only delete the potential issue status for just the profile we are inserting but any profile that has already imported data will continue to use the old settings unless you force it to refresh from the parent template by deleting its statuses. Deleting the whole table forces this refresh on all profiles.
8. Run script.
9. Restart project.

Test and Verify DQA Validation

Once the issues are set properly it's critical to verify that the DQA is working as expected. The DQA supports external testing using a separate open source tool, Immunization Registry Tester.

1. Install the Immunization Registry Tester locally or get a test account on the centrally hosted server.
2. Setup connect to the system you are testing.
3. Setup a test message that does not fail or generate warning messages.
4. Run profile on test system.

Clean Submitted Data for Testing

During testing it is sometimes necessary to remove all the data that was previously submitted and start back from scratch. Depending on the database doing this is quite simple:

• Oracle Run the clean.sql. This deletes all the imported data, messages received, and batches received values. Do not run this in production or you will loose all of your data.
• MySQL Simply delete the datbase and run the scripts again. A handy batch file clean-db.sql will run the scripts and do this. If you are not running on Windows, read the script and follow the steps manually or create a shell script.
• Hypersonic Simply update the database with the latest from the repository, overwriting what you have locally.