Facility and Laboratory Identification Changes

The main changes are to change the two existing columns that are named with FacilityCode (ReceivingFacilityCode and TestingFacilityCode) and rename them to LabCode (LIMSReceivingLabCode and LIMSTestingLabCode); this will more precisely reflect that they are Laboratories and not general facilities. They will then be able to join up to the new "Laboratories" table which describes those values. The following diagram shows the example joins.

The join for LIMSTestingLabCode is not illustrated but is the same as the join for LIMSReceivingLabCode.

The diagram also demonstrates the other changes: LIMSFacilityCode renamed to LIMSRequestingFacilityCode to be more precise, LIMSVendorCode added to distinguish between codes provided by different vendors, new Laboratories table.

There are other small changes but that’s the bulk of the design change. The concept is simple but minor changes need made to a lot of existing code, queries and reports.

Detailed Proposal and Explanation

This will seem very long but I want to make sure everyone understand the changes. The initial updates scripts may need to be changed to accommodate each country's implementation.

The script attached to this page performs the structural updates and some basic data updates. It will need slight alterations for each implementation but nothing major. There are comments in the script describing the changes.

The following are the newly proposed changes to how facilities and laboratories stored in the OpenLDR. This method will require significantly more changes on everyone's part than the previously suggested changes. Even though this method requires more changes I think this method is a more precise representation of the actual data and will make reference terminology easier to use in the future. The data providers won't require many changes to the data structure they send but more changes are required as you move through the life-cycle of the data: database inserts/updates and especially views, data extracts and existing reporting efforts.

• • Retroactive changes to the existing data

  • Creation and population of a new table

  • Structural changes to the data from data providers

  • Import routines

  • Related database Views

  • Reporting templates

  • And some basic terminology changes for added precision

The document is broken into three parts.

• • Structural Changes

  • Explanation of how main structural changes are to be used

  • Implementation plans

Structural Changes

This is just the list of changes; more explanation is provided in the next section.

Laboratories - New table

LabCode varchar(15)

LIMSVendorCode varchar(4)

FacilityCode varchar(15) - The official facility code where the laboratory resides. Directly relates to Facilities.FacilityCode

LabName varchar(50)

LabType varchar(25) - (Clinical, QA, Reference, etc) country specific

StaffingLevel - varchar(25) Country specific designator

Facilities table

Updating the Province, Region, District and SubDistrict codes from two characters to 15 is a convenience being made to allow people to utilize existing codes assigned to those levels so they don't have to make up new two digit codes. The change carries through to the size of the HealthcareAreas.HealthcareAreaCode column because that column is a concatenation of the CountryCode and those four columns.

FacilityType - Update to varchar(15)

FacilityLevel - Update varchar(10)

ProvinceCode - Update to varchar(15)

RegionCode - Update to varchar(15)

DistrictCode - Update to varchar(15)

SubDistrictCode - Update to varchar(15)

MoHFacilityCode - Drop this column if it exists but wait until all changes have been implemented

ParentFacilityCode - Drop this column if it exists but wait until all changes have been implemented

IsLaboratory - Drop this column if it exists but wait until all changes have been implemented

HealthcareAreas table

HealthcareAreaCode - Update to varchar(62). This accommodates the larger individual codes in the Facilities table.

Analyzers

LIMSCode renamed to LIMSVendorCode

Requests table

These are the old columns listed for convenience:

RequestingFacilityCode

ReceivingFacilityCode

TestingFacilityCode

LIMSFacilityCode

Requests changes:

New: CountryCode nvarchar(2) - Convenience column

New: LIMSVendorCode varchar(4) - Used to join to Laboratory table and to clarify code provided by a specific vendor.

ReceivingFacilityCode changes to LIMSReceivingLabCode and size updated to varchar(15)

TestingFacilityCode changes to LIMSTestingLabCode and size updated to varchar(15)

LIMSFacilityCode changes to LIMSRequestingFacilityCode - Contains facility code used internal to the LIMS who logged the specimen. Updated to varchar(15)

RequestingFacilityCode - Remains the same and is the official MOH code for facility requesting the test.

This brings up an interesting point. Out of habit when working with SQL Server I generally use NVARCHAR rather than VARCHAR. I notice the initial scripts created people's databases with VARCHAR. I don't think Mozambique has run into any issues using VARCHAR for their special language characters but maybe I just haven't heard about them. Anyone have any other comments on this issue?

Narrative of Changes

TestingFacilityCode and ReceivingFacilityCode are now more precisely labeled as laboratories. And that the codes are internal LIMS codes. To get information about these laboratories you will join Requests to Laboratories using LIMSVendorCode and LabCode. The LIMSVendorCode is necessary because each vendor could be using different codes in internally. It is unlikely that each country has official codes for their laboratories. The Laboratories.FacilityCode column will point each lab to the official facility where it is located.

Note: Much of the data in the existing LDR instances already had these columns populated with what was called lab prefixes where were essentially lab codes but which did not have entries in the Facilities table because they were not official Facilities.

The change to LabCode for these two columns should not require any data content changes except that entries need added to the Laboratories table. The Laboratories table can populated by selecting from the Requests table. The LIMSVendorCode is available using characters 3-6 of the RequestID and currently whatever codes are in the ReceivingFacilityCode and TestingFacilityCode columns can continue to be used as the lab codes. Just do a DISTINCT or GROUP BY to get the unique combinations. You may need to then update the other column in Laboratories, for example the FacilityCode and LabName columns.

Not all laboratories need you to create a new unique code. If a vendor as already sending in the MOH Facility Code in these two columns that is okay to continue to use. This may actually be very common if a facility has a single laboratory and they simply refer to the laboratory and facility using the same name or code. Just be sure to create an entry for the laboratory in the Laboratories table.

Changing LIMSFacilityCode to LIMSRequestingFacilityCode is just a more precise naming of what is stored in that column. This column will store the LIMS' internal code for the facility requesting the data. The RequestingFacilityCode should contain the official MoH code for the requesting Facility. Ideally, the rows will of the RequestingFacilityCode filled in because this is the column which should be used to join to the Facilities table. However, don't worry if your data providers aren't completely coordinated and all sending in the official MOH Facility Code. Just make sure there is an entry for the code they are sending over in the Facilities table and then work towards getting everyone to start sending the official facility codes in that column. It's a little more work on your part because you have to make sure data providers are coordinating those codes so they don't send in the same code for different sites; but most implementations are already doing that coordination. The goal is to work towards the vendors sending the official codes and gradually phasing out having any LIMS vendor facility codes in that RequestingFacilityCode column; it will take some retroactive data changes as you do it but eventually it will work.

Laboratories

The addition of the laboratories table does not have to be a major project. Currently, most countries only have LIMS in the larger facilities and that limits the entries in the Laboratories tables. Smaller facilities often have their own laboratory and eventually we may be collecting electronic data from them; when we do they will most likely just send the data using their official facility code in the LabCode columns. They won't need entries in the Laboratories table until they start sending in electronic data. And for further clarification, this is true of the DisaLink sites and other "remote entry and results terminals". Those sites facilities codes will be indicated in the LIMSRequestingFacilityCode and RequestingFacilityCode columns but not the two LabCode columns.

As previously stated, it should be relatively easy to initially populate the Laboratories table using the data you already have in the Requests table with the exception of the FacilityCode and LabName columns. NOTE: It is a requirement that if the same vendor has multiple systems within the country they should be coordinating their facility codes, laboratory codes and hopefully the LIMSPanelCodes and LIMSObservationCodes. If not, they will need to provide unique LIMSVendorCodes for each lab that uses its own set of codes.

Implementation Plans

There are a couple of different options for moving your OpenLDR implementation to the new structure and they are all valid it just depends on your current implementation. A complete switch at one point in time to the next versus a gradual approach.

Both options involve testing all changes on a parallel test system and not trying to do the changes on the production system until all scripts and processes have been tested on an interim test system.

The complete switch at one time is a good option if your implementation is relatively uncomplicated. If you have multiple data providers and multiple different reporting systems you might think about a more phased approach but it is a more complicated approach.

A phased approach would be keeping the existing columns and structure but adding the new columns. You can gradually move processes to the new structure and when all of the components that use the new structure are working you can run a process to do any necessary data updates and drop the old columns. The downfall of this is that many of your reports might not work for the time while you are doing the conversion; so keeping the conversion time to a minimum is suggested.

Here is an initial list of steps to do the conversion. Each implementation will be slightly different and I don't know all the details of every implementation but you should be able to go through the list of changes above and do some searches to identify where changes will need made.

Reminder: Do this in a separate system from you live system and do as much of it as possible with scripts so if you make a mistake you can easily restore the database and start over.

Notify data providers of the change in naming conventions and any data format changes you would like them to make.

• • With these changes you could probably get by with just changing your import routine to just take the data they are currently sending and put in the new column names or separate data into new columns like the CountryCode and LIMSVendorCode. If the data provider is easy to work with I suggest sending them a new definition of the requested data structure with new fields names.

  • You will need to run some test files through your import system when they make the changes.

  • You will also need to set a date as to when the change will go into the production system and stay in close contact.

Database structure update script

• • Add new Laboratoris table

  • Auto-populate Laboratories table

  • Add new columns and copy data from old columns

    • Note: For renaming columns I create a new column and copy contents to the new column. I wait until after everything is complete to drop those old columns and then do the testing again.

    • The CountryCode and LIMSVendorCode columns should be able to be populated from the RequestID column. If a data provider is not currently providing that data in the RequestID you will need to write a separate step for the data from that provider and exclude their rows from any other update of those columns.

Add more data to the Laboratories table: LabName, FacilityCode, etc.

• • Again, I would try to do this with a script so it's easy to repeat.

Go through all of your database Views search for instances where you use each of the four FacilityCode columns and change the column names and how they are joined to their informational tables (Facilities or Laboratories).

• • My suggestion is to export each View as an ALTER and then merge them all into a single View update script where you make the changes.

Change data extracts and reports to reflect new column names and new structures

• • This is very different in each country. This is very similar to the steps for changing the Views but more difficult to make the changes in a reporting utility like Crystal reports.

  • For Yahara, we will need to work on the underlying views they've created and determine which, if any, columns we will need to change.

  • For the CDC Viral Load dashboard we may be able to get away with only changing the view and not the names of any of the columns.

Testing

TBD

Note: Drop any old columns or columns used for interim data transfers/transformations.

Miscellaneous country specific notes

We need to check on the LIMSVendorCode in Zambia. The LIMS component of their RequestID is different than the other countries.

Tanzania will require a significantly more changes than Zambia and Mozambique and I can work with them on the changes.

Additional Note on Facilities and HealthcareaAreas tables

This is just a note I feel responsible to add while I have people's attention. Each country currently uses the Facilities and HealthcareAreas tables differently. I have written up the original design with examples on the website. https://sites.google.com/site/openldr/design-documents/facilities-and-healthcareareas

This is the original design. However, I can understand why people have gone different routes on how they utilize the Facilities table. The specification isn't something you have to adhere to to the letter of how it is described. However, when it comes to creating tools that can be use from country to country it does bring up issues. The current way we are planning to cope with the differences in these structures is not to force everyone to use the designed structure but instead create country specific Views the reporting tool(s) will use. Each country's Views will return the same columns but derive them from slightly different columns, tables, joins.

Example Changes to Views and Queries

These are examples to show how easy it is to change the queries (hopefully Views) to work with the new fields. These are the SQL implementation of the view at the top of the screen.

New view for Laboratories table

CREATE VIEW [dbo].[viewLaboratories]

AS

SELECT l.LIMSVendorCode, l.LabCode, l.LabName, l.LabType, l.StaffingLevel,

f.*

FROM Laboratories AS l

LEFT JOIN viewFacilities AS f ON l.FacilityCode = f.FacilityCode

Primary Query Changes

The changes are high lighted in green.

1. All of the same columns were selected and only the highlighted columns were added.

2. The JOINS using the LabCode columns were updated to join to viewLaboratories from viewFacilities.

ALTER VIEW [dbo].[viewVL_Result]

AS

-- This view grabs the request and a little extra information about LIMSObservationCode = HIVVL

-- This query may eventually need to have some other technical information added like unit and reference ranges but for now just keeping it simple

SELECT

req.RequestID, req.OBRSetID, req.LIMSPanelCode, req.LIMSPanelDesc, req.LIMSVendorCode, req.CountryCode, req.AuthorisedDateTime AS HIVVL_AuthorisedDateTime,

req.LIMSRejectionCode AS HIVVL_LIMSRejectionCode, req.LIMSRejectionDesc AS HIVVL_LIMSRejectionDesc,

hivvd.LIMSRptResult AS HIVVL_ViralLoadResult, hivvr.LIMSRptResult AS HIVVL_ViralLoadCAPCTM,

hivrl.LIMSRptResult AS HIVVL_VRLogValue,

req.AgeInYears, req.AgeInDays, req.HL7SexCode,

req.SpecimenDatetime, req.RegisteredDateTime, req.ReceivedDateTime, req.AuthorisedDateTime, req.AnalysisDateTime, req.LIMSRejectionCode, req.LIMSRejectionDesc,

req.DateTimeStamp, req.Versionstamp, req.LIMSDateTimeStamp, req.LIMSVersionstamp,

req.LOINCPanelCode, req.HL7PriorityCode, req.AdmitAttendDateTime, req.CollectionVolume,

req.LIMSFacilityCode, limsFacility.[Description] AS LIMSFacilityName, limsFacility.ProvinceName AS LIMSProvinceName, limsFacility.DistrictName AS LIMSDistrictName,

req.RequestingFacilityCode, requestingFacility.[Description] AS RequestingFacilityName, requestingFacility.ProvinceName AS RequestingProvinceName, requestingFacility.DistrictName AS RequestingDistrictName,

req.LIMSReceivingLabCode, receivingFacility.LabName AS ReceivingLabName, receivingFacility.LabType AS ReceivingLabType, receivingFacility.FacilityCode AS ReceivingFacilityCode, receivingFacility.[Description] AS ReceivingFacilityName, receivingFacility.ProvinceName AS ReceivingProvinceName, receivingFacility.DistrictName AS ReceivingDistrictName,

req.LIMSTestingLabCode, testingFacility.LabName AS TestingLabName, testingFacility.LabType AS TestingLabType, testingFacility.FacilityCode AS TestingFacilityCode, testingFacility.[Description] AS TestingFacilityName, testingFacility.ProvinceName AS testingProvinceName, testingFacility.DistrictName AS TestingDistrictName,

req.LIMSPointOfCareDesc, req.RequestTypeCode, req.ICD10ClinicalInfoCodes, req.ClinicalInfo, req.HL7SpecimenSourceCode, req.LIMSSpecimenSourceCode,

req.LIMSSpecimenSourceDesc, req.HL7SpecimenSiteCode, req.LIMSSpecimenSiteCode, req.LIMSSpecimenSiteDesc, req.WorkUnits, req.CostUnits,

req.HL7SectionCode, req.HL7ResultStatusCode, req.RegisteredBy, req.TestedBy, req.AuthorisedBy, req.OrderingNotes, req.EncryptedPatientID,

req.HL7EthnicGroupCode, req.Deceased, req.Newborn, req.HL7PatientClassCode, req.AttendingDoctor,

req.ReferringRequestID, req.Therapy, req.LIMSAnalyzerCode, req.TargetTimeDays, req.TargetTimeMins, req.Repeated

FROM Requests AS req

LEFT JOIN (

SELECT * FROM LabResults WHERE LIMSObservationCode = 'HIVVD' -- CAP/CTM

) AS hivvd ON req.RequestID = hivvd.RequestID AND req.OBRSetID = hivvd.OBRSetID

LEFT JOIN (

SELECT * FROM LabResults WHERE LIMSObservationCode = 'HIVVR' -- Viral Load Result

) AS hivvr ON req.RequestID = hivvr.RequestID AND req.OBRSetID = hivvr.OBRSetID

LEFT JOIN (

SELECT * FROM LabResults WHERE LIMSObservationCode = 'HIVRL' -- Log Value

) AS hivrl ON req.RequestID = hivrl.RequestID AND req.OBRSetID = hivrl.OBRSetID

LEFT JOIN OpenLDRDict.dbo.viewFacilities AS limsFacility ON req.LIMSFacilityCode = limsFacility.FacilityCode

LEFT JOIN OpenLDRDict.dbo.viewFacilities AS requestingFacility ON req.RequestingFacilityCode = requestingFacility.FacilityCode

LEFT JOIN OpenLDRDict.dbo.viewLaboratories AS receivingFacility ON req.LIMSVendorCode = receivingFacility.LIMSVendorCode AND req.LIMSReceivingLabCode = receivingFacility.LabCode

LEFT JOIN OpenLDRDict.dbo.viewLaboratories AS testingFacility ON req.LIMSVendorCode = testingFacility.LIMSVendorCode AND req.LIMSTestingLabCode = testingFacility.LabCode

WHERE req.LIMSPanelCode = 'HIVVL'

viewFacilities

This view does not need changed but here is the SQl as reference.

CREATE VIEW [dbo].[viewFacilities]

AS

-- You will notice that when creating the various geographic codes for each level that if a column is missing

-- data it defaults the code to 00. It is recommended you fill in all of your data and all the corresponding

-- HealthcareAreaCodes in the HealthcareAreas table.

--

-- If you do not use this same heiarchy you will need to change the way the columns are concatenated together

SELECT facility.*,

countryInfo.HealthcareAreaDesc AS CountryName, countryInfo.LattLong AS CountryLattLong,

provinceInfo.HealthcareAreaDesc AS ZoneName, provinceInfo.LattLong AS ZoneLattLong,

regionInfo.HealthcareAreaDesc AS RegionName, regionInfo.LattLong AS regionLattLong,

districtInfo.HealthcareAreaDesc AS DistrictName, districtInfo.LattLong AS DistrictLattLong,

subDistrictInfo.HealthcareAreaDesc AS SubDistrictName, subDistrictInfo.LattLong AS SubDistrictLattLong

FROM

(

-- Note that because of the unfinished state of the Facilities table I had to only select rows with all Province and District Codes

SELECT *,

CountryCode AS HealthcareCountryCode, -- Not strictly necessary but makes it consistent

CONCAT(CountryCode, dbo.IfEmptyReturnValue(ProvinceCode,'00')) AS HealthcareProvinceCode,

CONCAT(CountryCode, dbo.IfEmptyReturnValue(ProvinceCode,'00'), dbo.IfEmptyReturnValue(RegionCode,'00')) AS HealthcareRegionCode,

CONCAT(CountryCode, dbo.IfEmptyReturnValue(ProvinceCode,'00'), dbo.IfEmptyReturnValue(RegionCode,'00'), dbo.IfEmptyReturnValue(DistrictCode,'00')) AS HealthcareDistrictCode,

CONCAT(CountryCode, dbo.IfEmptyReturnValue(ProvinceCode,'00'), dbo.IfEmptyReturnValue(RegionCode,'00'), dbo.IfEmptyReturnValue(DistrictCode,'00'), dbo.IfEmptyReturnValue(SubDistrictCode,'00')) AS HealthcareSubDistrictCode

FROM Facilities

) AS facility

LEFT JOIN HealthcareAreas AS countryInfo ON facility.HealthcareCountryCode = countryInfo.HealthcareAreaCode

LEFT JOIN HealthcareAreas AS provinceInfo ON facility.HealthcareProvinceCode = provinceInfo.HealthcareAreaCode

LEFT JOIN HealthcareAreas AS regionInfo ON facility.HealthcareProvinceCode = regionInfo.HealthcareAreaCode

LEFT JOIN HealthcareAreas AS districtInfo ON facility.HealthcareDistrictCode = districtInfo.HealthcareAreaCode

LEFT JOIN HealthcareAreas AS subDistrictInfo ON facility.HealthcareSubDistrictCode = subDistrictInfo.HealthcareAreaCode;

GO