A Practical Guide to Use the Corporate 

Data Warehouse LabChem Data

Department of Veterans Affairs

Gowtham A Rao, MD, PhD 

PGY-3, Preventive Medicine Resident

University of South Carolina School of Medicine

William JB Dorn VA Medical Center

Columbia, SC

Chief Medical Informatics Officer

Blue Cross Blue Shield of South Carolina (July 2013)

1 

6/20/2013
Outline: CDW Lab Chemistry Data

 What is it?

What is Lab Chemistry (LabChem) data

 What's in it?

What are the data elements in LabChem data

 How do we use it?

 How do we identify a particular lab test 

What specimen was the test done on

• Research example (time permits)

A primer to how it may be used

2 
Outline: CDW Lab Chemistry Data

Consideration 1:

 Research users  project specific access

 Operational users  full access

 This presentation is catered to research audience 

 Screen shots  Operational view: generalizable

3 
Outline: CDW Lab Chemistry Data

Consideration 2:

 VINCI platform or non-VINCI platforms

 This presentation is applicable to both

 Screen shots  VINCI platform Microsoft Server 

Management Studio (SSMS)

 SAS© or other software tools: shared concepts

4 
Poll Question #1

Have you used CDW Lab Chemistry data 

before?

• Yes

•No

5 
What is it?

CDW Lab Chemistry

6 
CDW Lab Chemistry: What is it?

7 

• Extract – chemistry, hematology, toxicology, serology 

• Source: All VHA Vista systems 

• Updates: frequent

• Extracted into:

• Chem.LabChem 

• Dim.LabChemTest 

• Dim.LOINC

• Dim.NationalVALabCode
CDW Lab Chemistry: What is it?

• SSMS connected to server 

vhacdwrb01.vha.med.va.gov

• Look for CDWWork

Everyone: DIM

Operational: DIM and Chem

Chem.LabChem

Dim.LabChemTest

Dim.LOINC

Dim.NationalVALabCode

8 

Microsoft Server Management Studio (SSMS)
Example for research projects:

ORD_RAO_2012xxxxxx

View Dflt.LabChem 

Dflt.LabChem  Chem.LabChem

Ask data manager or data-steward

9 

CDW Lab Chemistry: What is it?
CDW Lab Chemistry: What is it?

10

SAS Enterprise Guide

LabChem data mapped 

directly to SAS®
CDW Lab Chemistry: What is it?

Chem.LabChem: 

• Single result from a single 

laboratory test

• Main table: lab results

• VistA file 63 (lab data) 

and 63.04

Source: VINCI Data Description – LabChem, retrieved June 2013

11
12

CDW Lab Chemistry: What is it?

Dim.LOINC: 

• Universal or standardized 

names and codes

• Key table: to identify lab 

test

• VistA file 95.3 (Lab 

LOINC)

Source: VINCI Data Description – LabChem, retrieved June 2013
13

CDW Lab Chemistry: What is it?

Dim.LabChemTest: 

• Lab test names used 

while ordering or for 

display

• Not standardized, subject 

to local variations

• Key table: to identify lab 

test

• VistA file 60 (Lab Test)

Source: VINCI Data Description – LabChem, retrieved June 2013
14

CDW Lab Chemistry: What is it?

Dim.NationalVALabCode: 

• WKLD codes, National VA 

Lab Code and LOINC

• Key table: to identify lab 

test

• VistA file 64: WKLD Code 

file

Source: VINCI Data Description – LabChem, retrieved June 2013
Poll Question #2

What best describes your role as a user of the 

CDW Lab Chemistry data?

• Researcher

• Clinician

• Administrator / Policymaker

• Other (including non-VA)

15
What’s in it?

CDW Lab Chemistry

16
CDW Lab Chemistry: What’s in it?

CDWWork.Chem.LabChem

Five main components: 

• Patient ID: PatientSID, SSN, ScrSSN, 

PatientICN 

• Lab Name: LOINCSID, LabChemTestSID 

• Time: LabChemSpecimenDateTime

• Specimen: Specimen

• Result: LabChemResultNumericValue, 

LabChemResultValue

17
18

CDW Lab Chemistry: What’s in it?

CDWWork.Chem.LabChem (Main Table)

Patient: PatientSID

• Link to PatientSIDSSN/ScrSSN/PatientICN crosswalk

• Foreign key – identify patient

• Single patient may have multiple 

PatientSIDs but only one 

SSN/ScrSSN/PatientICN
19

CDW Lab Chemistry: What’s in it?

CDWWork.Chem.LabChem (Main Table)

Lab Name: LOINCSID

• Link to Dim.LOINC

• Foreign key – identify test name
20

CDW Lab Chemistry: What’s in it?

CDWWork.Chem.LabChem (Main Table)

Lab Name: LabChemTestSID

• Link to Dim.LabChemTest

• Foreign key – identify test name
21

CDW Lab Chemistry: What’s in it?

CDWWork.Chem.LabChem (Main Table)

Time:

LabChemSpecimenDateTime

• This is date/time specimen was 

taken

• Represents time specimen 

collected (vs. time results were 

reported)
Result: LabChemResultValue

• Result exactly as is from the VistA

source system

• Human understandable, not 

machine understandable

• Character format - alphanumeric

• Misspellings – Positive, Poditive, POS, 

Possitive, +, +1, 1

22

CDWWork.Chem.LabChem (Main Table)

CDW Lab Chemistry: What’s in it?
23

CDW Lab Chemistry: What’s in it?

CDWWork.Chem.LabChem (Main Table)

Result:

LabChemResultNumericValue 

• LabChemValue converted to a 

numeric data type. 

• Results with alpha characters set to 

NULL

• Machine understandable, numeric 

calculations.
24

CDW Lab Chemistry: What’s in it?

CDWWork.Chem.LabChem – other important variables

Specimen

• Specimen type (Urine, Serum, 

Blood, etc.) – not standardized

LabChemLocationSID

• Location where the lab is 

performed.

• Beyond scope of this presentation
How do we use it?

CDW Lab Chemistry

25
CDW Lab Chemistry: How do we use it?

CDWWork.Chem.LabChem

Five main components: 

• Patient ID: PatientSID, SSN, ScrSSN, 

PatientICN 

• Lab Name: LOINCSID, LabChemTestSID 

• Time: LabChemSpecimenDateTime

• Specimen: Specimen

• Result: LabChemResultNumericValue, 

LabChemResultValue

26

LOINCSID may be linked to Dim.LOINC
CDW Lab Chemistry: How do we use it?

CDWWork.Dim.LOINC (Key Table)

27

• Three main variables

• LOINCSID (primary key)

• LOINC nnnnn-n (6 part)

•‘-n’ in nnnnn-n represents ‘type 

of method’

• LOINCIEN is the nnnnn of 

nnnnn-n (5 part) 

For more information on LOINC: www.loinc.org 
CDW Lab Chemistry: How do we use it?

CDWWork.Dim.LOINC (Key Table)

28

• Use an external site to identify 

LOINC for your tests of interest

www.loinc.org– search, common

www.ahrq.org– search LOINC

• Examples: (nnnnn-n)

 Serum Creatinine: 2160-0, 14682-9 

Platelet count: 26515-7, 778-1, 777-3
29

CDW Lab Chemistry: How do we use it?

Screen shot

www.loinc.org

June 17th 2013
30

CDW Lab Chemistry: How do we use it?

CDWWork.Dim.LOINC (Key Table)

Example:

• Creating a view in SSMS

• Identify LOINCSID for S. Creatinine
31

CDW Lab Chemistry: How do we use it?

CDWWork.Dim.LOINC (Key Table)

LOINCSID Quality

As of July 2011:

• 5.1 billion rows ( > 88.5% LOINCSID)

• #LabChemResultNumericValue

• 3.8 billion (> 95% LOINCSID)

• #LabChemResultValue with null for 

LabChemResultNumericValue

• 1.3 billion (< 15% LOINCSID)

• Newer results  Probably better 

mapping
32

CDW Lab Chemistry: How do we use it?

CDWWork.Dim.LOINC (Key Table)

Recommendation

Easy option:

• Create view of selected LOINC

• Use LOINCSID as ‘Foreign Key’ to 

query CDWWork.Chem.LabChem

• Consider for traditionally numeric 

results

• Hemoglobin, serum creatinine, 

HbA1c, CD4-count
33

CDW Lab Chemistry: How do we use it?

CDWWork.Dim.LabChemTest (Key Table)

• Dim.LabChemTest

• Key table – 

LabChemTestName

• Example: Serum Creatinine
34

CDW Lab Chemistry: How do we use it?

CDWWork.Dim.LabChemTest (Key Table)

Foreign key – links 

LabChem

Names and ordering, 

display of tests
35

CDW Lab Chemistry: How do we use it?

CDWWork.Dim.LabChemTest (Key Table)

Option: Complicated

Example: serum creatinine

• Subject matter expert on lab 

test names

• LabChemTestSID 

Chem.LabChem
36

CDW Lab Chemistry: How do we use it?

CDWWork.Dim.LabChemTest (Key Table)

May consider for:

• Rare tests and tests 

with non-numeric 

results

• Judgment - 

unexpected result 

range
37

CDW Lab Chemistry: How do we use it?

CDWWork.Dim.LabChem (Main Table - Specimen)

Specimen: About 4,500 unique specimen names.

• Many specimen names – no standardized naming convention

• With medical domain expertise, one option to recode as example 

above

• B- Blood

• S- Serum 

• P- Plasma

• U- Urine

• C- CSF

• F- Feces

• O- Other
Research Example 

CDW Lab Chemistry

38
39

Research Example: Serum Creatinine

Query for S. Creatinine

Specimen: Serum

 where SPECIMEN like (SERUM%)

LOINCIEN 2160,14682

 Serum Creatinine: 2160-0, 14682-9 

 www.LOINC.org
40

LabChemResultValue

LabChemResultNumericValue

- Check result range

- Non-numeric  ‘NULL’

Research Example: Serum Creatinine
41

Questions