SQL and Tableau
Have you heard about SQL?
If you have ever questioned Google about the most popular programming languages around, you will have definitely seen SQL among the list of answers. More precisely, the structured query language, or SQL, is a language that has been specifically designed for the domain of relational database management systems. This means it is a tool for handling large amounts of data stored in databases. Furthermore, its efficient coding structure has become a ‘role model’ to many other programming languages. Therefore, I can assert that there isn’t a programmer around the globe that hasn’t at least heard of SQL. Let alone that there are so many people either using this language or basing their work involving data manipulation on SQL…
So… Have you heard about Tableau, too?
If you are interested or working within the sphere of business intelligence and data science, I can bet that you have. Or, if you haven’t, let me say in my defense, that you should have!
Tableau is an absolutely wonderful, highly intuitive software that is easy-to-understand even for beginners. Its goal is to help users project their data by offering a huge variety of data visualization tools to choose from. Moreover, this can happen very quickly – just by dragging and dropping the relevant objects you see on the interface.
So, to sum up, we can say that on one hand we have the very well established structured query language, SQL, and on the other – we have Tableau, which is a drag-and-drop software tool allowing you to create awesome visualizations.
A quick side note: If you're curious to learn more about Tableau, check out our tutorials Connecting Data Sources to Tableau, How to Navigate through the Tableau Interface, and Tableau vs Excel - When to Use Tableau and when to Use Excel.
Hence, we could pose ourselves the following question:
How do we connect SQL and Tableau?
To answer this, let’s first analyse the bigger picture from a technological perspective to understand why we would want to do that.
Today, the world of programming is represented by various technologies allowing us to approach and solve diverse kinds of problems. And that is a huge advantage.
More precisely, programming allows us to connect and exchange information between servers, software applications and frameworks operating in different domains.
During the last few decades, the list of programming languages and software applications has grown so much that it seems to be endless. Nevertheless, when we look closer, we can see that each of these technologies boils down to the following structure: they involve the administration and manipulation of a given amount of data to produce a specific type of output that will help businesses improve decision making.
So, when we look at the bigger picture, we can see that today there’s an abundance of software, each relevant for its domain. And from a technological perspective, this is amazing. But what does the picture look like from a business point of view?
Well, imagine you want to solve the following business task:
“Considering the following dataset, create a bar chart showing the breakdown of male and female workers in a company.”
If you have your data in Excel, that seems to be an easy one, right? You can select the columns containing the data of interest, and then insert a chart or graph to your liking.
However, this will not always happen in real life.
Companies often use one software tool for database administration, another tool for computations, a third one for visualizations and so on. The reasons for using a variety of tools can be numerous:
- to optimize the capacity of the chosen software
- practical reasons
- cost reduction
- data security
- historical reasons, or, quite simply,
- because a tool which deals with all domains together does not exist!
And here comes the good news that will unsnarl all complications that may arise because of the last point we made.
All these software applications and languages do not live alone, they are not detached from the entire IT ecosystem.
They can be connected, and they can communicate. That is, they can exchange information between one another.
And if the thought that comes to mind right now is that you would probably need to implement some sort of integration, then you are spot on. In programming terms, you can integrate two or more programming languages and tools, with the idea of extracting a particular type of output.
So, let’s narrow down our theoretical discussion to the integration of SQL and Tableau.
An analyst may desperately need to integrate SQL and Tableau because these two tools can serve a common goal. And this goal is namely answering such business questions as the one mentioned before.
To begin, you can store an enormous amount of data in a database. Then, you can manipulate it with SQL. So, using this language, you will create and maintain the foundations of your analysis.
However, looking at hundreds and hundreds of thousands of rows of numbers and text is typically not so helpful when you are trying to interpret the data.
This is where Tableau can help.
Tableau’s main functions include quickly connecting to a server such as the SQL server, extracting the necessary data, applying relevant calculations, and then visualizing the obtained information.
Speaking in a more technical language, we can say this software will allow you to create graphs, charts, reports, and dashboards – operations that are a must for any business intelligence analyst out there. In fact, it is namely the reports and the dashboards that allow end users such as company executives and general managers to understand the core of a business and extract insights about it.
Hence, to sum up this subsection, integrating SQL and Tableau is about taking your data from the depths of your database, to its esteemed, beautiful representation in Tableau.
Cool! Enough about theory. Let’s get to the highpoint of the article – namely, exploring the most notable ways in which people can connect SQL and Tableau.
Union of data
Yes, it is true that in today’s versions of Tableau you can create all sorts of joins – inner, left, right, and outer joins, as well as a UNION of two data tables. However, you could also use Tableau’s Custom SQL option, which is something like a gateway to the data in your server, to access your database through Tableau, and extract the exact query output you’ll need for your analysis.
Stored procedures
SQL stored procedures allow you to apply complex table calculations that can achieve a much better performance if run on the SQL server rather than on the Tableau in-memory engine. In fact, the latter is also suitable for similar types of calculations when only using a smaller dataset. Therefore, knowledge of stored procedures is another valuable tool when working with Tableau.
Re-organizing your data in a proper format for analysis
Sometimes you won’t be able to store your data in a clean format, so you’ll need to pre-process it before analysis. Tableau is not the best place to complete the pre-processing step.
Pre-processing on a database level, instead, is associated with much better performance. While doing this, once more, SQL turns out to be the boss. Therefore, working on database level by using SQL or Custom SQL, a person can, for example, convert data from columns to rows, change the format of date values, thus preparing the data for analysis in Tableau.
So, we can infer that, in general, knowledge in SQL can help you a great deal while working with Tableau when preparing your data for analysis.
Also, Tableau is a very powerful tool when you need to make calculations that you will use to create beautiful and meaningful visualizations.
Now, we must say that all three options we just mentioned can be applied through the Custom SQL option available in Tableau Desktop only – the paid version of Tableau.
However, if we have to suggest an accessible software tool, we must mention Tableau Public - the free version of Tableau.
Tableau Desktop vs Tableau Public
How could using the free Tableau version harm your performance at work?
Tableau Public is characterized by the lack of some interconnectivity options that are only available in Tableau Desktop.
For example, the Tableau’s Custom SQL option is available in Tableau Desktop only. Moreover, there would be only a limited application of your knowledge about SQL’s stored procedure when working with Tableau Public.
In addition, it is worth mentioning that if you are about to connect Tableau with other languages like Python, for instance, it will only be Tableau Desktop that allows you to do that. Which, for some business organizations, can definitely tip the scales towards the paid version.
In spite of it all, remember that irrespective to the limitations you may experience while using the free version, the logic behind connecting your work in SQL with the visualization of the obtained data in Tableau is the same.
Therefore,
How can one overcome the limitations of using Tableau Public?
Here’s an idea.
You could execute one additional step: storing your SQL output in a CSV file, that can be immediately loaded in Tableau.
In other words, solving a business task using SQL and Tableau Public may follow this structure:
- Receive a business task
- Use SQL to execute a query retrieving a relevant dataset from the database
- Export the newly obtained data in a CSV file to be used in Tableau
- Create a professional and understandable visualization in Tableau that will clearly respond to the initial business task and will help end users come up with business insights.
We would like to conclude by looking beyond SQL and Tableau. In other words, what can we say about connecting other software tools?
Obviously, SQL and Tableau are not the only two software tools out there that can be combined in order to solve a business task.
In contrast to SQL, there are programming languages like R and Python, whose strong points are the execution of complex mathematical computations and their application in the sphere of business, statistics, and finance. C++, instead, is ideal for creating various sets of software tools, ranging from operating systems to graphic design applications.
So, one could come upon an integration of R and C++, SQL and Python, or even SQL, Tableau, and Python!
Therefore, the beauty of the programming world today is that the programming languages and the software applications available are intertwined, so they can be integrated, too.
And we can promise that we will be releasing more articles that will tackle such notable integrations. All aiming to solve challenging business tasks.
***
Eager to hone your SQL skills? Learn how to put theory into practice with our hands-on tutorials!
