Why Vizioneer?

My photo
Atlanta, Georgia, United States
The "Vizioneer" comes from mashing two words that have shaped my world for most of my adult life - Engineer and [data] Visualizations (or Vizes to those who know what's up). Graduating from first from Oglethorpe University in Atlanta, followed by Georgia Tech with my Bachelors and Masters in Civil Engineering, all of which taught me to think through anything and everything - problem solving, "engineering" solutions, teaching to the "ah ha" moments - is what I love to do. In 2010 that investigative, engineering mindset intersected a job change and a plunge into the world of Data Analysis. In the search for the next great thing I stumbled on to a data visualization and dashboarding product called Tableau software and things just took off. So now I guess you could call me that engineer with the sweet data visualizations - or just "The Vizioneer" :)

In 2013, I joined the incredible team at Slalom, focusing on Tableau and it's been an amazing experience. Recently in 2014, I was honored and humbled to receive Tableau's highest recognition of being named a Tableau Zen Master. Follow along to see what happens next :)

Wednesday, June 18, 2014

Dynamic Parameters - a sorta hack

...And we're back.

It's been a little more than six weeks since I've saddled up and hopped on the blog.  The #Tableau30for30 took a lot out of me and I've needed some time to recover :).

So dynamic parameters.  It's the number one most requested feature idea in the Tableau Community.  Everyone seems to have come across a time where this would come in handy.  It's a great idea and given the demand it should be implemented sooner rather than later.  

Unfortunately, it's not coming in the very soon to be released v8.2.  You'll hear a lot about some very slick new features - Story Points and the Visual Data Window to name a few.  But dynamic parameters ain't on the docket.  So it's time we take matters in to our own hands.

Let's start at the beginning.  What's the point of dynamic parameters?  Here's the text from the idea page (currently with 870 votes - only one with over 320 votes):


Parameters are really useful when you need to do something too complex to be handled by quick filters or action filters. However parameters are currently hobbled by the fact they have to be static lists.

It would be really, really useful and solve a lot of Tableau gotchas, if you could define the options available for a parameter dynamically, from the result of a datasource, and preferably with the option to apply filters also.

That's a bit about what dynamic parameters could do.  Let's talk about what parameters are in general.  Here's what we're NOT talking about - The type of parameter that is a numerical value that can be changed via a slider or typing something in that will affect calculations.  Those parameters work great - no complaints there.  Rather, what the "Idea" is typically talking about is a parameter based on a STATIC list (think one time snapshot) of values of a dimension in the data.  Here's how it typically goes:
Create a new parameter>>Change the data type to "String">>Click the button for a "List" of Allowable values>>Select "Add from Field" and select the dimension to get your list of values (I'm using Superstore data and going with "Category")

































This will create a list of values based on the dimension selected.
















This static list now lives apart from your data set.  If the data set changes (the company adds more product categories) this list will NOT update.  This is the gripe.  The thinking is that if the data changes, so should the parameter.

So here we go.  I'm going to propose an idea.  Follow me for a minute.

When we create a list of values in a parameter, it's as if we're informally creating a new table of values, based on a snapshot of the data, that now lives outside the the data.

What if we just more formally create a table of values that remains based on the underlying data, yet lives outside of the full datasource (as a parameter would).  What if, instead of calling this new table of a single dimension of data a "parameter", we called it what it really is - a small data source.

Here's what I'm proposing - Based on the same example from above (Creating a String Parameter from a List of Values based on the Category field), we head back to the data window and head for the Custom SQL.  What? You don't like SQL?  This is going to be the easiest three lines of code you've ever written.  It goes like this:


























In general terms it's:
Select [Your Dimension]
From [Your Datasource]
Group by [Your Dimension]

And using the SQL from above, what do we get?





























A single value list with all of the Product Categories - this looks strikingly similar to the parameter we created, except for one thing.  This is dynamic.  This list will change if the underlying data would change.

So we've created this dynamic list - what can we do with it?  Basically anything we want - we can do most of the same things we could do with parameters - use it in formulas, use it in quick filters (single select or multi-select - the second option is not currently possible with a parameter), etc....  The sky is the limit.  Here's an example:



































Notice the two data sources.  Superstore is our primary data source and the single value list dynamic table based on Categories is the secondary source.  Creating a multi-select quick filter works like a champ (make sure the two data sources have the correct links and relationships - which Tableau will make sure of if the headers have the same names).





Interesting stuff, I say.

Is it a perfect solution.  Nope - it's a hack.  Are there a dozen reasons why it's not a best practice? I'm sure there are, and I'm sure the comments section below will let that play out.  But with all that said - could it help solve a problem you're having? It just might.  

It's a crazy simple solution and my hope is that it sparks a conversation.  I by no means think this is anywhere near the last word on this topic, but with v8.2 walking out the door, it's time we begin to amass ideas for v9.0.

I look forward to your feedback.  Many thanks- 

Nelson

23 comments:

  1. This is a great hack - I've been using a combination of sets and filters to get what I need, but this may be easier - I could set up different views in SQL depending on the "filter" I want and link them as a "data sources" listing my dimensions I need.

    ReplyDelete
    Replies
    1. Thanks Stacey - I do hope it's helpful. I know there are some weak points in the solution, but I also think there may be some cases where this could really help. Keep me posted on how it goes when you implement this! Thanks!

      Delete
    2. Thanks Nelson! This looks promising.

      Hi Stacey any chance you can share your version of usage by combining sets and filters?

      Thanks!

      Delete
  2. Hey Nelson, some great thinking here around the problem, nice to get the ball rolling on how to solve for dynamic parameters.

    There are some use cases where the above does not currently work, for example if I want to use a calculated field like IF [Category]=[source2.product category] THEN [Category] ELSE 'Other' END and place that on the rows shelf or the color shelf it does not work. This is because a blend is required to get the viz, but that blend also means that any filters on the secondary source will also apply to the primary. So in a sense it becomes a quick filter rather than a parameter.

    Its so close, all that's missing is the ability for Tableau to utilize two unblended data sources in a single calculated field......

    ReplyDelete
    Replies
    1. Peter -
      Thanks for the feedback and you're right - because we end up using this as a quick filter you lose the ability to show an "Other". I've tried about 7 ways to Sunday using sets and combined sets to get around it, but to no avail at the moment. It's close, and I think it's fun to have ideas to talk about to kick the can down the road. Keep me posted if you come up with anything fun on this. Many thanks for checking it out.

      Nelson

      Delete
  3. PeterG - See http://drawingwithnumbers.artisart.org/creating-a-dynamic-parameter-with-a-tableau-data-blend/ for how to get something like what you want. You can turn off the Category as a linking dimension in the secondary source, in which case a filter on the Category in the secondary only filters the secondary and not the primary (and vice versa). However, to have the match work right for a [Category] vs. Other then the Category (or a field with finer granularity than Category) has to be in the view as a dimension (since all calculated fields using dimensions or measures from the secondary have to be aggregated, the view has to have fine enough granularity so the comparison will work), in which case to make an Selected vs. Other value you'd need a table calculation to aggregate over all the Categories. You can see an example of that in http://drawingwithnumbers.artisart.org/selecting-one-to-see-one-vs-all-other/.

    There are also ways to use Sets from secondary sources, see http://drawingwithnumbers.artisart.org/how-to-have-sets-with-your-secondary-data-sources/ for details. However, selecting the particular value of a dimension to use for the IN/OUT of a set from the secondary is just like for the primary, there's no real dynamic way to do that outside of Tableau desktop, unfortunately.

    Nelson: I'm glad to see someone else posting this idea, I've been exploring various funky use cases for data blending for awhile now and there are some very cool things we can do with it!

    Jonathan

    ReplyDelete
  4. Thanks Jonathan, using your data blend technique and Nelsons post I've been able to get most of the way there. As you state though I need Category in the view for it to work. I've tried using variations on window_sum calcs to get the aggregation but its not behaving like it would with a single source because the new field I create for 'selected vs other' can't be used for the partitioning (because under measures)

    I also tried the technique in your second blog post but it seems to be dependent on having another blend (i.e. on date), is that correct?

    This is certainly a brain tax!

    ReplyDelete
  5. Is it possible to insert these "parameters" into text?

    ReplyDelete
  6. Hi Nelson
    I am able achieve the above mentioned trick to filter different (Data Source) worksheets on a single dashboard, but when I follow the same for multiple Dashboards is it possible to achieve parameter action with the quick filter when all my data sources for each dashboard are blended together by a common field ?

    Thanks
    Praveen

    ReplyDelete


  7. very nice blogs!!! i have to learning for lot of information for this sites...Sharing for wonderful information.



    Nexus 5 Case

    ReplyDelete
  8. Get SAP Successfactors Online Training by Certified consultant from India. Enroll for Expert level Instructor Led Online SAP SuccessFactors Training. Apply today!

    ReplyDelete
  9. W3 D Technologies is a pioneer in Dell Boomi Training sector, providing the Dell Boomi Online Training course we have experienced expert faculty for Dell Boomi http://goo.gl/Rt7DuX

    ReplyDelete
  10. We are offering Dell Boomi Online Training with latest techniques. Our experts have more than 8 years experienced and certified. Dell Boomi Online Training

    ReplyDelete
  11. Nice Blog, thanks for the article. For tutorials, interview questions and online training on Dell Boomi Visit TekSlate.

    ReplyDelete
  12. Nice post....We provide the cool gadgets....For more detail about cool gadgets click here Cool Gadgets | Electronics Gadgets | Latest Technology

    ReplyDelete
  13. Useful information...... We provide gadgets at reasonable price....For more detail visit here Cool Gadgets | Electronics Gadgets | Latest Technology

    ReplyDelete
  14. Nice post.....We provide the guideline for educational purpose.....visit here Ehow-india | How to Guides India | Ehow-India How To India Guides

    ReplyDelete
  15. Hi there, awesome site. I thought the topics you posted on were very interesting.
    Buy iPhone 4s Back Cover in India.

    ReplyDelete
  16. How do we do if we have to set the parameter dynamically in production for multiple data sources. does tableau support this.

    ReplyDelete