Using Tableau on a regular basis by dragging and dropping pills all around, we easily forget what the machine does for us behind the scene. The structure is easy to understand though, and knowing about it could be very helpful. Actually it can allow you to optimize your calculations, your device memory and simplify the work of Tableau.
.
Tableau Software is a great tool accessible to anyone to build complex dashboards by simply dragging and dropping pills on shelves. But after playing around with the tool, it is time to understand how to optimize the performance of the software. In fact, when building on a 50 lines Excel document, it does not really matter. But once you start using online databases with millions of entries, you want to know your business and save some precious time. Lesson learned the hard way!
.
I have re-built a drawing made by Carl during the morning Tableau Intermediate class.
.
Tableau Software – Behind the scene
.
The language behind the scenes
The first thing to understand is that Tableau ‘speaks’ VizQL. VizQL stands for Visual Query Language and is literally translating drag-and-drop actions into data queries. But to make the link with another database, it needs a ‘translator’ or a driver. The driver is a piece of software that accesses a database. It allows an application – in this example Tableau – to compile or to access a particular database format. You will thus need to download the right driver for the right type of database format. Some are pre-installed some need to be installed, you can find them all here.
In the example below, the database is serving SQL queries. This is an arbitrary choice and a database could be responding to other data query languages (DQLs). There are multiple of them, but you can find a non-exhaustive list here.
.
.
Tableau is a read-only tool
So we have a translator software. And now what?
When we connect to a database, Tableau makes queries that the database can serve. But Tableau can also request calculations to be made a the database level, and those are Calculated Fields and Level Of Detail (LOD) Calculations. What is interesting here is that we make the most out of the database. In fact, a database is defined as a “collection of information that is organized so that it can be easily accessed, managed and updated.[…] Data gets updated, expanded and deleted as new information is added.” (TechTarget).
Tableau is a Read-Only tool therefore it won’t compromise the database, but it still can optimize that massive and effective computer to create new computed calculations.
.
.
What the heck is a cache?
Once Tableau has made a query and the database has served it, it is stored on your device, in the cache. The ‘cache’ is an auxiliary memory that allows you to retrieve information very quickly. It is used to store data temporarily. It is mostly used to improve the performance of recently accessed or frequently accessed data. (TechTarget).
What is interesting beyond the speed and the effectiveness, are the Table calculations. This is the last level at which calculations are allowed, after the right view has been set up, with the accurate filters for example. This is also the moment the user decides how Tableau must compute the calculations either across columns or down rows.
.
Optimizing tools
I guess it is becoming clear now that throughout the process, the tools available to serve the queries and make calculations are not the same! To optimize them, it is good to know about what Tableau can ask and at what level. Therefore, I would probably not make table calculations if my data set is big, because I know it will request a lot of my device resources. It will be more interesting to make the most of my calculations as calculation field and/or LOD calculations level. On the contrary, I will favor table calculations if I have a small data set that is static or stored in my cache, in order to be faster and more granular.
Hence the importance of knowing what is behind the scene !