Training Your Schema
Contents
NOTE: If you are running any version prior to version 6.6.6 of the Schema Engine software and want to use (or are using) the Schema Training feature we recommend that you upgrade your software. See the release notes for version 6.6.6 for more information and you should also review Upgrading From Prior Versions.
Starting with version 6.6.0 you can configure the SQL Schema Engine software to "Auto-Train" your database to accept your HL7 messages. When configured properly the SQL Schema Engine will dynamically alter your SQL Schema data tables at run-time by expanding the size of columns when a data truncation event occurs (data in your HL7 message is too large to fit into the designated column), by adding new columns to your tables if your trading partner sends information in fields or components which are NOT in your HL7 Vendor Definition, and even by adding new tables if your HL7 message contains undocumented "Z" tables or even if they send you an unexpected "newer" version of HL7 which includes standard tables which your schema doesn't know about.
In practice this means that you can actually create a database schema using the oldest HL7 standard which installs with the software (Default Version 2.2) and import HL7 messages in ANY version 2.xx format and your database schema will automatically be "trained" to accept all of the data with NO truncation, NO missing fields, NO missing columns and NO missing tables!
As was explained in Database Schemas 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 uses a "best guess" algorithm to determine how to size the columns in these tables because HL7 guidelines are pretty vague in this area. 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. The algorithm actually does a very good job, but there are always outliers and prior to version 6.6.0 the only options we could provide for relief were those outlined in the Schema Warnings topic.
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.
|
How to Configure Your Schema for Training
 The Global Settings Window
In the UltraPort SQL Schema Engine software "schema training" requires some important support functionality which is provided by the Database Maintenance service. For this reason you need to make sure that the Enable automated UltraPort database maintenance box is checked.
|
There are 3 (3) things that you need to do configure your SQL Schema for training. For it to work properly you MUST do all 3 of them.
 Editing Your Schema Profile
When you are creating (or editing) your Schema Profile check the "Automatically Train My Schema Tables" box. It's that easy but remember that you must also complete Step 2 below.
|
 Create a Maintenance Plan
For "Schema Training" to work you MUST enable and configure a database maintenance plan. In the Schema Engine software the database maintenance subsystems provide important support functionality for Schema Training.
|
Step 3, we know, will seem counter-intuitive because you have to set all of your warning levels to "Treat as FATAL" which for the entire history of this product has been an instruction for the software to HALT any inbound processor which encounters the condition and force a human (YOU) to either correct the issue in the database manually OR remove the offending HL7 message from your import queue.
We added Steps 2 and 3 to the training configuration because some might consider it an extraordinary step to allow software to dynamically alter a production database. So we added these additional steps to create a configuration that users really could not "accidentally" buy into without knowing what it does. If you do Step 1 without Steps 2 and 3 it doesn't work and if you do Steps 2 and 3 without Step 1 then your inbound processors will just HALT processing if they encounter one of the conditions (Truncation, Missing Column, Missing HL7 Field, Missing Table).
 Edit the Schema Warnings
Make sure that you set all 4 warning levels to "Treat as FATAL".
|
Actually this is very simple. All that you have to do is run the SQL Query shown below (substitute your schema prefix where it says <YourPrefix>.
SELECT * FROM <YourPrefix>_MessageErrors Where MessageID = 'SchemaTraining()' Order By DateLoaded
This will return xx number of rows which will show every SQL Statement executed by a Schema Engine inbound processor when running that alters the database. Database maintenance will never remove these rows from the table. See the example results screen shot below.
 Schema Training Results
In the results of the query the ErrorMessage column will contain the actual SQL statement that was executed by the inbound processor prefaced with a SQL comment. If you've executed the SQL command we've shown the commands will also be in the ORDER in which they were executed.
|
If you ever need to start over and completely Recreate Your Schema Tables then all of the changes made dynamically by "auto-training" will be lost and your schema tables are reset to their original layouts. If you do only this then training will start over when you begin importing new messages into your new tables.
It IS possible to recreate your schema tables AND retrain your database completely at one stroke, it just takes some planning. If you haven't done so you should read the section immediately above this one "How Can I Tell What Auto-Training has Done to My Database". If you want to recreate your schema tables AND retain all of the schema training done so far you would:
1: Execute this SQL Statement below BEFORE you recreate your Schema Tables.
SELECT ErrorMessage FROM <YourProfile>_MessageErrors Where MessageID = 'SchemaTraining()' Order By DateLoaded
The ErrorMessage column (if you read the section above this one) contains the Alter/Add SQL Statement executed by the training engine. You will next need to EXPORT the results of this query to a text file using your SQL Management Studio or controller and Voila! The file will contain runnable SQL (you may need to edit it a bit). The ORDER BY clause is very important. You want to make sure that when you run this file that your SQL Statements execute in the same order as the Schema Engine ran them to avoid problems. For instance, at 1:00 PM the schema engine alters column <xxxx> and changes it from a Varchar(30) to a Varchar(37) and then at 1:02 PM it encounters new data for the same column that is 40 characters wide and issues ANOTHER alter table to expand the column to a Varchar(45).
2: Recreate Your Schema Tables (see Creating Schema Tables)
3: In your SQL Management Studio RUN the file that you created in Step 1. |