Server (Cells) Design
Space shortcuts
Space Tools
Skip to end of metadata
Go to start of metadata

The OBSERVATION_FACT table is the fact table of the i2b2 star schema and represents the intersection of the dimension tables. Each row describes one observation about a patient made during a visit. Most queries in the i2b2 database require joining together the OBSERVATION_FACT table with one or more dimension tables.

OBSERVATION_FACT

 

 

PK

ENCOUNTER_NUM

int

PK

CONCEPT_CD

varchar(50)

PK

PROVIDER_ID

varchar(50)

PK

START_DATE

datetime

 

PATIENT_NUM

int

 

MODIFIER_CD

varchar(50)

 

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

 

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

 

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")

 



  • No labels