Thinking in ChartSQL

Tips about creating visualizations with ChartSQL

Introduction

In ChartSQL, data visualization meets the familiarity and power of SQL. ChartSQL is designed for developers who are comfortable working in tables, relationships, and queries. If you have ever wanted to chart your database results directly without switching gears into a different programming language or visualization tool, that is why we made ChartSQL. In this article, we dive into how to think about visualizations with ChartSQL.

ChartSQL Thought Process

1. Start with Your Data Query

In traditional visualization workflows, you might extract data using SQL, export it to CSV, process it in a programming language like JavaScript, and then use a charting library or copy it into Excel to visualize. In ChartSQL, you begin with what you know best: writing a SQL query. The emphasis here is on how you would normally query your database to retrieve the desired dataset.

Example: To analyze sales data, you might start with a query like:

SELECT year(OrderDate), month(OrderDate), SUM(TotalSales) AS Sales
FROM Orders
GROUP BY year(OrderDate), month(OrderDate);

2. Picture the Story You Want to Tell

Imagine the visual outcome you are aiming for. What insights are you hoping to share? Are you looking at trends over time, relationships between variables, or the contribution of a variable to the whole? This foresight guides the use of ChartSQL Directives, helping you to frame your data story effectively.

Example: If you want to show sales trends over time, you might envision a line chart with OrderDate on as the category (the x-axis) and Sales as the series (the y-axis data).

3. Assign Columns to Series

ChartSQL is most similar to Excel Charting. You select a chart type, and assign columns as series.

ChartSQL provides ‘at directives’ that enable you to assign columns in your result set your charts. Directives like @category, @series and @formats

• Category: What is the major axis of your visualization? The Category forms the x-axis and will typically be dates, or strings like product categories.

• Series: What are the quantitative insights? Series data, usually numeric, give substance to your story, acting as the y-axis values or pie chart segments.

• Groups: Do you need a hierarchical view? Like subplots in a larger narrative, grouping enabled sub-categories in your chort.

Example: To create a line chart of sales over time, you would use:

-- @chart: line
-- @category: OrderDate
-- @series: Sales
SELECT OrderDate, SUM(TotalSales) AS Sales
FROM Orders
GROUP BY OrderDate;

4. ChartSQL Assist: Auto, Assistive, or Manual Mode

ChartSQL offers a spectrum of control, from fully automatic detection to precisely guided directives. This helps you start out exploring and then refine your visualization.

• Auto Mode: Let ChartSQL decide the chart type based on your source data. This is great for quick insights and iterations. A defined set of charts can be auto detected. See the Auto Chartsexamples.

• Assist Mode: Define some aspects, such as the chart type or a category, and let ChartSQL figure out the rest. This mode helps when you quickly customize visualizations.

• Manual Mode: Take full control by defining all aspects of the chart. Use this when your visualization requires exact precision.

Example: To automatically generate a chart without specifying the type, you could simply write:

-- @title: Auto Line - Auto Generated Line Chart
-- @subtitle: An example chart which is auto detected to be a line
-- @formats: currency
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC;

Example of an auto generated line chart

ChartSQL will auto-detect a date column and a numeric column as a line chart with Month as the category and Sales as the series.

5. Utilize Formatting Directives

Ensure your chart is self explanatory. Formatting directives help you present data in the most understandable way, be it currency, percentage, or another style.

Example: To format the Sales series as currency, you would add @formats

-- @title: Auto Line - Auto Generated Line Chart
-- @subtitle: An example chart which is auto detected to be a line
-- @formats: currency
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH')
ORDER BY TRUNC(date_closed, 'MONTH') ASC;

6. Refine and Iterate

Developing the right visualization may require custom directives. With ChartSQL, you can adjust directives and immediately see the impact. Experiment with different chart types, stacking options, and formats to discover the visual representation that best conveys your data story.

Example: To switch to stacking a column chart, you simply add the @stacks directive

-- @chart: column
-- @title: Stacking - Example column chart with stacking
-- @groups: Month, Channel
-- @subtitle: An example column chart that has a single stack
-- @formats: currency
-- @series: Sales
-- @stacks: Channel
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
    Channel,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH'), Channel
ORDER BY TRUNC(date_closed, 'MONTH') ASC;

An example column chart with stacking

7. Comment Out to Debug

ChartSQL allows you to temporarily disable directives without erasing them. This feature is handy for experimentation.

Example: To temporarily disable the bar chart directive:

-- @chart: column
-- @title: Stacking - Example column chart with stacking
-- @groups: Month, Channel
-- @subtitle: An example column chart that has a single stack
-- @formats: currency
-- @series: Sales
-- //@stacks: Channel
SELECT 
	TRUNC(date_closed, 'MONTH') as Month,
    Channel,
	sum(amount) as Sales
FROM sales
GROUP BY TRUNC(date_closed, 'MONTH'), Channel
ORDER BY TRUNC(date_closed, 'MONTH') ASC;

Commenting out the stacking disables it but keeps it in the source code

Tips for Thinking in ChartSQL

• Write your SQL naturally: Continue thinking in SQL as you naturally would. Construct your visualization with the same logic you'd use for a database query or to show a table. You shouldn’t need to contort your SQL to fit the visualization. ChartSQL is designed to work with the way you’d naturally query.

• SQL data types are important: ChartSQL detects your data types as strings, numbers or dates, and works appropriately with them. You can use CAST in your SQL to assign different data types if necessary to change the way ChartSQL is interpreting your columns.

• Visualize As You Query: As you define each column, think ahead to how it relates to your visual endgame. Is it a category, a series, or a sub-category?

• What you Query is What you Chart: We call it "Whikee-Whic" — Your knowledge of SQL functions, joins, and aliases becomes your data shaping toolkit. ChartSQL directives deal with layout, and some light formatting and grouping. If you want to significantly manipulate your data, you do it in SQL.

• Toggle and Tweak: Try different directive combinations and toggle them on and off to see their effect. It’s a hands-on approach to charting akin to playing with an interactive SQL result set.

• Opt for Clarity: Use formatting directives to ensure the visual narrative is as clear and meaningful as possible. Legibility is key.

Final Thoughts

ChartSQL puts your SQL queries at the forefront. By extending your SQL work with @directives, ChartSQL streamlines the data storytelling workflow. Remember to start with a strong query, envision the story you want your visualization to tell, and then customize.