Table & View Definitions

1upHealth’s SQL on FHIR product includes enrichment tables, FHIR data tables, and views that you can use to generate analytics and insights, and get visibility into your FHIR data. You can use these definitions to write SQL queries and to help you understand the context of the data in the SQL tables.

Enrichment Tables

You can use the following enrichment tables to help you analyze and get insights into your FHIR data. Some of the tables can also be combined with the views to increase your visibility into your data.

Enrichment Table	Description
enr_rxnconso	Map NCD codes and RX codes Use with the vw_mapping_rxnorm_to_ndc view
enr_rxnrel	Map NCD codes and RX codes Use with the vw_mapping_rxnorm_to_ndc view
enr_rxnsat	Map NCD codes and RX codes. Use with the vw_mapping_rxnorm_to_ndc view
enr_valueset	Limited value sets from eCQM (Electronic Quality Measure)
enr_zip_lat_long	Latitude and longitude values for US ZIP codes Use to create geographic visualizations

FHIR Data Tables

You can use these FHIR data tables to get more detailed information about your FHIR resource data. Each table contains the information for one type of FHIR resource, which is denoted in the final part of the table name. Within each table, the columns directly map to the attributes defined by the FHIR specification for that resource.

For more information about each of the columns and possible nested values, refer to the HL7 FHIR Documentation link for each resource.

FHIR Data Table	FHIR Response Type
fhir_r4.condition	Condition • HL7 FHIR Documentation — Condition
fhir_r4.coverage	Coverage • HL7 FHIR Documentation — Coverage
fhir_r4.encounter	Encounter • HL7 FHIR Documentation — Encounter
fhir_r4.explanationofbenefit	Explanation of Benefits • HL7 FHIR Documentation — Explanation of Benefit
fhir_r4.observation	Observation • HL7 FHIR Documentation — Observation
fhir_r4.organization	Organization • HL7 FHIR Documentation — Organization
fhir_r4.patient	Patient • HL7 FHIR Documentation — Patient
fhir_r4.practitioner	Practitioner • HL7 FHIR Documentation — Practitioner

Views

You can use the following views to help you evaluate data quality and referential integrity. You can also use them to get view profile information for key FHIR tables, such as Patient, Practitioner, and Explanation of Benefits.

View	Description
vw_loaded_resources	Count of resources loaded by day
vw_ref_int_with_path_and_privclientid_aggr	Aggregate information on referential integrity among resources If a FHIR resource contains a reference, this is the count of resources that exist (match) or don’t exist (mismatch).
vw_ref_int_with_path_and_privclientid_details	Detailed information on referential integrity Lists all references and whether they exist (match) or don’t exist (mismatch).