Server (Cells) Design
Space shortcuts
Space Tools

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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

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