Building a Relationship Map Using Oracle APEX no-code platform and Oracle Database 23 ai

 



I am active on social media platforms like X, LinkedIn, and Threads. I like understanding how influencer relationships can provide valuable insights into my networks and trends. With Oracle APEX and the power of Oracle Database 23ai, I have created a robust influencer map to visualize these relationships. This blog post will guide you through building a similar relationship map using Oracle APEX and open-source data or your own dataset. I will go over the brief steps and then follow up with the detailed ones.

Step 1: Set Up Your Oracle APEX Environment

Before creating the relationship map, ensure you have a working Oracle APEX environment.

Create an Oracle APEX Workspace:

Step 2: Load Open-Source Data

For this example, we will use a Twitter dataset available on Kaggle.

Download the Dataset:

Go to Kaggle’s Twitter Data and download the dataset.

Create Tables to Store Data:

In Oracle APEX, navigate to SQL Workshop > SQL Commands and create tables for storing influencer and relationship data.

CREATE TABLE influencers (
id NUMBER PRIMARY KEY,
name VARCHAR2(100),
followers NUMBER,
following NUMBER,
tweets NUMBER
);

CREATE TABLE relationships (
id NUMBER PRIMARY KEY,
source NUMBER REFERENCES influencers(id),
destination NUMBER REFERENCES influencers(id)
);

Load Data into Tables:

Go to SQL Workshop > Utilities > Data Workshop and import your CSV files into the influencers and relationships tables.

Step 3: Create an APEX Application

Create a New Application:

Go to App Builder > Create > New Application.

Name your application (e.g., “Influencer Map”).

Add Pages to the Application:

Add a Dashboard page to display key metrics.

Add an Interactive Report page for the influencers table.

Add an Interactive Report page for the relationships table.

Add a Graph Visualization page to visualize the influencer network.

Step 4: Configure the Network Diagram

Let’s set up the Oracle JET Network Diagram to visualize the relationships.

Create a New Page for the Network Diagram:

Go to App Builder and add a new “Chart” > “Other” > “Network Diagram” page.

Name the page (e.g., “Influencer Network”) and finish the setup.

Configure the Data Sources for the Network Diagram:

Open the Network Diagram page.

Nodes Section:

Source Type: SQL Query
SQL Query:
SELECT
'n' || id AS id,
name AS label,
followers AS size,
NULL AS group_id
FROM influencers;

ID Column: id
Label Column: label
Size Column: size
Group Column: group_id

Links Section:

Source Type: SQL Query
SQL Query:
SELECT
'n' || source AS source,
'n' || destination AS destination,
NULL AS link_id
FROM relationships;

Source Column: source
Destination Column: destination
Link ID Column: link_id

Customize Node and Link Properties:

Node Styling:

Size: Based on followers count (size column)
Tooltip: Name: {label}, Followers: {size}

Link Styling:

Source Type: SQL Query
SQL Query:
SELECT
'n' || source AS source,
'n' || destination AS destination,
NULL AS link_id
FROM relationships;

Source Column: source
Destination Column: destination
Link ID Column: link_id

Customize Node and Link Properties:

Node Styling:

Size: Based on followers count (size column)
Tooltip: Name: {label}, Followers: {size}

Link Styling:

Uniform styling
Tooltip: Source: {source}, Destination: {destination}

Step 5: Deploy and Test Your Application

Save Your Changes:

Click on “Save” to apply all the configurations.

Run the Application:

Click on the “Run” button to deploy your application.

Test the network diagram to ensure that nodes and links are displayed correctly.

Following these steps, you’ve successfully created an influencer map using Oracle APEX and open-source Twitter data. This powerful visualization can help you understand the relationships and dynamics within social networks.

Oracle APEX’s integration with Oracle JET provides a robust platform for creating interactive and insightful visualizations, making it an excellent choice for analyzing complex data relationships.

Stay tuned for more posts on leveraging Oracle technologies to build innovative applications and visualizations!

Detailed Steps

This precess involves several steps, including setting up your Oracle APEX environment, loading the data into your database, and then creating and configuring the application. Here is a step-by-step guide to achieve this:

Step 1: Set Up Oracle APEX Environment

STEP1

Create an Oracle APEX Workspace:
— Go to the Oracle APEX workspace creation page: [Oracle APEX Workspace](https://apex.oracle.com/)
— Follow the instructions to create a workspace.

Log in to APEX:
— Use the credentials provided during the workspace creation to log in to your APEX instance.

STEP2

Load Open-Source Data
Choose Open-Source Data:
— For this example, let’s use the Twitter dataset available on Kaggle: [Twitter Data](https://www.kaggle.com/datasets).
— Download the dataset and prepare it for import (usually CSV format).

Create Tables to Store Data:
— In Oracle APEX, go to SQL Workshop > SQL Commands and create tables to store the data.

CREATE TABLE influencers (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    followers NUMBER,
    following NUMBER,
    tweets NUMBER
);

CREATE TABLE relationships (
    id NUMBER PRIMARY KEY,
    source NUMBER REFERENCES influencers(id),
    destination NUMBER REFERENCES influencers(id)
);
Load Data into Tables:
— Go to SQL Workshop > Utilities > Data Workshop and import your CSV files into the `influencers` and `relationships` tables.
STEP3
Create an APEX Application
Create a New Application:
— Go to App Builder > Create > New Application.
— Name your application (e.g., “Influencer Map”).
Add Pages to the Application:
— Add a Dashboard page to display key metrics.
— Add an Interactive Report page for the `influencers` table.
— Add an Interactive Report page for the `relationships` table.
— Add a Graph Visualization page to visualize the influencer network.
STEP4
Configure Dashboard Page
Add Cards to Display Metrics:
— Go to your Dashboard page, click on Add > Chart > KPI.
— Add cards to display metrics like total influencers, total followers, and total tweets.
SELECT COUNT(*) AS total_influencers FROM influencers

SELECT SUM(followers) AS total_followers FROM influencers

SELECT SUM(tweets) AS total_tweets FROM influencers
STEP5
Configure Interactive Reports
Interactive Report for Influencers:
— Go to the `influencers` report page and configure the report to display columns such as name, followers, following, and tweets.
Interactive Report for Relationships:
— Go to the `relationships` report page and configure the report to display the source and destination influencers.
STEP6
Create Graph Visualization
Install Oracle JET for Visualization:
— Oracle APEX supports Oracle JET for creating advanced visualizations. Go to Shared Components > Plug-ins and install the Oracle JET plugin if it’s not already installed.
Create a Network Graph:
— Go to your Graph Visualization page, add a new region, and select Oracle JET Network Diagram.
— Configure the network diagram to use data from the `relationships` table for edges and the `influencers` table for nodes.
SELECT
    'n' || id AS id,
    name AS label
FROM influencers
SELECT
    'n' || source AS source,
    'n' || destination AS destination,
    NULL AS label
FROM relationships
STEP7
Deploy and Test
Run the Application:
— Click on the Run button to deploy your application.
— Test the application to ensure all pages and reports are working correctly.
Adjust and Customize:
— Customize the look and feel of your application as needed.
— Add any additional functionalities or pages that may enhance the user experience.
Example Visualizations and Queries
Sample Query for Influencer Metrics:
SELECT name, followers, following, tweets
FROM influencers
WHERE followers > 10000
ORDER BY followers DESC
Sample Visualization of Influencer Network:
— Configure the Oracle JET Network Diagram to visualize relationships between influencers.
— Use the imported data to show connections between high-profile influencers and their followers.

Comments

Post a Comment

Popular posts from this blog

OCI Object Storage: Copy Objects Across Tenancies Within a Region

Image
Note : My original blog was published in ORACLE CLOUD INFRASTRUCTURE blog site on April 15th, 2019. I have republished it here with permission. Official Disclaimer : The views and opinions expressed in this blog are those of the author and do not necessarily reflect the official policy or position of Oracle Corporation. This post was developed jointly with Mohamad Charaf, Oracle Enterprise Cloud Architect. If you have two tenancies in the same region, and you want to copy data that is stored in Object Storage from one tenancy to the other without making the buckets public, this action requires some additional types of identity and access management (IAM) policies. This blog post walks you through how to create these policies. For this example, the source tenancy is named ACMEBMCS and the destination tenancy is named ACMEOCISA. In ACMEBMCS, the Object Storage buckets are in the benchmark compartment. In ACMEOCISA, the target compartment is oracleexa . The follow...
Read more

Religious Perspectives on Artificial Intelligence: My views

Image
  I am an Atheist. Born in a Hindu family, I have experimented with Christianity and Buddhism. Jokingly, I describe my journey from Many Gods to One and then None. I followed Hinayana Buddhism for a long time. Lately, I have been studying Lokayata/Carvaka school of the Hindu Darsana (philosophy). I like to be identified as a Hindu Atheist. Why Hindu? Because my birthplace is on the east side of the River Indus.
Read more

How MSPs Can Deliver IT-as-a-Service with Better Governance

Image
 Sanjay Basu As a solutions architect, I often support partners who deliver managed IT services to their end customers. Similarly, I work with large enterprises who manage IT for multiple business units. One of the most frequent requests I get is for best practices on how to align Oracle Cloud Infrastructure solutions and Identity and Access Management (IAM) policies with business-specific governance use cases. For enterprise customers, this means having better control over usage costs across multiple business units. For managed service providers (MSPs), this involves having better cost governance over the IT environments that they manage for end customers in their Oracle Cloud Infrastructure tenancy. This post is structured like a case study, in which an example enterprise customer, ACME CORP's Central IT team, faces the following business challenge: How do they enable their departmental IT stakeholders, and the operators within those departments, to have the aut...
Read more