Automating the Epic EHR with SQL/SSIS

There are multiple ways to interface with Epic, most of the attention is on Web Services or HL7 messages but web services seems to be the direction being pushed.  Not much attention is given to another type of interface, that is the Chronicles + SQL & SSIS method, given the potential opportunities, low cost, ease of customization, and rapid deployment opportunities, I believe it deserves more attention.

The basic idea is that you feed data from any number of existing 3rd party systems to a SQL Server database, this can be an HR system like PeopleSoft for personnel management (provisioning, terminations, transfers, auditing, etc..), a medical staff office database for maintaining provider privileges, licenses, or maybe a directory for provider address/phone/fax updates.  The point is, you can work with any system, all you need is an extract (flat file) or another means to access the data (setup a view, copy the tables you need, etc…).

Once we have the data available, a little SQL magic can take care of the rest, and this is why I believe this strategy falls apart for some teams… The people with the SQL knowledge are not usually your Epic analyst, this can be an obstacle as one without the other is impossible, and one with the other is still complicated when they don’t understand the capabilities and limitations of the other.

Here’s the basic concept

Epic EHR Automation

Overview diagram of Epic SQL automation.


I may have oversimplified the process a little, yes there are Epic jobs, batches, and runs that need to be set up in Chronicles to extract data (if not hitting Clarity) and then other jobs need to be set up to pull in those flat files that our SSIS packages will create, SSIS = SQL Server Integration Services, a wonderful graphical platform for all things ETL. 

As for the SQL Server part, this really depends on what your result is.  For terminations, you are probably going to pull data from HR, use that to search Epic for an account and then decide what action to take.  In my case, set an end date on the EMP, create a contact comment, create an import file, schedule the job and move on.  You also have to handle exceptions, but SQL Server makes it easy to send mail with existing stored procedures (sp_send_dbmail) or SSIS has some options which you can leverage to create service tickets for an analyst to review.  Oh and let’s not forget about the audit trail we need, the auditors come but once a year, so have a good log and no reason to fear.

My point is, automating processes in Epic EHR can be done using existing tools, faster than web services or HL7, and setup to run lights out (or semi-lights on if you use data courier and do everything in BUILD/POC). My next few posts are going to be a more in-depth look at the SQL process, working with some common data, and producing the results in a way that will lead us to an Epic ready import flat file.  I will also cover the different Epic released job templates that can be used for extracting data, importing data, and searching chronicles as well as maintenance and auditing tasks that can be completed using the same solution.  

Yes, there are CONs to using this solution… My next post will be a side-by-side comparison of automating processes in the Epic EHR with SQL/SSIS, HL7, and Web Services.