Tips for Working with Large EMR Data Volumes in Health360

Practically every Health360 implementation includes some level of integration with one or more Electronic Medical Record (EMR) systems - typically patient information and patient history – to get that 360 degree view of a patient.

In most cases, this refers to the use of HL7 messages, a standard used to transfer electronic data between disparate healthcare systems, though we are seeing the use of Fast Healthcare Interoperability Resources (FHIR), the latest standard to be developed under the HL7 organization, more and more. 

In this post, we will share some tips and tricks in working with large data volumes coming from HL7 messages, as well as steps to speed up your data integration performance without having to invest a lot of time and resources.

For reference, let’s take a look at a recent project that our Health360 team was tasked with. One of our customers needed help with the migration of 5.5MM patient records and ~32MM transactional records, as well as on-going integration (inbound and outbound). 

Let’s focus on the inbound messages. 

While not knowing upfront what type of throughput we were going receive, we set a target to have all of the data migrated within five business days.  To deal with the large data volumes, we leveraged the KingswaySoft SSIS Adapter for Dynamics CRM to help get the job done.

As you can imagine, the migration of a “Patient Record” is more than just demographic fields, each insert included a series of related records (up to four, in this case).  In our initial unit test of the data load, we were getting a throughput of approximately 25 records per minute which was much too slow so we rolled up our sleeves and made the following modifications to our process:

  1. Aggregate as much data in SQL for pre-processing – We were already populating a staging table with translated HL7 messages, however, there were quite a few “lookup” fields in the Health360 patient form that were contributing to the low load times. To mitigate, as a pre-processing steps, we ran a stored procedure on the staging data to populate the GUIDs for all lookup fields.  This pre-processing step allows us to remove this dependency and had a big impact to load time.
    SQL code
  2. SSIS/KingswaySoft has “Do not use” the For Each loop to prevent duplicates, instead we split the package into create and update processes.

  3. To find the first record to create an entity, try the following SQL query:SQL-code-2

  4. Disable all plug-ins and workflows during import – resist the urge to leverage plug-ins and workflows for data processing

  5. (For on-going integration) Ignoring unchanged fields, using Upserts instead of Updates – this is a tradeoff, but if relatively few (or potentially none) of the fields on an entity will change with an update, this can have a big impact.

  6. SSIS Parallel processing – KingswaySoft’s documentation indicate (and our results agree) that velocities of about 2.5x a single processing thread (at 5 parallels) using either the balanced data distributor data flow component or any other conditional split that makes sense for the data.

By making these adjustments we were processing up to 1,800 records single-threaded and approximately 3,500 multi-threaded during data migration in the end.

By making these adjustments we were processing up to 1,800 records single-threaded and approximately 3,500 multi-threaded during data migration in the end.

We hope that these tips and tricks help you with your EMR integration within Health360. Of course, if you need additional assistance, please reach out to our team for support.

Next Post