Schema Training in a Nutshell
Access Schema Training from the Actions Menu.
When your Database Schema was created we used a HL7 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 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 the CORE HL7 SQL Schema Engine will make dynamic changes to your Schema Database (HL7 Training), altering tables to make columns larger (to avoid data truncation), adding new database columns to tables (if the message unexpectedly contains a new HL7 Field or Component), and even creating NEW Schema tables if needed.
IMPORTANT NOTE: This obviously means that whatever SQL Server credentials you use in your Schema Profile, they MUST have full rights to the database. You don't have to use but the credentials MUST have full rights otherwise.
Training occurs in Training Cycles and it may take more than 1 Training Cycle to correct any issues. When a HL7 message is imported OR you go through the Schema Training method the CORE HL7 Schema Engine will examine the HL7 Message(s) evaluated 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 > 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 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.
|
When you create your HL7 database schema tables we use the HL7 Vendor Definition (which is just an object containing information about a particular version of HL7) you selected when creating your Schema Profile to determine which tables to create and what the database columns are which make up those tables. The software sizes the columns using the Default Column Size you selected in your Schema Profile. We do this because HL7.org is notoriously vague in the official documentation about this. For instance it might say that the Patient Name field (PID segment field #5) should accommodate 180 characters, but Field 5 of the PID segment is a HL7 Data Type XPN which can have from 7 to 15 different components (columns) depending on which version of HL7 you're using and there's not really any standard guidance on how to size the individual pieces that make up the field. For instance, the Last Name (Surname) which is component #1 should certainly be larger than the Suffix (Jr, Sr, etc) component.
Add to this, the issue of unknown or undefined fields and components as well as custom "Z" segments and for clients with less than reliable trading partners who don't adhere strictly to the HL7 standards and the problems only compound. Schema Training is finally the answer to all of this.
|
See Also: Database Training Demonstration In our YouTube Video.
