Epic Data Courier

Data Courier

When I began work at Community Medical Centers, the first skill I learned well was working with Data Courier.

If you have worked with PeopleSoft or some other enterprise level software, you may have performed “migrations”.  For PeopleSoft, a developer might produce a “project” to be migrated from a development environment to a test environment for testing and then into production.

For Epic, Data Courier is akin to a PeopleSoft “migration”.

A website called Health IT Social has a nice layout of typical Epic environments.  In this case, they address a special case you will likely run into if you have upgraded your POC environment, for instance and need to migrate data to your TST and PRD environment which have not yet been updated.  We went through this experience a number of times, and for awhile even had separate TST environments for ICD 9 and ICD 10.

What Data Courier is Not

In the old days, Epic used ETAN to move data from one environment to another.  ETAN is a manual process where you export a record from one system and import it without modification into another system.  ETAN does not check for pointers to other records.  So, it is possible to end up with records in your target system that either point to non-existent records or to wrong records.

When used properly, Data Courier is safer and more complete.

Unlike a relational database based on SQL, Epic is built on a different kind of database engine called Chronicles that is built on Intersystems Cache, a version of M or Mumps if you are familiar with GT.M Mumps, for instance.

Chronicles calls its tables, databases, and each one has a three letter name.  For instance, EMP is an employee or really a user database, sort of, but not really.  There are real users and there are EMP records for other purposes which I won’t speak of here.  ERX is for prescriptions.  (Think Epic RX), and it is used heavily by the Willow team.  There is BED for, well, hospital beds, and they have to point to ROM or room records.

Chronicles databases are not exactly like SQL tables because there are fields that repeat, and you do not see the same limitations related to data typing that you see in relational databases.

This may bother data modelers who find it distasteful to violate what relational database modelers call “the first three normal forms”.

For instance, if hospital patients may have one or ten phone numbers, it would be unthinkable for a database designer to define a SQL table for patients with, three phone numbers hard coded as home, office and cell.  Instead, the database designer would separate this into two tables–one for patients, and one for phones.

The down-side of this is that when you want to read in the data for this patient, you must access one patient record and one record for each phone.  Oracle improves upon this by allowing you to “cluster” tables so that the patient records and the phone records specific to that patient can be included into the same database block

How does Epic handle it?  They might have a multi-line phone field included within the patient record, and this field would include a counter for the number of phones present.

Let’s Assume Separate Databases Instead

In real life, you would never ordinarily use DataCourier to move a patient record from POC (where developers make their changes) to TST and then to PRD.  You would not use ETAN to do it either.  These are entered by the appropriate users–doctors, nurses, those who schedule appointments, etc.

But, suppose someone really goofed up in an impossible way and put a patient record into POC.  Ouch.  HIPAA violation.  Right?  While he’s fumbling around he uses Data Courier to move it to TST and then to PRD.

There are problems on so many levels with this scenario.  First, it would be outside the possibility of any sane change control methodology.  Normal Epic users don’t even know about the POC or TST environments let alone have logins.  And the rights to use Data Courier can be quite sensitive and limited.  Many places do not even allow the build teams to use Data Courier.  The hospital where I worked allowed the build team members to create a move record but not to execute on it.

But let’s say a record gets moved.  All the phone numbers would move together if they were contained in a multi-line field and not in a separate database.  The same would be true for ETAN since you would basically export the record and import it in TST and then into PRD.

But what if the phones are put into another database?

ETAN would not care.  It would export the patient record and import it later, and no phones would be moved up.  The TST environment would not likely even know about the phones unless they were moved separately.

Suppose, though, you changed a phone number in POC and you wanted the change moved up to TST.  You might export the record with ETAN and import it, but what employee would it belong to?  The employee record might not even be there.

The Game of IDs

There is another interesting issue.   Every record in Epic’s databases has a unique ID field.  Suppose you create a new employee record in POC and you assign the ID as FRED123.  Now you go to TST and create the same record and call it FRED123.  Now you make a change in POC and you use ETAN to move it up to TST.  If you don’t have any links going out to other records, you should be fine.  Even if you do, as long as those links are not changed and they match in every environment, everything should still be fine.

But what if you don’t assign an ID and the system assigns a unique ID instead?  Let’s say you create a record and the system looks for the first available ID number in POC and that number is 123.  However, in TST, 123 is already in use.  A record had already been created with that number so TST gives you 456.  Now, when you ETAN your changes up from POC to TST, it overwrites the old record 123 in TST.  Ouch!

What does Data Courier do?

Data Courier requires that each record not only have an ID for uniqueness within that Epic environment (within POC or within TST or within PRD), but each record must also have a CID number which is unique across ALL environments.

Now, let’s say you created a record in POC that ended up with an ID of 123, but the record does not exist in TST or PRD, so you use Data Courier to move it up.  What happens?

First, Data Courier looks at the record you are moving up and takes note of both it’s ID and it’s CID.  Generally, the CID is automatically created as well as the environment number followed by a sequence number.  So, if the environment number for POC, TST, and PRD are 100, 110, and 120 respectively and you create a record in POC, and the next available ID sequence number is 1234 while the next available CID sequence number is 05, your new record will have an ID of 1234 and your CID will be 10005, which is 100 followed by 05.

You can find ID in field 1 and the CID is generally in field 11 of each record.

Now, when you use Data Courier to move the record to TST, Data Courier will look to see if ID 1234 is in use, and if not, it will make use of it.  But if it is in use and say, the next available ID is 1256, then it will use 1256 for the ID in TST for this record, but it will still use 12005.  It will create the record and move the data in the POC record into the new TST record.

ID (field 1) CID (field 11)
POC 1234 12005
TST 1256 12005

The next time data is changed in POC to be moved to TST, Data Courier will merely match the record by CID and transfer the data up.

Dependencies

The BED database has beds.  It has information about beds, such as what room the bed is in (a pointer to a ROM record).

Hence BED records have ROM records.  You must have a room before you can have a bed in it.

Senior members of the build teams are going to jump all over me if I don’t get this right as this has been a great area for painful screw-ups of the most painful kind.  Here, you cannot simply move things up and hope nothing gets broken.

But I am going to use it as an example just because I remember having to import both rooms and beds and DC (Data Courier) them up carefully moving up rooms, making sure all is well, and then moving up beds and never the other way around, even though Data Courier handles dependencies.

Let’s say we have a special bed for people getting a brain transplant or giving birth to a monkey.   We create a bed in POC.  It requires a room to go into, and we don’t want this patient to go into any room, so we create a special room for that patient.

Now, since we in the build team are not allowed to Data Courier up our records, we put through a request to move this BED record up to TST and PRD.  We’re confident it needs no testing.

Data Courier sees that the BED record has a dependency.  It has a pointer over to the ROM record for this new room.   So, Data Courier decides to move the ROM record first.

Data Courier looks at the ROM record’s ID and CID.  Someone else actually already created the ROM record in TST and PRD by hand but with different CID numbers.  WHOOPS!

So, Data Courier sees that a record exists in TST with the same ID record, but not with the same CID record, so Data Courier assumes the records are unrelated, and Data Courier creates a new record in TEST.  Duplicate records, but the TST environment does not know that because the CIDs don’t match, and now the new ID is picked from the next available ID number.

So, which record is the “real” matching record–the old one with the matching ID, or the new one with the matching CID?

As it turns out, the “real” one, as far as Data Courier is concerned, is the new record because it has a matching CID or Courier ID.

Now it wants to move up the BED record, and it points to the new ROM record.  In short, you can end up with inconsistencies and a very upset Chronicles or Cache administrator struggling to fix a serious problem in production without breaking anything.

CID Mapping

At CMC, we imported our EMP user records individually to each environment.  Afterward, we used the Chronicles menu to CID map each record across the environments.  Years ago, we mapped them to the POC environment’s CID number to indicate the records were created in POC and moved up, but later, I believe Epic suggested that we standardize on the CID in PRD and map the other environments to the PRD records instead.  I see some value in this in that it does not require tampering with the PRD record so if something gets messed up, it is messed up in a less sensitive environment, though it’s better to get things right regardless.

Copying Back

The heaviest form of “migration” I remember doing is when we copied environments–for instance when we created all the training environments from the MST database to ACE1, 2, 3,…, and unlike many other installations, we also had a TRN1, 2, 3…  Altogether, we had appriximately 42 Epic environments which we were told was about the second most of any company.  And there was a constant struggle between those who wanted to add more and those who wanted to reduce.

Copying from PRD down to another non-PRD environment often involved pausing PRD, doing a quick SAN snap or clone, unpausing PRD, and copying that clone to REL.  Having an SSD based SAN is precious for that purpose.  There is, in effect, no production downtime incurred in this process.  The database writers generally fire off their copies to disk every 80 seconds, and the commits just continue flowing into the transaction logs, which is a  pretty normal operation for any database with ACID properties.

Alternative, and perhaps better, one can have two shadow or mirror database systems–one for reporting and one for disaster recovery.  One can pause a shadow, clone it, unpause it, and copy it to a new environment.  Epic has a nice utility for copying down from PRD without containing any patient information, and another utility for removing what’s still left over but unneeded, but that is beyond the scope of this article about Data Courier.

In summary, Data Courier is a real treasure.  And if you are among those who use it, please, always, always look before you leap.  Always examine what Data Courier is going to do.  If you have change control or required approvals for each move, make sure that you have approval not only for the records your build team intends to move up, but also for the records that would be moved up automatically as dependencies.

If you are managing Data Courier, make sure you block unauthorized people from making any inappropriate or harmful moves without proper authorization.  It is much more comfortable to prevent problems than to fix them once they occur–especially with a thousand or more users and their management breathing down your neck.

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *