iris-fhirsqlbuilder

This project shows how to use the FHIR SQL Builder to generate SQL statements for the FHIR Server from IRIS.

The end result will be available on Tableau.

Prerequisites

• IRIS Community Edition
• Git
• Docker
• Tableau Desktop

Installation

Git clone this repository.

git clone https://github.com/grongierisc/iris-fhirsqlbuilder.git

Run the following command to start IRIS.

docker-compose up -d

FHIR SQL Builder

For your convenience, this project contains preloaded data in the FHIR Server.

Preloaded data is available in the following folder.

data/fhir

Configure an FHIR Repository to analyze

Once IRIS is up and running, go to the FHIR SQL Builder and generate the SQL statements for the FHIR Server.

http://localhost:33783/csp/fhirsql/index.html

or

http://localhost:33783/csp/fhirsql/index.csp (if you are running IRIS for health 2023.1 or lower)

Login / password : SuperUser / SYS

Then register the current FHIR Server with the FHIR SQL Builder.

To do so, click on New Analysis.

Then click on Register FHIR Server.

Fully the form with the following values:

• FHIR Server Name: FHIRSERVER
• Host: localhost
• Port: 52773
• Credentials: SuperUser / SYS
  • Click on New Credentials to create a new user with the right credentials.
• FHIR repository url : /fhir/r4

Then click on Save.

Now we can generate an analysis.

• Selectivity : 100

Then click on Lunch Analysis.

Create projections

Once the analysis is done, we can create projections.

To help you in this task, you can import the following projections.

misc/ExportFHIRtoSQL.json

Click on Import Projections.

Then click on Import.

Select your analysis and click on Import.

If you want to see the projections, click on Edit Projections.

Generate SQL statements

Once the projections are created, we can generate the SQL statements.

Select your analysis, the projections and the schema.

Then click on Projection.

Usage

Now that we have the SQL statements, we can play with them into IRIS.

Go to the SQL UI :

http://localhost:33783/csp/sys/exp/%25CSP.UI.Portal.SQL.Home.zen?$NAMESPACE=FHIRSERVER

Let’s play with few SQL statements.

Patient

Display the patient as a table :

SELECT 
ID, AddressCity, BirthDate, Gender, Key, NameFamily, NameGiven, NamePrefix, NameUse
FROM AA.Patient

Result :

Join patient with an external table

Create an external table :

create TABLE AB.DriverLicense (
Code varchar(255) not null,
Valide TINYINT not null

)

Insert data into the external table :

INSERT INTO AB.DriverLicense (Code, Valide) VALUES ('S99916528', 1);
INSERT into AB.DriverLicense (Code, Valide) VALUES ('S99950276', 0);

Join the external table with the patient table :

SELECT 
P.NameFamily, P.NameGiven,DL.*
FROM AA.Patient P
inner join AB.DriverLicense DL on P.IdentifierValue = DL.Code

Result :

What is neat to note here is that we can join the FHIR Server with an external table.

Then it’s in the imported projections we ask to project not all the identifiers but only the driver license.

From ExportFHIRtoSQL.json :

        {
          "name": "IdentifierTypeCodingCode",
          "type": "String",
          "path": "Patient.identifier.type.coding.where(code = 'DL').code",
          "index": false
        },
        {
          "name": "IdentifierValue",
          "type": "String",
          "path": "Patient.identifier.where(type.coding.code = 'DL').value",
          "index": false
        }

Play with iknowpy

We can also play with iknowpy.

iknowpy is a natural language processing library available on python.

We can use it to extract information from a text.

Let’s create an SQL function in python :

CREATE FUNCTION sqliknowparser(tText VARCHAR(50000))
    RETURNS VARCHAR(50000)
    LANGUAGE PYTHON
{
    import iknowpy
engine = iknowpy.iKnowEngine()

# index some text
text = tText
engine.index(text, 'en')

t_output = ""

# or make it a little nicer
for s in engine.m_index['sentences']:
    for e in s['entities']:
        if e['type'] == 'Concept':
            t_output = t_output  + e['index']+ "|"

return t_output[:-1]

}

Then we can use it in a SQL statement :

SELECT
ID, DescriptionText, Key, LifecycleStatus, SubjectReference, $piece(sqliknowparser(DescriptionText),'|',1) as entities
FROM AA.Goal 
union
SELECT 
ID, DescriptionText, Key, LifecycleStatus, SubjectReference, $piece(sqliknowparser(DescriptionText),'|',2) as entities
FROM AA.Goal

Result :

Tableau

To make a connection with Tableau, you need to install the JDBC driver.

Copy the JDBC driver in the drivers folder of Tableau.

JDBC driver : https://github.com/intersystems-community/iris-driver-distribution/blob/main/JDBC/JDK18/intersystems-jdbc-3.6.1.jar

drivers folder :

• Windows : C:\Program Files\Tableau\Drivers
• Mac : ~/Library/Tableau/Drivers
• Linux : /opt/tableau/tableau_driver/jdbc

Just open Tableau and connect to IRIS with the JDBC driver.

Tableau file : TableauDemo.twb in misc folder.

Conclusion

We have seen how to use the FHIR SQL Build to create a SQL database.