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.
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.
Further tips on reducing dimension:
ReplyDelete1.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.
Also group two or more hierarchies into one sheet if necessary.
DeleteHi 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.
DeleteA use case http://community.tableau.com/thread/190903
ReplyDeleteNice work, Alexander! A clear and helpful explanation :)
ReplyDeleteThanks!
Keith Helfrich | Twitter
Red Headed Step Data
Nice work, Alex! I have a small doubt, what is the usage of Mock up function in Tableaud
ReplyDeletewhat is mock-up function? can you give an example or pointer?
Deletesry 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