Defining Measures & Dimensions in Visual SQL
You already know by now that what is needed to make the charts you are looking for is tables, and you know that tables are made up of columns and rows. What you might not yet know is how to get data from your database into the columns and rows that are going to make your table and in turn make your chart. First you have to understand what pieces of Visual SQL make up the columns and rows of the table you are creating. For that, we must talk about Measures & Dimensions.
As defined by Chartio:
Measures - Typically refer to quantitative data, such as number of units sold, number of unique visits, profit and so on. In the context of data visualization, measures map to the Y axis of a chart.
Dimensions - Refer to categorical data, such as state, gender, product name or units of time (e.g., day, week, month). Generally, dimensions are used to group quantitative data into useful categories (e.g., number of units sold by state) and typically map to the X axis.
In a previous tutorial we found the MRR for the company, this shows each individual row’s return for the MRR column. If we want to aggregate this column to find out the total sum of the MRR for our organization, we can move the MRR column from the Dimensions Box into the Measures box and assign it an aggregation type from the pre-built aggregation type library in Chartio.
At this point you know the total MRR for your whole company, but it doesn’t tell the whole story. For that we want to add some dimensionality. We will drag another column from our Accounts Table into the “Dimensions” box.
We will want to know how we want to split the MRR up, or what dimensions to give it. Often the desire is to see how sales is doing over time. So let’s grab a timestamp column from the accounts table. Timestamp columns are indicated in the schema by a little clock symbol on the left hand side.
For this we will drag the “Created Date” column (which denotes when a new customer account was created) to the Dimensions box and click “Run Query”. This will give us a new column and aggregate the Total Sum of MRR on the time period in the Created Date column. You can see the aggregation defaulted to by day, but by licking into that column in the Dimensions box you can change that column’s aggregation to a number of preset choices. Our example works best with month.