Navigating Schema Tables

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.

Table layout for the _HL7Data Table<prefix>_HL7Data Table Structure (click to open)

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.

Message Manifest Table<prefix>_MessageManifest Table (click to open)


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.

Example of a polling SQL StatementExample Polling SQL Script

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.


Example ReportExample HL7 Data Report. Schema Prefix [TBL] - (Click to open)