TeamSQL offers a new and redesigned feature to create your own charts based on query resulting data. The Charts feature of TeamSQL is now more powerful with advanced aggregation and visualization options. TeamSQL Charts is all free during Release Candidate period. Visualize your data by creating the charts you need, right in TeamSQL.
You can easily create bar, area, line, pie, scatter, funnel, and bullet charts, as well as heat maps, using the data you return from your database. You don’t need to export your data to a separate data visualization tool.
A hardware shop table to populate our charts
In the rest of the article, all the query examples and charts will be based on a simple hardware shop table called SALES. The SALES table has one record per sale done in a hardware shop. If we execute in TeamSQL a “select * from SALES” SQL query we can see the following image:
The examples in this article are all executed against a PostgreSQL database, however as TeamSQL is database agnostic, you can create charts based on data stored in many database managers like MySQL, Microsoft SQL, Redshift or Redis among others.
Feeding a chart with a SELECT SQL query
In TeamSQL we can create a chart directly with the query output data. Then, the first building block for a chart is a query. Once we executed a query in the SQL panel, and having selected the “Results” window, we can press the orange button “Charts” at the right of the “Execute” button and the following window will open:
The black boxes on the right side of the window are buttons to select the type of chart. TeamSQL supports several chart types like: bar, line, pie, scatter or area charts among others, We will go back later to chart types, now we will choose the default chart type, which is “bar”, and we will continue working with other parameters of the chart in the same window. We will scroll down and we see this part of the window:
The bar chart to be created will be based on two set of values. Every set of values must be associated with a column in the SELECT query we used as the origin of the chart.
The first set of values is called the “Measure” and should be a numeric value, because we need a measurable value. We need to press “+” sign and select which column of the query will act as a measure in the bar chart we are creating. We will talk later in detail about the measure set of values, but now let’s go to the next parameter in the chart.
The second set of values is called the “Dimension” and can be any data type, because this value will be shown as a title or legend in the chart. Again we need to press “+” sign and select which column of the query will act as a dimension.
Let’s see how to configure the measure and dimension for our bar chart.
In the previous image we selected the column quantity_sold as the measure we want to base our chart on. Is important to note, we also selected in the aggregation parameter, the value “Total sum of”. Finally we press “apply” button at the button and the following window is displayed:
Then we go to dimension parameter, press “+” and select them as we can see in the following image:
as we see in the previous image we selected “date of sale” column as the dimension column and then we press “Apply” button. Finally we can see the following window, where the chart is almost defined.
The last thing to do is to press “Apply” (this time at chart level), and see how our chart is built. The resulting chart is in the following image:
In the previous image we can see our first chart!. The dimensions are all the days when we sold something. And the measure is the sum of units sold in each day. A proof of that is sep 23, when the chart shows 8 units sold, and we have two sales: one sale is for 2 hammers and 6 paint units.
The TeamSQL Chart feature is really powerful and flexible and allow us to can make many changes to the chart. If we try to cover all the options, this article will become in a bored Reference Manual, however in order to show how flexible TeamSQL is, let’s change something.
Suppose we want now “one bar per month”, instead of “one bar per day”. Let’s change the dimension. We first need to remove the current dimension by pressing “-” at the right of current dimension.
Then we will add a new dimension using the same column “quantity_sold” but in the parameter “time bucket” we will choose a different value. We will select “month of date_of_sale” instead of “day of date_of_sale”. Then we press “Apply” twice and we can see the new chart, now oriented to month instead to day. Powerfull !
Chart types: too many options to choose one
Let’s talk now about chart types. TeamSQL Chart offers many different chart types. Depending of the chart type, we will need different set of values of feed the chart. For example heat map needs three set of values, while many others types (like bar in our previous example) needs two set of values. On other hand some types of charts are better to show different concepts. For example if we want to show how the market is shared for any specific product, the best type of chart is “pie”, while to showing trends or sales increases over the time, a line chart can be better.
Let’s create a line chart with a new query, we can see in the following image our new query on the chart creation window:
We added as a new column in the query the “sale_amount” value, using the expression in the select: quantity_sold * unit_price. Let’s try to create a line chart based on the amount sold every day. If we choose “Total sum of sale_amount” for measure and “Day of date_of_sale” for dimension we will obtain the following chart:
Other interesting feature of the chart, is that we can move the mouse over an interesting graph area, to know values behind the graph. Let’s suppose we want to know the reason behind the sales increase on march 21, we can put the mouse over the graph in march 21 and we will see the following image:
Let’s now suppose we want to know about the distribution of revenue based on articles. As we mentioned, for this kind of analysis, a pie chart is the best option, so choose pie chart, then select “Total sum of sale_amount” as measure and “product_id” as dimension and finally we can see the following chart:
By creating several charts using a simple base table, we showed how flexible and powerful is the TeamSQL Charts feature. We explained here: line, pie and bar charts, however there are more chart types to be covered. One of them is the interesting heat map, which we plan to cover in a next article. Let’s continue learning how to extract the best juice from TeamSQL, it is worth!