Navigating Your HL7 Schema

*Screenshots are from the UltraPort SQL Schema Engine version 5.2.3

As you may have noticed when you created your schema tables an UltraPort SQL Schema contains a number of tables. It's important not to let this intimidate you. For ordinary HL7 interfaces you might never have to deal with more than a dozen or so different HL7 segments. As to WHICH dozen, well that depends on what medical specialties you and your trading partner are trying to support.

Part 1 - Schema Table Naming Convention

In your schema profile you selected a schema 'Prefix'. When the schema tables were generated every object created begins with that prefix followed by an underscore (_). This was done to allow you to create multiple schemas within the same SQL database or to allow you to build your schema tables inside of a pre-existing SQL database without fear of overwriting any existing SQL objects (a remote chance, but stranger things have happened).

Part 2 - Global Tables

Global tables are any schema tables which are NOT segment data tables. Of the global tables most are used (or reserved for use) by the Schema Engine itself. There are 3 global tables which you will want to become familar with.

1) Your MAIN table, the one you will (probably) deal with more than any other is called <prefix>_HL7Data. This table will contain one (1) row for each HL7 message contained in your schema and should be your starting point when navigating the schema.

*[MessageID] [Varchar] (50) Not Null PRIMARY KEY, /*A GUID assigned assigned to the message */

[VendorName] [VarChar] (50) Not Null, /* name of the HL7 Vendor Definition in the schema profile */

[VendorVersion] [VarChar] (25) Not Null, /* HL7 version of the Vendor Definition in the schema profile */

[MsgControl] [VarChar] (50) Not Null, /* The Message Control ID (MSH Field 10) */

[PartnerAPP] [Varchar] (128) Not Null, /* Used to determine message uniqueness */

[DateLoaded] DateTime Default GetDate(), /* Date the message was first loaded */

[LastLoaded] DateTime Default GetDate(), /* Date the message was last loaded */

[LoadCount] Int Default 1, /* # of times the message has been loaded */

[MsgType] [VarChar] (20) Not Null, /* Message Type (MSH field 9.1) */

[MsgEvent] [VarChar] (20) Null, /* Message Event (MSH field 9.2) */

[Outbound] [Int] Not Null Default 0, /* non-zero indicates an outbound message */

[Inbound] [Int] Not Null Default 0, /* non-zero indicates an inbound message */

[Processed] [Int] Not Null Default 0, /* zero indicates unprocessed */

[Warnings] [Int] Null Default 0, /* # of entries in the _MessageErrors table */

** [Loaded] [Int] Not Null Default 0, /* non-zero indicates the segment tables are populated */

[SchemaLoaded] [Int] Not Null Default 0, /* reserved */

[StatusMessage] [VarChar] (1024) Null, /* populated by in and outbound processors */

[ArchiveID] [VarChar] (50) Null, /* reserved */

[HL7Format] [Int] Default 0, /* reserved */

[SegmentCount] Int Default 1, /* the number of segments the message contains */

[MessageSize] Int Default 0, /* the total byte-size of the message */

[HL7Message] Text Null ) /* the entire RAW HL7 message */

*NOTE: Use the value in the MessageID column to access data in the segment data tables and the message manifest table.

**NOTE: Very Important. The Loaded flag is very important for both importing and exporting HL7 data. When importing HL7 messages the Loaded flag will indicate whether the Schema Engine has completely finished loading the HL7 message into the database. When your Inbound Processor STARTS loading an HL7 message into the database the Loaded flag will be 0, when it has completed loading all of the Segment tables as well as the <prefix>_MessageManifest table it will set this flag to 1. When exporting HL7 messages your process should make sure that the Loaded flag is set to a non-zero value to indicate that YOU have completely finished loading your message data into both the Segment tables and <prefix>_MessageManifest table and the message is ready to be assembled.

2) The <prefix>_MessageManifest Table.This table will contain a 'vertical' listing for every HL7 message indicating each HL7 segment which that message contains and it's ordinal position within the message. This is particularly important to outbound processors which use this table when they 'assemble' an HL7 message for export.

Part 3 - Correctly identifying newly imported HL7 messages

When the Schema Engine imports an HL7 message the inbound processor does it in 3 steps for a NEW (ie not duplicate) message:

1.A row is INSERTED into the <prefix>_HL7Data table (refer to Part 2 above for the table structure) with a new GUID in the MessageID field. In this row the HL7Message field will contain the entire raw HL7 message, there are several fields from the MSH segment of the message to help you in making a quick decision without having to join to the MSH segment table like MsgType (MSH 9.1) MsgEvent (MSH 9.2) and MsgControl (MSH 10.1). The Inbound flag will be 1 (indicating an inbound message) and the Outbound flag will be 0. Finally, and VERY important the Loaded flag will be 0, indicating that the Schema Engine is IN PROCESS and loading the underlying Segment tables and the <prefix>_MessageManifest table.

2.The <prefix>_MessageManifest table and all of the underlying Segment data tables are populated with the HL7 message data.

3.Finally, when all of the above is completed the inbound processor will then UPDATE the <prefix>_HL7Data table setting the Loaded flag to 1 and the Processed flag to 0.

If an HL7 message is identified as a DUPLICATE message (see Identifying Duplicate Messages) the message is imported in four (4) steps:

1.The row in the <prefix>_HL7Data Table is identified and UPDATED. The Processed flag is set to 0 and the Loaded flag is set to 0, and the HL7Message field is updated with the raw value of the incoming message.

2.ALL of the data from the <prefix>_MessageManifest table and the Segment data tables is purged (deleted)

3.The <prefix>_MessageManifest table and all of the underlying Segment data tables are populated with the HL7 message data.

4.Finally, when all of the above is completed the inbound processor will then UPDATE the <prefix>_HL7Data table setting the Loaded flag to 1 and the Processed flag to 0 AND the LoadCount field is incremented by 1 AND the LastLoaded field is updated with the system date/time.

When you process messages which have been imported into an UltraPort MS SQL Schema we assume that your goal is to:

1.IDENTIFY new HL7 messages which have been imported into the schema tables which YOU have not yet processed.

2.FOR EACH HL7 message, you want to extract any/all data elements and import them into YOUR OWN separate database tables. For instance you wish to extract patient information from the Segment data table for the PID segment, insurance information from the IN1 segment tables etc, etc.

3.FLAG each HL7 message which you have extracted so that you don't inadvertently repeat the process.

To do this we recommend (but by no means require) that your process work like so:

•Your process should be a polling process rather than based on database triggers. In that it should periodically poll the <prefix>_HL7Data table (the more frequently the better, every 5 or 10 seconds) with a SQL Statement to identify new messages see the example below.

NOTE: In this example a Schema Prefix of ABC is used. You would obviously use the Schema Prefix you used in your Schema Profile.

/* SQL Generated by the UltraPort Schema Browser Version 3.0.0*/

SELECT ABC_HL7Data.MessageID, ABC_HL7Data.MsgControl,

ABC_HL7Data.MsgType, ABC_HL7Data.MsgEvent,

ABC_HL7Data.LastLoaded, ABC_HL7Data.DateLoaded,

ABC_HL7Data.LoadCount, ABC_HL7Data.SegmentCount,

ABC_HL7Data.Inbound, ABC_HL7Data.Processed, ABC_HL7Data.Warnings

FROM ABC_HL7Data

WHERE ABC_HL7Data.INBOUND <> 0

AND

ABC_HL7Data.PROCESSED = 0

AND

ABC_HL7Data.LOADED <> 0

ORDER BY ABC_HL7Data.LASTLOADED

Walking through this SQL Statement:

The SELECT statement

•The MessageID field is the unique GUID assigned by the SQL Schema Engine to every message

•The MsgControl, MsgType and MsgEvent fields all come from the MSH Segment (10.1, 9.1 and 9.2).

•The LastLoaded, DateLoaded and LoadCount fields relate to when and how many times a message has been loaded into the database. In 99.99% of cases the LoadCount field should ALWAYS be 1. If it's > 1 then you are processing duplicate HL7 messages and may need to investigate further as to WHY. See Identifying Duplicate Messages.

The WHERE statement

•INBOUND <> 0. Filters your results to only INBOUND messages (messages imported into the schema by an inbound processor). IF you DO NOT create outbound HL7 messages in this schema (and NEVER WILL) then this statement is unnecessary, but we recommend that you include it anyway so that should you decide in the future to create outbound messages you don't have to remember to come back and change it.

•PROCESSED = 0. For INBOUND messages (messages imported into the schema by an inbound processor) this PROCESSED flag belongs to YOU. After you have finished with each message (ie extracted the data elements, or even just looked at it and decided to ignore it) you should UPDATE this table and set the PROCESSED flag to a non-zero value, thus filtering this message out of any further table polling actions.

•LOADED <> 0. VERY IMPORTANT. This filtering statement insures that your polling query will not return any HL7 messages which are in the process of being loaded into the SQL Schema by the Schema Engine's inbound processor.

The ORDER BY statement

•ORDER BY LastLoaded. The order by statement insures that you process your HL7 messages in a "first in - first out" sequence which, in HL7 is very important.

•If your polling process returns NO RECORDS, then you have no messages to extract so just exit and try again later (5 or 10 seconds).

•If your polling process DOES RETURN RECORDS then you should iterate through EACH ROW returned, use the MessageID as the key value to access the HL7 message data in the Segment data tables and in the <prefix>_MessageManifest tables. After you have done this FOR EACH ROW you should then UPDATE the <prefix>_HL7Data table and set the PROCESSED flag to a non-zero value and only then MOVE ON TO THE NEXT ROW. After every row returned in your polling process has been evaluated and marked as processed you should then execute your polling SQL Statement again and repeat this process UNTIL YOUR POLLING PROCESS RETURNS NO RECORDS.

Frequently asked questions (Part 3):

Q: Why can't I use database triggers?

A: We don't say that you can't use triggers. We just don't recommend it for a couple of reasons. The most important reason is troubleshooting errors and problems, for instance here are a few things which can trip you up:

•If you read part 3 fully you should immediately see that if you intended to write a trigger on the <prefix>_HL7Data table it SHOULD NEVER be an INSERT trigger but only an UPDATE trigger. Why? Because the row is inserted FIRST into the <prefix>_HL7Data table BEFORE the underlying data tables have been populated.

•If your trigger encounters an UNHANDLED error it will cause the entire update to fail and the UPDATE you're triggering is being generated by the UltraPort Schema Engine which means that you will also cause it to fail and to go into an endless loop constantly trying to add your HL7 message, failing, resetting itself and trying again until your trigger is fixed. This means that IF you experience problems with your Schema Engine and contact customer service for support the first thing that we will tell you to do is to DISABLE your triggers and let the schema engine process the message and if that works, there's really NOTHING else we can do to assist you.

Q: Why should my process poll so often?

A: We assume that you want as near to "real-time" processing as possible. If done properly new HL7 messages can be a) received b) imported into the SQL Schema c) extracted from the SQL Schema to your database tables. All in a matter of seconds.

Q: How should I create My Process?

A: Honestly however you like. For MS Windows software developers we recommend that you write it as an MS Windows Service application (like the Schema Engine itself) using whichever environment you are strongest in like C#, VB.Net etc. If you're a very skilled MS SQL Programmer it can certainly be done as a scheduled job right in MS SQL Server.

Part 4 - HL7 Segment Data Tables

HL7 messages are made up of HL7 segments. The vast majority of the SQL tables that make up your schema are HL7 segment data tables which will be named <prefix>_SEGMENT_<segmentname>_<A or B>. Example for the MSH segment in a schema with the prefix ABC the schema table would be named ABC_SEGMENT_MSH_A.

NOTE: If the HL7 definition of any segment has 40 or more HL7 fields the Schema Engine will create TWO segment data tables. HL7 fields 1-39 will be stored in the table ending with _A and HL7 fields 40 and greater will be stored in the table ending with _B.

Layout: As generated by the Schema Engine the first 2 columns of each segment data table are: [MessageID] (the GUID assigned to the message) and [IDX] an integer which will be the ordinal position within the message (see the SegmentIDX column in the MessageManifest table). All of the remaining fields will be HL7 segment data columns.

Part 5 - HL7 Segment Data Table Columns

In all of your segment data tables other than the MessageID and IDX columns all other columns will be segment data columns which will be named like so:

•<Segmentname>_F<HL7 Field #>_C<HL7 Field Component#>

•Example. The patient's last name is stored in field #5 component #1 of the HL7 standard PID segment. So since the field number is < 40 we know that the column we want will be in the segment data table for the PID segment ending in _A. The column name will be PID_F5_C1 (PID segment_Field #5_Component #1)

Below is an example of a report pulled from the UltraPort Schema Browser which demonstrates a typical PID segment data table and actually shows how an inbound processor would populate that table with HL7 data. NOTE: The UltraPort Schema Browser can be an EXCELLENT resource for examining your schema tables.

PID - Patient identification

Schema Table: TBL_SEGMENT_PID_A

============================================================

Raw Value: PID||123-45-6789|123-45-6789||SHEIKH^ABID|||M||||||||||261113170|

============================================================

[MessageID]: 4d3a79bf-1a35-4823-8bcc-9723c5d369fb

[IDX] (Ordinal Position): 3

============================================================

HL7 Field #1 [SI] Set ID - PID

[PID_F1_C1] [ST] Sequence ID:

============================================================

HL7 Field #2 [CX] Patient ID

[PID_F2_C1] [ST] ID: 123-45-6789

[PID_F2_C2] [ST] check digit:

[PID_F2_C3] [ID] code identifying the check digit scheme employed:

[PID_F2_C4] [HD] assigning authority:

[PID_F2_C5] [IS] identifyer type code:

[PID_F2_C6] [HD] assigning facility:

============================================================

HL7 Field #3 [CX] Patient Identifier List

[PID_F3_C1] [ST] ID: 123-45-6789

[PID_F3_C2] [ST] check digit:

[PID_F3_C3] [ID] code identifying the check digit scheme employed:

[PID_F3_C4] [HD] assigning authority:

[PID_F3_C5] [IS] identifyer type code:

[PID_F3_C6] [HD] assigning facility:

============================================================

HL7 Field #4 [CX] Alternate Patient ID - PID

[PID_F4_C1] [ST] ID:

[PID_F4_C2] [ST] check digit:

[PID_F4_C3] [ID] code identifying the check digit scheme employed:

[PID_F4_C4] [HD] assigning authority:

[PID_F4_C5] [IS] identifyer type code:

[PID_F4_C6] [HD] assigning facility:

============================================================

HL7 Field #5 [XPN] Patient Name

[PID_F5_C1] [ST] family name (last name): SHEIKH

[PID_F5_C2] [ST] given name (first name): ABID

[PID_F5_C3] [ST] middle initial or name:

[PID_F5_C4] [ST] suffix (jr, sr, etc):

[PID_F5_C5] [ST] prefix (Mr, Mrs, Dr etc):

[PID_F5_C6] [ST] degree (MD, PHD etc):

[PID_F5_C7] [ID] name type code:

============================================================

HL7 Field #6 [XPN] Mother's Maiden Name

[PID_F6_C1] [ST] family name (last name):

[PID_F6_C2] [ST] given name (first name):

[PID_F6_C3] [ST] middle initial or name:

[PID_F6_C4] [ST] suffix (jr, sr, etc):

[PID_F6_C5] [ST] prefix (Mr, Mrs, Dr etc):

[PID_F6_C6] [ST] degree (MD, PHD etc):

[PID_F6_C7] [ID] name type code:

============================================================

HL7 Field #7 [TS] Date/Time Of Birth

[PID_F7_C1] [ST] Time stamp data:

[PID_F7_C2] [ST] degree of precision (optional):

============================================================

HL7 Field #8 [IS] Administrative Sex

[PID_F8_C1] [ST] Code value for user-defined table: M

============================================================

HL7 Field #9 [XPN] Patient Alias

[PID_F9_C1] [ST] family name (last name):

[PID_F9_C2] [ST] given name (first name):

[PID_F9_C3] [ST] middle initial or name:

[PID_F9_C4] [ST] suffix (jr, sr, etc):

[PID_F9_C5] [ST] prefix (Mr, Mrs, Dr etc):

[PID_F9_C6] [ST] degree (MD, PHD etc):

[PID_F9_C7] [ID] name type code:

============================================================

HL7 Field #10 [CE] Race

[PID_F10_C1] [ST] identifier:

[PID_F10_C2] [ST] text:

[PID_F10_C3] [ST] name of coding system:

[PID_F10_C4] [ST] alternate identifier:

[PID_F10_C5] [ST] alternate text:

[PID_F10_C6] [ST] name of alternate coding system:

============================================================

HL7 Field #11 [XAD] Patient Address

[PID_F11_C1] [ST] street address:

[PID_F11_C2] [ST] other designation:

[PID_F11_C3] [ST] city:

[PID_F11_C4] [ST] state or province:

[PID_F11_C5] [ST] zip or postal code:

[PID_F11_C6] [ID] country:

[PID_F11_C7] [ID] address type:

[PID_F11_C8] [ST] other geographic designation:

[PID_F11_C9] [ST] country ??:

============================================================

HL7 Field #12 [IS] County Code

[PID_F12_C1] [ST] Code value for user-defined table:

============================================================

HL7 Field #13 [XTN] Phone Number - Home

[PID_F13_C1] [ID] Telecommunications use code:

[PID_F13_C2] [ID] Telecommunications equipment type:

[PID_F13_C3] [ST] email address:

[PID_F13_C4] [NM] country code:

[PID_F13_C5] [NM] area/city code:

[PID_F13_C6] [NM] phone number:

[PID_F13_C7] [NM] extension:

[PID_F13_C8] [ST] any text:

============================================================

HL7 Field #14 [XTN] Phone Number - Business

[PID_F14_C1] [ID] Telecommunications use code:

[PID_F14_C2] [ID] Telecommunications equipment type:

[PID_F14_C3] [ST] email address:

[PID_F14_C4] [NM] country code:

[PID_F14_C5] [NM] area/city code:

[PID_F14_C6] [NM] phone number:

[PID_F14_C7] [NM] extension:

[PID_F14_C8] [ST] any text:

============================================================

HL7 Field #15 [CE] Primary Language

[PID_F15_C1] [ST] identifier:

[PID_F15_C2] [ST] text:

[PID_F15_C3] [ST] name of coding system:

[PID_F15_C4] [ST] alternate identifier:

[PID_F15_C5] [ST] alternate text:

[PID_F15_C6] [ST] name of alternate coding system:

============================================================

HL7 Field #16 [CE] Marital Status

[PID_F16_C1] [ST] identifier:

[PID_F16_C2] [ST] text:

[PID_F16_C3] [ST] name of coding system:

[PID_F16_C4] [ST] alternate identifier:

[PID_F16_C5] [ST] alternate text:

[PID_F16_C6] [ST] name of alternate coding system:

============================================================

HL7 Field #17 [CE] Religion

[PID_F17_C1] [ST] identifier:

[PID_F17_C2] [ST] text:

[PID_F17_C3] [ST] name of coding system:

[PID_F17_C4] [ST] alternate identifier:

[PID_F17_C5] [ST] alternate text:

[PID_F17_C6] [ST] name of alternate coding system:

============================================================

HL7 Field #18 [CX] Patient Account Number

[PID_F18_C1] [ST] ID: 261113170

[PID_F18_C2] [ST] check digit:

[PID_F18_C3] [ID] code identifying the check digit scheme employed:

[PID_F18_C4] [HD] assigning authority:

[PID_F18_C5] [IS] identifyer type code:

[PID_F18_C6] [HD] assigning facility:

============================================================

HL7 Field #19 [ST] SSN Number - Patient

[PID_F19_C1] [ST] string data:

============================================================

HL7 Field #20 [DLN] Driver's License Number - Patient

[PID_F20_C1] [ST] license number:

[PID_F20_C2] [IS] issuing state, province, country:

[PID_F20_C3] [DT] expiration date:

============================================================

HL7 Field #21 [CX] Mother's Identifier

[PID_F21_C1] [ST] ID:

[PID_F21_C2] [ST] check digit:

[PID_F21_C3] [ID] code identifying the check digit scheme employed:

[PID_F21_C4] [HD] assigning authority:

[PID_F21_C5] [IS] identifyer type code:

[PID_F21_C6] [HD] assigning facility:

============================================================

HL7 Field #22 [CE] Ethnic Group

[PID_F22_C1] [ST] identifier:

[PID_F22_C2] [ST] text:

[PID_F22_C3] [ST] name of coding system:

[PID_F22_C4] [ST] alternate identifier:

[PID_F22_C5] [ST] alternate text:

[PID_F22_C6] [ST] name of alternate coding system:

============================================================

HL7 Field #23 [ST] Birth Place

[PID_F23_C1] [ST] string data:

============================================================

HL7 Field #24 [ID] Multiple Birth Indicator

[PID_F24_C1] [ST] Coded value for HL7 Tables:

============================================================

HL7 Field #25 [NM] Birth Order

[PID_F25_C1] [ST] Numeric data:

============================================================

HL7 Field #26 [CE] Citizenship

[PID_F26_C1] [ST] identifier:

[PID_F26_C2] [ST] text:

[PID_F26_C3] [ST] name of coding system:

[PID_F26_C4] [ST] alternate identifier:

[PID_F26_C5] [ST] alternate text:

[PID_F26_C6] [ST] name of alternate coding system:

============================================================

HL7 Field #27 [CE] Veterans Military Status

[PID_F27_C1] [ST] identifier:

[PID_F27_C2] [ST] text:

[PID_F27_C3] [ST] name of coding system:

[PID_F27_C4] [ST] alternate identifier:

[PID_F27_C5] [ST] alternate text:

[PID_F27_C6] [ST] name of alternate coding system:

============================================================

HL7 Field #28 [CE] Nationality

[PID_F28_C1] [ST] identifier:

[PID_F28_C2] [ST] text:

[PID_F28_C3] [ST] name of coding system:

[PID_F28_C4] [ST] alternate identifier:

[PID_F28_C5] [ST] alternate text:

[PID_F28_C6] [ST] name of alternate coding system:

============================================================

HL7 Field #29 [TS] Patient Death Date and Time

[PID_F29_C1] [ST] Time stamp data:

[PID_F29_C2] [ST] degree of precision (optional):

============================================================

HL7 Field #30 [ID] Patient Death Indicator

[PID_F30_C1] [ST] Coded value for HL7 Tables:

============================================================

HL7 Field #31 [ID] Identity Unknown Indicator

[PID_F31_C1] [ST] Coded value for HL7 Tables:

============================================================

HL7 Field #32 [IS] Identity Reliability Code

[PID_F32_C1] [ST] Code value for user-defined table:

============================================================

HL7 Field #33 [TS] Last Update Date/Time

[PID_F33_C1] [ST] Time stamp data:

[PID_F33_C2] [ST] degree of precision (optional):

============================================================

HL7 Field #34 [HD] Last Update Facility

[PID_F34_C1] [IS] namespace id:

[PID_F34_C2] [ST] universal ID:

[PID_F34_C3] [ID] universal ID type:

============================================================

HL7 Field #35 [CE] Species Code

[PID_F35_C1] [ST] identifier:

[PID_F35_C2] [ST] text:

[PID_F35_C3] [ST] name of coding system:

[PID_F35_C4] [ST] alternate identifier:

[PID_F35_C5] [ST] alternate text:

[PID_F35_C6] [ST] name of alternate coding system:

============================================================

HL7 Field #36 [CE] Breed Code

[PID_F36_C1] [ST] identifier:

[PID_F36_C2] [ST] text:

[PID_F36_C3] [ST] name of coding system:

[PID_F36_C4] [ST] alternate identifier:

[PID_F36_C5] [ST] alternate text:

[PID_F36_C6] [ST] name of alternate coding system:

============================================================

HL7 Field #37 [ST] Strain

[PID_F37_C1] [ST] string data:

============================================================

HL7 Field #38 [CE] Production Class Code

[PID_F38_C1] [ST] identifier:

[PID_F38_C2] [ST] text:

[PID_F38_C3] [ST] name of coding system:

[PID_F38_C4] [ST] alternate identifier:

[PID_F38_C5] [ST] alternate text:

[PID_F38_C6] [ST] name of alternate coding system:

============================================================