Ruth Perrin, MA

VA Information Resource Center

March 28, 2013 
Acknowledgements

 Corporate Data Warehouse management

 Steve Martin, Jack Bates and Steve Anderson

 VIReC colleagues 

 Dr. Elizabeth Tarlov, Margaret Browning, Kristin de Groot, 

Linda Kok, Joanne Stevens and Puling Zhang

 Everyone who sends their great questions to the VIReC

Help Desk

 All the generous contributors to HSRData listserv

2
Objectives

• Provide resources to help you

• identify the Corporate Data Warehouse (CDW) data 

elements you need

• find detailed information about those CDW data elements

• Contacts + support

• Answer questions

3 
Topics 

How to find the answers to questions like:

1. How do I even find out what’s in the CDW? 

2. I’ve been using MedSAS data. Which CDW data elements 

should I use?

3. Is Scrambled SSN available in the CDW?

4. The Outpatient MedSAS variable I’m looking for isn’t in 

the CDW. Why not?

4 
Topics (more)

5. I’ve been using the DSS National Data Extracts 

(NDEs). How can I find the names of equivalent 

variables in the CDW DSS data?

6. Do the Vital Signs data include pain scores?

7. What is a Health Factor?

5 
Topics (more)

8. Can you use Health Factors to determine a 

veteran’s smoking status?

9. Where can I find a list of the surveys and tests 

included in the CDW’s Mental Health data?

10. What’s the best data element for identifying lab tests 

in the CDW data?

6 
Tip 

• No one resource can answer all CDW data questions.

• Each resource contains a piece of the puzzle.

7 
Tip 

• Metadata for the CDW is located almost solely on the VA 

Intranet, behind the VA firewall.

• Links for the resources mentioned in this presentation are 

on a slide at the end of the presentation.

8 
Poll Question #1

 What is your role with regard to VA data? 

 Research investigator

 Research programmer/statistician/project management

 Research other

 Operations 

 Other

9 
Poll Question #2

 How much CDW data have you analyzed? 

 Lots

 Some 

 A little

 None. We’re just getting started.

10
1. How do I even find out what’s 

in the CDW?

11

 
12
13

VA Corporate Data 

Warehouse (CDW)

A VIReC Resource Guide

VA Information Resource Center (VIReC)

June 2012
14

Topic

CDW Description

Data Currently Available

Research Utility- Data Quality

Research Utility- Strengths

Research Utility- Limitations

CDW Documentation

Additional Resources

VIReC Resource Guide for the CDW
15

CDW is 

• an SQL relational database 

• organized into data domains. 

• Each domain is a set of fact tables and dimension 

tables with a common theme

• Usually theme = source VistA application

• Data domains are categorized as “Production” or 

“Raw.”

VIReC Resource Guide for the CDW
16

• Documentation for Production (CDW-Prod)domains 

and Raw (CDW-Raw) domains are stored in different 

places on the CDW Intranet site.

• Documentation for Decision Support System (DSS) 

National Data Extracts is located with the CDW Raw 

documentation.

VIReC Resource Guide for the CDW
CDW User Manual

17

Contents

General Information 3

What is the Data Warehouse? 3

Who are Data Warehouse Customers? 3

Customer Responsibilities 3

Server Resources 4

Other Resources 5

Data Warehouse Policies and Guidelines 5

Policies Introduction 5

Policy Exemptions 6.....etc.

• Find this guide on the Tech Team tab of the CDW SharePoint site 

on the VA Intranet. 

Manual for Data Warehouse Customers 

Policies, Guidelines and Best Practices *
CDW SharePoint site: 

Metadata Report

18

Metadata for Production domains only

• Lists domain specifications such as

• Domain model (the Entity Relationship model)

• Table names

• VistA source file(s)

• etc.
CDW SharePoint site: 

Metadata Report

19

• Lists table specifications such as

• Field names, data type, length

• Primary and Foreign key indicators

• VistA source file and field

• VistA field description

• VistA field type

• etc.
CDW Metadata Report

20

Vital Signs Domain 

Entity Relationship 

(E/R) Model from the 

CDW Metadata Report
CDW SharePoint site: CDW Raw

21
CDW Raw

22
CDW SharePoint site: DSS in CDW

• CDW DSS data are located on the CDW Raw server 

• CDW consolidates DSS National Data Extract (NDE) 

files across years and VISNs 

• DSS field names are standardized across years 

• CDW DSS metadata are on the SharePoint’s CDW Raw 

page – scroll to the bottom

23
24

NDE fy05 fy06 fy07 fy08 fy09 fy10 fy11 fy12 fy13

Last Load

(records) Description

albcc X X X X X X X X X 767,463 Account Level Budgeter

albhr X X X X Account Level Budgeter Hours (ALBCC in 09)

char4 X X 16,021,849 4 Character National Code (new in FY11)

disch X X X X X X X X X 215,310 Discharge

ecs X X X X X X X X 9,095,254 Event Capture System

lar X X X X X X X X X 82,385,654 Lab Results

noshow X X X X X X X 1,846,130 No Show

obt X X X X X X X 31,955 Observation Treating Specialty

ocred X X X X X 13,754,023 Outpatient Credit Stop (new in FY09)

oipd X X X X X X X X X 102,009,789 Outpatient Intermediate Product Dept

out X X X X X X X X X 60,398,770 Outpatient

out2 X X X X X X X X X 1,975,834 Outpatient (zero cost encounters)

trt X X X X X X X X X 388,252 Treating Specialty

trtipd X X X X X X X X X 5,850,146 Treating Specialty Intermediate Product Dept

ward X X X X X X X 460,883 Inpatient Ward Segments

Annual Updates

ddc X X X X X X X 870,812 Denver Distribution Center

lab X X X X X X X X X 64,992,860 Lab

pha X X X X X X X X X 53,207,780 Pharmacy

rad X X X X X X X X X 2,250,963 Radiology

rai X X X X X X X 42,649 Resident Assessment Index

sua X X X X X X 15,912 Surgery Aborted/Cancelled Cases

sur X X X X X X 546,485 Surgery

Annual Updates

attend X X X Attending Physician Workload
CDW Data Profiling Domain

25

• Descriptive statistics for Production domains

• Record counts

• Null counts

• Frequencies for discrete variables

• Requires CDW access; not available for research 

purposes 

• Submit a preparatory to research request to 

Data Access Request Tracker (DART) 
Coming soon to the VIReC site

26

 Record Counts, Null Counts and Discrete Variable 

Frequencies for every table in the CDW Production 

domains 

 Produced by querying the CDW Data Profiling domain

 Access to the CDW not required to see this summary data
Coming soon to the VIReC site

27

 Sample rows of data: 20 randomly selected records from 

each table in the Production domains 

 De-identified/No personal identifiers/dates appear as 

YYYY-MM-DD HH:MM:SS 

 Access to the CDW not required to see this summary data
2. I’ve been using the MedSAS

data. Which CDW data 

elements should I use?

28

 
CDW SharePoint site: SAS XWalk

• See SAS XWalk on the SharePoint’s NDS page, see the 

left banner

• Enter a SAS variable name in the textbox provided, 

select SAS, crosswalk displays the CDW field name in 

the T-SQL column

• SAS XWalk currently covers MedSAS Outpatient files

29
CDW SharePoint site: SAS XWalk

30
CDW SharePoint site: 

Metadata Report

• See CDW Metadata report for

• Primary Key info

• Foreign Key info

• Field Description

• more

31
32

Table 

Name

Field 

Name

Field 

Datatype

Field 

Length

Vista 

File

Vista Field

VDiagnosis ICDIEN varchar(50) 50 V POV 

(9000010.07)

POV (.01)

Primary 

Key

Foreign 

Key

Vista Field Description Vista 

Field 

Type

Vista File 

Pointed 

To

N N POV is an abbreviation for "Purpose of Visit". Since 

Purpose of Visit isoften confused with "Chief 

complaint", another abbreviation might betterbe 

"Problem of Visit". This is the Provider's conclusion 

about what wastreated at the visit. The Provider should 

be able to indicate a preferrednarrative for what was 

treated and an ICD Diagnosis code. If the 

problemtreated is from the Problem List, then the 

problem list entry informationcan be used for the 

"Problem of Visit" entry. The provider canalternatively 

have this information automatically captured via 

scannedEncounter Forms (e.g., AICS - the VA's 

Encounter Form Data Capturepackage). At VA facilites, 

the ICD Diagnosis is screened by Inactive Code and 

itmust be appropriate for the Patient's age and sex. At 

IHS facilities, the ICD Diagnosis is screened by Inactive 

Code,appropriate for the Patient's age and sex, and 

Not "E" codes.

Pointer 80

CDW Metadata Report- selected columns
3. Is Scrambled SSN available in 

the CDW?

33
CDW SharePoint site: SAS XWalk

34
Tip regarding SCRSSN

 CDW uses is the same SCRSSN as is used in the MedSAS 

and other VA databases

35
4. The Outpatient MedSAS

variable I’m looking for isn’t 

in the CDW. Why not?

36

 
VIReC Research User Guides

37

• MedSAS includes some elements calculated from Vista 

data.

•In the VIReC Research User Guide for MedSAS data, in 

the one-page variable description for your target 

variable, the VistA data source will say “Derived”. 
VIReC Research User Guides

Extracted from the VIReC Guide for Fiscal Year 2009 VHA Medical 

SAS Outpatient Datasets and Inpatient Encounters Dataset: 

Variable Name: NCPTTOT

Definition: Total Number of CPT codes for one encounter

Variable Availability in Medical SAS® datasets: 

Event FY2005 – to date; 

Inpatient Encounters FY2005 – to date

VistA Data Source: Derived

38
5. I’ve been using the DSS 

National Data Extracts (NDEs). 

How can I find the names of 

equivalent variables in the 

CDW DSS data?

39
40
41

Extract – selected columns from the DSS NDE Layout Specifications 

FY2012

DSS NDE: ALB (Account Level Budgeter) page

SAS Variables SQL Variables

DSS FIELD 

NAME FY05 SAS

FY06 

SAS FY07 SAS

FY08 

SAS

FY09 

SAS

FY10 

SAS

FY11 

SAS

FY12 

SAS

SAS

LENG

TH SQL

SQL Data 

Types

REC TYPE N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A N/A

VISN visn visn visn visn visn visn visn visn 4 visn tinyint

COMPANY 

CODE sta3n sta3n sta3n sta3n sta3n sta3n sta3n sta3n 4 sta3n smallint

DIVISION sta6a sta6a sta6a sta6a sta6a sta6a sta6a sta6a 6 sta6a varchar(6)

FISCAL 

YEAR fy fy fy fy fy fy fy fy 4 fy smallint

FISCAL 

PERIOD fp fp fp fp fp fp fp fp 4 fp smallint

DSS 

PRODUCTIO

N UNIT produnit

produni

t produnit

produni

t

produni

t

produni

t

produni

t

produ

nit 2

produni

t varchar(2)

EXEMPT 

STATUS exempt exempt exempt exempt exempt exempt exempt

exem

pt 1 exempt varchar(1)

COST 

CENTER albcc albcc albcc albcc albcc albcc albcc albcc 10 albcc

varchar(10

)

ACCOUNT 

CODE albacct albacct albacct albacct albacct albacct albacct

albacc

t 11 albacct

varchar(11

)

COST TYPE N/A N/A N/A N/A costtyp costtyp costtyp

costty

p 3 costtyp varchar(3)

CATEGORY 

NUMBER N/A N/A N/A N/A catnum catnum catnum

catnu

m 2 catnum varchar(2)

ACCOUNT 

TYPE edi edi edi edi edi edi edi edi 1 edi varchar(1)

STATUS active active active active active active active active 1 active varchar(1)

ACT COST act_dllr act_dllr act_dllr act_dllr act_dllr act_dllr act_dllr

act_dll

r 8

ActualD

ollars

numeric(2

0,6)

ACT REG 

COST reg_dllr reg_dllr reg_dllr reg_dllr reg_dllr reg_dllr reg_dllr

reg_dl

lr 8 reg_dllr

numeric(2

0,6)

ACT VSH 

COST vsh_dllr

vsh_dll

r vsh_dllr

vsh_dll

r

vsh_dll

r

vsh_dll

r

vsh_dll

r

vsh_dl

lr 8 vsh_dllr

numeric(2

0,6)

ACT OT 

COST ot_dllr ot_dllr ot_dllr ot_dllr ot_dllr ot_dllr ot_dllr ot_dllr 8 ot_dllr

numeric(2

0,6)

ACT HOURS

act_hou

r

act_ho

ur

act_hou

r

act_ho

ur

act_ho

ur

act_ho

ur

act_ho

ur

act_h

our 8

ActualH

ours integer

ACT REG 

HOURS

reg_hou

r

reg_ho

ur

reg_hou

r

reg_ho

ur

reg_ho

ur

reg_ho

ur

reg_ho

ur

reg_h

our 8

reg_ho

ur integer

ACT VSH 

HOURS

vsh_hou

r

vsh_ho

ur

vsh_hou

r

vsh_ho

ur

vsh_ho

ur

vsh_ho

ur

vsh_ho

ur

vsh_h

our 8

vsh_ho

ur integer

ACT OT 

HOURS ot_hour ot_hour ot_hour ot_hour ot_hour ot_hour ot_hour

ot_ho

ur 8 ot_hour integer

IPD 

NUMBER

dcm_de

pt

dcm_d

ept

dcm_de

pt

dcm_d

ept

dcm_d

ept

dcm_d

ept

dcm_d

ept

dcm_

dept 10 IPD

varchar(10

)

IPD 

NUMBER 

DESCRIPTIO

N N/A N/A N/A N/A

IPDDe

sc

IPDDe

sc

IPDDe

sc

IPDDe

sc 30 ipddesc

varchar(30

)
“I’m interested in using new data 

available now for the first time 

from the CDW”

•Vital Signs

•Health Factors

•Mental Health

•Lab results for all lab tests

•Clinical notes

•More

42
6. Do the Vital Signs data 

include pain scores?

43
VINCI Central

• VINCI Central is the VA Intranet web site where the 

VINCI group posts useful information and CDW 

metadata

• VINCI Central has the list of Vital Signs 

• On the Data tab, select Data Available then Vital Signs

44
45

PAIN 363475783

VENTILATOR MINUTE VOLUME 86704

VENTILATOR TIDAL VOLUME 86780

BLOOD PRESSURE 369612858

VENTILATOR MODE 42670

CENTRAL VENOUS PRESSURE 523754

CIRCUMFERENCE/GIRTH 1000887

PULSE 344803275

PULSE OXIMETRY 95485935

RESPIRATION 293103643

TEMPERATURE 287732921

WEIGHT 166764666

HEIGHT 84513025 

(as of 4/14/2011) 

VINCI Central Vital counts 
CDW SharePoint site: 

Metadata Report

• See additional, essential metadata on the Vital Signs in 

the Metadata Report, such as

• Parameters such as dates and locations

• EnteredInErrorFlag 

46
Other resources

47

• See also

• Noël PH J Rehabil Res Dev. 2010: VHA Corporate Data 

Warehouse height and weight data: opportunities and 

challenges for health services research. 
7. What is a Health Factor?

48
Health Factors

• VistA data elements 

• Hold patient info not stored elsewhere such as

• Family History of Alcohol Abuse

• Lifetime Non-smoker

• No Risk Factors for Hepatitis C

• Often used to capture results from clinical 

reminders

• Not standardized across the VA 

49
Data Architecture Repository

• List of current health factors available from the VistA

Metadata Repository in the Data Architecture 

Repository (DAR) on the VA Intranet

50
51
52
53
8. Can you use Health Factors 

to determine a patient’s 

smoking status?

54
Smoking Status Health Factors

• Several dozen Health Factors pertain to smoking 

status

• None of the smoking related Health Factors are 

standardized across the VA

• Other VA data on smoking status:

• Tobacco Use related CPT codes not often used

55
Other Smoking Status Data Sources

• Tobacco Use related CPT codes (not often used), 

including codes for tobacco cessation counseling

• Pharmacy data – prescriptions for nicotine or nicotine 

agonists

 

56
Other resources

Previous studies of smoking among veterans found the 

CDW Health Factors to be the most reliable smoking 

status indicators available. See:

Validating Smoking Data From the Veteran’s 

Affairs Health Factors Dataset, an Electronic 

Data Source

Kathleen A. McGinnis, M.S., Cynthia A. Brandt, M.D., 

M.P.H., Melissa Skanderson, M.S.W., et al., Nicotine Tob

Res. 2011 Dec;13(12):1233-9.

57
9. Where can I find a list of the 

surveys and tests included in 

the CDW’s Mental Health data?

58

 
Mental Health Dim.Survey

 No list of the surveys in CDW Metadata

 The CDW table Dim.Survey holds the names of the 

surveys + tests. 

 The domain includes data from interviews, surveys 

and tests available in VA’s Mental Health Assistant 

package, except those that are copyrighted. 

59
Mental Health

Non-exhaustive lists of tests/surveys/interviews in the 

Mental Health table Dim.Survey.SurveyName:

• AUDC • AIMS • AUDIT

• PHQ-2 • BRADEN • MISS

• PC PTSD SCALE • MOMC

• PHQ-9 • PCLC • MCM13

• AIMS • BD12 • SF-36

• MORSE FALL • PCLM • BPRS

SCORE • BAI • MBMD 

 • MMPI2 

60

 
10. What’s the best way to 

identify a lab test in the 

CDW?

61
CDW SharePoint site: 

training available online

• See Richard Pham’s course “Laboratory and 

Chemistry – Round 1” and “...Round 2” for 

• Pros and Cons of using the identifiers LabChemTestName, 

NationalVACodeIEN and LOINC 

• Suggested code

• On the Support tab, select Training, then on under 

Training Links, select CDW Data: Datasets and Basic SQL 

62
Summary: Resources

63
Resources

64

CDW 

SharePoint 

site

On the VA Intranet: 

https://vaww.dwh.cdw.portal.va.gov/Pages/welcome.aspx 

VIReC web 

site

On the Internet: http://vaww.virec.research.va.gov/

On the VA Intranet: http://www.virec.research.va.gov/

VINCI 

Central: 

On the VA Intranet at 

http://vaww.vinci.med.va.gov/vincicentral/default.aspx
Resources

65

Data 

Architecture 

Repository

On the VA Intranet at 

http://vacodeve2.dva.va.gov/pls/apex/f?p=VISTA:1:92964

9074432930

Decision 

Support 

Office

On the VA Intranet at 

http://vaww.dss.med.va.gov/nationalrptg/nr_extracts.asp
Contacts & Support

66
67

VINCI VINCI users can get support through the VINCI 

Service Desk from 8:30 to 4:30pm Mountain 

Time, Monday through Friday. VINCI Contact Method Contact Information

 Service Desk Phone 801-588-5212 

 Email VINCI@va.gov

Austin 

Help 

Desk

The Austin Information Technology Center’s 

National Service Desk offers technical support for 

users of the CDW 24 hours of every day.

NSD Contact Method Contact Information

Help Desk Phone

512-326-6780 or 

888-326-6780

TDD 512-326-6638

Email

nsd-aus@va.gov or 

cdco.nsd@va.gov
68

VIReC The VA Information Resource Center is a good resource for 

VA researchers with questions about CDW data.

VIReC Contact Method Contact Information

Help Desk Email virec@va.gov

Help Desk Phone 708-202-2413

Internet website http://www.virec.research.va.gov

VA Intranet website http://vaww.virec.research.va.gov

Physical address VA Information Resource Center 

(151V)

Health Services Research and 

Development Service

Department of Veterans Affairs

Edward J. Hines, Jr. VA Hospital

5000 South 5th Avenue

Hines, IL 60141
69

HSRData 

Listserv

VIReC hosts this virtual community of VA 

researchers who share knowledge and 

experiences about VA data and information 

systems.

Please see VIReC’s web site for enrollment 

information.

The Listserv is only open to individuals with 

“va.gov” email addresses.
Questions & Suggestions

70