Oracle Cloud Infrastructure (OCI) Ops Insights offers a robust platform for proactive, predictive, and insightful analytics, particularly for database and IT management. Oracle Application Express (APEX), a powerful low-code development framework that empowers developers of all skill levels to build scalable and secure enterprise applications rapidly and with ease. The seamless integration between APEX and Ops Insights can revolutionize how organizations visualize and interact with these insights. The blog explores how to leverage Oracle APEX to create custom dashboard applications that seamlessly fetch and display data from OCI Ops Insights, enabling businesses to make data-driven decisions efficiently and effectively.

Figure 1: OCI Ops Insights landing page
Figure 1: OCI Ops Insights landing page

Integration Reference Architecture

Figure 2: APEX integration with Ops Insights Reference Architecture
Figure 2: APEX integration with Ops Insights Reference Architecture

The main components of this architecture are: 

  • O&M – OCI Ops Insights
  • APEX Autonomous Database

Ops Insights and APEX integration deliver deep insights into multicloud database environments

Oracle Cloud Infrastructure (OCI) Ops Insights is a powerful service designed to deliver deep insights into the performance and health of databases and applications within a multicloud environment. It leverages advanced analytics and machine learning algorithms to provide predictive analytics across workloads. This includes detailed metrics on database load, capacity forecasting, and anomaly detection, which are critical for maintaining optimal performance and avoiding potential issues before they escalate.

On the other side, Oracle Application Express (APEX) is a low-code development platform that enables users to quickly build scalable and secure applications. It is particularly adept at creating web applications that can interact with varied data sources through RESTful Web Services. Oracle APEX includes features such as REST-enabled SQL, which allows it to directly query and manipulate data over REST from external sources, and Web Source Modules, which can consume REST APIs to fetch data from remote services like OCI Ops Insights. 

This integration enables the customers to utilize existing Ops Insights data in APEX to create a data-driven application for optimizing infrastructure operation: 

  • Developer driven highly customizable cloud infrastructure workload dashboard
  • Easy to share infrastructure capacity planning dashboard without OCI account
  • Reuse OCI cloud services data for cross pillar data Science and artificial intelligence projects to optimize infrastructure operations
  • Additional value generated for customers who are using Ops Insights

Overview configuration of Ops Insights integration with APEX

The following prerequisites need to be completed across OCI Ops Insights and APEX before the actual configuration work. 

Now, in terms of configuration, here are few steps that need to be completed on the OCI and APEX sides respectively.

Setup OCI connection using OCI API key

OCI Side: 

  • Create OCI API key for the service account
  • Open Profile menu and click My profile.
  • In the Resources section at the bottom left, click API Keys.
  • Click Add API Key at the top left of the API Keys list. The Add API Key dialog displays.
  • Click Download Private Key and save the key to your .oci directory. In most cases, you do not need to download the public key.
  • Click Add.
  • Update the permissions on your downloaded private key file so that only you can view it. Use the command chmod go-rwx ~/.oci/<oci_api_keyfile>.pem to set the permissions on the file.

APEX Side: 

  • In App Builder: Create Web Credentials in Workspace Utilities using the OCI API key created
Figure 3: APEX Configuration – web credentials
Figure 3: APEX Configuration – web credentials

For more details, check the documentation: How to generate an API Signing Key

Check the OCI REST API Reference and test the Ops Insights API endpoints

OCI Side: 

  • Test and verify the OCI Ops Insights REST API endpoint in curl or Postman

For more details, check the documentation: Oracle cloud Infrastructure REST APIs in Postman

Setup Oracle APEX REST Data Sources

Within Oracle APEX, developers can set up REST Data Source to point to the REST API endpoints provided by OCI Ops Insights. APEX handles the authentication, querying, and retrieval of data through these endpoints. This setup enables APEX applications to receive real-time data from Ops Insights.

APEX Side: 

  • In App Builder: Create new REST Data Source in Shared Components
    • On the workspace home page, click App Builder.
    • Select an application.
    • On the Application home page, click Shared Components in the center of the page.
    • Under Data Source, select REST Data Sources.
  • On the REST Data Sources page, click Create.
  • Select From Scratch and click Next.
  • REST Data Source Type – Oracle REST Data Services. Select Oracle Cloud Infrastructure (OCI) REST Service.  
  • Provide the name for the REST Data Source
  • Provide URL Endpoint of the Ops Insights REST API endpoint, please check the API Reference for complete list of API endpoints. For example, to collect SummarizedHostInsightResourceUsage data, you can reach out to API endpoint – https://operationsinsights.us-ashburn-1.oci.oraclecloud.com/20200630/hostInsights/resourceUsageSummary   
  • Choose the Authentication method by using the OCI API key credential
  • Use Advanced option to provide REST API query parameters for the Data Profile. Use Response Sample to upload a sample JSON response. Once a file is uploaded, APEX performs a Data Profile Discovery on the uploaded file. 
  • Choose HTTP Method based on the API reference documentation
  • Or Use Discover option to have APEX invokes the REST service URL and inspect the JSON response, APEX derives attributes and their data types and uses this to create a Data Profile. 
  • Click Create REST Data Source. 
Figure 4: APEX Configuration – REST Data Source
Figure 4: APEX Configuration – REST Data Source

For more details, check the documentation: Understanding REST Data Sources

Configure Oracle APEX Data Source Synchronization using OCI Ops Insights data

Using the data fetched from OCI Ops Insights, developers can utilize Oracle APEX’s REST Data Source Synchronization automatically sync the contents of a local table with the data from an external REST service. Basically, APEX invokes the REST Service defined in the REST Data Source, downloads all data and synchronizes to a local table.  APEX can create the local table based on the columns in the REST Data Source’s Data Profile. Since the table is physically available in the database schema, developers can add indexes, change physical properties, or even add more columns. APEX Components (reports, charts, etc) will be able to use the synchronized local table instead of invoking the the actual REST Service request.

APEX Side: 

  • In App Builder: Configure REST Data Source Synchronization to materialize the REST Data Source in APEX local table
    • On the Workspace home page, click App Builder.
    • Select an application.
    • On the Application home page, click Shared Components in the center of the page.
    • Under Data Sources, select REST Data Sources.
  • On the REST Data Sources page, click the REST Source Name.
  • Click Manage Synchronization on the right side of the REST Data Source page. The Data Synchronization page appears.
  • Under Details, provide the following details for the Data Synchronization: 
    • Local Table Owner – Select the schema of the local table to synchronize data to. By default, the application Parsing Schema is used.
    • Synchronize to – Select either New Table or Existing Table and click Apply Changes. If creating a new table, the message Table does not exist appears. Click Create Table.
    • Synchronization Type – Select a synchronization type REPLACE.
    • Job Execution Interval – Use the Database Scheduler Calendaring Syntax to define repeating synchronization schedules, such as “every Tuesday and Friday at 4:00 p.m.” or “the second Wednesday of every month.”. 
    • Under Steps – Use Steps to pass parameter values or specific external filters to the REST Service. If no step is defined, the REST Data Source will be invoked once, with default parameters. If steps are defined, APEX will invoke the REST Data Source once for each step, and pass the configured parameters.
    • Under Advanced Settings, select Replace Type as TRUNCATE(non-transactional)
  • Save your changes, click Save. 
  • Test the REST Synchronization, click Save and Run. 

For more details, check the documentation: Synchronize Data from REST Services to local Table

Develop Custom Dashboards in APEX

APEX Side: 

  • Use Sample Charts from the App Gallery to jump start your APEX application development
  • Customize the Sample Charts dashboard widgets to visualize Ops Insights REST API endpoint data. 

Visualizations Made Easy….

Once setup, the APEX framework provides a wide range of visualization widgets to display the Ops Insights cloud resources utilization, trend and forecast dataset. 

Figure.5 CPU and Memory Utilization in Meter Gauge, Pie and Donut chart
Figure.5 CPU and Memory Utilization in Meter Gauge, Pie and Donut chart
Figure.6 Host CPU Usage breakdown by host in BarChart
Figure.6 Host CPU Usage breakdown by host in BarChart
Figure.7 CPU Trend and Forecast in Line Chart
Figure.7 CPU Trend and Forecast in Line Chart

 

The integration of OCI Ops Insights with Oracle APEX represents a significant advancement in the way organizations can visualize and utilize data for making informed decisions. By harnessing the powerful analytics and predictive capabilities of OCI Ops Insights and combining them with the intuitive, low-code development environment of Oracle APEX, businesses are equipped to create highly customized, real-time dashboards. These dashboards not only enhance data accessibility but also empower decision-makers to proactively manage and optimize their database resources efficiently. As cloud technologies continue to evolve, leveraging such integrations will be crucial for staying competitive in a data-driven world. Organizations looking to maximize their operational efficiencies and strategic insights should consider this potent combination as a cornerstone of their IT strategy.

Reference

Contributors: Girish Kumar Balachandran, Waymon Whiting