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.

image (8).png

Figure 1: Architecture Diagram

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.

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://solpoc.blob.core.windows.net/dcr/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

Email Phone Number Zone Zip Code Products Sales Date Sales Delivery
user0_3@email.com 000-000-0005 1A 51500 product_5 02-04-2021 43.57
user0_3@email.com 001-001-0011 2A 20500 product_3 05-05-2021 97.3
user0_3@email.com 002-002-0023 1A 77300 product_5 21-04-2021 94.33
user0_3@email.com 003-003-0033 1A 77800 product_2 18-04-2021 75.39
user0_3@email.com 004-004-0042 2A 55300 product_4 16-04-2021 36.27

4.2 Media Data Table

Email Phone Number Status Age Band Sales Date Campaign Sec View Cost
user0_3@email.com 000-000-0007 MEMBER 50 02-04-2021 campaign_1 25 2.31
user1_3@email.com 001-001-00110 MEMBER 60 05-05-2021 campaign_1 35 0.06
user10_1@email.com 010-010-0108 MEMBER 65 21-04-2021 campaign_1 39 0.03
user100_2@email.com 100-100-1005 SILVER 30 18-04-2021 campaign_1 15 2.5
user101_2@email.com 101-101-1011 GOLD 60 16-04-2021 campaign_3 34 2.75

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.

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.

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.

Screenshot (219).png

Figure 1: Drafting the Workflow

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.

Screenshot (216).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 (217).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 (218).png

Figure 4: Run the Workflow

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.

Comments

Please sign in to leave a comment.

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