Page Comparison

Currently, the process has issues with the size of the data that is trying to be passed through it. The proposed solution is to break the process up and have the tracing and the extraction handled separately.

Extraction

The extraction will take the form of a simple query that will grab the AKI alerts for a set period. A simple UI could be built that would allow the user to specify the time range and would output a CSV fit for use by the stats team. It might be the case that the query is run directly on the database by a member of the stats team if access to the data is granted. The extraction itself requires further planning but in essence, it should be very simple to get the data out with a query and build a tool later if it is required/desired.

Pre Extraction

Before the extraction can take place, the data supplied with each alert needs to be compared with the data returned as a result of running NHS tracing. The proposed solution to get around the volume of alerts is to run the tracing daily(or weekly depending on numbers). A new table would be created that would store data in cases where the data from an alert and the data from a trace do not match. This would address the issue of the temporal state of traced data at the time of the alert and allow the data supplied by the lab to remain unaltered.

For example, if an alert contains the first name Andy but the result of a trace on the record returns first name Andrew then a row would be generated in the new table that would hold the column name of the incorrect value, plus the correct traced value and a reference to the alert that had the issue. At the time of extraction, a row in the CSV would hold both the raw data supplied by the lab and the corrected trace data.

Database Changes

To accommodate the above proposed changes the aki_reg database will require some alterations. Firstly the ALERT_RAW database will require some extra data points to assist with keeping track of tracing results and audit. A new table will be created to hold any data points that didn't match the raw data. This table will hold the traced data and a link back to the corresponding alert.

Use Case

An alert is raised with the following (uncomplete for simplicity) data.

A scheduled query will search for any alerts that have a traced status of NULL and create a tracing file with the results. A TRACE_ID will be set that will link all the alerts returned with the trace file. Once the tracing file has been traced, the results will be compared with the alerts with matching TRACE_ID's. For the majority of alerts a traced status and date will be added to the alert and no further action will be required because the tracing data will match the data of the alert. The extract will reflect that the data was identical.

For the alert above the tracing file contains the following data

Here there are two data points picked up by tracing that don't match, FORENAME and POSTCODE. This would generate two new rows in the TRACED_DATA table as follows

When the extract is run it will grab all of the raw data plus check for any differing traced data and produce a CSV that will look something like this

For Consideration

• Anonymised data points
• Opt-Out
• Date of Death