In today’s interconnected business landscape, organizations often rely on multiple cloud platforms and SaaS applications to meet their diverse needs. This blog demonstrates how to build a powerful multicloud employee management solution that seamlessly integrates:

  • Oracle NetSuite (SaaS application on OCI) for employee data
  • Oracle Exadata Database Service on Exascale Infrastructure on Oracle Database @ Azure for data storage
  • Azure OpenAI for embeddings and generative AI capabilities

Oracle Exadata Database Service on Exascale Infrastructure brings Oracle’s powerful database capabilities to Azure with shared infrastructure that significantly reduces minimum costs while maintaining all of Exadata’s unique features. This makes it ideal for developers who need high-performance database capabilities with cloud-native integration.

Architecture Overview

Our solution follows this data flow:

  1. Extract employee data from NetSuite via REST APIs
  2. Store structured data in Oracle Database 23ai running on Azure
  3. Generate vector embeddings for each employee record using Azure OpenAI
  4. Enable similarity search and AI-powered text generation for queries

Prerequisites

Before we begin, ensure you have:

  • Oracle Exadata Database Service on Exascale instance on Azure
  • Azure OpenAI instance with deployed models
  • NetSuite account with REST web services enabled
  • Windows VM in the same Virtual Network as Exadata

Step 1: Setting Up Oracle Exadata on Exascale Infrastructure

Oracle Exadata on Exascale provides shared infrastructure that delivers:

  • Up to 95% lower minimum infrastructure costs compared to dedicated infrastructure
  • All Exadata’s unique capabilities including Smart Scan and storage optimization
  • Rapid cloning features for development and testing environments
  • Elastic scaling with pay-as-you-go pricing

Create your Exadata instance through the Azure portal, ensuring it’s deployed in your target virtual network for secure connectivity.

Step 2: Configuring Azure OpenAI Services

  1. Create an Azure OpenAI resource
  2. Navigate to Azure AI Foundry and deploy the following models:
    • text-embedding-3-large for generating vector embeddings
    • gpt-35-turbo for generative AI capabilities
  3. Copy your endpoint URL and API key for later use

Step 3: Setting Up the Development Environment

Create a Windows VM in the same Virtual Network as your Exadata instance and install:

  • Python 3.8 or higher
  • Visual Studio Code with Python extensions
  • Required Python packages

Install Required Python Packages

pip install oracledb
    pip install openai
    pip install requests
    pip install requests_oauthlib
    pip install array

Step 4: Connecting to Oracle Database

Establish a connection to your Oracle Database 23ai instance:

import oracledb
    
    # Oracle DB connection parameters
    username = "sys"
    password = "your_password"
    hostname = "your-scan-hostname.oracle.com"
    port = 1521
    service_name = "your_service_name"
    
    # Create DSN and connect
    dsn = f"{hostname}:{port}/{service_name}"
    connection = oracledb.connect(
        user=username,
        password=password,
        dsn=dsn,
        mode=oracledb.SYSDBA
    )
    
    print("Connected to Oracle Database")

Step 5: Setting Up Azure OpenAI Client

Configure your Azure OpenAI client:

import os 
    from openai import AzureOpenAI 
    
    # Initialize Azure OpenAI client
    client = AzureOpenAI(
        api_key=os.getenv("AZURE_OPENAI_API_KEY"),
        api_version="2024-06-01",
        azure_endpoint=os.getenv("AZURE_OPENAI_ENDPOINT")
    )

Step 6: Creating the Employee Table

Create a table to store employee data with vector embeddings:

CREATE TABLE EMPLOYEES (
        PersonId NUMBER PRIMARY KEY,
        FirstName VARCHAR2(100),
        LastName VARCHAR2(100),
        HireDate DATE,
        WorkEmail VARCHAR2(200),
        Title VARCHAR2(200),
        ExpenseLimit NUMBER,
        EMBEDDINGS VECTOR(3072, FLOAT32)  -- Vector column for embeddings
    );

Step 7: Extracting Data from NetSuite with OAuth Authentication

Configure NetSuite REST API access with proper OAuth 1.0 authentication:


    #
    #Code truncated for brevity
    #
    def get_netsuite_employees():
        # Set OAuth 1.0 credentials 
        
        # API endpoint and query
        method = 'POST'
        url = f"https://{account}.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql"
        body = {
            "q": "SELECT id, hiredate, firstname, lastname, title, email, entityid, expenselimit FROM Employee"
        }
        
        # Generate OAuth parameters
            
        # Create signature base string
            
        # Create signing key and signature
            
        # Build authorization header
        
        }
        
        try:
            print("Making request to NetSuite...")
            response = requests.post(url, json=body, headers=headers)
            
            print(f"Status Code: {response.status_code}")
            
            if response.status_code == 200:
                data = response.json()
                print(f"Successfully retrieved {data.get('count', 0)} employee records")
                return data
            else:
                print(f"Error: {response.status_code}")
                print(f"Response: {response.text}")
                return None
                
        except requests.exceptions.RequestException as e:
            print(f"Network Error: {e}")
            return None

Step 8: Processing and Storing Employee Data

Extract data from the JSON response and store it in the database with robust error handling:

# 
    #Code truncated for brevity 
    #
    def process_and_store_employees(json_data):
        """Process NetSuite employee data and store in Oracle Database"""
        if not json_data or "items" not in json_data:
            print("No employee data to process")
            return
        
        cursor = connection.cursor()
        count = json_data.get("count", 0)
        processed_count = 0
        error_count = 0
        
        print(f"Processing {count} employee records...")
        
        for i in range(count):
            try:
                # Initialize variables with defaults
                            
                # Extract employee data with individual error handling            
                try:
                    FirstName = json_data["items"][i].get("firstname", "")
                except (KeyError, TypeError):
                    FirstName = ""
    
                #            
                # Extract remaining employee data fields from the JSON 
                #            
                
                # Insert employee data into database
                sql = """
                    INSERT INTO EMPLOYEES (
                        PersonId, FirstName, LastName, HireDate, 
                        WorkEmail, Title, ExpenseLimit
                    ) VALUES (:1, :2, :3, :4, :5, :6, :7)
                """
                
                values = (PersonId, FirstName, LastName, HireDate, 
                         WorkEmail, Title, ExpenseLimit)
                cursor.execute(sql, values)
                
                # Generate and store embeddings for this employee
                generate_and_store_embeddings(cursor, PersonId, FirstName, 
                                            LastName, HireDate, WorkEmail, 
                                            Title, ExpenseLimit)
                
                 
            except Exception as e:
                error_count += 1
                print(f"Error processing employee record {i}: {e}")
                continue
        
        # Commit all changes
        connection.commit()
        print(f"Successfully processed {processed_count} employees, {error_count} errors")
    
    # Main execution
    if __name__ == "__main__":
        # Fetch data from NetSuite
        employee_data = get_netsuite_employees()
        
        if employee_data:
            # Process and store the data
            process_and_store_employees(employee_data)
        else:
            print("Failed to retrieve employee data from NetSuite")

Step 9: Generating Vector Embeddings

Create vector embeddings for each employee record using Azure OpenAI:

import array 
    def generate_and_store_embeddings(cursor, PersonId, FirstName, LastName, 
                                    HireDate, WorkEmail, Title, ExpenseLimit):
        # Create text representation for embedding
        embed_text = (f"PersonId: {PersonId}, FirstName: {FirstName}, "
                     f"LastName: {LastName}, HireDate: {HireDate}, "
                     f"WorkEmail: {WorkEmail}, Title: {Title}, "
                     f"ExpenseLimit: {ExpenseLimit}")
        
        try:
            # Generate embedding using Azure OpenAI
            response = client.embeddings.create(
                input=embed_text,
                model="text-embedding-3-large"
            )
            
            # Convert to array format for Oracle
            embedding_vector = array.array("f", response.data[0].embedding)
            
            # Update the employee record with the embedding
            sql = """
                UPDATE EMPLOYEES 
                SET EMBEDDINGS = :embedding
                WHERE PersonId = :person_id
            """
            
            cursor.execute(sql, {"embedding": embedding_vector, "person_id": PersonId})
            
        except Exception as e:
            print(f"Error generating embedding for PersonId {PersonId}: {e}")

Step 10: Verifying the Implementation

Use SQL Developer or similar tools to verify your data:

-- Check employee data
    SELECT PersonId, FirstName, LastName, Title FROM EMPLOYEES;
    
    -- Verify embeddings are populated
    SELECT PersonId, FirstName, LastName, 
           CASE WHEN EMBEDDINGS IS NOT NULL THEN 'Has Embedding' ELSE 'No Embedding' END as EmbeddingStatus
    FROM EMPLOYEES;

Benefits of This Architecture

For Developers:

  • Simplified Infrastructure: Exascale helps eliminate the need to manage dedicated database servers
  • Cost Efficiency: Pay only for resources used with up to 95% lower minimum costs
  • AI Integration: Seamless integration with Azure OpenAI services
  • Rapid Development: Quick cloning features for dev/test environments

For Organizations:

  • Multicloud Flexibility: Leverage best-of-breed services across cloud platforms
  • Scalability: Elastic infrastructure that can grow with your needs
  • Performance: All Exadata capabilities including Smart Scan and optimization
  • Security: Enterprise-grade security with Azure networking features

Next Steps

In our next blog post, we’ll build upon this foundation to:

  1. Implement similarity search using vector embeddings
  2. Create a user-friendly frontend interface
  3. Add AI-powered query capabilities for generating emails and reports
  4. Demonstrate advanced vector search patterns in Oracle Database 23ai

Conclusion

This multicloud solution demonstrates the power of combining Oracle’s database expertise with Azure’s AI capabilities. Oracle Exadata on Exascale provides the perfect foundation for developers who need high-performance database capabilities without the complexity of managing dedicated infrastructure. 

For organizations seeking even greater automation, this solution can also be built using Oracle Autonomous Database at Azure, combining self-managing database technology with Azure’s AI capabilities.

The integration of vector embeddings opens up powerful possibilities for AI-driven applications, making employee data more searchable and enabling intelligent automation for HR processes.

By leveraging this architecture, organizations can build sophisticated, AI-powered applications that span multiple cloud platforms while maintaining cost efficiency and performance.

Resources: