I was browsing the Tableau forums recently and stumbled across this mammoth post which began out with a complex request of how to count distinct users over a running time period and generated into a lot of detail about the internal workings of Tableau. It’s one of the most useful threads I’ve read, hence I felt I needed to copy the information about the order of events/operations within Tableau and put it here.
Massive thanks to 2 world renowned Tableau experts, Joe Mako and Jonathan Drummey, who put this together.
- Tableau generates the SQL query/ies (typically one per datasource) and packs as much of the calculation, filtering, & computation as it can into them.
- Inside Tableau, filters on aggregate calcs are applied.
- Densification happens. Densification depends on pill type, pill arrangement, whether domain padding is being requested via Show Missing Values, compute using settings of table calcs, mark types, data structure/density, etc.
- Data blending occurs.
- Compute blended aggregates – These are aggregate calcs in one data source that use fields from the other data source.
- Filters on blended aggregates are applied.
- Compute table calcs.
- Compute grand totals & subtotals.
- Compute total-based reference lines on regular aggregates, and percentile distribution reference lines for regular aggs and table calcs.
- Apply filters on table calcs.
- Filter latitude (generated) and longitude (generated).
- Manually hide marks.
- Compute all other reference lines.
- Pages Shelf.
- Compute Tooltips, Title, Annotations.
A question I’ve seen asked many times in different guises is how to keep an aggregation static and unaffected by filters for comparison purposes in Tableau. Dependent on the circumstances of how the data is set up in the background and how the visualisation needs to be filtered is how the solution is derived. Unfortunately I can’t be clearer than that, as everybody who’s familiar with Tableau knows every solution is different – and there are often multiple techniques availbale to reach the same goal.
In this post I’m going to detail what I feel is the simplest way to do a comparison of individual values against an aggregated value – for example how an individual stores sales compare against a group average, or how a regions new business leads compare against the entire country.
The key to this is to have the aggregation not affected by the filtering to allow for comparison. Read more…
If you have created tables in Tableau you might have noticed once you get to a six dimension columns in the table the left hand columns begin to merge into one column. This only happens when using dimensions as column headers, not measures as when displaying multiple measures in a table this can be achieved by putting only 1 pill, the Measure Names, on the shelf. If you want to know how to add measures read my article describing how to display multiple measures in a table.
Tableau is not really designed for showing tabular data but sometimes it’s useful to show line item data as part of a drill down in a table, or perhaps you need to create a report specifically with the purpose of exporting Tableau to Excel. Read more…
If you have had to use Tableau Server in medium to large environments it’s likely you have had to deal with Tableau Server permissions . Initially I found it wasn’t the most logical way of assigning permissions, I had to do a lot of repetitive tasks.
The thing to keep in mind is that Tableau Server permissions work at each level and can require assigning at each level – i.e. view => workbook => project – but if you assign permissions from the workbook level or below, if the permissions differ for many workbooks/views in the project maintenance becomes increasingly time consuming. I have found the most efficient way to assign permission is from the top down – i.e. project => workbook => view – and I’ll explain the reasons later in the article. Read more…
This article is off topic from most of the site but this caused me pain so worth writing about. Recently I stayed in a hotel where to access the internet you are redirected to a page which makes you agree to their terms and conditions, more or less standard. It was free internet so no complaints…until I realised it had invaded my Firefox and every time I tried to open a certain page – bbc.co.uk in this case – I was redirected to the hotel broadband login page even when after I checked out of the hotel and was in a completely different city. The culprit redirect site wass hotel-broadband.com.
I searched high and low on the internet on how to get rid of this unwanted redirect as it was preventing me getting to the BBC homepage. Best and most effective solution was to clear the cache. Options – Options – Advanced – Network - Cached Web Content – Clear Now.
I’m still curious about where this redirection instruction was stored in my Firefox – and it was only a Firefox issue as IE worked fine – but couldn’t find anything hidden in my Firefox settings.
I have to thank Ben Sullins for this and his post on how to add an All to a parameter in Tableau, finding that article saved me a lot of time.
To begin create the parameter and add an ‘All’ option. Display the parameter on your worksheet.
Next drag the field you want to filter to the filters shelf.
To filter the field using the parameter right click on the field on the filters shelf and select Filter, then Condition.
In the By Formula box enter the following formula:
IFNULL([FilterField],’Null’) = IF [Paramter] != ‘All’ THEN [Paramter] ELSE IFNULL([FilterField],’Null’) END
How does this formula work? Read more…
Recently I’ve been experimenting in Tableau with dashboard sizes for the ipad. I prefer to set it up landscape viewing and found the optimal size is 1020 x 610 pixels, this more or less seems the perfect size to fill the full screen and remove the need for scrolling.
On many occasions I have found Tableau data extracts are slow to refresh on the Tableau Server when pulling from SQL (and I’m sure this happens with all data connections). I always used to put this down to the slow execution of the query, which could definitely be a cause as if it’s slow to run on the SQL Server it will be slow to run when refreshing an extract. It also appears that Tableau can be the cause of the slow updating in some cirucmstances. I have just come across this write up on extract performance by one of the Tableau staff which they put on their forums. I think it is very useful so I’ve copied it into this article.
Basically it advises what to check to see what the cause is of the slow updating of extracts and advises what to do if the blame is on the Tableau side. Read more…
Recently I have had to reshape text files using custom SQL in the data connection. Being reasonably competent in SQL I thought this would be easy. Unfortunately I was wrong. Tableau uses the MS Jet data engine which has slightly different syntax to standard SQL used in SQL server. For example when using the DATEADD function the syntax differs. With DATEADD the syntax also differs between the Custom SQL connection and the Tableau calculated fields. This link could also help with the datepart intervals.
Also the CASE statement isn’t recognised by MS Jet SQL, instead an alternative has to be used, such as nested IIF statements. This post I found suggesting CASE alternatives, written back in 2006, suggests CHOOSE can be used instead, it all depends on how the CASE statement was going to be used. Read more…
To remove the All option from a Tableau autofilter is possible by following these simple steps:
1. Click the small down arrow in the autofilter heading
2. Scroll down to Customize
3. Uncheck where it says ‘Show “All” Value’ so there’s no longer a tick mark against it.
To show the All option follow steps 1 and 2, and in step 3 check the ‘Show “All” Value’ so the tick mark appears against it, as in the diagram below.