This post describes how an "advanced menu" can be used to work around the need for dynamic parameters when filtering across multiple data sources in Tableau.
In his post "Creating a Collapsing Menu Container in Tableau", Robert Rouse does a great job of walking through the mechanics of how to build a "dynamic and collapsable menu" in Tableau.
Some of my favorite mobile apps like Slack, Feedly and Google Maps have a slide-out menu that appears when I tap a small icon. That common design element makes plenty of room for user inputs and gets them out of the way when you're done - perfect for small screens.
To elaborate further on that concept, in this note today I explain how we can leverage the idea of a "dynamic and collapsable menu” to tackle some additional, rather complex data design challenges.
Why Multiple Data Sources?
First off, why would we deliberately use multiple data sources in a single dashboard?
Well, on large data volumes, for performance! In fact as your data volume grows large, Data Architecture decisions like this one quickly become imperative.
For two years running at the annual Tableau Conference, the performance program manager for version 9.0, Kate Morris, and her peers have discussed the design for performance technique of using multiple data sources each at differing levels of detail.
This means, for a typical Tableau dashboard which follows Schneiderman's mantra2, "Overview first, zoom and filter, then details-on-demand”, the overview visualizations should be built upon an overview (pre-aggregated) data source.
Thus, the details-on-demand views are the only ones that query the deeply granular data. And, they do so with very specific and narrow queries, the result of having "zoomed and filtered" before drilling-down.
In other words, for optimal performance on large data: we want to avoid runtime summarization. Summarization requires computation. And computation takes time. For the best user experience, we definitely want to perform that summarization in advance instead of making our consumers wait for it in real time.
Jonathan Drummey also speaks to this multi-source concept in his very thorough and recent treatise on when to extracts vs. a live connection.
I recently answered a question for a new Tableau user on when to use a Tableau Data Extract (TDE) vs. a live connection, here's a cleaned-up version of my notes: My preference is to first consider using a live connection because extracting data adds another step to the data delivery chain.
This brief note of mine is the first of various instructional posts I will write on the subject of Data Architecture for Tableau.
But, The Challenge
The difficulty arises however, when our multi-data-source dashboard must also employ headline filters. When filters need to work across all of the data sources, things begin to get complicated. And, as usual, options are available if you're willing to explore them.
Dynamic Parameter Work Arounds
As of today’s writing, in Tableau version 9.2, various alternatives exist for emulating a "dynamic parameter like”, cross data source filter in Tableau:
- SQL: Dynamic Parameters - a sorta hack
- Alteryx: Crosspost from DataBlick – Tableau Dynamic Parameters Using Alteryx
- Blending: Creating a Dynamic 'Parameter' with a Tableau Data Blend
The contra these approaches share, in my opinion, is that they force you to extend your work outside of the standard, friendly, Tableau canvas. So the genesis for this innovation was derived simply from the need to satisfy some very basic requirements while still continuing to work only within the standard, friendly, dragy-droppy canvas environment of Tableau.
The filter must:
- update dynamically
- be multi-select
- apply across data sources
How To Get There ?
Dashboard Actions! Up thru at least version 9.2, a dashboard action is the only mechanism for filtering that is both multi-select and works across multiple data sources in Tableau.
The only problem with them is, each new worksheet consumes a huge amount of real estate.
The Advanced Menu
By building an "advanced menu" (which is nothing more than a popping layout container with basic worksheets inside), we can use those worksheets to drive action filters into all of the other data sources of the dashboard.
These simple worksheets in the "advanced menu" layout container needn’t be more than a dimension pill on the rows. This is more than enough to update dynamically when your underlying data changes. Depending on your needs, perhaps you’ll want to mimic the "Only Relevant Values” by applying one dimension to the other. And, by holding down the ⌘ key, the advanced filter sheets allow for multi-select.
The problem of too many sheets, occupying too much real estate?
That is solved by dynamically hiding the advanced menu out of sight when it isn’t being used: in negative space on the dashboard.
The Apply Button
Again for optimal performance: there is never a single, magic solution. Rather, good performance is the culmination of many many design decisions.
One problem you might encounter with your advanced menu is that, when holding the ⌘ key for multi-select, each click applies to every data source as you interact. And that can take time, especially if you want to multi-select various filters. Why watch the hourglass as you apply each filter incrementally?
We may prefer to instead select all of the filter values first, and then “APPLY” them at once to the dashboard.
This is easy to do with an APPLY button. The dashboard actions on the “advanced filter” sheets each have the APPLY button as their only target. And from there, the APPLY button has the worksheets in your dashboard as the targets.
Faster Still ?
For the fastest "popping" action, try to build your advanced menu sheets from the smallest data source possible.
Clicking Thru The Layers
One challenge you may have faced, when building sheet popping into your own solutions, is that the layout container that you use for the “popping” action blocks your mouse from clicking into an underlying layer that is “behind” the container.
Fear not, however. Because in Tableau Server, majestically: that empty space of the layout container can be clicked through. And in Tableau Server, it is possible to interact with the worksheets that lie behind that empty space of the layout container.
This means that once you publish to Server, then in the web browser you can completely interact with the dashboard; even if that layout container driving the “advanced menu pop” occupies the entire dashboard real estate (which, in Desktop, renders things rather un-useful).
This apparent disconnect between the behavior of Desktop and Server is one that we’ve observed for more than a year now. I called it out in the Tableau Community forums back in December of 2014. And, then again in September of 2015.
While there’s no definitive promise from Tableau that they will keep the server behavior the same, because I’ve been using this to my benefit for more than a year now, intuition tells me that this is a fairly safe piece of functionality to take advantage of!
In his excellent post, Zen Master Robert Rouse illustrates the mechanics of how to build a Collapsing Menu Container in Tableau for Quick Filters.
The very same approach can also be used to build a dynamic collapsing menu made of worksheets. And those “advanced menu” worksheets can be used to effectively achieve a multi-select, dynamic, cross data source filter mechanism via Dashboard Actions.
In addition, for optimal performance on large volumes of data: there is no substitute for a well-thought Data Architecture.
To avoid runtime summarization, it is advisable to customize your data sources down to only the minimum level of detail required by the sheets which make use of them. This approach often necessitates cross data-source filtering. And, the advanced menu is a great way to move forward in these situations.
Of course, coming in version 10, Tableau has announced plans for native cross-data source functionality from quick filters. Never-the-less, even then, you can be certain that future problems will require future creative solutions.
Word Count: 1,252
- "Creating a Collapsing Menu Container in Tableau", Robert Rouse, InterWorks, January 04, 2016
- "The Eyes Have It: A Task by Data Type Taxonomy for Information Visualizations", Ben Schneiderman, Department of Computer Science, Human-Computer Interaction Laboratory, and Institute for Systems Research, University of Maryland, September, 1996
- "TURBO CHARGING YOUR DASHBOARDS FOR PERFORMANCE", Kate Morris + Dan Cory, Tableau Conference 2014, Recorded Session, September, 2014
- "TURBO CHARGING YOUR DASHBOARDS FOR PERFORMANCE", Kate Morris + Rapinder Jawanda, Tableau Conference 2015, Recorded Session, October, 2015
- "TDE or Live? When to Use Tableau Data Extracts (or not)", Jonathan Drummey, Drawing With Numbers, January 05, 2016
- "Dynamic Parameters - a sorta hack", Nelson Davis, The Vizioneer, June 18, 2014
- "Crosspost from DataBlick – Tableau Dynamic Parameters Using Alteryx", Jonathan Drummey, Drawing With Numbers, August 30, 2015
- "Dynamic Parameters in Tableau", Derrick Austin, InterWorks, December 17, 2015
- "Creating a Dynamic 'Parameter' with a Tableau Data Blend", Jonathan Drummey, Drawing With Numbers, August 02, 2013
- "Sheet Swapping and Popping with Joe Oppelt (and a Tip on Searching the Tableau Forums)", Matthew Lutton, MLutton BI, October 04, 2014
- "We made a video of Sheet Swapping and Legend/Filter Popping on a dashboard.", Comment by Keith Helfrich, December 07, 2014
- "We made a video of Sheet Swapping and Legend/Filter Popping on a dashboard.", Comment by Keith Helfrich, September 11, 2015
- "Creating a Collapsing Menu Container in Tableau", Robert Rouse, InterWorks, January 04, 2016