SQL Data Visualization: The Complete Guide

How to Create SQL Dashboards: SQL Data Visualization in Five

Data visualization through charts, dashboards, or data grids is a critical task of enterprise IT, business, and data analyst teams. As most enterprise data is stored in relational databases, let’s explore how to create SQL dashboards and data grids using Five.

Charts and dashboards are often the first steps businesses take toward more advanced business intelligence (BI) or data sciences.

Data grids are an Excel-like view of the data contained in a SQL database. They give end-users an easy and intuitive way to work on their data in a tabular format.

Don’t Feel Like Reading? Watch the Video Instead.

This blog post explains how to visualize data stored in a MySQL database by building a web application with forms, charts, dashboards, and data grids using Five’s low-code IDE.

Build a Custom Dashboard on a SQL Database
Get Started for Free Today

What is Five?

Five helps software developers rapidly build and deploy online database applications on a MySQL database.

Typical applications built with Five are operations software or business applications designed to store, update, process, analyze, or visualize data stored in a MySQL database.

Applications built with Five are responsive and can be accessed from any device. Through Five’s in-built authentication features or Single-Sign-On (SSO), developers can govern application access and permissions, making sure that data is only exposed or accessible to selected user groups.

Using Five For SQL Visualizations & Data Processing

Five’s low-code IDE lets anyone familiar with SQL create insightful and information-rich decision support systems and SQL reporting tools on a MySQL database.

Five offers several ways to store, process and visualize data:

1. Create and Manage a MySQL Database.
   Inside Five, developers can visually create a cloud-hosted MySQL database, and its tables, fields, and relationships. Instead of having to connect to an external MySQL database, every application developed with Five comes with an integrated MySQL database that developers can create and manage using Five’s table wizard or database modeler.
   
2. Build or Write SQL Queries using Five’s Query Editor.
   Inside Five, developers can query the data stored in their MySQL database. Five lets developers build and write queries using Five’s visual SQL editor. Data from queries can be served to the front end in real time through Five’s use of Webhooks.
   
3. Create Charts.
   Inside Five, developers can select from a library of out-of-the-box charts for MySQL data visualization. Five’s chart library includes the most common chart types, such as bar, line, or radar charts.
   
4. Combine Charts and Reports into Dashboards.
   Developers can define a UI grid to place charts and reports to build custom dashboards.
   
5. Filter and Query Data as Data Consumers.
   End-users who consume the data through the finished application can search and filter their data. Five provides pre-built features for end-users to do so. Five also lets end-users export data to CSV.

How is Five Different From Pure-Breed Data Visualization Tools?

Typical data visualization or business intelligence tools, such as Microsoft PowerBI or Tableau only consume data from data sources. They are solely used to create dashboards or read-only data visualizations. These tools are not designed to store, process, or update data, for example. Nor can they incorporate business logic, such as functions, processes or notifications.

Five, on the other hand, is a low-code development environment for creating feature-rich web applications. Applications built with Five come with a MySQL Database, and can contain forms, dashboards, in-app notifications, or multiple user roles and permissions. SQL data visualizations are but one of Five’s application development features.

In an application built with Five, end-users can be assigned Create, Read, Update, and Delete (CRUD) permissions.

To use a simple example: say, you want to build a lead-generation web application with the following functionality:

1. Consumers can sign up for your services using a form that is accessible on a public URL.
2. Your sales team gets notified by email every time someone signs up and can process consumers’ information inside the same application.
3. Management has a dashboard that tracks daily sign-ups in real time.

This entire application can be developed with Five. Other systems that can be built with Five include inventory management systems, product management systems, or real estate applications, for example.

How is Five Different From React Data Grid?

The React data grid is a popular choice by developers to give users an editable view of their data.

Five’s data grid comes with the same controls that you would expect from the React Data Grid, such as

1. Custom sorting
2. Editing
3. Create – Read – Update – Delete (CRUD) data from inside the grid
4. Pinning a column to the left or right
5. Hiding columns
6. Using different display types natively inside the data grid, such as date or time pickers
7. Adding functions and events into the grid, such as a calculation that is automatically performed when a user enters a cell of the grid.

The difference between Five and React’s data grid is that the data grid is just one of Five’s development features – on top of all its other features, such as

• a cloud-hosted MySQL database,
• development, testing, and production environments,
• a JavaScript & TypeScript code editor, as well as many others.

Using Five to Analyze and Visualize MySQL Data

Let’s explore Five’s data visualization and reporting features to analyze data and create SQL business intelligence. We will focus on four of Five’s features:

1. Forms, drill-downs, and Filters
2. Reports & Charts
3. Dashboards
4. Data Grids

We are using Five’s Portfolio application to demonstrate these features. To follow along:

1. Download Five for free from our website, and
2. Import the Portfolio application.

To understand the Portfolio application, let’s first analyze its Entity Relationship Diagram (ERD).

We will primarily work with two tables in this diagram: the Portfolio and the Buy table. For now, simply note that a portfolio can have several buy transactions. Also note, that the application’s database was created using Five’s table wizard and visual database modeler.

1. Forms and Filters

As just mentioned, note how the Portfolio table has a one-to-many relationship to the Buy table in our ERD. Let’s explore this relationship in our end-user application.

Forms, drill-downs, and filters are Five’s most basic building blocks for your end-users to interact with your database.

To explore Five’s user interface and how it displays forms, drill-downs, and filters, follow these steps:

1. Launch the Portfolio Application.
2. Click on Portfolios > Growth Portfolio > Down Arrow. You have just drilled down into your Growth Portfolio. Note how in the top navigation bar, your end-users can now see the drill-down: Home > Portfolios (Growth Portfolio) > Buys.
3. Click on Buys to see the buy transactions that are related to your portfolio. Click on any of the buy transactions, let’s say BHP. The form on the right now displays all information that pertains to this buy transaction.
4. To filter your data, click on the Filter icon inside the search bar. Five lets you filter your data based on multiple dimensions.
5. Of course, you can also perform simple text searches using the Search Bar.

To see these features in action, check out this video:

2. Reports & Charts

Now let’s look at reports and charts. Again, we will be looking at the portfolio application.

1. Launch the Portfolio Application,
2. Click on Daily Volume in the menu on the left. The Daily Volume shows a line chart at the top and a report at the bottom.
3. Note how by using the Filter icon, your end-users can filter the report at the bottom.

Five lets developers write SQL queries during the application development to build reports and charts in your end-users’ application. Follow a step-by-step guide to build the line chart and report by following our Quick Start Guide.

Showing Data In Real-Time

Reports and charts can show data in real-time by using Five’s live query feature. Say, your line chart above is supposed to automatically update as daily volume evolves over time. This can be accomplished by using a live query.

Check out this YouTube video to better understand how live queries work and how they are different from normal queries here:

3. SQL Dashboards

When we looked at the report and chart just now, we saw both were displayed on the same page. This is what Five calls a dashboard: a single page that combines different pieces of data.

The Daily Volume dashboard has one column and two rows. Inside of Five, we can define a custom grid for our dashboard and position charts or reports as per end-users’ requirements in this grid.

Here is another example of a dashboard built with Five. Here our developers built a dashboard with two columns and two rows. We used a radar and a bar chart in the upper row. In the lower row, we only placed one report. And, of course, we created a cool dark theme for this particular dashboard.

To learn how to build dashboards with Five, follow our Quick Start Guide here:9 – Design a Dashboard | Five | Low-Code For Real Developers

4. Data Grids

The data grid is one of Five’s most powerful data visualization tools. A data grid shows all data contained in a database table. It gives end-users an Excel-like view of their data inside an editable grid.

The data grid is a frequently-asked-for feature request by application end users. Popular design libraries such as React and Material-UI offer Data Grids as a UI component.

In Five, developers can create a data grid as a page inside a form. No custom code is required to add data grids to your forms.

To understand how a data grid looks to the application’s end-users, watch the video below. In it, we have developed a simple two-table application that shows members by location. For each location, we can view all its members in a data grid.

Data grids are typically used to perform quick sanity tests on data. Instead of having to click through forms, the data stored in your SQL database is shown straight on the end-user’s front end, the same way they would see it if it were stored in a spreadsheet.

To learn how to build a data grid, visit our user community and follow the steps.

Conclusion: SQL Data Visualization

This blog post gives a quick introduction to some of Five’s MySQL data visualization and business intelligence features. Five can be used as a powerful SQL reporting tool and comes with many useful SQL data visualization features. Moreover, Five also lets developers build and use powerful SQL data validation techniques to ensure data is stored in a clean and consistent manner.

Starting from simple forms that can be filtered or searched by application users, we explored

• charts,
• reports
• dashboards, and
• Five’s editable data grid.

Everything shown in this article is fully developed in Five, using Five’s pre-built features as well as a few lines of standard SQL.

Beyond application development, Five also offers one-click deployment features. Developers can build and test their applications locally by using Five’s free download. To launch applications to Five’s managed cloud, sign up for a paid subscription on our website.