Library

Browse and search developer information

ClearWater User Guide

By Connecting for Health | 2012

Introduction

ClearWater is a set of software tools to assist organisations in importing urgent care clinical dashboard (UCCD) data into a Microsoft SQL Server database.

Urgent care dashboard data is distributed as XML files. Each file contains the following basic structure as shown in figure 1.

Figure 1 - UCCD XML structure.

Figure 1 – UCCD XML structure.

Each feed record represents data for a single patient for either an Out of Hours/Walk In Centre (OOHWIC) encounter or a Accident and Emergency (AE) encounter.

A distribution file can ONLY contain feed records of the same type, OOHWIC or AE. Different feed types cannot be mixed in the same distribution file.

ClearWater supports Urgent Care Dashboards Domain Message Specification Version: 1.0; Status: RC4, and is backward compatible with RC1, RC2 and RC3.

ClearWater reads and validates XML distribution files. It then writes the contents, if valid, to a set of staging tables in Microsoft SQL server. These staging tables can then be queried by an organisation to extract relevant data for inclusion in their own database tables.

Architecture

ClearWater has been developed in C# using the .NET Framework 3.5. It consists of a core library of .NET types called ClearWaterLib which implements the functionality for:

  • Reading and interpreting distribution files
  • Writing distribution file contents to SQL Server staging tables
  • Audit logging
  • Error logging

A windows client called ClearWaterWin which uses the library has been developed. This allows an end user to:

  • Configure application settings
  • Load a single distribution file into the SQL Server staging tables
  • Load all distribution files within a folder into the SQL Server staging tables

A console client called ClearWaterCmd which uses the library has also been developed. This can be run from either the command console or can be used in a command script to aid automation of the loading process. It provides the same functionality as ClearWaterWin.

Figure 2 - ClearWater Architecture.

Figure 2 – ClearWater Architecture.

ClearWaterWin

Starting

Start the application.

Figure 3 - ClearWaterWin

Figure 3 – ClearWaterWin.

The application has only one main window which consists of four areas:

  • Settings – where you can view, change and save application settings
  • Load XML File – where you can select a distribution file and load it into the SQL staging tables
  • Load XML Files from Folder – where you can select a folder that contains distribution files and load them into the SQL staging tables
  • Status – where status messages are displayed

Settings

The settings area displays the application settings. When the application first starts these are loaded from the application configuration file, ClearWaterWin.exe.config.

The Audit Log File text box contains the full path name to a text file which will be used to store audit records. If the file does not exist, ClearWaterWin will create it the first time it tries to write an audit record. Note that the audit log file will NOT contain any patient identifiable data.

To change the audit log file, simply type a new full path name to a text file in the Audit Log File text box.

To enable the writing of audit records to the audit log file, tick the Enable check control next to the Audit Log File text box.

To disable writing of audit records to the audit log file, clear the Enable check control next to the Audit Log File text box.

The Error Log File text box contains the full path name to a text file which will be used to store error records. If the file does not exist, ClearWaterWin will create it the first time it tries to write an error record. Note that the error log file will NOT contain any patient identifiable data.

To change the error log file, simply type a new full path name to a text file in the Error Log File text box.

To enable the writing of error records to the error log file, tick the Enable check control next to the Error Log File text box.

To disable writing of error records to the error log file, clear the Enable check control next to the Error Log File text box.

The Database Connection String text box contains the .NET framework ADO.NET format string to connect to SQL Server. A good reference site for connection string syntax is: http://www.connectionstrings.com/. Note you need to explicitly set the name of the database you have created as the Initial Catalog value.

To change the database connection string, simply type a new connection string in the Database Connection String text box.

The XSD Folder text box contains the full path name to a folder which contains the XSD files. These XSD files are read and used to help validate the content of distribution files.

To change the xsd folder, simply type a new full path name to a folder in the XSD Folder text box.

If you have made any changes to the application settings they will be validated and used when you either load a XML file or load XML files from a folder. However they will not be automatically written back to the application configuration file.

To save any application settings changes to the configuration file, click the Save Settings button.

Load XML File

First select the XML file to load, by either typing a full path name to the file in the XML File text box or click the Browse button next to it. This will display an Open XML File dialog box that will allow you to select the XML file.

Figure 4 - Open XML file dialog.

Figure 4 – Open XML file dialog.

Once the XML file has been selected click the Load button to read, process and store the contents of the distribution file into the SQL Server staging tables.

The load process first takes the application setting values visible in the settings section and checks:

  • Audit log file can be opened
  • Error log file can be opened
  • A database connection can be made to SQL Server
  • The XSD files in the XSD folder can be loaded

If any of these checks fails, an error message is shown in the status area and the loading process is aborted.

The load process then checks that the XML file exists and can be opened.

If this check fails, an error message is shown in the status area and the loading process is aborted.

The load process then reads and validates the distribution envelope within the distribution file. The distribution envelope contains a data item called TrackingID which is a globally unique identifier for each distribution file. The load process checks to see if the SQL Server staging tables already contain a record with the TrackingID value read from the distribution file. If it does then this distribution file has already been loaded, an error message is shown in the status area and the loading of the distribution file is aborted.

If both the application setting values checks, the XML file check and the TrackingID check are passed, the load process finally reads the rest of the distribution file content, processes it and stores it to the appropriate SQL Server staging tables.

If an XML validation error is encountered during the load process, an error message is shown in the status area, the loading of the distribution file is aborted and any data associated with the distribution file that has been written to the SQL Server staging tables is deleted.

If a database error is encountered during the load process, an error message is shown in the status area and the loading of the distribution file is aborted.

Load XML Files from Folder

First select the folder from which to load XML files from, by either typing a full path name to the folder in the Folder text box or click the Browse button next to it. This will display a Browse For Folder dialog box that will allow you to select the folder.

Figure 5 - Browse for folder dialog.

Figure 5 – Browse for folder dialog.

Once the folder has been selected click the Load button to read, process and store the contents of each distribution file in the folder into the SQL Server staging tables.

The load process will read ALL files found in the folder irrespective of their file name or file extension, therefore it is important that the folder contains ONLY distribution files you want loading.

The load process first takes the application setting values visible in the settings section and checks:

  • Audit log file can be opened
  • Error log file can be opened
  • A database connection can be made to SQL Server
  • The XSD files in the XSD folder can be loaded

If any of these checks fails, an error message is shown in the status area and the loading process is aborted.

The load process then checks that the folder exists and can be opened.

If this check fails, an error message is shown in the status area and the loading process is aborted.

For each file found in the folder:

  1. The load process reads and validates the distribution envelope within the distribution file. The distribution envelope contains a data item called TrackingID which is a globally unique identifier for each distribution file. The load process checks to see if the SQL Server staging tables already contain a record with the TrackingID value read from the distribution file. If it does then this distribution file has already been loaded, an error message is shown in the status area and the loading of the distribution file is aborted.
  2. If the TrackingID check is passed, the load process reads the rest of the distribution file content, processes it and stores it to the appropriate SQL Server staging tables.
  3. If an XML validation error is encountered during the load process, an error message is shown in the status area, the loading of the distribution file is aborted and any data associated with the distribution file that has been written to the SQL Server staging tables is deleted.
  4. If a database error is encountered during the load process, an error message is shown in the status area and the loading of the distribution file is aborted.

Stopping

To stop the application click the close button.

ClearWaterCmd

ClearWaterCmd is a console application that can be run from either the command line or from within a batch file.

Arguments

The application takes the following arguments:

Table 1 - Command line arguments.

Table 1 – Command line arguments.

The optional arguments allow you to either directly specify application settings without using a configuration file or allow you to override specific setting values read from a configuration file.

 Load XML File

If an XML file has been specified as an argument, ClearWaterCmd will read, process and store the contents of the distribution file into the SQL Server staging tables.

The load process first takes the application setting values provided (from configuration file and/or arguments) and checks:

  • Audit log file can be opened
  • Error log file can be opened
  • A database connection can be made to SQL Server
  • The XSD files in the XSD folder can be loaded

If any of these checks fails, an error message is written to the command line and the loading process is aborted.

The load process then checks that the XML file exists and can be opened.

If this check fails, an error message is written to the command line and the loading process is aborted.

The load process then reads and validates the distribution envelope within the distribution file. The distribution envelope contains a data item called TrackingID which is a globally unique identifier for each distribution file. The load process checks to see if the SQL Server staging tables already contain a record with the TrackingID value read from the distribution file. If it does then this distribution file has already been loaded, an error message is written to the command line and the loading of the distribution file is aborted.

If both the application setting values checks, the XML file check and the TrackingID check are passed, the load process finally reads the rest of the distribution file content, processes it and stores it to the appropriate SQL Server staging tables.

If an XML validation error is encountered during the load process, an error message is written to the command line, the loading of the distribution file is aborted and any data associated with the distribution file that has been written to the SQL Server staging tables is deleted.

If a database error is encountered during the load process, an error message is written to the command line and the loading of the distribution file is aborted.

Load XML Files from Folder

If a folder has been specified as an argument, ClearWaterCmd will read, process and store the contents of each distribution file in the folder into the SQL Server staging tables.

The load process will read ALL files found in the folder irrespective of their file name or file extension, therefore it is important that the folder contains ONLY distribution files you want loading.

The load process first takes the application setting values provided (from configuration file and/or arguments) and checks:

  • Audit log file can be opened
  • Error log file can be opened
  • A database connection can be made to SQL Server
  • The XSD files in the XSD folder can be loaded

If any of these checks fails, an error message is written to the command line and the loading process is aborted.

The load process then checks that the folder exists and can be opened.

If this check fails, an error message is written to the command line and the loading process is aborted.

For each file found in the folder:

  1. The load process reads and validates the distribution envelope within the distribution file. The distribution envelope contains a data item called TrackingID which is a globally unique identifier for each distribution file. The load process checks to see if the SQL Server staging tables already contain a record with the TrackingID value read from the distribution file. If it does then this distribution file has already been loaded, an error message is written to the command line and the loading of the distribution file is aborted.
  2. If the TrackingID check is passed, the load process reads the rest of the distribution file content, processes it and stores it to the appropriate SQL Server staging tables.
  3. If an XML validation error is encountered during the load process, an error message is written to the command line, the loading of the distribution file is aborted and any data associated with the distribution file that has been written to the SQL Server staging tables is deleted.
  4. If a database error is encountered during the load process, an error message is written to the command line and the loading of the distribution file is aborted.

Outputs

As well as writing error messages to the command line, ClearWaterCmd will also write status messages to the command line.

All these messages are written to stdout, and as such can be redirected to a file if required, for example:

ClearWaterCmd “D:\test.xml” > output.txt

ClearWaterCmd will also return an exit code that can be tested when the application is used within a batch file. The return values are:

0 – No error has occurred
1 – An error has occurred

SQL Server ClearWater Database

Overview

The database for ClearWater contains a set of staging tables to store the contents of the distribution files loaded.

The columns within these tables store all individual data items within a distribution file as varchars except for dates and times which are stored as SQL datetime.

Although all columns (except columns used for keys) can contain NULL, where there is no value found for a data item in a distribution file an empty string value “” will be inserted. For SQL datetime columns a NULL value will be inserted where there is no value found for the date/time in a distribution file.

Below in figure 6 is an overview of the database schema for the database used by ClearWater.

Figure 6 - Database schema.

Figure 6 – Database schema.

It is the responsibility of a database administrator or an appropriate application to delete data from the tables if required, for example after data has been SELECTed and INSERTed into other database tables. Foreign Keys are implemented in the database schema. Note as tables PrescribedItems, Investigations and PresentingComplaints can be linked to both OOHWICFeeds and AEFeeds tables, foreign keys are only provided to the DistributionFiles table. A cascading delete has been implemented for all foreign keys, therefore when a database administrator or an appropriate application deletes a record in the DistributionFiles table, all associated records in all other tables will be deleted.

The ClearWaterWin and ClearWaterCmd clients use optimistic database locking when using the database. It is therefore recommended, to improve performance, that when either ClearWaterWin or ClearWaterCmd are running no other access is allowed to the database.

DistributionFiles Table

The DistributionFiles table contains a record for every distribution file loaded.

Table 2 - DistributionFiles database table.

Table 2 – DistributionFiles database table.

OOHWICFeeds Table

The OOHWICFeeds table contains a record for every OOHWIC feed record loaded.

Table 3 - OOHWICFeeds database table.

Table 3 – OOHWICFeeds database table.

PresentingComplaints Table

The PresentingCompaints table contains a record for every presenting complaint associated with an OOHWIC or AE feed.

Table 4 - PresentingCompaints database table.

Table 4 – PresentingCompaints database table.

PrescribedItems Table

The PrescribedItems table contains a record for every prescribed item associated with an OOHWIC or AE feed.

Table 5 - PrescribedItems database table.

Table 5 – PrescribedItems database table.

Diagnoses Table

The Diagnoses table contains a record for every diagnosis associated with an OOHWIC feed.

Table 6 - Diagnoses database table.

Table 6 – Diagnoses database table.

Investigations Table

The Investigations table contains a record for every investigation associated with an OOHWIC or AE feed.

Table 7 - Investigations database table.

Table 7 – Investigations database table.

Outcomes Table

The Outcomes table contains a record for every outcome associated with an OOHWIC feed.

Table 8 - Outcomes database table.

Table 8 – Outcomes database table.

AEFeeds Table

The AEFeeds table contains a record for every AE feed record loaded.

Table 9 - AEFeeds database table.

Table 9 – AEFeeds database table.

Treatments Table

The Treatments table contains a record for every treatment associated with an AE feed.

Figure 10 - Treatments database table.

Figure 10 – Treatments database table.

SecondaryDiagnoses Table

The SecondaryDiagnoses table contains a record for every secondary diagnosis associated with an AE feed.

Table 11 - SecondaryDiagnoses database table.

Table 11 – SecondaryDiagnoses database table.

View_OOHWIC View

Some users have requested a single table view of the OOHWIC staging tables to help them load data into their UCCD applications.

The SQL View View_OOHWIC achieves this. The view contains all the columns in the table OOHWICFeeds plus 10 sets of repeating columns for the associated feed record data from the tables PresentingComplaints, PrescribedItems, Diagnoses, Investigations and Outcomes. These repeating columns are named with the pattern of, for example, Complaint01… Complaint02… etc. If there are no values for any of the repeating columns these are shown as NULL. Each of the repeating groups has an addition column named for example ComplaintMore. If there are more than 10 items in the group this will be set to ‘true’ otherwise it will be NULL. Finally the view contains columns from the DistributionFiles table.

The view columns are defined below in table 12.

Table 12 - View_OOHWIC database view.

Table 12 – View_OOHWIC database view.

The view can be used as if it were a table, for example:

SELECT * FROM View_OOHWIC

SELECT performance is likely to be dependent on the WHERE clause. For example a query using an indexed column should be relatively efficient:

SELECT * FROM View_OOHWIC WHERE TrackingID = ’…’

However a query using a non-indexed column will normally use table scans which are inefficient, for example:

SELECT * FROM View_OOHWIC WHERE VisitorStatus = ’…’

Source Code

The source code for ClearWater is provided in the folder:

InstallLocation\ClearWater\Dev

ClearWater has been developed using Microsoft Visual C# 2010 Express. The Visual Studio solution file is called:

InstallLocation\ClearWater\Dev\ClearWater.sln

When opened by Microsoft Visual C# 2010 Express you will see three projects as shown in figure 7.

Figure 7 - Microsoft Visual Express Projecs.

Figure 7 – Microsoft Visual Express Projects.

The ClearWaterCmd project contains the source code for the ClearWaterCmd console application. It contains the following objects shown in table 13.

Table 13 - ClearWaterCmd project.

Table 13 – ClearWaterCmd project.

The ClearWaterLib project contains the source code for the ClearWaterLib class library. In terms of design; the XML content of a distribution file is parsed into an object model, whose content values are then inserted into the SQL Server staging tables. The majority of the classes in this library represent the object model. The project contains the following objects shown in table 14.

Table 14 - ClearWaterLib project.

Table 14 – ClearWaterLib project.

The ClearWaterWin project contains the source code for the ClearWaterWin windows form application. It contains the following objects shown in table 15.

Table 15 - ClearWaterWin project.

Table 15 – ClearWaterWin project.