Tag: Data Sources

My session at DataFam Europe included a section about some limitations of Tableau’s multi-fact model, especially regarding stuff you can do in an “old” model but not with multiple fact (base) tables.

A recent bout of troubleshooting with a customer unearthed some new revelations, and a partial solution, for the “Dimension switching” problem that I had demonstrated.

Let’s start with the problem. I have a multi-fact data source, as below. The two fact tables have five common dimensions (Date, Currency, Promotion, Sales Territory, and Product), Customer is related only to Internet Sales, and Employee and Reseller only to Reseller Sales.

I‘ll start by creating a parameter that will enable me to switch dimensions in my worksheet:

Now I create a calculated field based on this parameter, which can be placed in my chart or table, and I get an error:

Not good. In an old model, with just one base table, you can switch any field using this technique. Now, even if I’m only intending to use this “Selected Dimension” field with a measure from one base table (and I haven’t placed it in the worksheet yet), Tableau is already giving me an error.

This is where the customer’s story comes in. One of the developers tried this, and received an error as expected, but another developer, working with a different data source (also multi-fact), managed to create a similar calculated field and use it in a worksheet. So we started investigating, and after some trial and error we now understand when the technique works, and, more importantly, why.

I already know (from a conversation with Thomas Nhan, the Product Manager) that Tableau calculations in multi-fact data sources start from the base table. We’ll keep this in mind during the following examples.

Step 1

We start by referencing only a couple of shared dimensions from different tables (note that Category and Sub-Category are from the same table):

That’s an error, because Tableau can’t decide from which base table to start calculating.

Step 2

We add a dimension that’s related only to one base table:

No error, because Tableau can decide, based on the [Reseller Name] field, that it has to use the FactResellerSales base table.

That’s the key: give Tableau a clue regarding which base table to use, and it will use it. Let’s try something else:

Step 3

That’s not working, because [Reseller Name] and [Customer Name] are related to different base tables. And it reminds us that dimension switching will only work with measures from a single base table, which we’ll see in the final result.

But even if all the dimensions you need are related to multiple base tables – add a dummy field from the table that you’re referring to, just as a pointer:

Step 4

This is OK. Category, Sub-Category, and Promotion are all related to both Internet Sales and Reseller Sales, but Sales Order Number, which will never be used because “—” is not a value in the parameter list, is in the Internet Sales fact table, so it is telling Tableau to start calculating from that base table.

And this is where you have to look out for the actual results. By pointing Tableau at a specific base table, we are limiting the use of the dimension to that table only – even though the selected dimensions are related to two or more tables:

In the screenshot I have selected the Sub-Category dimension, but in the data pane it is applying to Internet Sales Amount only, even though it is related to Reseller Sales as well – because I “pointed” Tableau at Internet Sales with my dummy row in the calculated field. Note that Reseller Sales Amount even has the icon indicating a broken link, and hovering over it displays a message:

Conclusion

Dimension switching in a multi-fact model works, just as it does in the old relationship model with one base table, as long as Tableau is told explicitly which base table to use. However, there is currently no improvement in the capabilities, so you can’t switch dimensions in a single worksheet and display measures from multiple fact tables. Maybe that will come in a future iteration. And now you know how to “force” Tableau to choose a base table for the dimensions when necessary.

This is a revised copy of my original post in the Tableau Community blogs from 2024.

Back in the day (before 2020.2), you could add worksheets from different data sources to a dashboard, define a filter from one of them, “Apply to selected sheets”, and it would automatically filter the other sheets using blending.

Then came relationships, which enabled us to create more flexible and efficient data sources. However, the filtering option was suddenly lost, because the secondary data source in a blend cannot include a relationship. When you try to apply a filter from one worksheet to another, and both are based on relationships, the second is suddenly disabled (grayed out or empty), and when diving into it you see the dreaded message:

So we started creating workarounds – and especially using parameters as filters, with all their disadvantages: no “All” option, no multi-select, no “relevant values”. And telling customers (or users) that on dashboards using multiple “advanced” data sources their filtering options were limited.

And then, after almost 4 years of dithering around this, I had an epiphany. Looking around a bit (using mostly Google Search, of course) I couldn’t find anyone who had implemented this sort of solution, or at least written about it.

So here are the sample workbook:

https://public.tableau.com/app/profile/dan.chissick/viz/RelationshipFiltering/SalesDashboard

and the explanation:

The data is based on the Microsoft AdventureWorksDW database. There are two data sources with relationships to various dimensions: Internet Sales and Reseller Sales. Both have similar data and some common dimensions.

Basically the idea is to create a supporting data source for each dimension used as a filter, with no relationships. Joins are possible, of course – if you want to create a hierarchy, for example.

In this case we have three supporting data sources:

·        Sales Territory (including a hierarchy of Group, Country, Region)

·        Product Category (including Category and Sub-Category)

·        Dates – this actually contains all dates within the relevant data range, and is based on a separate view from the actual data. I named the date field “Order Date” because this is the field’s name in the main data sources.

Now create a dashboard and add worksheets from the two main data sources. I’ve added two bar charts.

For the Sales Territory Country filter (for example), create a worksheet with only Sales Territory Country on the rows. Add the same field as a filter as well (select “Use all” for now).

Go to your dashboard, and drag the worksheet onto it. Then make it disappear: set “Outer padding” in the layout to zero, and the height and/or width in pixels to 1.

In the worksheet menu select Filters –> Sales Territory Country, and the filter will appear. Place it wherever you want, Apply to Worksheets –> Selected Worksheets, and select the worksheets with the data.

And there it is – the filter affects both bar charts, using the Sales Territory data source, which has no relationships, as the secondary data source for the filter.

Using the Product Category data source and another hidden worksheet, I created a couple of additional filters for Category and Sub-Category.

As for the dates, the process is the same, and I can use any type of date filter – relative, range of dates, year, etc. It’s important for the independent Dates data source to include all possible dates in the actual data sources, so the blending works properly.

To summarize – the finished dashboard has charts from two different data sources with relationships and multiple logical tables, filtered by common dimensions with little effort. The supporting “dimension” data sources can be re-used, so this is a relatively simple workaround for organizations working with many complex data sources and seeking to combine them within multiple dashboards.