Tag Archives: Epic Automation

Epic EMR Automation – Web Services, HL7, SQL

Posted on by

Three distinct methods exist for automating processes within the Epic EMR, specifically, automation of personnel operations related to EMP & SER, they are web services, HL7, and Flat Files + Imports.   Each has its strengths, my focus is on leveraging SQL for lights out automation of all things maintenance.  Similar results could be had leveraging interface engines with HL7 messages or Web Services but I have opted to stick primarily with SQL/SSIS (flat files) for the lights out processes.

This post is in follow up to Automating the Epic EHR with SQL/SSIS and is a quick overview of some of the Pros/Cons of the various integration methods available.  The focus of this is automation/maintenance tasks that would fall under “operations”.

I considered a side by side comparison but it’s not that simple, what you are trying to accomplish will drive the solution.  Much of what I work on comes from database extracts, I can be handed the information, as is the case with PeopleSoft – HR (new hires, terminations, transfers) or I capture it by leveraging SQL Server to look for changes to a database extracts day-over-day and capture updates to things like provider privileges from the Medical Staff Office or address/phone/fax updates from directories.

If you are looking for a web application and real-time updates then you will be leaning towards Web Services, if everything happens behind the scenes and you want lights out, then SQL + scheduled imports will probably fulfill the requirements and likely without the need for big project teams and kickoffs.  HL7 is, in my opinion losing some footing in this space, there are some good use cases but when it comes to new deployments in this space (operations/maintenance/auditing tasks) I don’t know of any that would use HL7 over Web Services so I’m going to pass on discussing it.

We do lose “real-time” updates when relying on SSIS generated flat files, then scheduled import jobs to pick them up.  This can be mitigated if required, by scheduling jobs to recur at set intervals (hourly?) but for the most part, operations tasks do not require this and it’s unnecessary to have more than one sweep a day.  Case in point, terminations and/or provisioning, you take a list of employees that were processed the previous day and run them all as soon as you pickup your file, process them in SQL Server, drop a flat file to an ftp site and schedule a job to import that file.  Web Services would not lend itself well to this type of operation.

I’ve seen a demonstration of a web application used for account provisioning, allowing analyst to make updates to templates/subtemplates/user groups, etc… directly from a web form in real time, this would not lend itself as well to a SQL/import process unless that file was scheduled to be picked up and imported at say, 15 minute intervals… Probably not realistic and web services would outperform on many levels, but this comes back around again to real-time with an analyst interacting with a web app vs behind the scenes automation.

Another criteria which varies from organization to organization is the availability of talent, how many Epic Analyst have SQL backgrounds… probably a higher percentage than those with XML backgrounds, this can way heavily on deployment time for new projects, when you rely on web app teams you have…..relied on web app teams.  Depending on the organization, this can go both ways, and depending on how much experience they have it could be a prolonged process,  there’s a good chance it has been used for MyChart so this shouldn’t be new and Epic has a lot of sample code for web services via the Interconnect framework that covers a lot of common (EMP) tasks.

There are other issues to consider when relying on imports and some caveats that need to be considered as well as the impact of  your environment strategy (i.e if you provision in POC/BUILD you need a process to move your changes up to production, but this is not solved by one method of automation over another, that is to say, whether you use web services or imports, if you do not maintain EMP & SER in production you are still faced with the data courier issue.

The next post will cover how I leveraged SQL to aid in the data courier process and what options exist for simplifying/expediting moves and other Epic EMR automation tasks.

Automating the Epic EHR with SQL/SSIS

Posted on by

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.