FHIR SQL Builder (Experimental feature)
Note:
This capability first became available in InterSystems IRIS for Health 2022.3 as an experimental feature. It is now fully supported for production use in 2023.1.
The FHIR SQL BuilderOpens in a new tab (Builder) is a sophisticated projection tool to help developers create custom SQL schemas using data in their HL7® FHIR® (Fast healthcare interoperability resources) repository without moving the data to a separate SQL repository. The objective of the Builder is to enable data analysts and business intelligence developers to work with FHIR using familiar analytic tools, such as ANSI SQL, Power BI, or Tableau, without having to learn a new query syntax. The Builder enables a data steward to create a customized projection of their FHIR repository, using tables, columns, and indexes, and makes it possible for data analysts to query FHIR data without the complexity of learning FHIRPath or the FHIR search syntax. The Builder is designed to support providers, medical device, pharmaceutical, and insurance companies who want to combine their FHIR data with other data in InterSystems IRIS for Health.
The Builder consists of several components as shown in the following figure.
The Builder analyzes InterSystems IRIS for Health FHIR repository to display FHIR summary information such as the number of each type of resource, elements, and values. This information is displayed in a client UI. The user is given the opportunity to decide what FHIR resources and elements they wish to include in their custom SQL projection and how they should be mapped. After these decisions are made and relayed back to the Builder, custom SQL projection tables are built.
Capabilities Include:
-
Analysis of a FHIR repository to summarize resources and elements. For large repositories, the user can specify to analyze only a percentage of total repository to speed up analysis.
-
Defining a customized SQL schema based on FHIR resources, elements, user selections, and column transformations
-
GUI selection tool which makes resource and element selection simple
-
Automatic projection of FHIR repository data to a customized SQL schema
-
Ability to define multiple SQL schemas
For more details, see the FHIR SQL Builder guideOpens in a new tab.
Note:
The FHIR SQL Builder is only included in the Advanced Server Tier of InterSystems IRIS for Health.
SQL Development
InterSystems SQL now offers a machine-readable format for query plans. Using this new option, the $SYSTEM.SQL.Explain() method will now produce a JSON-based rendering of the query plan with significantly more detail on the plan itself as well as the tables and indexes it accesses. Where the previous XML-based format used simple English phrases to describe different steps, the new format is easier to consume for tools that wish to perform more thorough analysis or graphical rendering of query plans.
This release introduces an opt-in capability to sample runtime parameters for SQL statements. The Statement Index already records a rich set of metadata such as detailed runtime statistics and query plans for each statement, which typically has any literals substituted for placeholders that parameterize the cached query code. Now, the statement index can be extended with a sampling of the actual runtime values for those parameters. These can then be combined with the normalized statement text to, for example, build a representative workload that can be run against another deployment, for example to benchmark a new hardware environment or experiment with a different set of indexes for the schema.
InterSystems SQL now supports CREATE SCHEMA and DROP SCHEMA commands to include in scripts for setting up and tearing down your application environment.
