Scaffolding is a way to blend data. In quite a few cases, it gets the job done quite well. Thus there seems no more need to join or union data at the record level.

In recent articles, I described "Lossless Data Blending via Scaffold" and "Blending Dates via Scaffold". Both are in their simplest form: One dimensional scaffold to blend two data sources of different dimension components. The scaffolding dimension must be the superset of those same dimensions in the secondary data sources.

Again, I would emphasize that the difference between regular blending and scaffold-based blending is:
  • Blending: Loss of data in the secondary sources.
  • Scaffolding: No loss of data if we wish. Or we can choose to keep only those data of interest. The scaffold acts as the primary. All actual data sources are equally secondary.
Now, the scaffolding can help us blend the data together and show us a rather cool chart. The new question is: how do we filter it by certain dimensions?

The short answer is, we need to build the filtering dimensions into the scaffolding first. Then we can create the chart and filter the result afterwards.

There comes the multi-dimensional scaffolding. And the detailed answer follows.

Let's take the same example as in "Taking Stock with Start and End Dates". Assume we need to filter the result by Product Category and Customer Segment.

In that example, we created a single date dimension scaffolding. Now we need to add two more dimensions. The steps are as follows.

1.Create one column per dimension per sheet in Excel
So we have these 3 sheets friendly named: Date, Product Category and Customer Segment. But they could be using the default names like Sheet1, Sheet2 and Sheet3. Each sheet has a single column with header and dimension elements.
2.Cross join all the dimensions using custom SQL
A SQL one-liner suffices to generate the multi dimensional scaffolding

Select * from [Date$],[Product Category$],[Customer Segment$]

There are 2 elements in Date: Start date and End date. There are 3 elements in Product Category and 4 elements in Customer Segments. Cross joining them will generate 2x3x4=24 combinations thus 24 rows in the scaffolding.

The size of the scaffolding equals to the multiplication of the sizes of each dimension.
The next step is to make sure all the secondary data sources are blending with the primary on all 3 dimensions.

Last, by creating the same measure "Outstanding Orders" and dragging Customer Segment and Product Category to the filter shelf, we now can filter the measure and associated chart by the two dimensions.

The resulting interactive workbook can be downloaded here.

Dimension Reduction
We see that the scaffolding is created using 3 dimensions. The size of the scaffold or the number of rows are obtained by multiplying the sizes of each dimension. This number can become huge if a few of them are big. Sometimes, such a huge and bulky scaffolding is unnecessary because it takes up space and decreases performance. So we need to do some dimension reduction.

For example, in our superstore data set (depending on versions), there are 3 product categories and 17 sub-categories. If we want to filter by these two dimensions, according to the above, we seem to need 3x17=51 rows of scaffolding. This is assuming the two dimensions are orthogonal. In reality, they are not. Each category is just a label on the 17 sub-categories. And each sub-category belongs to one category only. So these two dimensions can be put in one sheet. Thus the size of the scaffolding is reduced from 51 to 17. If necessary, this sheet can be cross-joined with other dimensions.
This is how multi-dimensional scaffolding works! It can help us blend multiple data sources and build dimension filters in a very flexible way. This actually creates alternatives to union or join at the record level.
8

View comments

  1. Further tips on reducing dimension:

    1.Group hierarchical dimensions in one sheet. Time and product are two independent hierarchies, for example. So, Year, Month, Week, Day all go to one sheet. Product, Product Sub-Category, Product Category go to another sheet. These independent dimensions are also called orthogonal to each other.

    2.Pick only those dimensions that matters for the viz. No need to include all the possible dimensions.

    ReplyDelete
    Replies
    1. Also group two or more hierarchies into one sheet if necessary.

      Delete
    2. Hi Alex! I have a small doubt . How can i find out all years all months last day transaction data ( i have 4years of data) in Tableau.

      Delete
  2. A use case http://community.tableau.com/thread/190903

    ReplyDelete
  3. Nice work, Alexander! A clear and helpful explanation :)

    Thanks!
    Keith Helfrich | Twitter
    Red Headed Step Data

    ReplyDelete
  4. Nice work, Alex! I have a small doubt, what is the usage of Mock up function in Tableaud

    ReplyDelete
    Replies
    1. what is mock-up function? can you give an example or pointer?

      Delete
    2. sry for wrong entry this is not a function. I face this type of questions in my Interview not only this, their asking some different questions like....what is Moke up and what is Wire framing and Metrics in tableau

      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.