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://download.fortanix.com/clients/CCM/usecases/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://download.fortanix.com/clients/CCM/usecases/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

4.2 Patients Data Table

4.3 Encounters Data Table

4.4 Medications Data Table

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.

• Data Connector Type: CSV

• Connector Name: results_export

• Description: This outbound connector exports processed data results in CSV format to an HTTPS endpoint.

• Labels: (optional)

• URL: https://download.fortanix.com/clients/CCM/usecases/output.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).

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.

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.

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.

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.

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.