This is the second video in the series – Demystifying table calculations in Tableau.
Here, I introduce you to the concept of the “3 Paradoxical Problems in the Reporting World” and then we will look at the first paradoxical problem in detail.
Here is the gist of the the video:
Some operations are very easy for us as human beings to do, but they are hard for databases to do (and hence for the reporting/analytics tools). It turns out that the most-often needed calculations in business like Ranking, Top 10, etc. suffer from the first paradox – If you want to know what this paradox is about, please watch the video or read on.
Normally, I would like to help you save time by summarizing the key take away first so that you can decide whether to invest your time in watching the video or not. But in this case, if I summarize it, I am afraid that it would take away the impact of the message. So, please spare 4 minutes of your time to watch the video.
Here is a text-version of the video for those of you who prefer to read than to watch – please note that this is NOT a transcript of the video.
Before we get into the 3 paradoxical problems in the reporting world, let us define the word “paradox”. Here is the dictionary definition of paradox:
a person or thing that combines contradictory features or qualities.
In essence, paradox is about some contradiction. So, what is the contradiction we are talking about in these 3 problems in the reporting world? These problems are easy and intuitive for us as human beings, but hard and un-natural for the databases.
You might wonder – how is it possible that databases are easily able to crunch millions or even billions of rows in a fraction of seconds, but will find it hard to do these simple problems that we find to be easy and intuitive as human beings? Yes, that is the paradox.
Let me illustrate the problem with a very simple report. Let us use the yearly sales report given below – nothing fancy here. I want to add another column and display the difference in sales from previous year.
Please take a moment to quickly do the calculation either in your head or on paper – it is important that you do it so that you can experience the essence of the paradox, rather than just reading about it. Here is the answer:
Was it hard for you to do it? Most likely, you are going to say that it was super easy. If it is so easy for you, why is it hard for the databases?
To understand this, let us reflect consciously on the operations we are doing here. Let us take year 2012 – we take the current sales value of 15,000 and then we go to the previous record and pick up its value of 10,000 and then do the simple mathematical operation called subtraction. Math operations have been around for quite a while. So, it can’t be difficult on databases. Then? It turns out that…
Accessing a previous or next record is very easy for a human being, but for the databases, it is NOT easy.
Then, you might ask “What are databases good at?”. From a reporting perspective, databases are extremely good at 3 things:
- Aggregation or Summarization – they can take a million records and produce a single value like Total Sales, Average Sales, Min Sales, Max Sales, etc.
But databases were not built for accessing the previous or next record easily. To be fair, databases were not really built for that (I could get into the technicalities here, but I am skipping them…). But we need this functionality all the time. And the still-reigning Hero of the reporting world, Excel, makes it super-easy to access the previous or next record (I acknowledge the limitations of Excel, but Excel indeed makes life easy for many average users).
So, that is the paradox – this is an operation we do it so intuitively and hence we use it all the time, but databases were not really built for that. Hence, the reporting tool has to compensate somehow to make this functionality available.
Let us give this first paradox a name so that we can easily remember its essence: What name would you give? Please take a moment to come up with a good name.
Let us summarize what we are doing here:
We take the current value of Sales measure and compare it to the previous value in the series. In other words, we are doing a ”me vs. my peer” comparison. “Peer comparison” is a good name and it is simple and formal.
With Tableau, we are in the world of Visualization, that means images which evoke emotions. So, here is my attempt to come with another name that would bring up images and/or emotions in us: “Peer pressure” problem. Didn’t we suffer from it during our teen years? Ahh, in fact, aren’t we suffering from it even now, no matter how old and how wise we get? 🙂
So, let us summarize the first paradoxical problem and be done with it:
- Peer comparison is a very instinctive trait in us. We do it all the time.
- Look at the kinds of questions we ask in our workplaces: Ranking, Top 10 customers, Top 10 products, Year over Year growth %, etc.
- Even at home, when our children bring their report card, we look at their rank, their relative performance in their classroom, etc.
- The essence of the first paradox is accessing a previous record and doing peer comparison.
With that said, let me stop here. For the 2nd paradoxical problem, let us take the first paradox and put it on steroids 🙂