Definitely saved us a bunch of money (and) helped us identify a big gap in the data.

Background and Challenge

A leading multi-state healthcare organization was converting and importing a large volume of data from a legacy lab system into Epic’s Beaker application. Before importing this data into the Production environment, they needed to ensure the imported data matched the exported data and identify any potential errors.

The traditional manual method for this effort is time intensive, error-prone, and costly. They would need 40+ temporary chart reviewers to review the data for errors. Finding a solution they could trust for this first batch of lab data and future import and export matching required a team of technical and analytics import experts.

Results

Solution

The client sought a cost-effective solution that would reduce effort, promote accuracy, save time, and be repeatable for future validation projects. The client would have to cancel the migration if they could not ensure a near-perfect matching of the imported data before moving it into the Production environment. Doctors use the imported data to treat patients and monitor their progress. Altered lab results pose a risk to a patient’s well-being.

The plan was to take a sampling of data from the legacy lab system, import that data into a copy of the Production system, and then use a raw pre-imported dataset to perform an automated validation. The format would display results in a sortable format with a separate error log for corrections. Automation would help remove human error, increase productivity, and provide an easily repeatable process.

Selecting the Tools

The client provided the raw legacy lab files as the source of truth and access to their Dev Clarity Beaker environment, which housed the data needed to compare. We built a standalone SQL database within the client’s infrastructure and configured the appropriate database connections on a virtual desktop to perform this work remotely. The local IT team installed the required tools on the virtual desktop, including Visual Studio, SSMS (used to deploy the SSIS package), Oracle SQL Developer, and Notepad++.

These tools allowed our data architect and engineer to query and extract data from Clarity, import data into the standalone SQL database, code and run the automated validation job, and export results for review. We met with the client’s lab conversion team after each round of validation to determine the validity of errors, help identify root causes, and receive feedback to make necessary changes for the next round of validation.

Testing and Honing the Process

We performed the first round of validation on 10,000 cases with a matching percentage rate of 89%. The second round of validation on 20,000 cases had a matching percentage rate of 99.74%. A third and final round of validation on 20,000 cases resulted in a matching percentage rate of 99.76%. The system compared 50,000 (277,763 rows of data) over eight weeks.

The manual review was estimated to take about five minutes per record, including loading the record in each system, reviewing each data element, documenting discrepancies, and closing the records. This amounts to 4,166 working hours (~520 days) to manually review all 50,000 records.

Providing Guidance for the Future

Along with delivering the automated solution, our team provided two training sessions and instructions for running the automated process. The training included making changes to the SSIS package and configuration files to use the solution for other Epic and non-Epic validation efforts. Using this solution for any future validation effort will produce long-term cost savings.

Outcomes

Go Live with Confidence

The automated solution, with a data validation matching percentage reaching 99.7%, gave the client complete confidence that the legacy data they migrated into the Epic Beaker Production environment was of the highest quality. To ensure that no errors fell through the cracks, the .3% with discrepancies were sent to an error log for correction before being imported.

Reduce Stress, Save Money Now and Later

The solution decreased the workload by providing an automated system and repeatable process. The estimated initial labor cost savings were between $150,000 and $250,000. By applying the solution to other projects, the potential savings will grow exponentially.