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
Creating In-Place Help Page for Tableau Dashboards
Accommodating Variable Height in a Text Table in Tableau
A 30-Day Learning Plan for Mastering Tableau
A 30-Day Learning Plan for Mastering Tableau
Embedding Mundane Charts with Pareto Insights
Creating Pareto Chart Multiples in Tableau
A Preview of Sankey Chart Type in Tableau 2024
AI's Perception of People Looks in USA
1
AI's Perception of Top US Banks as a Dog
Multi-Level Sankey with the New Sankey Chart Type in Tableau
A First Look into the New Sankey Chart Type in Tableau
Visualizing Cross-Tab Tables with Column Sort
1
A Couple of Mismatches in Tableau's Map
Converting Date to Fiscal Calendar Date in Excel
Converting Date to Fiscal Calendar Date in Tableau
Resolving Simpson's Paradox: The Whole vs The Parts
#TweakThursday: Horizontal vs Vertical Bars
Recreating Datasaurus Dozen in Tableau
Rendering Anscombe's Quartet in Tableau
How to install database drivers for Tableau in Mac
Drawing Line Charts without Axis Offset in Tableau
Charting "Top N and Others" via Table Calculations in Tableau
Calculating YoY of Quarterly and Yearly Sums in Tableau
Imagining Tableau Social
Imagining Tableau Social
Imagining an Enhanced Formula Editor for Tableau
Imagining an Enhanced Formula Editor for Tableau
Filtering Partial Date Period in Tableau
How to Send Email from Tableau with a Template
How to Make Phone Calls from Tableau via Cisco Jabber
Labeling Trellis Chart via Stacked/Shared Axis
Sizing a Trellis Chart in Tableau
Placing Multiple Labels in Trellis Chart
Creating Compact View of a Sparse Table in Tableau
Labeling Trellis Chart Anywhere in Tableau
3
Labeling Trellis Chart in Tableau
1
Computing Spearman's Rank Correlation in Tableau
Computing Cumulative Grand Total in Tableau
Filling Down in Tableau
Pre-Filters, Post-Filters and the Order of Operations in SQL
2
Charting Project Flow via Multiple Data Densifications
Calculating Period-To-Date/PoP with Indicators for Better Performance
Counting Consecutive Occurrences in Tableau
1
Creating Bridge Chart with ARR vs AOP
Calculating the Difference Between Any Two Points in Tableau
Drilling Down with Containers
Creating Sunburst Chart via Map Layers in Tableau
Simple Calculations for YTD/YoY, QTD/QoQ, MTD/MoM and WTD/WoW in Tableau
9
One Button Sheet Swap in Tableau
One Button Sheet Swap in Tableau
2
Coloring the Area between Two Lines in Tableau
Sorting 100% Stacked Bars by Percentage
Creating Kanban Board in Tableau
4
Multiple Bars and Lines in Layers
Referencing Another Data Source without Relationship in Tableau
2
Dynamic Date Granularity and Show Missing Grains
4
Creating Global Variables in Tableau
Creating Sankey Funnel Chart via Template
59
Creating a Simple Toggle Button in Tableau
Monitoring Data Source Delay in Business Dashboard
Analysis of Tableau's Multi-Variable Clustering Algorithm
Iris Classification via Clustering in Tableau
Always Showing Empty Columns in Tableau
Creating Date Range Filter with Dynamic End Date = Max Date
5
Visualizing Crosstab and Grand Totals
Getting the Last Date/Value/Variance in Tableau
A Template for 3-Stage Sankey Chart in Tableau
26
Deselecting Button on a Different Sheet
Deselecting/De-dimming Buttons in Action-Driven Sheet Swap
3
Game of Life in Tableau
Enhancing Text Tables with Visual Background
1
20 Calculation Use Cases: LOD vs Non-LOD
Nested Sorting and Hierarchical Nested Sorting in Tableau
1
Creating 3D Bar Chart via Custom Shapes
Creating Sankey Chart with Sorting/Grouping
5
Creating Sankey Chart with Grouping
4
Enhancing Chart Contrast #TweakThursday 43
Creating a Multi-Stage Sankey Chart via Template Cascading
13
Creating Radial Stacked Bar Chart in Tableau
11
Creating Waffle Chart Multiples in Tableau
Creating Waffle Chart from a Single Percentage in Tableau
Creating Hierarchical Bar Chart in Tableau
Hiding Subtotals at Single Subcategory in Tableau
3
Making Label Text Wrap in Tableau
9
Accessing Excel Files on SharePoint from Tableau
Accessing Excel Files on SharePoint from Tableau
Creating Bar Chart Multiples In One Sheet 2/2: Different Measures
Creating Bar Chart Multiples In One Sheet 1/2: Same Measure
Using Color to Reveal More Insights #TweakThursday 42
Customizing Subtotals and Grand Totals in Tableau
4
Annotating Dual Axis with Alternative Color Legend #TweakThursday 41
A Divide-n-Conquer Approach to Counting Word Occurrence
Sorting Stacked Bars in Tableau
8
256 Cellular Automata in One Sheet
Creating Polygon Hex Maps in Tableau
Creating Joy Plot in Tableau With Fewer Steps
How Amazon failed to deliver: Analytical lessons
Polygon-based US Hex Map Revisited
Visualization, Data and Tableau Eco System
Creating Sankey Chart as Easy as 1-2-3 in Tableau
62
Tableau Licensing Cost Visual Calculator
Tableau Licensing Cost Visual Calculator
Creating 3-Way Venn Diagram with Dynamic Labels
1
Placing and Aligning Labels on Small Multiples
1
#WorkoutWednesday 35 - Superstore Small Multiples Tiled Map with Quad Chart
Creating Polygon Circumscribing Graph #MathVizMonday
Blog Archive
Loading
Dynamic Views theme. Powered by Blogger. Report Abuse.