The world’s leading publication for data science, AI, and ML professionals.

Hair-Splitting Quirks and Nuances of Tableau

Common issues and resolutions for effective reporting in Tableau

Image by Angga Afin87 on Vecteezy
Image by Angga Afin87 on Vecteezy

Overview

It’s a known fact that Tableau is a great visualization tool that helps derive actionable insights from your data. However, like the saying "There is no perfect spaghetti sauce…", Tableau has its quirks too. In most cases, the more you explore, the more the features and workarounds. Let’s see the answers to a few commonly faced issues in Tableau; and some quirks of Tableau.

Where is my data stored? Why aren’t my dashboards getting refreshed?

A common question that many folks getting started with Tableau Server might have is – Where is my data stored? why isn’t my dashboard getting refreshed? This happens especially while using an Extract connection. This is a basic overview of how Tableau stores and processes data files.

Fig. 1.0 Tableau Workbook (.twb-unpackaged and .twbx - packaged) using a Live Connection (Image by author)
Fig. 1.0 Tableau Workbook (.twb-unpackaged and .twbx – packaged) using a Live Connection (Image by author)

In Fig. 1.0, the dashboard uses 2 flat files which are generated by some jobs that run nightly. The files get refreshed and new data keeps getting added to the flat files. As the Tableau Desktop uses a live connection, the dashboard will get refreshed automatically as it references the actual flat files that are located in the share drive that get refreshed by the jobs.

Fig. 1.1 Tableau Unpackaged (.twb) Workbook using an Extract Connection (Image by author)
Fig. 1.1 Tableau Unpackaged (.twb) Workbook using an Extract Connection (Image by author)

In Fig 1.1, we look at a Tableau workbook that uses an extract. With a ‘.twb’ workbook, the extract files will get refreshed in the same location that was specified when the extract was created. In other words, it is the location that Tableau asks you to provide when you change the data connection from ‘Live’ to ‘Extract’. So, if you save it in the same Windows Share drive where the original files are located, the extracts are saved and refreshed right there.

Fig. 1.2 Tableau Packaged Workbook (.twbx) using an Extract Connection (Image by author)
Fig. 1.2 Tableau Packaged Workbook (.twbx) using an Extract Connection (Image by author)

In Fig 1.2, we look at a Tableau packaged workbook that uses an Extract connection. Jobs run nightly and refresh the files in the Windows Share Drive. The Tableau Workbook however uses an extracted copy of the original files in the Windows Share Drive. This extract is a snapshot of the original file and is stored in the Temp folder of one’s local machine in the ‘Documents’ folder. If this workbook is uploaded to Tableau Server, then the extract can be scheduled to refresh daily. This way, the data in the extract gets refreshed on a schedule and the Tableau dashboard automatically displays the latest data after the extract has been refreshed.

Fig. 1.3 Tableau Packaged Workbook (.twbx) on Tableau Server using an Extract Connection (Image by author)
Fig. 1.3 Tableau Packaged Workbook (.twbx) on Tableau Server using an Extract Connection (Image by author)

In Fig 1.3, the packaged workbook resides on Tableau Server. The Tableau Service Manager creates extracts in the server location in the ‘tabsvc/temp’ folder.

Tableau Extracts are a great boost to the performance of the workbooks, especially when there are a large number of rows being incrementally added to the data files during subsequent job runs (or) when multiple calculations are being done.

Troubleshooting Error Messages on Extract Refreshes

In some cases, cryptic error messages may come up on Tableau packaged workbooks. Imagine having a production-ready workbook that crashes all of a sudden with some error messages. Here is a trick to troubleshooting these –

  1. Unpackage the .twbx workbook (i.e. save the .twbx as a .twb workbook)
  2. Use a notepad editor such as Notepad++
  3. Open the .twb workbook using Notepad ++
  4. .twb workbooks are in an ‘XML’ format. You can troubleshoot the XML to see where the issues reside and fix them accordingly.

Adding Comments to Calculated Fields

It is often useful to add comment lines to calculated fields in Tableau to leave behind some documentation when you pass on your workbook to someone else. In Tableau’s calculated fields, single-line comments can be added using a / and end with a /. Multi-line comments can be added using a //. In earlier versions of Tableau (Tableau 2020), it would give an error when the workbook was uploaded to Tableau Server. But this has been fixed in newer versions of Tableau. Another funny quirk is that Single line comments should be in a new line and not in the same line as a calculated field in Tableau server 2020.2. This has been fixed too, in newer versions.

Discrete measures in a table and subtotals

Subtotals on discrete measures are not supported. This is a pretty bad limitation in Tableau, which hinders building Tabular reports. One might argue that Tableau is a visualization tool and should not be used for building Tabular reports. But a lot of financial stakeholders often want simple tabular reports to serve their basic needs.

Scaling in word clouds with real-world messy data (adjusting scaling etc.)

You might have seen online blog posts having beautiful word clouds built in Tableau. Oftentimes, these look good because the data is manipulated to render a nice output that is well-scaled. However, when word clouds are built with real-world data, the scaling isn’t great. The ‘size’ card helps, but only to a certain extent. When the individual word counts are pretty much the same; and when you want to apply weights to scale them, it gets pretty tricky.

When a chart is added to the tooltip, it does not gather all the data values being passed.

Yes, you heard it right. In case you didn’t know, you can add an entire chart to the tooltip. This will then show up when you hover over another chart. Imagine having a KPI tile and when you hover over it, it shows the trend of the KPI over time! This is a cool feature. However, if you have some filters being used in the KPI tile, all the filters may not get passed to the chart being displayed via the tooltip. Hence, while using this feature, ensure that you validate that all filters are being passed from the first chart to the second chart. In case, you didn’t know about this feature, here is an interesting read- https://help.tableau.com/current/pro/desktop/en-us/viz_in_tooltip.htm

Data Source Metadata

When you use a direct SQL query in Tableau as a data connection, the fields are pulled in the exact format. e.g. A column ‘AB_c’ is pulled in as ‘AB_c’. But when a .csv file is used as a data source, a column ‘AB_c’ would become ‘AB C’. Beware of this while replacing flat file connections to live database connections and vice versa. If you act upon it early enough, you can save a lot of time in replacing missing fields and tweaking them to get your visuals to work again.

Certain calculated functions may not be available with certain data connections

DATEPARSE is not available in live database connections but is available only in flat file connections. Hence, when you replace a data source from a flat file to a live database connection, you will need to replace all calculated fields that utilize a ‘DATEPARSE’ function. Similarly, it is important to note that REGEX functions (Regular expressions) are available with flat file connections but not with some database connections such as MySQL. As an alternative, Tableau provides excellent pass-through functions for database connections that enable users to develop calculated fields using SQL syntax, also called RAWSQL.

Time to refresh large data extracts

Tableau takes a long time to refresh extracts that are larger than 10 Gigs. Thankfully, Philip Lowe from the Information Lab shares this helpful workaround – https://www.theinformationlab.co.uk/2016/06/23/wont-tableau-refresh/

Availability of Button Filters

Button Filters in Tableau are not available natively. However, this can be availed using the Bizfactory extension. Check out https://getbootstrap.com/docs/4.0/components/buttons/. My personal favorite is btn btn-outline-info.

Non-availability of KPI Tiles

Tableau does not have readily usable KPI tiles unlike tools like PowerBI. However, they can be created from scratch using text tables and trends. Multiple worksheets can be brought together as floating views and superimposed on top of each other, simulating the look of a KPI tile. Containers can be used to hold these KPI tile constituents together when being published to Tableau Server.

Alerts in Tableau – A cool feature that can do better

Alerts are a great utility that can help with implementations such as control charts and anomaly detection. They enable stakeholders to get notified when a data point crosses a certain threshold. The quirk here is that every time an alert runs, the Tableau alerting mechanism looks for all of the past data and sends out alerts for every single data violation. Luckily, there are some workarounds that can help set the alert for the current date. Here are some interesting workarounds suggested by fellow developers –https://community.tableau.com/s/question/0D54T00000C6cbgSAB/trying-to-make-alerts-work-on-current-date-rather-than-the-entire-graph

If you are new to alerts in Tableau, check this out – https://help.tableau.com/current/pro/desktop/en-us/data_alerts.htm

Concluding Remarks

Yes, I agree with Howard Moskowitz – ‘There is no perfect spaghetti sauce. There are perfect spaghetti sauces". No tool is perfect but it will be great if one tool learns from the other. Tableau is not the perfect tool but it is a great tool that is constantly evolving and implementing new features and functionalities. There are quirks and nuances, but there are always one or more workarounds to achieving what you want. Planning ahead of time and designing dashboards that use the strategies mentioned above can help save time and produce efficient dashboards and accurate business reports.

Other Helpful References

Tableau Server "dataengineextract" Folder Consumes Disk Space | Tableau Software

Tableau hyper Extract in Temp Folder

Extract File Path Displays Temp Location in Tableau Data Extract Connection Dialog | Tableau Software

Temp Tables in Tableau Desktop and Tableau Server | Tableau Software

Symbols & Icon CreditsSome Icons made by Pixel perfect from www.flaticon.com


Related Articles