We recently developed an Oracle analytics cloud (OAC) dashboard for Customer and Sales 360-degree analysis. The customer had big data for ten years and connected to almost ten source systems. You can then imagine the volume and diversity of data and the frequency of data loading & streaming. We decided to use the OCI MySQL heatwave after a Proof of concept to develop the database on a cloud for the OAC dashboard.

Oracle MySQL HeatWave delivers the critical advantages of query processing with a high real-time analytics performance having a single DB for both Online Transaction Processing (OLTP) & Online Analytical Processing (OLAP). This article contains a sample dataset loaded into MySQL Database Service (MDS) DB System and compares the execution time with and without HeatWave on Oracle Cloud Infrastructure (OCI). 

Oracle MySQL HeatWave delivers the critical advantages of query processing and high real-time analytics performance, having a single DB for both Online Transaction Processing (OLTP) & Online Analytical Processing (OLAP). In this article, a sample dataset loaded into MySQL Database Service (MDS) DB System compares the execution time with and without HeatWave on Oracle Cloud Infrastructure (OCI).

Challenges of Existing Data & Analytics Solutions

MySQL is optimized for OLTP, but it is not designed for analytic processing (OLAP). As a result, organizations that need to run analytics efficiently must move their data to another database. This approach of moving data to another database introduces complexity, such as:

  • Lengthy Extracts, Transforms, and Loads (ETL) process with only periodic updates of data rather than real-time data
  • Stale data makes it challenging to make critical decisions with the most current information
  • An additional cost of maintaining two separate databases

Benefits of using MySQL HeatWave Architecture

  • It enables customers to run OLTP and OLAP workloads in MySQL, within a single database platform, without needing ETL data in a separate database for analytic processing.
  • Real-time data is available for analytics.
  • Optimized for Oracle Cloud Infrastructure to provide the best price-performance database service
  • MySQL Autopilot provides Machine Learning (ML) automation that improves the performance, scalability, and ease of use of HeatWave. It automates the database lifecycle operations, including provisioning, data loading, query processing, and error handling.
  • HeatWave ML provides familiar MySQL interfaces enabling MySQL users to train models and generate inferences and explanations without needing extensive ML expertise.
  • We can create a dashboard simply by creating a connection in Oracle Analytics Cloud Instance to connect to MySQL Database Service rather than creating a dashboard separately for analytics.

Setup

Follow the Infrastructure Configuration to complete the required steps:

  • Create a Virtual Cloud Network and allow traffic through MySQL Database Service port
  • Create a Bastion Host compute instance
  • Connect to the Bastion Host, install MySQL Shell and download the sample Dataset
  • Create an Oracle Analytics Cloud instance
  • Create an instance of MySQL in the Cloud
  • Enable HeatWave Cluster to MySQL Database Service
  • Configure Private Access Channel – OAC

After completing these steps, import data into MDS and load tables to HeatWave, execute queries leveraging HeatWave, and compare the query execution time with and without HeatWave enabled. To connect to the bastion host, we will use the cloud shell, a small Linux terminal embedded in the OCI interface.

Use Case:

Visible improvement in performance by 10X using MySQL heatwave

The use case involves developing a customer 360 OAC dashboard with Oracle MySQL Heatwave. Oracle Integration Cloud (OIC) was used to get the data from the Oracle fusion and CRM systems to OCI.

After you have properly configured the infrastructure, let’s connect to the instance using the public IP address of the compute instance. Command:

ssh -i <private-key-file-name>.key opc@<compute_instance_public_ip>

Now you can connect to MySQL DB System with MySQL Shell with the following command.

Command:

mysqlsh –user=admin –host=<mysql_private_ip_address> –port=3306 –sql

Here we illustrate the sample data to analyze the count of customer orders to generate a report. In this use case, the Orders table stores information about each order, such as the customer who placed the order, the employee who handled the order, the order and shipment date, which shipper made the shipment, etc.

Query was executed to obtain the count of customer order information from a sample dataset “customer_data” without enabling heatwave; Query took an execution time of 11-12 sec approx.


The heatwave was enabled query was re-executed and better performance was noted in query execution time by 0.6324 sec.

The above execution demonstrates how simple it is to increase performance by utilizing Oracle MySQL Heatwave. Using HeatWave, it took about 1 second as opposed to the query’s processing time of 11 seconds, which is considerably longer than when a HeatWave cluster is enabled.

Conclusion:

Through this use case, I’ve illustrated how we can use HeatWave on the real-time dataset avoiding the process of ETL and improving the performance of an analytical query, which will result in the performance of the OAC dashboards. You should give it a try using your own compute instance and dataset.

Related Blogs:

What are the 5 steps to move to Oracle Cloud?

What is your Cloud Migration Strategy?

How Do You Migrate from Oracle Business Intelligence Cloud Service (BICS) to Oracle Analytics Cloud (OAC)

Cloud Migration : Why Are Enterprises Moving to the Cloud

Latest posts by Hiba Mol (see all)