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