String calculations take up a lot of processing power. When you have lots of string calcs, it can slow down a dashboard.
In this article, we will show you how we reduced the time by 99%.
You can also look this video to see how we did it!
The Dataset had 28 million Rows and 112 million Calculations
It was a movie review dataset.
We extracted the year in which the move was released which was embedded in the movie title. E.g., ‘The Shawshank Redemption (1994)’, with even multiple parentheses in some cases.
We wrote 4 new calculated fields, using string functions, to extract the movie year. An example calculation is shown below:
So, it has 112 million calcs (4 row level string calcs * 28 million rows).
The Viz Loaded in 7.9 Minutes!
A simple viz that shows #movies and #reviews by movie year, took 7.9 mins to load!
We feel dashboards should be fast that they don’t delay the speed of our thought process. And our ability to derive insights from data.
So, we needed to do way better.
First Solution: We Moved the String Calcs to Data Source
To start with, we wanted to try taking the load off Tableau and do the calculations in the database itself.
So, we computed the ‘Movie Year’ field in MS SQL server and saved it to the data source.
Now, the same viz took 1.44 min to load in Tableau with a Live connection – that is 82% lesser time!
But we wanted to improve it further.
Second Solution: We ‘Materialized’ the String Calcs in Hyper Extract
As we did not need real-time data, we took the initial workbook (5.5 mins) and created an Extract for it. This materialized the calcs on it.
What does materializing a calc mean, in Tableau? It means two things:
- Pre-calculating the calculation results:
You’re shifting the processing in Tableau to a time when a user isn’t waiting.
2. Storing those results on the Extract:
Tableau can use those results to be faster, instead of computing every time a query is made with that respective calculation.
It can be done by right clicking on the datasource > Extract > Compute Calculations Now.
With that done, the viz just took 4.65 seconds to load – a 99% reduction in time!
4.65 sec still feels a bit slow, right?
Third Solution: Leverage the Power of Tableau Hyper Extracts
We wanted to see how much would be the viz load time, with an extract of the data source that already has ‘Movie Year’ field on it.
In this case, the viz took just 1.8 seconds to load – a 99.6% reduction in time!
This shows that Hyper Extract is better optimized to handle Tableau’s queries than other databases.
To Conclude
Tableau dashboards can perform better when you:
- Move your string calcs to data source if you need real-time data.
- Materialize your string calcs on Hyper Extract if you don’t need a live connection.
- Move your string calcs to data source and use Hyper Extract simultaneously.