Embedded Files
This section describes how the main data tables have been used to flexibly store extra data elements collected about a result or request. This explanation is targeted at a developer and therefore does not use strict laboratory terminology. Terminology varies from laboratory to laboratory, section to section and LIMS to LIMS and this page does not try to standardize that but instead utilizes the terminology a data person will understand in order to make sense of the data structure.
"Panels" and "Results"
In the simplest model you can think of a RequestID as a specimen identifier as well. A group or "Panel" of "Tests" can be ordered to be performed on a specimen. Each row in the Requests table represents a Panel ordered for a specimen. The RequestID/OBRSetID are the unique identifiers for the Requests table. The OBRSetID is the internal ID used for joining Requests to LabResults for each panel. When you join Requests to LabResults you use RequestID/OBRSetID in order to get the "Results" related to each Panel for the specimen.
The trick about this Panel/Result structure is to not think of them strictly as panels of tests and as test results. Instead, the Request/Panel can be thought of as a way of grouping related information together with the rows in the LabResults table contains the data elements related to that Request/Panel. For example, a Panel of "HIVVL" is assigned to a specimen for Viral Load testing and the related records in the LabResults table contain the outcomes of the Viral Load test. Similarly, a Panel of "VIRAL" is assigned to the same specimen but the related rows over in the LabResults table do not contain test results but instead contain extra information collected at the time of registration. The following sample data illustrates this relationship.
Warnings
This model provides a flexible model for supplying data to the OpenLDR and makes it easy for data providers and the ever changing needs of data collection. However, it can be too flexible. Notice in the example above the VIRAL panel has the Specimen Collection Date and Time in the "flexible" data element rows. The catch is that there is already a fixed column in the the Requests table for this data element (SpecimenDatetime). You will need to monitor this as new data is provided and determine which is the correct element to use when reporting.
Data Extraction
There are many models for storing data and often the way data is stored is not optimal for how you may need to report the data. Some people like every element as a column, other people like every row to contain a specific data element. Many people are most comfortable using a spreadsheet like Excel to do their data analysis and usually that means having a nice "wide" table with all of the data elements listed as individual columns. Some reporting is more easily done with a very granular row based model. The following is an example of extracting the "flexible" data elements stored as rows and turning them into columns.
The simplistic and obvious method in this example is to join the Requests table to sub-selects from the LabResults table for each element needed.
This is an example of a view based on this methodology. It's been shortened here but the full view is in the scripts attached to the Database Scripts page. The red text shows an example of how to turn the LabResult row for Pregnant into a column of the view.
CREATE VIEW [dbo].[viewVL_Info]
AS
-- This view grabs the request and extra registration information related to LIMSObservationCode = VIRAL
SELECT
req.RequestID, req.OBRSetID, req.LIMSPanelCode, req.LIMSPanelDesc,
CASE WHEN preg.LIMSRptResult Is Null THEN 'Unreported'
ELSE preg.LIMSRptResult
END AS Pregnant, -- preg.LIMSObservationCode,
CASE WHEN bf.LIMSRptResult Is Null THEN 'Unreported'
ELSE bf.LIMSRptResult
END AS BreastFeeding, -- bf.LIMSObservationCode,
CASE WHEN ft.LIMSRptResult Is Null THEN 'Unreported'
ELSE ft.LIMSRptResult
END AS FirstTime, -- ft.LIMSObservationCode,
CASE WHEN cday.LIMSRptResult Is Null THEN 'Unreported'
ELSE cday.LIMSRptResult
END AS CollectedDate, -- cday.LIMSObservationCode,
ctime.LIMSRptResult AS CollectedTime, -- ctime.LIMSObservationCode,
tarvd.LIMSRptResult AS DataDeInicioDoTARV, -- tarvd.LIMSObservationCode,
tarvp.LIMSRptResult AS PrimeiraLinha, -- tarvp.LIMSObservationCode,
tarvs.LIMSRptResult AS SegundaLinha, -- tarvs.LIMSObservationCode,
TARVQ.LIMSRptResult AS ARTRegimen,
LABTI.LIMSRptResult AS TypeOfSampleCollection,
VIRAD.LIMSRptResult AS LastViralLoadDate,
VIRR1.LIMSRptResult AS LastViralLoadResult,
LABNO.LIMSRptResult AS RequestingClinician
FROM Requests AS req
LEFT JOIN (
SELECT * FROM LabResults WHERE LIMSObservationCode = 'ENCON' -- Pregnant
) AS preg ON req.RequestID = preg.RequestID AND req.OBRSetID = preg.OBRSetID
LEFT JOIN (
SELECT * FROM LabResults WHERE LIMSObservationCode = 'AMAME' -- Breast Feeding
) AS bf ON req.RequestID = bf.RequestID AND req.OBRSetID = bf.OBRSetID
LEFT JOIN (
SELECT * FROM LabResults WHERE LIMSObservationCode = 'VIRAP' -- First time
) AS ft ON req.RequestID = ft.RequestID AND req.OBRSetID = ft.OBRSetID
LEFT JOIN (
SELECT * FROM LabResults WHERE LIMSObservationCode = 'LABDA' -- Collection Day
) AS cday ON req.RequestID = cday.RequestID AND req.OBRSetID = cday.OBRSetID
LEFT JOIN (
SELECT * FROM LabResults WHERE LIMSObservationCode = 'LABHO' -- Collection Hour
) AS ctime ON req.RequestID = ctime.RequestID AND req.OBRSetID = ctime.OBRSetID
LEFT JOIN (
SELECT * FROM LabResults WHERE LIMSObservationCode = 'TARVD' -- Data de in¡cio do TARV
) AS tarvd ON req.RequestID = tarvd.RequestID AND req.OBRSetID = tarvd.OBRSetID
LEFT JOIN (
SELECT * FROM LabResults WHERE LIMSObservationCode = 'TARVP' -- Primeira Linha
) AS tarvp ON req.RequestID = tarvp.RequestID AND req.OBRSetID = tarvp.OBRSetID
LEFT JOIN (
SELECT * FROM LabResults WHERE LIMSObservationCode = 'TARVS' --
) AS tarvs ON req.RequestID = tarvs.RequestID AND req.OBRSetID = tarvs.OBRSetID
LEFT JOIN (
SELECT * FROM LabResults WHERE LIMSObservationCode = 'ESCOL' -- Motivo da Requerimento
) AS motivo ON req.RequestID = motivo.RequestID AND req.OBRSetID = motivo.OBRSetID
LEFT JOIN (
SELECT * FROM LabResults WHERE LIMSObservationCode = 'TARVQ' -- ART Regimen
) AS TARVQ ON req.RequestID = TARVQ.RequestID AND req.OBRSetID = TARVQ.OBRSetID
LEFT JOIN (
SELECT * FROM LabResults WHERE LIMSObservationCode = 'LABTI' -- Type of Sample Collection
) AS LABTI ON req.RequestID = LABTI.RequestID AND req.OBRSetID = LABTI.OBRSetID
LEFT JOIN (
SELECT * FROM LabResults WHERE LIMSObservationCode = 'VIRAD' -- Last Viral Load Date
) AS VIRAD ON req.RequestID = VIRAD.RequestID AND req.OBRSetID = VIRAD.OBRSetID
LEFT JOIN (
SELECT * FROM LabResults WHERE LIMSObservationCode = 'VIRR1' -- Last Viral Load Result
) AS VIRR1 ON req.RequestID = VIRR1.RequestID AND req.OBRSetID = VIRR1.OBRSetID
LEFT JOIN (
SELECT * FROM LabResults WHERE LIMSObservationCode = 'LABNO' -- Requesting Clinician
) AS LABNO ON req.RequestID = LABNO.RequestID AND req.OBRSetID = LABNO.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.viewFacilities AS receivingFacility ON req.ReceivingFacilityCode = receivingFacility.FacilityCode
LEFT JOIN OpenLDRDict.dbo.viewFacilities AS testingFacility ON req.TestingFacilityCode = testingFacility.FacilityCode
WHERE req.LIMSPanelCode = 'VIRAL'
Page updated
Google Sites
Report abuse