OBSERVATION_FACT

PK

ENCOUNTER_NUM

int

PK

CONCEPT_CD

varchar(50)

PK

PROVIDER_ID

varchar(50)

PK

START_DATE

datetime

 PK

PATIENT_NUM

int

 PK 

MODIFIER_CD

varchar(50)

 PK

INSTANCE_NUM

int

VALTYPE_CD

varchar(50)

TVAL_CHAR

varchar(255)

NVAL_NUM

decimal(18,5)

VALUEFLAG_CD

varchar(50)

QUANTITY_NUM

decimal(18,5)

UNITS_CD

varchar(50)

END_DATE

datetime

LOCATION_CD

varchar(50)

OBSERVATION_BLOB

text

CONFIDENCE_NUM

decimal(18,5)

UPDATE_DATE

datetime

DOWNLOAD_DATE

datetime

IMPORT_DATE

datetime

SOURCESYSTEM_CD

varchar(50)

UPLOAD_ID

int

OBSERVATION_FACT

Key

Column Name

Column Definition

Nullable?
(Default = YES)

PK

ENCOUNTER_NUM

Encoded i2b2 patient visit number

NO

PATIENT_NUM

Encoded i2b2 patient number

NO

PK

CONCEPT_CD

Code for the observation of interest (i.e. diagnoses, procedures, medications, lab tests)

NO

PK

PROVIDER_ID

Practitioner or provider id

NO

PK

START_DATE

Starting date-time of the observation
(mm/dd/yyyy)

NO

  PK

MODIFIER_CD

Code for modifier of interest (i.e. "ROUTE", "DOSE").
Note that the value columns are often used to hold the amounts such as "100" (mg) for the modifier of DOSE or "PO" for the modifier of ROUTE.

YES

  PK

INSTANCE_NUM

Encoded instance number that allows more than one modifier to be provided for each CONCEPT_CD.
Each row will have a different MODIFIER_CD but a similar INSTANCE_NUM.

YES

VALTYPE_CD

Format of the concept
N = Numeric
T = Text (enums / short messages)
B = Raw Text (notes / reports) NLP = NLP result text

TVAL_CHAR

Used in conjunction with VALTYPE_CD = "T" or "N"
When the VALTYPE_CD = "T"
Stores the text value

When VALTYPE_CD = "N"
E = Equals
NE = Not equal
L = Less than
LE = Less than and Equal to
G = Greater than
GE = Greater than and Equal to

NVAL_NUM

Used in conjunction with VALTYPE_CD = "N" to store a numerical value

VALUEFLAG_CD

Used in conjunction with VALTYPE_CD = "B", "NLP", "N", or "T"

When VALTYPE_CD = "B" or "NLP" it is used to indicate whether or not the data in the blob column is encrypted.
X = Encrypted text in the blob column
When the VALTYPE_CD = "N" or "T" it is used to flag certain outlying or abnormal values
H = High
L = Low
A = Abnormal

QUANTITY_NUM

Quantity of the value in the NVAL_NUM column

UNITS_CD

Units of measurement for the value in the NVAL_NUM column

END_DATE

The end date-time for the observation

LOCATION_CD

A location code, such as for a clinic

CONFIDENCE_NUM

Assessment of accuracy of data

OBSERVATION_BLOB

Holds any raw or miscellaneous data that exists, often encrypted PHI

UPDATE_DATE

As defined in the above section ("General Information")

DOWNLOAD_DATE

As defined in the above section ("General Information")

IMPORT_DATE

As defined in the above section ("General Information")

SOURCESYSTEM_CD

As defined in the above section ("General Information")

UPLOAD_ID

As defined in the above section ("General Information")