Training Your HL7 SQL Database

Use your UltraPort HL7 SQL Schema Engine to dynamically train your Schema database tables while importing HL7 messages.

Schema Training simply means that when you import HL7 messages into your HL7 Schema Tables the software needs to ensure that every possible bit of atomic data is directly addressable by you. Every HL7 Segment in the message MUST have a Segment Table(s). Every HL7 Component of every HL7 Field in that HL7 Segment MUST have a database column to go into.

In order to accomplish this our UltraPort HL7 SQL Schema Engine will make dynamic changes to your Schema Database by altering columns to make them larger (to avoid data truncation), adding database columns to tables (if the message unexpectedly contains a new HL7 Field and Column), and even creating NEW Schema data tables if needed.

How Does It Work?

Training occurs in Training Cycles and it may take more than 1 Training Cycle to correct any issues. When a HL7 message is imported the Schema Engine software will examine it to look at three factors in order:

1. Missing Tables. For every HL7 Segment in the message, is there a corresponding Segment Data Table? If not it generates SQL Statements to create any missing tables, executes those statements, and that completes a cycle.

2. Missing Columns. For every component in every field of every segment in the message is there a corresponding data column in the Segment Data Table? If not it generates SQL Statements to create any missing data columns, executes those statements, and that completes a cycle.

3. Truncation Issues. It looks at the individual actual VALUE of every component and determines if the LENGTH of that value is GREATER THAN the Varchar column size of the corresponding data column the Segment Data Table. If not it generates SQL Statements to ALTER the table and increase the column sizes, executes those statements, and that completes a cycle. NOTE: Here there is an algorithm used which is to always add FIVE (5) to the new altered column size and if the NEW size is greater than 512 characters the column size is altered to Varchar(Max). Example: A data column is a Varchar(10), we discover a component value of "0123456789ABCBDE" (16 characters), this would trigger a training event to alter the column and increase it from a Varchar(10) to a Varchar(21), 5 characters longer than the data discovered.

Since it evaluates the 3 conditions in order it should never take more than 3 training cycles on a single message to completely update your Database Schema to be able to import the source message with no errors. This is because we designed Training Cycles to avoid conflicts with each other in the processing order. Consider the above, if there are Missing Tables (#1) that Training Cycle will ONLY create any missing tables and then refresh the Schema Info it will not evaluate for missing columns. If there are no missing tables but there are Missing Columns (#2) that Training Cycle will ONLY create any missing columns and then refresh the Schema Info. It is ONLY if #1 does nothing, and #2 does nothing, that #3 is evaluated.