How to create a SQL dashboard that pulls in data from your database

So you’ve built a great database. Or you have access to one.

You know the database is full of valuable data, but accessing and manipulating that data to extract useful information is complex and time consuming.

At best (if you know the database well and are confident writing SQL queries) you can produce a backward-looking SQL report that can be shared with colleagues via email, or at your next meeting.

But that will only help the business identify past problems and opportunities.

The data that you need to overcome current challenges (and to take advantage of the opportunities that exist now) will probably be written to your database as you deliver your report.

In other words: It will be old by the time you start pulling together your next report!

That’s why you need a SQL database dashboard.

Preferably one that serves up data from your SQL server in real-time, giving you and your team ready access to the information you need to continually improve business performance—rather than course correcting periodically.

This article will help you create such a dashboard.

What is a SQL dashboard?

A SQL dashboard is an assembly of metrics, indicators and visualizations that pull in data from a SQL database, which is a relational database that can be managed with SQL (Structured Query Language).

How to create a SQL dashboard

Options

There are a variety of ways to create dashboards by pulling in data from a SQL database. In all scenarios you’ll need to:

• Assemble SQL queries to pull in the raw data;
• Manipulate and aggregate the data to build metrics and KPIs;
• Visualize and assemble the metrics and KPIs to create a dashboard.

There are a variety of ways to accomplish these three tasks, and a variety of services and platforms that can help you along the way.

If you’d rather not write SQL, you can use a free tool like Metabase, which allows you to point and click your way to pulling in slices of data via handy drop downs (the SQL query gets written behind the scenes).

The MySQL add-in for Excel helps you in a similar way by enabling you to select and dump rows and columns of data into Excel without writing SQL.

Alternatively, if you’re handy with SQL and your data is in a MySQL database, you can use a free tool like phpMyAdmin to write the query and then select the option to export the data you pulled as a csv or xml file.

BI tools like Tableau and Klipfolio come with their own tools and wiziwigs to pull and view data from a SQL database.

These same tools can help you aggregate and “mashup” SQL database data with other data sources to build metrics and KPIs, and to visualize and assemble the metrics and KPIs to create dashboards. (Learn more: What is a key performance indicator (KPI)?)

So you’ve got lots of options to consider.

Questions to ask before building a SQL dashboard

In weighing your options to build your SQL database dashboard, consider resources (time and financial), the type of dashboard you’re trying to create, and how you intend to use it.

Here are a few questions worth answering before getting started:

Will the dashboard be shared with people outside of your organization?

If so you may want to consider a cloud-based solution that enables you to publish public (indexed by search engines like Google) and/or private (not indexed) links. If you’d like to open these dashboards up to the public, look for applications that support embedding data visualizations or entire dashboards on web pages.

Do you want the dashboard displayed on large-screen TVs and office walls?

Again, cloud-based solutions tend to excel in this area. Services like AirTame, RiseVision, True Digital and Screen Cloud can help you get your dashboard up on office walls, if your dashboard has a URL. If you’re working in a desktop application, you won't have this capacity unless you publish it to the web—make sure you can.

How “real-time” do you need the dashboard to be?

Lots of dashboard providers claim to be “real-time,” but most aren’t in the true sense of the word (updating when your data updates).

And most of them that are tend to be quite costly. Do you need your dashboard to be updating minute-by-minute? Every time you sign-on? Every few hours? Different dashboard applications will have different limitations surrounding data refreshing.

What’s your budget for getting this up and running?

Dashboard applications that enable you to plug-into SQL databases can cost you hundreds to tens of thousands of dollars a year.

Those that are more costly tend to be more enterprise focused, and usually come with a bevy of more advanced functions and analytics associated with traditional Business Intelligence.

The less expensive options tend to focus more on plugging into the data and visualizing it, with less power/emphasis on data discovery and analysis.

Will this be used for SQL reporting?

In addition to monitoring the data on a dashboard, you may want to send static reports or screenshots of the dashboard in certain instances (I’m thinking client reporting, or to share KPIs and data visualizations with someone who’s not going to be looking at the dashboard regularly).

If you want the ability to do this, make sure you can download dashboards and data visualizations as images or PDFs, and that the look and feel of the data visualizations are maintained when you do.

How big is your data set and how much data do you need to visualize?

Some dashboard providers store the data you pull in, and some don’t. Some have limitations on how much data you can bring in at a time. Look into these limitations and consider them against your needs.

Will you be combining SQL database data with other data sources (data mashups)?

If so, consider the number of data source integrations the dashboard provider offers, the “openness” and flexibility of the app infrastructure (if a web service is RESTful and follows best practices you should be able to connect to it), and the opportunities and limitations surrounding mashing up data sets.

Does the dashboard provider offer some sort of editor or WYSIWYG to manipulate and mashup data sets? Does this process require coding or IT support or can you handle this yourself?

Is Klipfolio the right solution?

It’s a tool that will take you a bit of time to master, but if what’s described below is what you’re looking for, you’ll be glad you invested the time.

• Pull in moderate amounts of data from your database (2,000 entries or less);
• Build custom data visualizations and dashboards to spec;
• Have data visualizations and your dashboards update in near-real time (refreshing up to every 60 seconds);
• Share and view dashboards on mobile devices and large screen TVs;
• Not spend hundreds of dollars a month.

If Klipfolio feels like a tool that could be right for you, here's an introduction and a few tutorials to help get you building SQL dashboards:

Building SQL database dashboards with Klipfolio

Klipfolio is a cloud application for building real-time business dashboards, helping you connect to and visualize data from virtually any data source, including databases.

With Klipfolio, you can connect to a wide variety of database management systems including:

• MS SQL (Microsoft SQL Server)
• MySQL
• Oracle Thin
• Oracle OCI
• Sybase SQL Anywhere
• PostgreSQL
• Firebird
• DB2

You can also connect to cloud data warehouses like Amazon Redshift and Google BigQuery, and to APIs like Segment.

In this tutorial I’ll walk you through the steps to:

1. Connect a MySQL database to Klipfolio
2. Write a SQL query to create and save a SQL data source in Klipfolio
3. Build custom data visualizations with a SQL data source

I’ll also introduce you to Metabase, one of my favourite tools for building SQL queries, without having to write them manually.

Connecting database data to Klipfolio with SQL

The process of building custom data visualizations and dashboards in Klipfolio starts by connecting your various data sources to Klipfolio, and you do that in the data library.

Here’s a video tutorial on how to create a SQL database data source to Klipfolio (if you’d rather read about it, the step by steps are below the video):

Please note: In this video tutorial, we’re using a host that is no longer accessbile so if you use the sample credentials you won’t be able to connect. Feel free to connect to your own SQL database. We are working on building a live sample database that we can use in this tutorial. Please reach out to us if you get stuck anywhere.

From the dashboard page click Library, then select the Data Sources tab, and click Create a New Data Source.

To connect to data that lives in a database, select the SQL Database Query from the list of core connectors on the right hand side.

Fill in the fields provided, write your query, then click execute and save. You’ll have connected a new database data source to your Klipfolio library.

Here’s a sample SQL query I used to create a new database data source, referencing our sample MySQL database (you can play with this sample data as well, using the database information listed up top):

SELECT order_date, company, first_name, last_name, product_code, product_name, unit_price, quantity

FROM customers

INNER JOIN orders on customers.id=customer_id

INNER JOIN order_details on orders.id=order_details.id

INNER JOIN products on order_details.product_id=products.id

ORDER BY order_date

By the way if you’re new to SQL, there are tons of great sites and tutorials out there to learn how to write SQL. Here's a great one at W3.

And if you don’t care to learn SQL, that’s not a problem either. I’ve fallen in love with Metabase, an open source tool that helps you build SQL queries without having to write SQL. Here’s a quick tutorial on how to use Metabase to build a SQL query that you can use to access your data in Klipfolio:

Now that you’ve connected your database data source to Klipfolio, you can reference it to build data visualizations (we call them “Klips”), and every time your database data source updates, so will your Klip.

Building a data visualization in Klipfolio with a database data source

Once you’ve connected your database data source to Klipfolio, you can create data visualizations and dashboards based on that data source.

Here are two tutorials that will walk you through the process of building a multi-component Klip (if you’d rather read about it, the instructions are down below):

From the dashboard page click Add a Klip, then Build a Custom Klip.

Then select the visualization component you’d like to start building with, and on the next screen where you’re asked what data source you’d like to start building with, select Use an existing data source from the library.

Then find and select your database data source from the drop down list.

That’ll take you to the Klip Editor where you can reference your database data source to build out a custom data visualization.

A final word

We know firsthand the challenges that can arise at various parts of this process. I’ve watched people at all levels successfully build out SQL dashboards, and I’m convinced that you can as well.

Especially because our customer support team is here to help you at every step of the way.

I’m also happy to field any questions you may have. Just drop them in the comment section below. Best of luck!

Also See:

Building a dashboard with Excel data in Klipfolio