User medical history fact table (Redshift)
Bi_FactUserMedicalHistory
Fact table recording guests' medical-history entries, including diagnosed health conditions, diagnosis dates, resolution status, family history, and genetic-risk flags.
Field | Field type | Description | Primary Key | Mapped Dimension table | Mapped Dimension field |
|---|---|---|---|---|---|
FactUserMedicalHistoryWId | BIGINT IDENTITY | Surrogate warehouse key for the user medical history fact record | PK | ||
FactSegmentId | DECIMAL(19,0) | Business/natural key for the fact segment | |||
MedicalHistoryPK | BIGINT | Source system primary key of the medical history record | |||
MedicalHistoryID | VARCHAR(36) | Unique identifier (GUID) of the medical history record | |||
UserWid | BIGINT | Foreign key to the user dimension | Bi_DimUser | userwid | |
UserId | VARCHAR(36) | Unique identifier (GUID) of the user/guest | |||
healthconditionwid | BIGINT | Foreign key to the health condition master dimension | Bi_DimEMRHealthConditionMaster | EMRHealthConditionMasterWId | |
HealthConditionID | VARCHAR(36) | Identifier (GUID) of the health condition | |||
ConditionName | NVARCHAR(256) | Name of the health condition | |||
Category | SMALLINT | Category code classifying the health condition type; see EMR domain definition | |||
DiagnosisDate | TIMESTAMP | Date when the condition was diagnosed | |||
Status | SMALLINT | Status code of the medical history record; see EMR domain definition | |||
ResolutionDate | TIMESTAMP | Date when the condition was resolved | |||
Notes | NVARCHAR(512) | Clinical notes associated with the medical history record | |||
IsFamilyHistory | BOOLEAN | Indicates whether this is a family history record rather than a personal diagnosis | |||
userrelationwid | BIGINT | Foreign key to the org user relationship dimension | Bi_DimOrgUserRelationship | OrgUserRelationshipWId | |
UserRelationID | VARCHAR(36) | Identifier (GUID) of the relationship type for family history records | |||
IsGeneticRisk | BOOLEAN | Indicates whether the condition represents a genetic risk factor | |||
RelatedType | INT | Code indicating the type of entity this history is related to; see EMR domain definition | |||
RelatedId | VARCHAR(36) | Unique identifier (GUID) of the related entity | |||
FormId | VARCHAR(36) | Unique identifier (GUID) of the form through which the record was captured | |||
RelatedTime | TIMESTAMP | Timestamp of the related entity event | |||
CriticalType | SMALLINT | Criticality classification code; see EMR domain definition | |||
OrganizationId | VARCHAR(36) | Unique identifier (GUID) of the organization | |||
CreatedByWid | BIGINT | Foreign key to the user dimension for the record creator | Bi_DimUser | userwid | |
CreatedBy | VARCHAR(36) | Identifier (GUID) of the user who created the record | |||
CreatedDate | TIMESTAMP | Timestamp when the record was created | |||
UpdatedByWid | BIGINT | Foreign key to the user dimension for the last updater | Bi_DimUser | userwid | |
UpdatedBy | VARCHAR(36) | Identifier (GUID) of the user who last updated the record | |||
UpdatedDate | TIMESTAMP | Timestamp when the record was last updated | |||
centerwid | BIGINT | Foreign key to the center dimension | Bi_DimCenter | centerwid | |
CenterId | VARCHAR(36) | Unique identifier (GUID) of the center | |||
EtlCreatedBy | BIGINT | ID of the ETL process/user that created this record | |||
EtlCreatedDate | TIMESTAMP | Timestamp when the ETL process created this record | |||
Void | BOOLEAN | Indicates whether the record has been voided/soft-deleted | |||
PodName | NVARCHAR(100) | Name of the source pod from which the data originated | |||
groupid | BIGINT | Group identifier for multi-tenant partitioning |