In this Oracle Magazine article, you’re going to build a web application on top of US flight arrival data, and using the newly integrated charts from Oracle JET, you will easily create several data visualizations to better interpret the results.
This article’s sample application is built in Oracle Application Express 5.1. If you’re not already running Oracle Application Express 5.1 or later locally, you can request a free workspace at https://apex.oracle.com. Alternatively, you can download the Database App Development Virtual Machine from the Oracle Technology Network, which includes a preconfigured Oracle Database 12c Release 1 Enterprise Edition database, Oracle Application Express 5.1, Oracle REST Data Services, Oracle SQL Developer, and Oracle SQL Developer Data Modeler. You will also need to download and unzip the comma-separated value (CSV) file for this article to load the sample data.Creating the Application
Begin your Oracle Application Express 5.1 data visualization exploration by creating the initial application:
"for Optionally Enclosed By, and click Next.
AIRLINE_DELAYSfor Table Name, and click Next.
Airline Delays, choose Interactive Grid for Report Type, choose Single Page for Page Type, and click Create Application.
You’ve now created a table in Oracle Database; inserted your spreadsheet data as rows into your table; and created a fully functional, multiuser, editable grid on this table, accessible from a web browser.
Click the Run Application icon, and log in with your workspace credentials. Your application should look similar to Figure 1.
Figure 1: Application home page with interactive girdInteractive Grid Data Visualizations
The interactive grid, a new component in Oracle Application Express 5.1, enables you to easily manipulate data simply by clicking on a cell and editing its value. The interactive grid includes many features for powerful reporting, including fixed headers, frozen columns, scroll pagination, multiple filters, sorting, aggregates, computations, and more. A future Oracle Magazine article will discuss the interactive grid in greater depth. Right now, let’s move on to data visualizations!
The Oracle Application Express 5.1 charting engine for interactive grids has been directly integrated with Oracle JET charts, which results in greatly improved visualizations and also a wider array of built-in chart types (such as bubble, polar, radar, range, and scatter). Charts can be easily created in interactive grids and saved as custom views. End users can also create and save their own custom charts on the data.
Now add a pie chart to your Oracle Application Express 5.1 application:
Figure 2: Pie chart showing flight delays on a percentage basis
Your application should look similar to Figure 2. You have created a pie chart showing, on a percentage basis, the total number of flight delays 15 minutes or greater in the US for October 2016. Hover over the slices of the pie chart to get greater detail about this particular data point. Hover over each of the airline names in the legend, observing the highlighting of the respective slice. Click an airline name in the legend to remove that portion of data from the pie chart. These are all examples of the out-of-the box functionality present in Oracle JET charts.
You can save your chart as a custom view of the data in the interactive grid. Click the Actions button at the top of the report, click Report, and then click Save. At the top of the report, you’ll now notice an icon, enabling you to toggle between the grid view and the chart view.
The formatting of charts in interactive grids is concise and limited, intended to cover the majority of basic charting needs. Oracle Application Express 5.1 also supports chart regions, which offer a greater array of customization options and showcase the power of Oracle JET charts and the native declarative integration with Oracle Application Express.
Add a page to the application with a new chart region to show the aggregate counts of airline delays by carrier:
Delays by Carrierfor Page Name, and click the Next button.
select sum(carrier_ct) value, carrier_name, 'Carrier' as series from airline_delays group by carrier_name union all select sum(weather_ct) value, carrier_name, 'Weather' series from airline_delays group by carrier_name union all select sum(nas_ct) value, carrier_name, 'National Aviation System' as series from airline_delays group by carrier_name union all select sum(late_aircraft_ct) value, carrier_name, 'Late Aircraft' as series from airline_delays group by carrier_name order by carrier_name
The full power of Oracle SQL can be used in Oracle Application Express to create beautiful data visualizations.
You have now created a vertical bar chart, showing the sum of all delays for each airline and specific type of delay.
At this point, the chart probably looks jumbled and confusing to you, but with the adjustment of a few small attributes, you can easily improve it:
The chart is looking better, now that you have distinguished the separate series in the chart. The data labels in the chart, however, simply add clutter and should be removed. The information will be easier to consume if the chart orientation is changed too:
By adjusting a few attributes, and without writing any additional code, you’ve created a very attractive horizontal stacked bar chart, comparing different delays across different airlines. Your chart page should now look similar to Figure 3.
Figure 3: Stacked bar chart showing flight delays
Click one segment of the bar chart, and then use the arrow keys on your keyboard to navigate around the various segments of the chart. Resize your browser window until it approximates the dimensions of a smartphone. Another great benefit of Oracle Application Express applications and the new Oracle JET charts is that they are responsive out of the box. Your applications will display appropriately across desktop and mobile devices.Conclusion
The new charting engine in Oracle Application Express 5.1, based on Oracle JET, is a great advancement in the breadth and quality of data visualizations possible with Oracle Database and Oracle Database Cloud, and the low-code nature of Oracle Application Express provides a high level of developer productivity. As you’ve seen in this article, you can quickly and easily enhance a bar chart without writing a lot of code, and no separate deployment steps are required between development and execution.
Photography by Ricardo Gomez, Unsplash