OBSERVATION_FACT Table

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