FileMaker Developer Conference 2010
INT013
Working with HL7 Data:
Tools and Techniques
Kevin Cunningham
KCunning Consulting
kcunning@alum.mit.edu
Who Am I? – FileMaker/Web Developer
• Co-authored MIT’s first official web page
• MIT’s “FileMaker Release Coordinator” 1996-2004
• Independent FileMaker Consultant 2004-present
• Founder/coordinator of BAFDA (Boston-Area
FileMaker Developer’s Association)
• Certified Developer 7/8/9/10/11
• TechNet/FBA Member
• DevCon Speaker 2005
Key Clients Who Use HL7
• Emergency Department at major urban hospital
– Registration + Lab data flows to our FM charting system
– Chart data flows from our FM system to Meditech
• Multi-office Cardiology practice in MA and RI
– Hospital + ePrescription data flows to our FM EMR
• Vertical Market EMR Solution developer
– ePrescription data flows to EMR
Overview
• What is HL7?
• Structure of HL7 Messages
• Mapping HL7 to FileMaker
• Options for Import/Parsing
• Options for Export
• Examples/Demos
• Some Additional Considerations
• Workflow Summary (One Approach)
What is HL7?
• HL7 = “Health Level Seven”
– International Standards Development Organization (SDO)
– Accredited with American National Standards Institute
(ANSI)
– Focuses on clinical and administrative data (while other
SDOs have other focuses)
– Derives its name from ISO’s Open Systems Interconnect
(OSI) model, where level 7 is “application layer”
• Industry Standard Format for Health Care Data:
– Demographic
– Financial (insurance, etc.)
– Clinical (visits, labs, meds, orders)
What is HL7?
• History of the Standard:
– Version 1.0 (1987) focused on exchanging information about
admissions, discharges, and transfers (ADT)
– Version 2.0 (1988) extended standard to include orders,
test/treatment reports, following ASTE E.1238.88 standard
– Version 2.1 (1991) was first widely-used version
– Latest V2 Version is 2.6 (2007) – 1,965 pages/717K words
– Versions have worked to preserve backwards compatibility
– Work on Version 3 began in 1992 and is ongoing, built on a
Reference Information Model (RIM) approach
– V2 is an 80/20 standard format for (EDI) messaging; V3 is a
model-based methodology with messaging implications
What is HL7?
• Usage of the Standard:
– “The vast majority of HL7 messaging
is done using messages that
approximate HL7 2.3 or HL7 2.3.1”
[Dave Shaver, The HL7 Evolution]
– “When you’ve seen one
implementation of V2, you have seen
one implementation; every one is
different.”
[T. Benson, Principles of Health
Interoperability]
What is HL7?
• Recent Focus on HL7 – “Final Rules” on Standards
and Certification include HL7 as a standard:
– Labs data to public health agencies: HL7 2.3.1 or 2.5.1
(and HL7 Implementation Guide if 2.5.1 is used)
– Surveillance data to public health agencies: HL7 2.3.1 or
2.5.1
– Info to immunization registries: HL7 2.3.1 or 2.5.1
– Vocabulary for immunization data: HL7 “CVX-Vaccines
Administered” standard
– Patient Summary Record exchange standard: HL7 Clinical
Document Architecture, R2 (CDA) Continuity of Care
Document (CCD)
Sample HL7 Message (v 2.3)
MSH|^~\&|MedSource|11448^Medical Services of NE|||20100719235213||
ADT^A28|6558575|P|2.3||||||||
EVN|A28|20090719235213|||11448|
PID|1|12916597|12916597|12916597|Cunningham^Kevin^M^^^^||19630909|M|||2
00 Lexington Street^^Woonsocket^RI^02895||(401)123-0684|||M|||023456789|
PD1||||F94145^Careman^Alvin^C^DO^|||||||||
PV1|1|||||||^^||
GT1|1||McDonough^Jackson^V^^||^^^^|||19430909|M||SE|037269828||||||||||||||||||M
|||||||||||||||||||||||| |
IN1|1|7954^Blue Chip for Medicare|116154|Blue Shield of Rhode Island|444
Westminster St^^Providence^RI^02903|^|(401)123- 1590|||||||||
McDonough^Jackson^V^^|SE|19430909|Po Box 3361^1097 Park
Avenue^Woonsocket^RI^02895|||1||||||||||||||0943223167002||||||||||||||
Structure of HL7 Messages
• Message (multi-line)
• Segment
– First field is 3-letter segment name
– Segments separated by carriage-returns
– Can be multiple of same segment type per message
• Field
– Separated by vertical bars |
• SubField
– Separated by carets ^
• In-Field components
– Separated by tildes ~
Notes about Content of HL7 Messages
• Dates are YYYYMMDD, or YYYYDDMMHHMM
• Not every field is supplied in every case, even by those who
follow the spec
• Often site-specific codes are used (think “value lists”)
• Sometimes multiple segments of the same type are included,
but the segment counter may not be incremented
• There are a variety of different event types (“A04” etc.) with
different implications for how the data should be handled
• Organizations frequently do not even follow their own spec
(though at least they usually do have a spec)
• The columns in the MSH segment are numbered differently
• Sample from Vendor Spec
Mapping HL7 to FileMaker
• HL7 message is the record of a multi-faceted event
– can’t simply map Message = record and be done (usually)
• Most obvious mapping is:
– Segment = record
– Relational
• But there are issues with this:
– Might be overkill if all you want is certain data
(e.g., just demographics, just prescriptions, etc.)
– Might want to collapse some relational fields into single master
table to allow easier import
– Might want to store full HL7 in FM, or store in external system (e.g.,
MySQL) and use ESS to grab specific data as needed (but to
maintaining that volume of data may be prohibitive)
Mapping HL7 to FileMaker
• This?
– MSH/EVN table
– PV1 table
– INS table
– OBX table
– Etc. …
• Or this?
– PATIENT table, with ins1, ins2, obx1, obx2, obx3 fields
• Or even this?
– PATIENT table, with ins[2] and obx[8] fields
Mapping HL7 to FileMaker
• How Should the HL7 Data Be Mapped?
– Need to understand HL7 model
– Need to understand how the user will use the data
– Need to accommodate (to some extent) existing database
architecture
– Need to understand trade-offs of relational data modeling in
FileMaker
• HL7 Map DB
Mapping HL7 to FileMaker
• Warning: HL7 Is Not Plug-and-Play!
– Each vendor implements HL7 in slightly different ways – so you’ll
need to modify the HL7 parser for each new installation.
– Missing Fields – some vendors omit fields rather than leave them
empty (this changes the location of subsequent fields).
– Same Data/Different Field – the same information may be located
in other fields (or even different segments) in different vendors.
– Same Data/Different Format – e.g., timestamp information should
be 19991231100000.000, but could be 19991231^100000.000
– Different Versions – specs may differ across (and possibly within)
different implementations of HL7.
– Missing Values (incl. mandatory fields) – 95% of fields are optional,
yet some vendors omit even the few required values.
– Invalid Segment Grammar – some vendors don’t adhere to the
segment grammar (what segments are included, in what order)
Options for Import/Parsing
• How Do You Get the HL7 Data in the First Place?
– Vendor must set up HL7 feed on their end
– Data is transmitted across the internet (e.g., over TCP/IP using the
HL7 Minimal Lower Layer Message Transport protocol Release 2)
• How Do You Receive It? (Need Communication Software)
– Vendor may supply receiver software for you to install (e.g., to
embed extra security for them that you can’t tinker with)
– You may install third-party receiver software (recommendation:
“HL7Comm” — see http://nule.org)
– I suppose you could “roll your own”… (e.g., 24U SimpleTalk?)
• Where Do You Put the Data You Get?
– Typically saved as files in a directory you specify, with a new file
created by timeframe (e.g., every 2 minutes, every 30 seconds,
etc.)
• Demos: HL7 Feeds
Options for Import/Parsing
• Key Question: When in the process should you parse
the HL7 stream?
– Before import? – need external parsing tools (PHP, OS)
– After import? – can use FileMaker techniques (calculations,
custom functions, etc.)
• Key Issues Impacting Decision:
– Speed of import
– Working file size
– Impact on other users in multi-user environment
• Demos: Import Raw HL7 versus Parse It First (arrays)
Options for Import/Parsing
• Key Question: Where should you put the parsed
data?
– Directly into destination table?
– Into intermediate staging table?
– a. Delete data immediately?
– b. Maintain HL7 shadow structure?
• Demos
Options for Import/Parsing
• Key Question: How should you update existing
records?
– Overwrite all fields – assume latest HL7 is best data
– Overwrite selected fields – assume HL7 data is only one part
of the puzzle
– What is the system of record?
– What are the match criteria?
• Demos
Options for Import/Parsing
• Key Issues related to updating existing data from HL7
import:
– Record locking
– Overwriting user-entered data (when okay to do so?)
– Importing bad data
– Dealing with corrections, esp. change of ID
Options for Export
• Preparing data for export as HL7:
– Know the recipient’s required format
– Construct the message with FileMaker calcs, custom funcs
– Send via a standard HL7 communication software
• Demos
Options for Export
• Sending the data:
– Export to OS, and send from there (or send directly from
FileMaker via serial plug-in?)
– Mark record as sent
– Clean up OS afterwards
• Demos
Some Additional Considerations
• How Should You Manage Multiple HL7 Sources?
– Remember: No two HL7 implementations are the same!
– Can have multiple send/receive applications running
simultaneously (of course, listening on different ports!)
– Watch out for resource competition (CPU)
– Watch out for db-access competition (will an automated
script kill a running script? May want to use a db script,
rather than an OS script, to trigger the load)
• Keeping the HL7 Data Secure:
– Machine security
– When to delete data from OS
Workflow Summary (One Approach)
• Model the expected incoming HL7 data
• Set process for obtaining HL7 data (e.g., HL7Comm)
• Create “Incoming” and “Archive” directories
• Automatically put HL7 data into Incoming directory
• In “near-real-time”, process/parse HL7 data into CSV file
• Put processed files into “Archive” directory
• Import processed data into FileMaker staging area
• Automatically transfer imported data into appropriate tables
– Overwrite existing data, if appropriate (deal with dupes)
– Manage data that should not overwrite (require intervention)
– Alert users of changes, if appropriate
• Delete CSV file from OS if successful
• Clear out temporary working areas (“Archive”, staging tables)
Questions?
FileMaker Developer Conference 2010
Web Update Page
www.filemaker.com/devcon/speaker_updates
(This is also listed in your conference binder)
This session WILL have updates.
Thank You!
Please remember to fill out your
evaluation at:
www.filemaker.com/devcon/evaluations
