Message-ID: <1895964373.8448.1711696640651.JavaMail.confluence@ip-172-30-4-17.ec2.internal> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_8447_1339174153.1711696640649" ------=_Part_8447_1339174153.1711696640649 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html OBSERVATION_FACT Table

OBSERVATION_FACT Table

The OBSERVATION_FACT table is the fact table of= the i2b2 star schema and represents the intersection of the dimension tabl= es. 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 =3D 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. di= agnoses, 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 o= ften used to hold the amounts such as "100" (mg) for the modifier of DOSE o= r "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_N= UM.

YES


VALTYPE_CD

Format of the concept
N =3D Numeric
T =3D= Text (enums / short messages)
B = =3D Raw Text (notes / reports)



TVAL_CHAR

Used in conjunction with VALTYPE_CD =3D "T" o= r "N"
When the VALTYPE_CD =3D "T"
Stores the text value

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



NVAL_NUM

Used in conjunction with VALTYPE_CD =3D "N" t= o store a numerical value



VALUEFLAG_CD

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

When the VALTYPE_CD =3D "N" or "T" it is used to flag certain out= lying or abnormal values
H =3D High
L =3D Low
A =3D Abnormal



QUANTITY_NUM

Quantity of the value in the NVAL_NUM column<= /p>



UNITS_CD

Units of measurement for the value in the NVA= L_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 exis= ts, 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")




------=_Part_8447_1339174153.1711696640649--