New post
Article
· Jan 7, 2020 3m read

Load Ensemble Messages into HL7 Spy v2020.1

#HL7 #Interoperability #Ensemble #Health Connect #InterSystems IRIS for Health

I'm always on the lookout for tools that make the development and testing of my interfaces more efficient. A couple of years ago I came across HL7 Spy, from Inner Harbour Software. It quickly became my go-to tool for running message comparison reports for interface engine migrations, message statistics gathering, and troubleshooting message receipt and delivery. It also offered enhanced functionality for things like fetching messages via sftp that other tools don't provide.

I've recently been working with HL7 Spy's author, Jon Reis, to enable support for fetching messages directly from the Ensemble message store. Its SQL Loader feature now has native Caché/IRIS support, and I've contributed a small server-side class to support the extraction of messages using it.

Version 2020.1 and higher, downloadable from this page, includes this native support along with a sampling of SQL Loader queries that facilitate the retrieval of messages from Ensemble. This article provides a walk-through for configuring the latest release and shows some selected queries.

Once you've installed the beta, locate the SQL Loader tab at the bottom of the window and select it:

The bottom pane will change to an SQL editor window with a Connect button in the upper left corner:

Click Connect, then create a new connection profile by clicking the Add button, and filling in the form. Note: If your admin has implemented SSL/TLS security on the SuperServer port, include the string Ssl=True in the Additional Parameters text box (#8 in the image below); also, the default ports (#7) are 51773 for IRIS and 1972 for Caché:

Next, click the Test button to verify that the connection works:

Finally, download the class HICG_HL7.xml, import it to your production namespace using Studio, and compile it. This class provides a SQL custom function called HICG.GetMsg() that takes a message body ID as its argument and populates a field in the returned result set with that ID's HL7 message.

That's it for installation and configuration. You can now take advantage of IRIS/Caché SQL to select messages based on values in Ens.MessageHeader or EnsLib.HL7.Message. A few examples:

1. Messages delivered to a Routing Process from a Service within a specified time frame

Click on each row in the bottom pane and HL7 Spy will display the related message in the upper pane.

2. Suspended Messages

3. Grouping

You can drag column headers to the grey bar at the top of the table pane, and records will be grouped/sub-grouped by the distinct values in those columns:

4. Selecting by both Message Header and Body Properties

Tips and Caveats

  • Selecting messages by body (EnsLib.HL7.Message) properties seems to require a bit of a hack. You must provide a WHERE clause that starts with a selection by ID, even something as simple as ID > 0; in the case of a JOIN, that can be either the Header or Body ID.
  • There's no reasonably performant option for selection by body content (other than the Identifier and Name properties, which are derived from MSH:10 and MSH:9 respectively). You should use IDs and other indexed properties such as SourceConfigName/TargetConfigName and Status to fetch the messages, then use HL7 Spy's powerful selection and filtering tools to find the specific message(s) you're looking for.
    • EDIT (10 January 2020): This is still generally a good idea, but I've incorporated the ability to do SearchTable queries as part of the message selection SQL query. That can speed things up ... see the comment below this article.
  • Searching by a Date/Time range using TimeCreated is much faster if you use the method shown in example #1.

Finally ... I'll be updating this article as new discoveries are made and features added.

Thanks for reading laugh

Discussion (5)2
Log in or sign up to continue
Jeffrey Drumm · Jan 9, 2020

I've added a query to the HICG.HL7 class that lets you leverage Ensemble's HL7 Search Tables to select messages by the fields specified for indexing. The new version is available on the HL7 Spy website.

An example, using the default search table to select messages by PatientID/MRN:

-- region 8 - message rows by SearchTable

SELECT
    tbl.BodyId,
    msg.TimeCreated,
    msg.Name,
    msg.DocType,
    HICG.GetMsg(msg.Id) As Message
    FROM
        HICG.TblSrch('EnsLib.HL7.SearchTable','PatientID','4444') tbl
    INNER JOIN
        EnsLib_HL7.Message msg
    ON
        tbl.BodyId = msg.Id
-- endregion
0 0
Jeffrey Drumm · Apr 20, 2023

Theoretically, yes. HCC supports ODBC access as of August 2022, but I suspect updates to firewall rules on your VPN connection to your HCC instances will be required. I believe TLS will have to be enabled for the ODBC connection as well (HL7 Spy can support this).

You'll also need to install the server-side class to support the fetching of HL7 messages (HICG_HL7.xml) to any interoperability namespace that you'll need access to from HL7 Spy.

The HCC instances are pretty locked-down, so user role(s) may have to be adjusted for access to the required tables ... and that may require special dispensation from ISC.

0 0