---

 

Training your HL7 Database Schema Tables

 

When your HL7 Database Schema was created we used a HL7 Vendor Definition to determine which SQL Tables to create and which columns to put in those tables.

 

Schema Training simply means that when you import HL7 messages into your Schema Tables the API 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 your COREHL7SQLImporter object will make dynamic changes to your Schema Database, altering tables 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 tables if needed.

 

IMPORTANT NOTE: This obviously means that whatever SQL Server credentials you use in your COREHL7MSSQLConnector object they MUST have full rights to the database. You don't have to use SA but the credentials MUST have full rights otherwise.

 

 

 

 

How The CORE HL7 MS SQL Schema API Differs From The UltraPort SQL Schema Engine:

 

When you create your HL7 Database Schema using the CreateSchemaTables() method in the COREHL7MSSQLSchema object it will generate all of the tables according to the HL7 Definition you pass to that method. Unlike the UltraPort SQL Schema Engine software there is not a complex algorithm for calculating column sizes, instead all of the numbered segment data columns in your segment data tables are created as Varchar columns with a defined size of the BaseDBColumnSize property of the COREHL7MSSQLSchema object.

 

Also, unlike the UltraPort SQL Schema Engine software, Schema Training is not a "setting" which you can opt out of, it is automatic and will always be done when importing HL7 messages into your HL7 Database Schema.

 

How EXACTLY Does It Work?:

 

Training occurs in Training Cycles and it may take more than 1 Training Cycle to correct any issues. After every training cycle completes a call to RefreshSchemaInfo() is made.  When you call the ImportHL7Message() method OR the TrainHL7Message() method the COREHL7SQLImporter object will examine the HL7 Message passed in parameter 1 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 > 512 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 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.

 

When you call the ImportHL7Message() or TrainHL7Message() an error will be generated if there are more than 5 training cycles attempted.