Charting Your Data with TeamSQL

TeamSQL’s charts feature lets you visualize your database queries in just a few clicks. You can select from a variety of different chart types, perform complex data aggregation and sort, and customize the appearance of your charts right from TeamSQL. Charts work with any database supported by TeamSQL and can even be exported as images.

In this post, we’ll demonstrate TeamSQL Charts using this sample MySQL database, which contains employee and salary records for a fictional company. The database includes a table for employees (employees) as well as their departments (departments), salaries (salaries), titles (titles), and department managers (dept_manager).

 

Charting the Data

We’ll demonstrate the chart feature by creating two charts: one that shows a range of employee salaries based on the employee’s title, and one that shows how employee salaries have changed over time.

 

Chart 1: Employee Salaries by Title

The salaries table contains a list of salaries for each employee. Likewise, the titles table lists out each employee’s title. We’ll join these two tables to match each salary with a title based on the employee’s unique ID.

 

After the results appear, click the “Charts” button (next to the “Execute” button) to open the TeamSQL Charts window.

 

In the center of the window is the query we just ran along with a preview of the result set. To the left is a list of chart types we can choose from. Below the result set is a set of controls for configuring the chart’s data sources, filters, and settings.

In this example, we’ll create a bar chart. Click on the bar chart icon on the left-hand side of the page, then scroll down to the section called “Aggregations”. This is where we will set the chart’s data sources, otherwise referred to as its measures and dimensions.

Measures are numeric values used to populate the y-axis. Each action consists of a:

  • Source: the database field used to fill the measure’s values
  • Label: the name to display in the legend
  • Aggregation: the calculation performed on the values such as the minimum, maximum, average, or standard deviation
  • Sort: arranges the values in ascending or descending order

We’ll add two measures: one for the lowest salary per title, and one for the highest salary per title. We’ll sort our titles by the highest salary in ascending order. Even though we’re only sorting by our highest salary measure, the chart automatically reorders the entire chart to fit the sort.

Click the “+” button to open the Select Measure dialog and configure each measure as shown below.

 

 

Next, we’ll set a dimension. Dimensions are used to populate the x-axis. Unlike measures, which are strictly numeric, dimensions can be anything from numerical values to text. In this case, we’ll use the title column as our dimension. What this means is that for each title, we’ll display the corresponding values from our two measures.

Click “+” to open the Select Dimension dialog and configure the title dimension as shown below.

Finally, we’ll create the chart by clicking Apply. TeamSQL automatically creates the chart with two bars per title: one for the lowest salary, and another for the highest.

To make this chart more readable, we’ll move the lowest salary bar next to the highest salary bar. Click “Chart options” to open the options dialog. This is where you can customize the appearance of the chart, such as the title and subtitle, font size, orientation, axis labels, and legend values. In the General tab is a series of checkboxes that toggle various features of the chart. Uncheck “Stacked” to display the two measures as two separate bars. Click “Apply” to save the changes.

Now we have a much more unobstructed view of our employees’ salaries. With “Show Tooltips” enabled, you can hover the mouse over any one of the bars to see the actual values. You can also display the values within the chart itself by clicking the “Series” tab and checking “Value labels”.

 

 

 

 

Chart 2: Employee Salaries Over Time

The last chart only shows us salary ranges as of today’s date. Suppose we want to see how employee pay has changed over time. To do this, we’ll create a new chart that plots employee salaries for each year since the start of the organization.

We’ll start by modifying our existing query to include dates. Each record in the salaries table contains the date range that the salary was valid from (the from_date and to_date fields). We’ll use the to_date field in our query since that indicates the last day that the salary was valid.

 

 

Next, we’ll create the chart. This chart is identical to the previous chart except for two fundamental differences:

  1. We’ll replace the Title dimension with a date dimension using the to_datefield.
  2. We’ll sort the chart by date instead of by the highest salary.

Add the two salary measures like before, but instead of sorting by the highest salaries in ascending order, keep both measures unsorted. Next, add a new dimension and select “to_date” as the source. Since “to_date” is a date field, TeamSQL provides a field called Time Bucket, which allows you to aggregate the field by the period. For example, you can group dates by day, week, month, year, or by the time of day. We’ll arrange by year and sort in ascending order.

 

 

 

Before we create the chart, there’s one minor issue with the data set that we need to address. Salaries that are still current contain “1/1/9999” in the to_date field. This causes the chart to display “9999” as one of the years. To prevent this, you can either modify your SQL query to replace instances of “1/1/9999” with today’s date or create a chart filter that only accepts dates earlier than 1/1/9999.

Click “Apply” to create the chart.

 

Conclusion

The charts feature is a powerful tool for visualizing your SQL data. You saw how easy it is to create and customize charts with just a few clicks. You can access a variety of different chart types (including pie charts, scatter charts, funnel charts, bullet graphs, and heat maps), change the appearance of your charts, and export your charts as images.

The charts feature is free to use during the Release Candidate period, and you can download TeamSQL from here.