Fortanix Confidential Computing Manager - Complex Use Case - SQL

1.0 Introduction

This article describes a complex example of how you can use Fortanix Confidential Computing Manager with SQL queries to streamline data analysis within the healthcare institution.

As the leader of a busy healthcare institution, you are always looking for methods to improve patient care and operational efficiency. Utilizing the capabilities of Fortanix Confidential Computing Manager, you aim to simplify the data and uncover the valuable insights hidden within your huge datasets.

2.0 Contextualizing Confidential Computing Manager

While managing the stacks of patient files and data reports, you have decided to explore Fortanix Confidential Computing Manager. After releasing the potential of this application, you envision a future where your team can analyze data efficiently to make informed decisions while upholding compliance and safeguarding data privacy.

To achieve optimal outcomes in healthcare data analysis, it is imperative to follow a structured approach. The following procedure outlines key steps to leverage Fortanix Confidential Computing Manager effectively, ensuring secure and insightful data exploration:

  1. Step 1: Accessing Data Sources - In this step, you will begin by accessing data from various sources securely and efficiently, laying the foundation for your analysis.
  2. Step 2: Data Analysis: Unveiling Insights - This step involves delving into your data to uncover valuable insights that can inform decision-making and improve operational efficiency.
  3. Step 3: Exploring Data with SQL Scripts - Here, you will utilize SQL scripts to explore your datasets in detail, extracting meaningful information to gain a deeper understanding of your data.
  4. Step 4: Aggregating Data for Comprehensive Analysis - This step focuses on merging datasets to create a comprehensive view, enabling more thorough analysis and interpretation of the data.
  5. Step 5: Exporting Analyzed Results - After your analysis is complete, you will securely export the analyzed results to share them with relevant stakeholders, ensuring data privacy and compliance.
  6. Step 6: Workflow Configuration and Execution - Finally, you will configure and execute workflows to streamline data processing and analysis, optimizing your use of the Fortanix Confidential Computing Manager platform.

3.0 Accessing Data Sources

To gather your real-world data effectively, you will utilize Inbound Data Connectors to access information from diverse sources, including CSV files and Google BigQuery. These connectors serve as gateways to collaborate data into the Fortanix Confidential Computing Manager, ensuring seamless integration and accessibility of your datasets for further analysis and processing.

3.1 Inbound Connector 1: HTTPS-CSV

To start, fetch the data about incoming medications from a CSV file hosted on the healthcare provider's internal server. This file contains details such as medication names, dosages, prescribing physicians, patient IDs, and prescription dates.

  • Data Connector Type: CSV
  • Connector Name: medications_data
  • Description: This connector fetches data regarding incoming medications from a CSV file hosted on the healthcare provider's internal server. The CSV file includes information such as medication names, dosage, prescribing physician, patient ID, and date prescribed.
  • Labels: (optional)
  • URL: https://fortanix-pocs-data.s3.us-west-2.amazonaws.com/medications.csv 
    NOTE
    Ensure to obtain a valid and pre-signed URL for accessing the data if you want to fetch the data from a URL.  This URL can be hosted on Amazon Web Services (AWS), Microsoft Azure, or the Google Cloud Platform (GCP).

3.2 Inbound Connector 2: HTTPS-CSV

Next, access the data about incoming patients from a CSV file stored on the hospital's local network. This file includes patient IDs, names, dates of birth, genders, contact information, and admission dates.

  • Data Connector Type: CSV
  • Connector Name: patients_data
  • Description: This connector retrieves data about incoming patients from a CSV file stored on the hospital's local network. The CSV file contains details such as patient IDs, names, dates of birth, genders, contact information, and admission dates.
  • Labels: (optional)
  • URL: https://fortanix-pocs-data.s3.us-west-2.amazonaws.com/patients.csv 
    NOTE
    Ensure to obtain a valid and pre-signed URL for accessing the data if you want to fetch the data from a URL.  This URL can be hosted on Amazon Web Services (AWS), Microsoft Azure, or the Google Cloud Platform (GCP).

3.3 Inbound Connector 3: Google BigQuery

For more comprehensive data, connect it to a Google BigQuery dataset named Conditions_data. This dataset, maintained by the hospital's research department, contains detailed information about various medical conditions, including names, descriptions, diagnostic criteria, and treatment protocols.

  • Data Connector Type: Big Query
  • Connector Name: conditions_data
  • Description: This connector accesses a Google BigQuery dataset named Conditions_data, which contains comprehensive information about various medical conditions. The dataset is maintained by the hospital's research department and includes data such as condition names, descriptions, diagnostic criteria, and treatment protocols.
  • Labels: (optional)
  • Project ID: fortanix
  • Dataset Name: healthcare
  • Table Name: conditions-1
  • API Key: To create the API key, refer to the official documentation of Google Big Query.

3.4 Inbound Connector 4: HTTPS-CSV

Finally, retrieve the data about patient encounters from a CSV file hosted on a secure server. This file contains encounter IDs, patient IDs, encounter dates, attending physicians, diagnoses, and procedures performed during the encounter.

  • Data Connector Type: CSV
  • Connector Name: encounters_data
  • Description: This connector retrieves data about incoming patient encounters from a CSV file hosted on a secure server. The CSV file contains details of patient encounters, including encounter IDs, patient IDs, encounter dates, attending physicians, diagnoses, and procedures performed during the encounter.
  • Labels: (optional)
  • URL: https://fortanix-pocs-data.s3.us-west-2.amazonaws.com/encounters.csv 
    NOTE
    Ensure to obtain a valid and pre-signed URL for accessing the data if you want to fetch the data from a URL.  This URL can be hosted on Amazon Web Services (AWS), Microsoft Azure, or the Google Cloud Platform (GCP).

4.0 Data Analysis: Unveiling Insights

After securely importing your data into Fortanix Confidential Computing Manager, it is time to delve into the analysis. This involves merging various datasets such as patient records, medication details, and encounter information. You can achieve this by using SQL scripts, which enables you to handle and process the data effectively.

4.1 Conditions Data Table

Start Stop Patients ID Code Description
01-05-2001 10-06-2001 1d604da9-9a81-4ba9-80c2-de3375d59b40 40055000 Chronic sinusitis (disorder)
09-08-2011 16-08-2011 8d4c4326-e9de-4f45-9a4c-f8c36bff89ae 4.45E+08 Viral sinusitis (disorder)
16-11-2011 26-11-2011 8d4c4326-e9de-4f45-9a4c-f8c36bff89ae 1.96E+08 Acute viral pharyngitis (disorder)
13-05-2011 27-05-2011 10339b10-3cd1-4ac3-ac13-ec26728cb592 10509002 Acute bronchitis (disorder)
06-02-2011 14-02-2011 f5dcd418-09fe-4a2f-baa0-3da800bd8c3a 1.96E+08 Acute viral pharyngitis (disorder)

4.2 Patients Data Table

Patients ID Birthdate SSN Name Gender Address
1d604da9-9a81-4ba9-80c2-de3375d59b40 25-05-1989 999-76-6866

José Eduardo181

Gómez206

M 427 Balistreri Way Unit 19
034e9e3b-2def-4559-bb2a-7850888ae060 14-11-1983 999-73-5361 Milo271 Feil794 M 422 Farrell Path Unit 69
10339b10-3cd1-4ac3-ac13-ec26728cb592 02-06-1992 999-27-3385 Jayson808 Fadel536 M 1056 Harris Lane Suite 70
8d4c4326-e9de-4f45-9a4c-f8c36bff89ae 27-05-1978 999-85-4926 Mariana775 Rutherford999 F 999 Kuhn Forge

4.3 Encounters Data Table

Patients ID Organization Provider Encounter Class Description
034e9e3b-2def-4559-bb2a-7850888ae060 e002090d-4e92-300e-b41e-7d1f21dee4c6 e6283e46-fd81-3611-9459-0edb1c3da357 ambulatory Encounter for symptom
034e9e3b-2def-4559-bb2a-7850888ae060 772ee193-bb9f-30eb-9939-21e86c8e4da5 6f1d59a7-a5bd-3cf9-9671-5bad2f351c28 wellness General examination of patient (procedure)
1d604da9-9a81-4ba9-80c2-de3375d59b40 5d4b9df1-93ae-3bc9-b680-03249990e558 af01a385-31d3-3c77-8fdb-2867fe88df2f ambulatory Encounter for symptom
1d604da9-9a81-4ba9-80c2-de3375d59b40 3dc9bb2d-5d66-3e61-bf9a-e234c6433577 bb17e691-262b-3546-93d5-d88e7de93246 wellness General examination of patient (procedure)
10339b10-3cd1-4ac3-ac13-ec26728cb592 b03dba4f-892f-365c-bfd1-bfcfa7a98d5d 7ed6b84a-b847-3744-9d42-15c42297a0c2 wellness General examination of patient (procedure)

4.4 Medications Data Table

Patients ID Base Cost Payer Coverage Dispenses Total Cost
8d4c4326-e9de-4f45-9a4c-f8c36bff89ae 677.08 10 12 8124.96
8d4c4326-e9de-4f45-9a4c-f8c36bff89ae 624.09 0 12 7489.08
8d4c4326-e9de-4f45-9a4c-f8c36bff89ae 43.32 20 12 519.84
10339b10-3cd1-4ac3-ac13-ec26728cb592 8.14 7 1 8.14
1d604da9-9a81-4ba9-80c2-de3375d59b40 11.91 0 1 11.91

5.0 Aggregating Data for Comprehensive Analysis

To streamline your analysis, merge the patient data, medication details, and encounter information into a single table named patient_medication_encounter. This table will facilitate comprehensive analysis of patient demographics, medication usage, and encounter details.

This SQL script combines data from multiple tables—patients, medications, conditions, and encounters—using JOIN operations. It selects distinct patient IDs along with their gender, race, encounter class, and condition codes.

Table Name: Patient_Medication_Encounter

Query Language: SQL Join Query Script (Join Query)

SELECT DISTINCT "patients"."patients-Id",
    "patients".GENDER,
    "patients".RACE,
    encounters.ENCOUNTERCLASS,
    conditions.CODE as condition_code
FROM "patients"
    JOIN medications ON medications."patients-Id" = "patients"."patients-Id"
    JOIN conditions ON conditions."patients-Id" = "patients"."patients-Id"
    JOIN encounters ON encounters."patients-Id" = "patients"."patients-Id"
WHERE medications.CODE = '860975'
    AND medications.STOP = '';

6.0 Exploring Data with SQL Scripts

In this section delves into SQL scripts to explore your data more deeply. These scripts help you find specific information you may be interested in, like patient demographics and health conditions.

6.1 SQL Script 1: Patient Condition with Gender Female

This script focuses on and helps you identify and analyze conditions specific to women's health.

  • Name: Patient_Condition_Gender_F
  • Description: Filters patient conditions for females.
  • Group: Fortanix Marketplace Imports
  • Query Language: SQL
    SELECT COUNT(DISTINCT "patients-Id")
    FROM joined_data
    WHERE ENCOUNTERCLASS = 'ambulatory'
    AND GENDER='F';
    

6.2 SQL Script 2: Patient Condition with Gender Male

This script is similar to the previous one, but targets on the health conditions that predominantly affecting men.

  • Name: Patient_Condition_Gender_M
  • Description: Filters patient conditions for males.
  • Group: Fortanix Marketplace Imports
  • Query Language: SQL
    SELECT COUNT(DISTINCT "patients-Id")
    FROM joined_data
    WHERE ENCOUNTERCLASS = 'ambulatory'
    AND GENDER='M';
    

6.3 SQL Script 3: Patient with Diabetic Retinopathy

This script targets patients diagnosed with diabetic retinopathy, a complication of diabetes. It helps you understand the prevalence and management of this condition within the patient population.

  • Name: Patients_Diabetic_Retinopathy
  • Description: Identifies patients diagnosed with diabetic retinopathy.
  • Group: Fortanix Marketplace Imports
  • Query Language: SQL
    SELECT COUNT(DISTINCT "patients-Id")
    FROM joined_data
    WHERE condition_code = '422034002';
    

7.0 Exporting Analyzed Results

After completing your analysis, it is essential to securely share your findings with the relevant stakeholders. To accomplish this, you will need to set up Outbound Connectors within the Fortanix Confidential Computing Manager. These connectors enable you to export your processed data securely to external systems or stakeholders. You can configure the Outbound Connectors with specific details such as the type of connector, description, and destination URL to ensure the safe transfer of data.

7.1 Outbound Connector 1: HTTPS-CSV

This outbound connector It facilitates the secure transfer of healthcare data to external systems or stakeholders. The exported CSV file contains sanitized patient data, aggregated statistics, or other relevant information derived from data processing operations.

The following is the output of SQL queries, each offering insightful metrics regarding patient counts based on specific criteria such as gender distribution and the prevalence of a particular medical condition within the dataset.

"SELECT COUNT(DISTINCT ""patients-Id"")
FROM joined_data
WHERE ENCOUNTERCLASS = 'ambulatory'
AND GENDER='M';",25

"SELECT COUNT(DISTINCT ""patients-Id"")
FROM joined_data
WHERE ENCOUNTERCLASS = 'ambulatory'
AND GENDER='F';",22

"SELECT COUNT(DISTINCT ""patients-Id"")
FROM joined_data
WHERE condition_code = '422034002';",6

8.0 Workflow Configuration and Execution

After setting up all your data connectors and scripts, it's time to create and optimize your workflow to ensure efficient data processing and analysis.

8.1 Creating a Workflow

Create a workflow in the Fortanix Confidential Computing Manager user interface (UI). For detailed information, refer to the Creating a Workflow documentation.

Screenshot (227).png

Figure 1: Drafting the Workflow

8.2 Configuring the Workflow

After you have added the inbound connector, scripts, and outbound connectors to the Fortanix Confidential Computing Manager UI, now you must place them in the workflow working area and connect them. For detailed information, refer to the Configuring a Workflow documentation.

Screenshot (228).png

Figure 2: Connect the Nodes

8.3 Requesting and Approving the Workflow

With your workflow configured, you need to send a request for approval and wait for them to review the workflow approval request and either approve or decline as necessary. For detailed information, refer to the Requesting the Workflow Approval documentation.

Screenshot (229).png

Figure 3: Approve the Workflow

8.4 Running the Application Workflow

After your workflow is approved and finalized, you can proceed with running the ACI application workflow. For detailed information, refer to the Running the ACI Application Workflow documentation.

Screenshot (231).png

Figure 4: Run the Workflow

Congratulations! By effectively utilizing Fortanix Confidential Computing Manager, you have optimized your healthcare data analysis workflows. Through secure data gathering, thorough analysis, and seamless export processes, you have gained invaluable insights that will empower better decision-making and enhance patient outcomes throughout your organization.

Comments

Please sign in to leave a comment.

Was this article helpful?
0 out of 0 found this helpful