FHIR SQL Builder: step by step

The FHIR standard establishes a powerful but flexible data model that can smoothly adapt to the complexities of operational healthcare data management. This flexibility comes at the cost of a data model with many tables and relationships, even for simple data such as the patient's record of telephone numbers, addresses, and emails. It would easily require querying 4 different tables. However, FHIR SQL Builder eliminates this problem, allowing you to create visual projections (mappings) in web wizards. It lets you consolidate data from 4 or more tables into just 1 and gives you the advantage of defining your table and field names yourself. This technique is essential for creating an analytical view of your FHIR repository without having to design ETL flows and stage/intermediate repositories. Other use cases that run better with FHIR SQL builder also include the following (source: https://www.intersystems.com/br/resources/introduction-to-fhir-sql-builder):

1. Population Health analysis;
2. Reports;
3. Public Health surveillance;
4. Anonymized research data sets;
5. Building machine learning models – predictive analytics;
6. HEDIS measures (Healthcare effectiveness data and information set);
7. Silver data layer for a FHIR data lake (Medallion Architecture).

The complexity of the FHIR Data Model

To understand how fundamental FHIR Builder looks like, check the following diagram showing the high-level view of FHIR data entities (source: https://hl7.org/fhir/R4/):

This diagram illustrates the intricacy of the FHIR model (157 data entities/resources) and the healthcare business scope. Every healthcare business topic has data entities that handle it, including clinical, diagnosis, medications, workflows, financial, patient, practitioner, care team, organization, locations and healthcare services, security, compliance, terminology, etc.
This figure demonstrates the sophistication of obtaining patient data (1 parent table with 5 more associated tables), meaning that you must access the patient resources and several other associated resources (source: https://www.hl7.org/fhir/patient.html):

The FHIR SQL Builder has a visual web tool for selecting various resources (patient, contact, etc.), setting different names, and consolidating everything into 1 or 2 tables using IRIS SQL projections. This article will walk you through the implementation of this solution.

Install an InterSystems FHIR Server to run FHIR SQL Builder

Let's install a sample FHIR repository from InterSystems' free application catalog, Open Exchange. To do that, follow the steps mentioned below:

1. Go to https://openexchange.intersystems.com/package/iris-fhir-template and complete the installation procedures as directed.

2. If you already have an active IRIS for Health, use IPM:

USER>zpm "install fhir-server"

3. If /you do not have IRIS for Health, get an IRIS Community (for trial testing purposes only) with FHIR enabled utilizing Git and Docker:
a. Clone the example project: git clone https://github.com/intersystems-community/iris-fhir-template.git.
b. Proceed to the application directory and run the next line:

docker-compose up -d

4. The article employed the Docker option run from the VSCode terminal, which after a few minutes presented the following result:

Accessing and learning about the FHIR SQL Builder UI

1. To access the FHIR SQL Build UI, go to http://[hostname]:[iris web port]/csp/fhirsql/index.html (for this FHIR template project, it is http://localhost:32783/csp/fhirsql/index.html).
2. Fill out the Login page (Username: _SYSTEM and Password: SYS):

3. The home page will be displayed as illustrated below:

4. On the top left, access the menu icon to see the next options:

• Home: go to the initial page.
• Repository Configuration: configure the connection with the FHIR Repository and the credentials to access it.
• Documentation: get detailed documentation online.
• Logout: end the web session.

Configure the credentials and FHIR repository connection

1. Go to the Repository Configuration:

2. Click the button New (second button) in the section Credentials:

3. Fill in the Credentials form with the data shown below:

• Name: SQLBuilderCreds
• Username: SuperUser
• Password: SYS

4. Click the Save button:

5. Now, click the first New button in the section FHIR Repository Configurations:

6. Fill out the form with the following information:

• Name: SQLBuilderConfig
• Host: localhost
• Port: 52773
• URL Prefix and SSL Configuration: do not fill it
• Credentials: SQLBuilderCreds
• FHIR Repository URL: /fhir/r4

7. Click the button Save:

 

Create analyses, transformation specifications, and projections

1. Proceed to the Home:

2. Click the button New in the section Analyses:

3. Set the form as demonstrated below:

• FHIR Repository: SQLBuilderConfig
• Selectivity Percentage: 100

4. Click the button Launch Analysis Task:

5. Click the button New in the section Transformation Specifications:

6. Configure the form with the next information:

• Name: SQLBuilderTransformation
• Analysis: SQLBuilderConfig

7. Click the button Create Transformation Specification to access the visual mapping editor for the modifications:

8. We will create a transformation for the Patient.
9. Click the Patient and select "name" from a drop-down menu. Tick family (String) as and given (String) as, and add the values LastName and FirstName respectively:

10. Click the button Add to Projection and check out the created mappings:

11. Now select "telecom", and type the value PatientPhone in the Column name:

12. Click Add To Projection:

13. At this point, pick "gender" and add the value PatientGender to the Column name:

14. Click Add To Projection:

15.Choose "birthDate" and type PatientBirthDate in the Column name:

16. Click Add To Projection:

17. Click Address and add Subtable name: PatientAddress. Fill in the following lines as mentioned below:

• latitude.valueDecimal: AddressLat
• longitude.valueDecimal: AddressLong
• line: Street
• city: City
• state: State
• postalCode: PostalCode
• country: Country

18. Click Add To Projection:

19. Click the Done button to make the transformations:

20.Click the New button in the Projections section:

21. Set the form with the next data:

• FHIR Repository: SQLBuilderConfig
• Transformation Specification: SQLBuilderTransformation
• Package Name: patientdata
• Package Users: click the button Add Entry and grant access to _SYSTEM and SuperUser

22. Click the button Launch Projection:

 

Accessing the projection data
 

1. Proceed to the Management Portal to the namespace FHIRSERVER(http://localhost:32783/csp/sys/UtilHome.csp?$NAMESPACE=FHIRSERVER) > System Explorer > SQL:

2. Pick the schema "patientdata":

3. Expand Tables to see the created projections:

4. Now, in the Execute Query tab, input the following line:
 

SELECT
Patient->PatientNames->FirstName, Patient->PatientNames->LastName, Patient->PatientGender, Patient->PatientBirthDate, Patient->PatientPhone, AddressLong, AdressLat, City, Country, PatientAddresssNumber, PostalCode, State, Street
FROM patientdata.PatientAddresss

5. Execute the query and witness the results.
6. Enjoy!