A recent question in Tableau Forum prompted me to write this piece.

Scaffolding is one of the lesser known techniques in Tableau because it's not officially documented. There is a video session on it by the omnipresent Zen Master Joe Mako. That's all I can find.

Data scaffolding is very often used to blend data of multiple sources, especially when one can't use the native blend function of Tableau. Specifically, when the elements in the blending dimension are mutually non-inclusive, and we want to include all the data, scaffolding is necessary. In other words, scaffolding makes full outer join possible in Tableau. It keeps all the data without loss when blending data.

The Problem
Assume we have two data tables. Each table shows a number of products and their sales from one of the two stores. The question is what is the total sales from both stores.

Some kind of blending is necessary. Custom SQL can be applied to union the data together. Sometimes, SQL may not be accessible. So here we assume SQL is not an option.

The native blending in Tableau works like left join or right join. Some rows of the secondary data source have to be dropped, if the index dimension elements can't find a match in the primary source's same dimension.

For example, if Table 1 is primary and Table 2 is secondary, only 2 rows of Table 2 will be blended with Table 1, because product d can't find a match in the primary.

If Table 2 is primary and Table 1 is secondary, only 2 rows of Table 1 will be blended with Table 2, because product a can't find a match in the primary.
Because of data loss, the regular blending is not going to cut it.

The Scaffold
Here comes the scaffolding technique. We need to build a table with a single column that includes all the possible products.
Then we will make Scaffold table as the primary and both Table 1 and Table 2 as secondaries. And let the secondaries blend with the primary. And we get the grand total of all the sales as shown in the table below.
Note that ZN() is needed because some data marks can be Null. ZN() will turn them into zero so that they can be added.

The key of scaffolding is to create a dimension that is the super set of that in either of both tables. This way during the blending, no data will be lost.

In case of dynamic data, the scaffold dimension needs to have a complete list of products.

So this is how scaffold works to blend data of multiple sources. The workbook for this example can be downloaded here.

PS. A search on scaffolding turns up a few more articles for your reference:
Joshua Milligan: GOOGLE ANALYTICS IN TABLEAU: BLENDING DATA FROM MULTIPLE ACCOUNTS
Jonathan Drummey: Tableau Data Blending, Sparse Data, Multiple Levels of Granularity, and Improvements in Version 8


4

View comments

  1. Nice article. I've used scaffolding on numerous occasions. The biggest downside is the need to maintain the scaffold table as the data changes. I suppose that would be a great use-case for a tool like Alteryx. :-)

    ReplyDelete
    Replies
    1. True. It's a case by case thing. It is cheaper than alteryx :)

      Delete
    2. Hi Alex, can u please tell me how can i find out every year every month last day transactions data in tableau

      Delete
    3. http://kb.tableau.com/articles/howto/show-all-date-values-as-last-day-of-month

      Delete
[Forward: I asked ChatGPT o1-mini who then wrote this. Hope it helps. All the credit and the blame go to ChatGPT.

I went over the plan and it looked decent. Whether it can be done in 30 days or not, it depends on the person and the time he spends on it.
Just came back from Tableau Conference 2022 at Las Vegas. What an exciting event! The most exciting thing is reuniting with old friends and meeting with the datafam people known online for years.

Attended first time the Tableau Visionary summit.
A little enhancement in the formula editor can make a big difference for whose who create formula all the time in Tableau. Here are my wishes for a future editor. 

Highlighting Syntax Words

Currently a formula in Tableau can look plain and a bit uninspiring.
Blog Archive
Loading
Dynamic Views theme. Powered by Blogger. Report Abuse.