Fortanix Confidential Computing Manager - Simple Use Case - SQL

1.0 Introduction

This article describes a simple example of how you can use Fortanix Confidential Computing Manager to streamline data analysis within the airline industry.

As a dynamic player in the airline industry, you are constantly looking for innovative methods to improve the customer experience and drive revenue growth. Utilizing the capabilities of Fortanix Confidential Computing Manager, you are ready to revolutionize your data analysis practices and unlock valuable insights hidden within your datasets.

2.0 Contextualizing Computing Manager

Given the complexities of airline operations and customer interactions, you have decided to explore Fortanix Confidential Computing Manager. After releasing the potential of this application, you envisioned a future where data-driven decisions drive strategic initiatives, resulting in improved operational efficiency and customer satisfaction.

To achieve optimal outcomes in airline 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 customer data from a CSV file securely hosted online. It provides access to essential details such as customer contact information and sales data pertinent to the airline industry.

• Data Connector Type: CSV

• Connector Name: airline_data

• Description: This connector fetches the customer data from a CSV file hosted securely online. It provides access to details such as customer contact information and sales data for airlines.

• Labels: (optional)

• URL: https://download.fortanix.com/clients/CCM/usecases/airline_customers.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, retrieve the customer data from a CSV file securely hosted online. It grants access to vital information regarding media customers, including contact details and relevant sales data.

• Data Connector Type: CSV

• Connector Name: media_data

• Description: This connector seamlessly accesses and integrates the data from an online CSV file, specifically tailored to media customers. You can gain insights into customer behavior, preferences, and engagement metrics to optimize marketing strategies and enhance customer satisfaction within the media industry.

• Labels: (optional)

• URL: https://download.fortanix.com/clients/CCM/usecases/media_customers.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 airline customer data and media customer data. This is achieved by using SQL scripts, which allow you to handle and process the data effectively.

4.1 Customer Data Table

4.2 Media Data Table

5.0 Aggregating Data for Comprehensive Analysis

To streamline our analysis, merge the airline customer data and media customer data into a single dataset named combined_customers. This dataset will facilitate a comprehensive analysis of customer demographics, sales performance, and campaign effectiveness.
This SQL script combines data from multiple tables, airline_data and media_data, using JOIN operations. It selects distinct customer IDs along with their contact information, sales data, and campaign details.

Table Name: JOIN

Query Language: SQL Join Query Script (Join Query)

SELECT
    a.EMAIL,
    a.PHONE AS Airline_Phone,
    a.Zone,
    a.ZIP AS Airline_ZIP,
    a.PRODUCT AS Airline_Product,
    a.SLS_DATE AS Airline_Sales_Date,
    a.SALES_DLR,
    m.PHONE AS Media_Phone,
    m.STATUS,
    m.AGE_BAND,
    m.SLS_DATE AS Media_Sales_Date,
    m.CAMPAIGN,
    m.SEC_VIEW,
    m.COST
FROM
    airline_customers AS a
JOIN
    media_customers AS m ON a.EMAIL = m.EMAIL;

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 customer demographics and purchasing patterns.

6.1 SQL Script 1: Sales Impact

This script helps in analyzing the overall sales impact of your campaigns.

• Name: Sales Impact

• Description: Calculates total sales revenue from joined data, indicating campaign effectiveness.

• Group: Clean room

• Query Language: SQL Aggregate

  SELECT SUM(SALES_DLR) AS total_sales_revenue
  FROM
      joined_data;
  

6.2 SQL Script 2: Age Group

This script helps you understand the common age groups among your customers.

• Name: Age Group

• Description: Identify the most common age group among customers.

• Group: Clean room

• Query Language: SQL Aggregate

  SELECT COUNT(AGE_BAND) AS common_age_band_count
  FROM
      joined_data
  GROUP BY
      AGE_BAND
  ORDER BY
      common_age_band_count DESC
  LIMIT 1;
  

6.3 SQL Script 3: Campaign Cost

This script calculates the total cost spent on campaigns.

• Name: Campaign Cost

• Description: Calculate total expenditure on campaigns.

• Group: Clean room

• Query Language: SQL Aggregate

  SELECT SUM(COST) AS total_cost_spent
  FROM
      joined_data;
  

6.4 SQL Script 4: Average Sales

This script determines the average sales revenue per campaign.

• Name: Average Sales

• Description: Find the average revenue per campaign.

• Group: Clean room

• Query Language: SQL Aggregate

  SELECT AVG(SALES_DLR) AS avg_sales_revenue_per_campaign
  FROM
      joined_data;
  

6.5 SQL Script 5: Researcher Query

This script provides insights into the commonality of email addresses within our data.

• Name: Researcher Query

• Description: Discover common email addresses in the dataset.

• Group: Clean room

• Query Language: SQL Aggregate

  SELECT COUNT(DISTINCT EMAIL) AS common_emails_count
  FROM
      joined_data;
  

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 connector securely transmits data to the designated Analyst CSV file. It enables seamless integration for exporting data to the specified destinations while ensuring data security and integrity.

• Data Connector Type: CSV

• Connector Name: Analyst

• Description: This connector facilitates the secure transmission of data to the Analyst CSV file. It enables seamless integration for exporting data to the designated destinations.

• Labels: (optional)

• URL: https://download.fortanix.com/clients/CCM/usecases/analyst.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 email counts within the dataset:

sql,output
"SELECT COUNT(DISTINCT EMAIL) AS common_emails_count
FROM
    joined_data;",343

7.2 Outbound Connector 2: HTTPS-CSV

This connector facilitates efficient data transfer to the Researcher CSV file. It offers a reliable method for exporting data while upholding stringent security measures, ensuring the integrity of the data throughout the transmission process.

• Data Connector Type: CSV

• Connector Name: Researcher

• Description: This connector ensures efficient data transfer to the Researcher CSV file. It provides a reliable means of exporting data, maintaining security and integrity throughout the process.

• Labels: (optional)

• URL: https://download.fortanix.com/clients/CCM/usecases/research.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 sales and demographic data:

sql,output
"SELECT AVG(SALES_DLR) AS avg_sales_revenue_per_campaign
FROM
    joined_data;",49.11014157014157
"SELECT SUM(COST) AS total_cost_spent
FROM
    joined_data;",1414.81
"SELECT COUNT(AGE_BAND) AS common_age_band_count
FROM
    joined_data
GROUP BY
    AGE_BAND
ORDER BY
    common_age_band_count DESC
LIMIT 1;",435
"SELECT SUM(SALES_DLR) AS total_sales_revenue
FROM
    joined_data;",38158.58

8.0 Workflow Configuration and Execution

Now that all your data connectors and scripts are set up, it is time to create and fine-tune your workflow to ensure smooth data processing and analysis.

8.1 Creating a Workflow

Create a workflow in the Fortanix Confidential Computing Manager 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 in 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! Your efficient use of Fortanix Confidential Computing Manager has enhanced your healthcare data analysis workflows. Through secure data collection, thorough analysis, and smooth export processes, you have acquired invaluable insights. These insights will support better decision-making and improve patient outcomes across your organization.