--- title: "Fortanix Confidential Computing Manager - Complex Use Case - SQL" slug: "fortanix-ccm-complex-use-case-sql" updated: 2026-06-26T16:16:17Z published: 2026-06-26T16:16:17Z canonical: "support.fortanix.com/fortanix-ccm-complex-use-case-sql" --- > ## Documentation Index > Fetch the complete documentation index at: https://support.fortanix.com/llms.txt > Use this file to discover all available pages before exploring further. # 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 (CCM) with SQL queries to streamline data analysis within a 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 CCM, 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 CCM. 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 CCM 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 CCM 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 CCM, 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](https://download.fortanix.com/clients/CCM/usecases/medications.csv) > [!NOTE] > 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](https://docs.aws.amazon.com/AmazonS3/latest/userguide/using-presigned-url.html) (AWS), [Microsoft Azure](https://learn.microsoft.com/en-us/azure/storage/common/storage-sas-overview), or the [Google Cloud Platform](https://cloud.google.com/storage/docs/access-control/signed-urls) (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](https://download.fortanix.com/clients/CCM/usecases/patients.csv) > [!NOTE] > 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](https://docs.aws.amazon.com/AmazonS3/latest/userguide/using-presigned-url.html) (AWS), [Microsoft Azure](https://learn.microsoft.com/en-us/azure/storage/common/storage-sas-overview), or the [Google Cloud Platform](https://cloud.google.com/storage/docs/access-control/signed-urls) (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* [*Google Big Query*](https://cloud.google.com/bigquery/docs/reference/rest)*.* ### 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](https://download.fortanix.com/clients/CCM/usecases/encounters.csv) > [!NOTE] > 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](https://docs.aws.amazon.com/AmazonS3/latest/userguide/using-presigned-url.html) (AWS), [Microsoft Azure](https://learn.microsoft.com/en-us/azure/storage/common/storage-sas-overview), or the [Google Cloud Platform](https://cloud.google.com/storage/docs/access-control/signed-urls) (GCP). ## 4.0 Data Analysis: Unveiling Insights After securely importing your data into Fortanix CCM, 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) ```bash 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 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 ```bash 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 ```bash 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 ```bash 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 CCM. 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](https://download.fortanix.com/clients/CCM/usecases/output.csv) > [!NOTE] > 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](https://docs.aws.amazon.com/AmazonS3/latest/userguide/using-presigned-url.html) (AWS), [Microsoft Azure](https://learn.microsoft.com/en-us/azure/storage/common/storage-sas-overview), or the [Google Cloud Platform](https://cloud.google.com/storage/docs/access-control/signed-urls) (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. ```bash "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 CCM user interface (UI). *For more information, refer to* [*Create a Workflow*](/v1/docs/fortanix-ccm-run-the-workflow-web-interface-connectors-scripts#40-creating-a-workflow)*.* ### 8.2 Configuring the Workflow After you have added the inbound connector, scripts, and outbound connectors in the Fortanix CCM UI, you must now place them in the workflow working area and connect them. *For more information, refer to* [*Configure the Approved Workflow*](/v1/docs/fortanix-ccm-run-the-workflow-web-interface-connectors-scripts#60-configure-the-approved-workflow)*.* ### 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 more information, refer to* [*Request the Workflow Approval*](/v1/docs/fortanix-ccm-run-the-workflow-web-interface-connectors-scripts#50-requesting-the-workflow-graph-approval)*.* ### 8.4 Running the Application Workflow After your workflow is approved and finalized, you can proceed with running the ACI application workflow. *For more information, refer to* [*Run the ACI Application Workflow*](/v1/docs/users-guide-run-the-workflow-web-interface-aci-applications-1#50-run-the-aci-application-workflow)*.* Congratulations! By effectively utilizing Fortanix CCM, 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. ## Related - [Run the Workflow - Web Interface - Connectors and Scripts](/fortanix-ccm-run-the-workflow-web-interface-connectors-scripts.md) - [Fortanix DSM with Microsoft SQL Server TDE - Advanced](/fortanix-dsm-with-microsoft-sql-server-tde-integration-advanced.md)