All of the tables above can be linked together using SQL joins to obtain more data.
Example:
A concept will have a code in the OBSERVATION_FACT.CONCEPT_CD column but will have to be joined to the CONCEPT_DIMENSION.CONCEPT_CD column to find the NAME_CHAR and / or CONCEPT_PATH defined for the concept.
The following are some examples of common columns used to join tables in the star schema.
OBSERVATION_FACT |
|
|
ENCOUNTER_NUM in OBSERVATION_FACT |
can be joined to |
ENCOUNTER_NUM in the VISIT_DIMENSION table |
PATIENT_NUM in OBSERVATION_FACT |
can be joined to |
PATIENT_NUM in the PATIENT_DIMENSION and VISIT_DIMENSION tables |
PROVIDER_ID in OBSERVATION_FACT |
can be joined to |
PROVIDER_ID in the PROVIDER_DIMENSION table |
|
|
|
PATIENT_DIMENSION |
|
|
PATIENT_NUM in PATIENT_DIMENSION |
can be joined to |
PATIENT_NUM in the OBSERVATION_FACT and VISIT_DIMENSION table |
|
|
|
VISIT_DIMENSION |
|
|
ENCOUNTER_NUM in VISIT_DIMENSION |
can be joined to |
ENCOUNTER_NUM in the OBSERVATION_FACT table |
PATIENT_NUM in VISIT_DIMENSION |
can be joined to |
PATIENT_NUM in the OBSERVATION_FACT and PATIENT_DIMENSION tables |
|
|
|
CONCEPT_DIMENSION |
|
|
CONCEPT_CD in CONCEPT_DIMENSION |
can be joined to |
CONCEPT_CD in the OBSERVATION_FACT table |
|
|
|
PROVIDER_DIMENSION |
|
|
PROVIDER_ID in PROVIDER_DIMENSION |
can be joined to |
PROVIDER_ID in the OBSERVATION_FACT table |
