Tableau is a great visualization tool but can be quite confusing for an excel user. Why? The reason is their fundamental difference in approaches while dealing with data tables. In this note, we will talk about these differences and in the process will talk about how we need to prepare data for the purpose of working on Tableau.
Tableau vs Excel
Tableau is a vectorized tool, compared to excel which is cell-based. This means when we write formulas in an excel spreadsheet, these are specific to the cell and can be dragged (using the fill handle) for spreading them out into other cells (columns or rows). Whereas in Tableau all formulas (calculated field) are written in a vectorized format, i.e they are applied uniformly for the entire column or rows as defined by the user. The other major difference is that in excel all forms of aggregations have to get fixed on the lowest level of granularity, which is nothing but the cell. Whereas in Tableau, this granularity can be flexibly altered by the user for the purpose of aggregation and processing. And not only that — the tool enables the user to run a second level of aggregation on top of the primary one, i.e at the window level — making Tableau not only quite different from Excel but also difficult for the purpose of implementing some simple tasks that we can do it very easily while using excel.
Simple vs Difficult
The flexibility that Tableau enables to process data makes some of the simple tasks very difficult and counterintuitive to a newly migrated excel user. Let’s say, we want to calculate incremental data from a series of cumulative data — excel can do it very easily. We can simply point our “difference formula” to the cell above and then drag it down (using the fill handle) to complete the column.
Tableau being a vectorized tool, has to do it differently. First, we have to lay out the data in the adjacent column phase shifted by one cell in parallel to the other, and then calculate the difference.
Note the scalar vs vector approaches between Excel and Tableau. Now, this vector approach of Tableau makes the tool quite powerful & flexible compared to excel. For example, in case you want the quarterly increments to reset every year, Excel will need you to add one more calculation (If, then & else) on top of this to check the start of Q1. But in Tableau this can be done without writing any more calculations using the Pane definition of the window. This is depicted in the following figure.
Preparing Data
The Need
Tableau’s vector-based approach to processing data makes the work of “data preparation” extremely important and mandatory for a Tableau user. This is quite unlike Excel where most calculations are done directly on spreadsheets on unprepared data — making it very difficult for the excel user to scale and maintain these sheets. To save time, we mostly use formulas that break within the same row/column, and that too sometimes with hard coded data — making the spreadsheet impossible to maintain and scale. Whereas Tableau forces the user to run calculations using a well-defined calculated field that runs uniformly across rows/columns. Also, there is no opportunity for “hard coding” and the tools enforce the discipline of using “Parameters” for the purpose of alternating user-driven inputs.
Sample DataSet
In Tableau, input data is structured as a data frame. This means that the dataset needs to be structured in columns and rows. Each of these columns can be set as either a “measure” or a “dimension”. “Measures” are simple values, and “Dimensions” are those which define these values, eg date, company, region, etc. Now, since the tool (Tableau) sees every row of the dataset as an individual vector, therefore it should be self-contained — with names of dimensions linking the data repeated in every row of the dataset. A sample-prepared dataset is depicted in the following figure.
How to Prepare
There are tools available that allow us to prepare raw data — making it palatable for the purpose of being used in Tableau. But most of these tools do not work in situations where the data is cluttered and needs customized processing. Hence, using python to process the raw data can be quite effective and powerful. Recently, I have written a couple of notes to describe this process — 1) Curating Tabular Data for Processing; 2) Curating RBI’s Data Tables for Visualization. You might what to go through them, which will give a fair idea of what is going on here.
Though these examples are not exhaustive, as it only layouts out the approach that one needs to adopt while working with Tableau. Please note, the intent here is not to become an expert on Python, but to focus on absorbing the skills which empower us in using the power of visualization so that the story behind the dataset can be unlocked for the purpose of making business decisions.
This is the first of the series on processing data using Tableau. The follow-up notes will discuss what other tools and processes we can use to make the data processing and its visualization more effective and optimal. Please note, I am not an expert on this subject. The idea is to learn and improve as we all together go through this process. Thanks for reading.
(I am aggregating all the articles on this topic here, for easy discovery and reference.)
