FHIR SQL Builder Introduction

The FHIR SQL Builder, or Builder, is a sophisticated projection tool used to create custom SQL schemas using data in an HL7® FHIR® repository without moving the data to a separate SQL repository. The Builder is designed specifically to work with FHIR repositories and multi-model databases in InterSystems products.

The objective of the Builder is to enable data analysts and business intelligence developers to work with FHIR using familiar analytic tools, without having to learn a new query syntax. FHIR data is encoded in a complex directed graph and cannot be queried using standard SQL syntax. A graph-based query language, FHIRPath, is designed to query FHIR data, but it is non-relational. Enabling a data steward to create a customized SQL projection of their FHIR repository, using tables, columns, and indexes, the Builder makes it possible for data analysts to query FHIR data without the complexity of learning FHIRPath or the FHIR search syntax.

The following diagram shows the relationships between the Builder and other components in InterSystems products.

The Builder analyzes a FHIR repository to generate summary information, including the types of resources, elements, and values it contains, as well as the number of each type of resource. You decide which FHIR resources and elements to include in your custom SQL projection and how to map them.

When setting up your custom SQL projection, you can set up the following additional structures to improve later query speed:

• You can create standard indexes on individual columns. For example, if a customer reports slow response time when seeking certain data, you can set up an index for the column containing that data to improve speed.

• You can store a local copy of a set of columns. This is especially effective for queries related to data in collections where a single value is projected as a column. For instance, if you have a collection of identifiers — say driver’s license number, hospital ID, and social security number, and SSN is in the last position — if you were looking for a SSN, it is faster to fetch from a local copy than to iterate the collection over and over.