Whitepapers & Broschüren

Running Ad Hoc Queries in Real Time
Against the CHCS Database

Dave Nelson
Engineer, DoD Programs
InterSystems Corporation

Scott Lofgren
Manager, DoD Programs
InterSystems Corporation

Abstract

Working with experts from InterSystems Corporation, IT personnel at Davis-Monthan AFB Medical Center have developed a solution that reduces the time required to construct and run ad hoc queries against the CHCS database from weeks to minutes. The solution, running on a Caché data server, projects CHCS data structures as read-only object classes, and uses GUI tools to construct ad hoc queries. Almost all the processing is done on the Caché server, resulting in a minimal load on the CHCS data server.

The MDAR-T (Medical Data Access Real-Time) solution is suitable for any military treatment facility (MTF) employing the CHCS database, and has already been deployed at several military installations/bases.

Introduction

Health care administrators within the military have a problem. The data stored in the Department of Defense's CHCS-1 (Composite Health Care System) database is organized at the level of individual tables - almost 3000 tables. While this system is adequate for the doctor who treats one person at a time, it makes it difficult for those who need hospital-wide or program-wide statistics to compile the necessary information.

Currently, the only way to compile statistical information from the CHCS database is to construct and run a complicated ad hoc query. These queries require the programming skills of an expert, and even so may take up to two weeks to build and perfect. Additionally, complex queries can only be run overnight, to avoid slowing the day-to-day activity on the CHCS data server. Therefore, information needed to monitor the cost and progress of programs, to write reports, and to spot public health trends is often weeks out-of-date.

The MDAR-T Solution

Since the CHCS is based technology from InterSystems Corporation, IT experts at Davis-Monthan AFB turned to InterSystems for advice on how to extract statistical information from the database in real time, without overloading the system. The answer was to add a server running InterSystems' flagship DBMS, Caché. Caché is compatible with the multidimensional arrays used to store CHCS data, but it also has the capability of projecting data as objects. Additionally, Caché automatically enables SQL access to data, thus providing users a well-known, easy-to-use language for constructing queries.

To create the MDAR-T (Medical Data Access Real-Time) applications, developers built read-only object classes on the Caché server that corresponded to the low level data structures in the CHCS database. Because of a unique feature of Caché called the "Unified Data Architecture", all Caché object classes are automatically available as relational tables, and can be queried using SQL. SQL (Simple Query Language) is a widely-known industry standard used by most data analysis and reporting tools. In the MDAR-T solution, complex queries can be constructed in mere minutes using a GUI tool included in Caché. Alternatively, SQL queries could be created using any commercial query tool, or even a simple text editor.

When a query is run, the Caché server connects to the CHCS server via a socket-to-socket connection called DCP (Distributed Cache Protocol). DCP allows the Caché server to treat the CHCS server like another hard drive in the system, as it reads the data it needs to fulfill the request. Because the Caché server does most of the processing, normal operations on the CHCS server are not affected. The Caché-to-CHCS connection is read only, so there is no danger of CHCS data being changed, corrupted or deleted.

Benefits

Speed and ease-of-use are the obvious benefits provided by the MDAR-T solution. Information derived from queries can be pulled from the CHCS database in minutes rather than the weeks that it had taken to create/perfect ad hoc queries. Plus, with GUI tools for constructing queries, the work can be done by users at the treatment facilities - it does not require a high level of programming expertise.

Another benefit is flexibility of data presentation. Caché automatically enables both object and relational access to data. Relational access allows the data to be used by any ODBC or JDBC compliant application. Caché objects can be exposed to Java, C++, COM, XML, and other object-oriented technologies, easily enabling Web and wireless applications.

Future Steps

MDAR-T was prototyped at Davis-Monthan AFB, and is currently being deployed at installations/bases in the United States. MDAR-T could potentially be deployed at any facility that needs to extract statistical data from the Department of Defense's CHCS database.

MDAR-T is designed as a data analysis and reporting solution, but other applications could be developed employing the same technique of using a server running Caché to create an object representation of data in the CHCS database.