Creating a Schema Profile

Creating a Schema Profile throws what looks like a TON of information that you have to fill out, but it's really not. All you really need is a NAME, a Schema Prefix, and a connection to a Microsoft SQL Server database to get started. You WILL need to add some type of Data Source for HL7 messages (either files in a folder OR over TCP/IP OR both!), but to start with what's shown above is all you need.

Encrypted SQL Connections: Effective with version 1.2 of this software and in keeping with Microsoft's future visions for MS SQL Server you MUST check the Use Encrypted SQL Connection box.

In Part 1 you configure your Microsoft SQL Server Connection information and enable or disable Schema Maintenance.

SQL Connection Information:

Anyone who has worked with Microsoft SQL Server before will recognize these properties, as they are very common. You need to enter the SQL Server Name and the Database Name which will contain your Schema Tables.

NOTE: This software does NOT create SQL Databases. You have to create the database (or use an existing one).

After entering the Server Name and Database Name select the Connection Timeout, a value between 10 seconds and 60 seconds. Give some thought to this value. IF you are connecting to the SQL Server over a slow(ish) network OR the SQL Server itself is either slow(ish) or under a heavy load, you should steer more toward the higher end of the values. We also use the Connection Timeout as the basis for allocating a COMMAND timeout when we EXECUTE SQL Statements so speed of connection is not the only factor to consider.

Encrypted SQL Connections: Effective with version 1.2 of this software and in keeping with Microsoft's future visions for MS SQL Server you MUST check the Use Encrypted SQL Connection box.

Security Protocol. Here you have 2 choices:

•Use a Microsoft SQL Server User ID and Password

•Use a 'Trusted' Connection (IE Your Windows Logon)

If you choose SQL Server Security then the SQL User ID and SQL Password fields will become visible and you MUST enter them.

NOTE: IF you choose to use the 'Trusted' connection remember that IF you intend to run the CORE SQL Schema Engine MS Windows Services you will very likely have to manually configure each service to run as a Windows User who actually has a 'Trusted' connection.

Once you've entered these values test them by clicking Test Your Connection to the Database.

Database Maintenance:

If you want the Schema Engine to perform automated database maintenance (we recommend it) as it's running just check Enable Database Maintenance and fill out the remaining values. You can see Schema Maintenance for a detailed explanation of how the CORE HL7 SQL Schema Engine approaches database maintenance.

In Part 2 you have 2 sections.

Section 1: Schema Tables Creation and Verification

The HL7 Definition. You have only 3 choices, a Truncated definition based on version 2.2 of HL7, the ANSI Default version 2.3 definition and the ANSI default version 2.5.1 definition. IMPORTANT NOTE: The HL7 Definition is ONLY used when creating your Schema Tables (See Creating Schema Tables). The value you select will only affect how many segment data tables are INITIALLY created.

The Default Column Size. When your Schema Tables are created AND if new Schema Tables or Columns are created during Schema Training we will use the value you select here as the initial column size.

Run a Stored Procedure After Import

Preferred Text Encoding: This setting only matters if your HL7 messages have (or might have) special characters like (•, ™, or €) which are outside of the basic ASCII character set (the first 128 characters in any ASCII table). The VAST majority of HL7 2.xx messages do NOT contain characters from the extended ASCII character set.

Preferred Text Encoding Explained

Section 2: HL7 Import Settings

Check for Duplicate Messages. This value determines behavior when importing HL7 messages. See the topic on Message Uniqueness for more information.

Keep Segment Data in the Manifest table.

Folders and File Extensions to Monitor. Here is the primary setting for importing HL7 messages into the database from files on the file system. You can have up to four (4) different Folder / File Extension pairs in this setting and you use the and buttons to add or remove folders. When running your Schema Profile will monitor all of these folders SIMULTANEOUSLY for HL7 data files that match the extension you select and import all messages in those files into your database. It does not matter whether the files each contain only 1 message or more than 1 message.

In Part 3 you will select the HL7 Message Envelope settings. These are:

•BOM. The beginning of message character(s). All HL7 messages sent to this listener must begin with this character(s). The ANSI HL7 standard is a single Hex (0B) character

•

•EOM. The end of message character(s). All HL7 messages sent to this listener must end with these character(s). The ANSI HL7 standard is a Hex (1C) followed by a Hex(0D)

•

•SOM. The HL7 message segment delimiter. HL7 messages are made up of 'Segments'. The segments must be separated by this character(s). The ANSI HL7 standard is a single carriage return character  (a Hex(0D) ).

These three values are displayed in HEX. To change click the  button next to the value you want to change.

•Field Delimiter. This is the character used as the field delimiter in HL7 messages you expect to receive.

IMPORTANT NOTE: You should typically NEVER have to changes any of these values from their ANSI HL7 Default values unless you have a very specific reason to do so. Which, if you do, you will understand what you're doing and why. If the values are ever NOT set to their ANSI values the Reset Envelope to ANSI HL7 Default Values button will be enabled and you can just click it to restore all 4 of these settings.

See TCP/IP Listener for information about enabling the TCP/IP Listener.