The "Test Schema" functionality has been deprecated starting with version 6.6.0 in favor of the more superior Auto Training functionality. For users of versions that have older versions you can expand the section below.
Open the Schema Testing window by clicking the Test Schema button on the toolbar in the Schema Utilities window.
Schema Test Window - Default View
Schema Test Window - Enhanced View
In the Schema Testing window you can use actual HL7 messages to validate your database table structure looking for potential issues which might cause problems like missing tables, missing columns, and even possible data truncation issues. IF issues are detected the test results will give you suggestions (including actual SQL statements you can execute) to fix the problem(s). We believe that this testing feature will greatly enhance your overall user experience with the UltraPort SQL Schema Engine.
The Schema Engine software uses the HL7 Vendor definition you selected in your Schema Profile to determine which HL7 data tables to create, which columns go into each table, and the defined size of those columns. It does this using an algorithm we created and in general it does a pretty good job, but, like all algorithms, it's not perfect. Here's a real world example taken from the screenshots above. In version 2.5.1 of HL7 in the PID segment field 11 component 9 is defined as the County / Parish Code. When the Schema Engine generates the SQL script to create the PID table it will create a column called PID_F11_C9 Varchar(20) to hold that data. When we scan a set of HL7 messages against the Schema we got the result snippet shown below.
Table: ABC_Segment_PID_A (1 Column)
Column Name: PID_F11_C9
Exception: [PID_F11_C9] in [ABC_Segment_PID_A] should be altered to size [25 bytes]
Data Detected: Wayne (City of Detroit)
Suggested SQL Fix: ALTER TABLE ABC_Segment_PID_A ALTER COLUMN PID_F11_C9 Varchar(25) NULL;
In the test we scanned 51 real messages and found at least 1 message that had data larger than the size our algorithm allocated "Wayne (City of Detroit)". You aren't required to do anything to fix this problem the Schema Engine can be configured to handle data truncation when Importing HL7 (see Schema Warnings and How C0 Columns Work), and there are ways to work around it if you need to create Outbound HL7 messages, but you might just want a quick fix to insure that you can handle whatever data your trading partner throws at you.
There is no right or wrong answer to this, OUR opinion here is that you should test your schema as soon as you create it. In fact you might actually want to run many cycles wherein you Create Your Schema, run extensive tests, then Remove Your Schema completely and start over.
What if I already have data in my Schema and don't want to lose it? This not really an issue. Just make sure that your UltraPort SQL Schema Engine windows services are NOT running if you intend to execute any SQL statements which will change the database structure.
Before you test your Schema you must first have created your Schema Tables. They do not have to actually have any data in them yet, in fact it's probably best if they don't.
You should assemble HL7 messages you wish to use for your test into a data file. It works best if you compile them into files with multiple messages (as opposed to individual 'single message' files). The testing engine will allow you to load up to 100 messages from a single file. If your file contains more than 100 messages everything over 100 is just ignored.
Click the 'Open File' button on the toolbar to select a data file containing 1 or more HL7 messages. The testing engine will allow you to load up to 100 messages from a single file. If your file contains more than 100 messages everything over 100 is just ignored. It will analyze your file and load the HL7 messages into memory and run a test cycle against all messages and display the results in the Results Area. See below.
Click to Zoom
You can click the Scan All button to retest the HL7 message(s) currently loaded.
IMPORTANT NOTE: Testing results are cumulative per file. This means that every time you open a new file, results from the previous file are discarded. What does that mean? Here's an example, let's say that we have created a column in the PID segment table called PID_F1_C1 and we created it as a Varchar(10). If you analyze a file with 10 messages and in that file message #1 has 11 bytes of data in PID 1.1 the tester would detect the error [need to resize the field to Varchar(11)]. If then message #9 has 13 bytes of data in PID 1.1 the tester would DISCARD the first error [need to resize the field to Varchar(11)] and replace it with a new one [need to resize the field to Varchar(13)]. If you don't take action on the results from file 1 and then load another file it would start from scratch.
Your test results will fall into 3 different categories. We will summarize them below and there will be extended discussions in another section below in this document.
1.Missing Data Tables. This can happen when your HL7 message contains custom HL7 segments (Z segments). It can also happen if, for example, you create your Schema using a HL7 Definition for version 2.3 of HL7 and you are testing with HL7 messages from a higher version (2.4, 2.5.1, 2.6, etc).
2.Missing Data Columns. Typically this would occur if the HL7 message being tested is a higher HL7 version than the one used to create your Schema. However this is not always the case. In the USA for years and years there a very large laboratory would (for some reason known only to them) add two extra components of data to the end of the Patient Name field (PID Field 5). So for a HL7 version 2.3.1 message which should have had a maximum of 7 components of data (HL7 data type XPN) theirs would contain 9. If you created your Schema with a version 2.3 HL7 definition, it would create 7 data columns for PID field 5 (PID_F5_C1, PID_F5_C2,...,PID_F5_C7). If you TESTED that Schema with one of these HL7 messages from that laboratory it would tell you that you were missing 2 columns (PID_F5_C8 and PID_F5_C9).
3.Columns with Truncation Issues. We use an algorithm to calculate how large to make our database columns in the Schema's Segment Data Tables and it generally does a good job but it's not perfect. Testing your schema will tell you if the data columns we created are large enough to contain the data in the messages without truncation.
When evaluating your test results you have to be clear on what your mission is with the UltraPort Schema Engine. Are you IMPORTING HL7 messages and want to make sure that your database tables will support the types of messages you need to receive? Are you EXPORTING HL7 and need to make sure that you are able to CREATE HL7 messages using the Schema Engine which are similar to the messages you test with? OR is it both? Your decisions on what actions to take with your test results might be very different if you are IMPORTING HL7 as opposed to EXPORTING HL7.
Missing Data Tables. This can happen when your HL7 message contains custom HL7 segments (Z segments). It can also happen if, for example, you create your Schema using a HL7 Definition for version 2.3 of HL7 and you are testing with HL7 messages from a higher version (2.4, 2.5.1, 2.6, etc). If you look at the example test results in the screen shots (see the image to the left), you'll see this entry about a table that is missing from our Schema:
Missing Table: ABC_Segment_ZZA_A
To Fix: Modify your HL7 Vendor definition to support this table and then repair your schema.
What this result tells us is that at least one of the HL7 messages tested contained a custom segment (ZZA) which is not part of the HL7 definition used to create your Schema. The result tells us how to fix the problem (Modify your HL7 Vendor definition to support this table and thenrepair your schema). Now you have a decision to make:
Do you care that this table is missing? If not then just proceed to steps 3b and 3c. There are many reasons why you might not care. For instance, if your mission is to IMPORT HL7 messages and you have no need in your mission for any data that might actually be in this ZZA segment then do nothing. Just configure the Schema Engine to ignore missing segments (which is the default behavior) by making changes to your Schema Profile (See Schema Warnings). However, if you need to create an HL7 message for EXPORT that contains a ZZA segment then you MUST fix the problem.
IF you need to fix the problem: We recommend that if you have missing tables AND you intend to fix the problem that you don't do anything else (IE Steps 3b and 3c below) until you have addressed the missing table(s) issue. Fixing the problem takes 2 steps:
1.You need to know what the HL7 structure of the missing table should be. You should be able to contact your Trading Partner or whomever provided you with the HL7 message(s) you are testing with and ask for their HL7 document which details the specifics of what fields and data types are contained in the missing Segment.
2.After you have that information you will need to edit the HL7 definition (or create a new one) and add the missing segment information (See HL7 Definitions) and then repair your Schema (See Repairing Your Schema).
After you've completed these fixes then run your tests again and proceed to steps 3b and 3c
Missing Data Columns. This happens when the HL7 definition used to create the Schema indicates that a particular HL7 field is made up of <x> components but the HL7 message actually has data in a higher component OR if your trading partner just decides to violate the standard and put data wherever they want (hey, it happens). If you look at the test results in the screen shots (see the image to the left) you will see this entry about a missing column:
Table: ABC_Segment_OBR_A (1 Missing Column)
Column Name: OBR_F20_C2
Exception: [OBR_F20_C2] is missing from table [ABC_Segment_OBR_A]
Data Detected: HNA_ACCN
Suggested SQL: ALTER TABLE ABC_Segment_OBR_A ADD OBR_F20_C2 Varchar(13) NULL;
What this result tells us that in the HL7 Vendor definition used to create the Schema tables field #20 of the OBR Segment is made up of a HL7 data type which only contains ONE (1) component. Thus, the Schema Engine only created 1 component data column (OBR_F20_C1). In at least 1 of the HL7 message(s) tested there was data in a 2nd component of Field #20 of an OBR Segment (HNA_ACCN). Now you have a decision to make:
Do you care that this column is missing? If not then just proceed to step 3c. There are many reasons why you might not care. For instance, if your mission is to IMPORT HL7 messages and you have no need in your mission for any data that might actually be in this column then do nothing. Just configure the Schema Engine to ignore missing columns (which is the default behavior) by making changes to your Schema Profile (See Schema Warnings). However, if you need to create an HL7 message for EXPORT that contains data in a Component #2 of Field #20 of the OBR segment then you really need to fix the problem.
IF you need to fix the problem: You can just click the Show SQL Fixes button (or select the Suggested SQL and right click), copy the SQL Statement(s) to the clipboard, paste them into a Query window in your database manager (MS SQL or MySQL) and run them. AFTER you run the SQL statements come back to the Test Your Schema window and click to re-test.
If your Schema is missing an entire HL7 field?. When you look at the segment data tables in your Schema you can see that the column names tell you where in the HL7 message the data that populates that column will come from. Consider the column shown above [OBR_F20_C2] this name tells you that the data in that column will come from Field #20, Component #2 of the OBR HL7 segment of your messages. The Schema Engine creates these columns based on the HL7 Vendor Definition you used to create your Schema. In the Vendor Definition there is a structure for each HL7 Segment which tells how many HL7 Fields there are (the F20 portion of the example column name), and within each HL7 Field how many Components there are (the C2 portion of the example column name). The testing engine can handle notifications and SQL Fix suggestions if there are missing or truncated records which represent COMPONENTS within an existing HL7 field. However, if there IS NO HL7 Field in the Vendor Definition, then there won't be any Columns defined in the database for that field number and thus no easy SQL only fix.
EXAMPLE: In HL7 version 2.5.1 the DG1 (Diagnosis) segment contains 21 HL7 fields. So in a SQL Schema based on HL7 version 2.5.1 there would be a table called ABC_Segment_DG1_A with lots of columns which would END at column DG1_F21_C1. If you run a test with an HL7 message which for some reason contained data all the way out in Field #24 not only would there be no columns to hold that data BUT you would also be missing all columns for Field #22 and Field #23 as well. Below is a sample of what the test result would look like for this:
Table: ABC_Segment_DG1_A (1 Missing Column)
Column Name: DG1_F24_C0
Exception: There are no data columns in Segment [DG1] for Field 24 in this schema
Data Detected: 6Chars
Suggested SQL: No SQL Fix. Modify the HL7 Vendor Definition for Segment [DG1] to accommodate up to HL7 Field # 24, Repair your Schema, and Test again.
As shown we cannot create a SQL fiix for you in this scenario you would need to follow the instructions to edit the HL7 Vendor Definition to correct the problem. Then Repair Your Schema so that the new field definition(s) have Columns created for them. Finally run your test again to make sure that you didn't miss any columns, or have any truncation issues.
IMPORTANT NOTE: IF you use the Suggested SQL Fixes presented to you in the test results we strongly suggest that you SAVE those SQL Scripts for later use. In the future you may need to drop this Schema and recreate it. You can create your own new internal process for dealing with this which looks like this:
STEP 1: Click the Create Schema Tables in the UltraPort SQL Schema Engine (See Creating Schema Tables)
STEP 2: Run the SQL Script that I saved from my initial testing of the Schema which contains all of the changes that I need.
A Note about the Suggested SQL and how it works: You will see in the example above that the missing column was detected and that the data it found was (HNA_ACCN). This data represents the LONGEST piece of data detected in ALL of the messages tested for OBR 20.2. If you count you will also note this data is only 8 characters (bytes) and that in the Suggested SQL it recommends adding the column as a Varchar(13). This happens because the testing engine will ALWAYS add 5 bytes to the recommended size presented to you and it will NEVER suggest creating a column smaller than Varchar(10).
Columns with Truncation Issues. This happens when a component of data in the HL7 message is larger than the defined size of the database column intended to hold it. In our example test we analyzed 51 messages and below is one of the truncation results.
Table: ABC_Segment_PID_A (1 Column)
Column Name: PID_F11_C9
Exception: [PID_F11_C9] in [ABC_Segment_PID_A] should be altered to size [28 bytes]
Data Detected: Wayne (City of Detroit)
Suggested SQL Fix: ALTER TABLE ABC_Segment_PID_A ALTER COLUMN PID_F11_C9 Varchar(28) NULL;
What this result tells us that in all of the HL7 messages we tested we found at least 1 value intended for the column PID_F11_C9 with data that is too large. In the database this field is a Varchar(20), too small to contain the longest value detected (Wayne (City of Detroit)). If the Schema Engine tried to import this data it would have to truncate the value to ( Wayne (City of Detroi ). If you needed to CREATE the HL7 message you would face the same problem. Now you have a decision to make:
Do you care that this data would be truncated? If not then do nothing. There are many reasons why you might not care. For instance, if your mission is to IMPORT HL7 messages and you have no need in your mission for any data that might actually be in this column then do nothing. Just configure the Schema Engine to ignore truncated data (which is the default behavior) by making changes to your Schema Profile (See Schema Warnings). However, if you need to create an HL7 message for EXPORT that contains the full value in this field (Wayne (City of Detroit)) then you should follow the Suggested SQL Fix.
IF you need to fix the problem(s): You can just click the Show SQL Fixes button (or select the Suggested SQL and right click), copy the SQL Statement(s) to the clipboard, paste them into a Query window in your database manager (MS SQL or MySQL) and run them. AFTER you run the SQL statements come back to the Test Your Schema window and click to re-test.
IMPORTANT NOTE: IF you use the Suggested SQL Fixes presented to you in the test results we strongly suggest that you SAVE those SQL Scripts for later use. In the future you may need to drop this Schema and recreate it. You can create your own new internal process for dealing with this which looks like this:
STEP 1: Click the Create Schema Tables in the UltraPort SQL Schema Engine (See Creating Schema Tables)
STEP 2: Run the SQL Script that I saved from my initial testing of the Schema which contains all of the changes that I need.
A Note about the Suggested SQL and how it works: You will see in the example above that the offending data it found was (Wayne (City of Detroit)). This data represents the LONGEST piece of data detected in ALL of the messages tested for that PID 11.9. If you count you will also note this data is only 23 characters (bytes) and that in the Suggested SQL it recommends adding the column as a Varchar(28). This happens because the testing engine will ALWAYS add 5 bytes to the recommended size presented to you and it will NEVER suggest creating or altering a column smaller than Varchar(10).
