Thursday, January 19, 2023

Using Oracle APEX with MySQL Heatwave Service for Data Visualisation and Reporting

 

Using ORACLE APEX to Build and Visualize and Analyse MySQL Database

 Main objective of this blog is to connect MDS with APEX tool and visualize and get insights of MySQL data with low code solution called Oracle APEX. 

Below is the glimpse of topic to be covered in this blog 

1. What is all about Oracle Apex 

2. What are benefit of using Oracle Apex 

3. Installation and Configure Oracle APEX with MDS 

4. Conclusion 


Let’s get started… 


What is all about Oracle Apex ? 


It is database centric web application development framework which you can use to Develop desktop & mobile web apps ,visualize and maintain data, leverage SQL skills and database capabilities. 
Basically,Oracle APEX (APEX) is a low-code development platform that enables you to build scalable, secure enterprise applications with world-class features that can be deployed anywhere. 
Oracle APEX is also certified with MySQL database service(MDS) which help MySQL customer to build modern, beautiful, responsive applications without the need to become specialised experts. 
That’s a another benefit MDS customer get on Oracle Cloud Infrastructure(OCI) to leverages home grown services. 



 In this blog I am referring to fully managed APEX offering which runs on OCI 

Configuration, patching, monitoring, and upgrading of all Oracle APEX components is fully managed by Oracle, leaving you free to focus on developing your solutions and solving your business problems. 

With Oracle APEX and low code, your organization can be more agile and develop solutions faster, for less cost, and with greater consistency. 

You can adapt to changing requirements with ease. And you can empower professional developers and everyone else in your organisation to be a part of the solution. 

More info :- 

https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/apex-intro.html#GUID-F275EA9F-F9A4-4A72-B777-5548362FDDA5


High Level Architecture for MDS+ APEX 


APEX access data from MDS via OCI database tools service , actually APEX do require “REST enabled SQL end point” to get data from MDS , that is only possible at this current release by using “middle man” , OCI Database Tools Service. 

Once data get accessed from DB service then you’re free to customize ,developed interactive user friendly dashboard to visualize and analyze data. 

What are benefit of using Oracle Apex ? 

Oracle APEX is to empower developers to easily build compelling apps with superior functionality, performance, and end user experience. In order to deliver on this mission, Oracle APEX aims to eliminate the enormous complexities of developing and deploying enterprise apps. 
Here are a few reasons why choosing Oracle APEX makes sense for your next development project. 
- Eliminating Complexity 
- Simple Development 
- Secure 
- Powerful and Proven , Portable 


More info:- https://apex.oracle.com/en/platform/why-oracle-apex/ 


Installation and Configure Oracle APEX with MDS 


Prerequisite 

To connect APEX with MDS you must have to first configure 
1. Access to Virtual Cloud Network(VCN) 
2. MySQL Database Service(MDS) instance up & running 
3. database tools service which will provide Rest enabled SQL end points which is required during configuration of APEX service. 


Setup OCI network 

Please review below blog 

https://docs.oracle.com/en-us/iaas/mysql-database/doc/creating-virtual-cloud-network.html#GUID-A5D4A49C-53AD-4A6B-978A-5A7435E73762 

Setup MySQL Database Service(MDS) 

Please follow below tutorial 

https://docs.oracle.com/en-us/iaas/mysql-database/doc/creating-db-system1.html#GUID-F2CB52E0-6C6A-407F-A8AC-D82371186D2D 

Setup Database Tools connection with MDS 

Below is the step to follow from OCI console page:- 
1. Create Vault >> Identity & SecurityVault 
2. Create Master Encryption Keys inside Vault 
3. Create Private End Point 
4. Create Connection 
To get started click as below 



Here is the final snap of the connection details 


Make a note of OCID ,we do require during APEX configuration . 


Let’s Getting Started with APEX 



Login into APEX console 

Click on “Launch APEX” 


Click on workspace utilities from App Builder >> Web Credentials. 




Web Credentials 
Store authentication credentials for external REST services or REST Enabled SQL services. 
The Client Secret will be stored encrypted, and it can only be used by Oracle APEX and cannot be retrieved in clear text. 
Also, credential information will not be included in application export files. After importing an application into the target workspace, prompts will be displayed to re-enter the credentials. 


To get the details of OCI User ID ,OCI Private Key , Tenancy ID , Public Key Fingerprint 

Click on user profile , you see right top >user settings>API KEY




Create a “Rest Enabled SQL Services” in APEX App Builder 





Example of an endpoint URL: 
https://sql.dbtools.<OCI Region >.oci.oraclecloud.com/20201005/ords/<OCID of of Database Tools Connection Details > 
you may get OCID for database tools connection details from navigating to from Database Tools>>Connections>>Connection Details>>OCID 




Create an APEX application with MDS 





Conclusion 

Using Managed APEX Service together with MDS, you can quickly build nice-looking web applications through RESTful API’s. 

APEX easily able to connect with MDS and build next gen dashboard and analyze the data from the MySQL table .