3 Replies Latest reply on Jun 20, 2019 9:19 AM by John Taylor

    A Dynamic Parameter using Union All *womp*, No more manual updated parameters :-)

    Tyler Garrett

      Hello, it's just a dynamic parameter - and might work for you. Or you can call it a work-around to many to many in Tableau.


      A lot of peeps and businesses have a lot of data, lots of sources, and then eventually you want a single filter.


      What happens when you need 20+ data sources? Do we call it a monster workbook and then run away?


      After you hit 10-20-30 data sources, things slow down, and this speeds it up every time. Like a glove. I'm excited to drop it here.


      I've used this to clean up manual parameter updates - and also consolidated 10,20,30,50...+ data sources. Into one single extract. (micro process it in your database, and frontend excel stacking SQL is below!)


      Dynamic parameter and Solution to TOO MANY DATA SOURCES.


      A CIO asked, "why doesn't it have multiple checkboxes.... Like it did in the DEMO... (i didnt do the demo )"


      In Tableau Desktop V8... I didn't have an answer to why parameters didn't have multiple checkboxes if the answer was MANY TO MANY isn't working. Until we whiteboarded... But of course... I tried to explain it to him right away.. lol


      I tried to explain, "... many to many, vs one to many..."


      "make it work, or this doesn't work."


      Okay here's the solution and powerpoint slide to help sell it to the business. SQL on bottom. Excel/csv syntax.


      Seems to be one of my favorite tools too.

      Screen Shot 2017-10-23 at 7.47.57 PM.png

      This removes manually fixing parameters that are used to filter.


      If your parameters need to be updated, please stop using that method.


      If you need to ever manually change a parameter... please stop doing that. And start utilizing this below.

      Screen Shot 2017-10-23 at 7.48.07 PM.png


      This solution also allows you to be able to offer stacked bar chart and multiple lines.


      Work to dimensionalize the lines using the same column.


      Here's the presentation if you need to 'sell it' as a solution.

      Screen Shot 2017-10-23 at 8.04.17 PM.png


      I've implemented this across 30+ Tableau environments.


      Please consider micro processing 'every stack' - utilize the database for what it's designed to do.


      Processing loads. Then select * your dimensionalized data.

      Screen Shot 2017-10-23 at 7.58.34 PM.png




      Screen Shot 2017-10-23 at 7.58.57 PM.png

      Report flag is helpful.


      This solution was almost generated in the product with the Version 9 Union All.


      I believe this needs to be implemented directly into the product. Because of how helpful it is to have a single extract from a filtering perspective VS offering global filters that don't optimize singular extract jobs, singular tables, singular micro processed stacks of data, and instantly queried back to use in Tableau Desktop.


      Screen Shot 2017-10-23 at 8.03.16 PM.png


      ExcelSQL to utilize.

      Select   "Employee" as [ReportFlag],

        [Employee$].[Name] as [Employee Name],

        [Employee$].[PhoneNumber] as [PhoneNumber],

        [Employee$].[Region] as [Region],

        [Employee$].[Department] as [Department],

        0 as [Sales],

        0 as [Profit],

        "" as [ItemID],

        "" as [WarehouseLocation],

        0 as [Cost]

      From   [Employee$]

      union all

      Select   "Revenue" as [ReportFlag],

        "" as [Employee Name],

        "" as [PhoneNumber],

        [Revenue$].[Region] as [Region],

        [Revenue$].[Department] as [Department],

        [Revenue$].[Sales] as [Sales],

        [Revenue$].[Profit] as [Profit],

        "" as [ItemID],

        "" as [WarehouseLocation],

        0 as [Cost] 

      From   [Revenue$]

      union all

      Select   "Inventory" as [ReportFlag],

        "" as [Employee Name],

        "" as [PhoneNumber],

        [Inventory$].[Region] as [Region],

        [Inventory$].[Department] as [Department],

        0 as [Sales],

        0 as [Profit],

        [Inventory$].[ItemID] as [ItemID],

        [Inventory$].[WarehouseLocation] as [WarehouseLocation],

        [Inventory$].[Cost] as [Cost]

      From   [Inventory$]





      It's the key to a lot of ideas. Hope it helps you too.



      Don't try to put big data in Tableau, don't try BILLIONS with this solution because Tableau will probably crash and not function with billions.

      The front end offers a place for "acceptable solutions" and when the data size becomes "un-acceptable" you have an opportunity to LEARN something and use a better database.

      There are a lot of cloud databases that are extremely affordable, much more affordable than waiting on a slow dashboard.

      Please consider a "smart use case" because it works.


      Feel free to give feedback, even if it's has nothing to do with the solution, I will give you an honest answer with feedback, and next steps.


      We aren't dealing with complex functions, only 1 function will solve dynamic string parameters.



      Tyler Garrett


        • 1. Re: A Dynamic Parameter using Union All *womp*, No more manual updated parameters :-)
          . Tableautester

          This might work when you have small tables, but doing UNION ALL with tables which are several million rows or a few billion rows doesn't seem practical.

          1 of 2 people found this helpful
          • 2. Re: A Dynamic Parameter using Union All *womp*, No more manual updated parameters :-)
            Tyler Garrett

            Thanks for pointing out this detail and taking the time to say something. You are correct, forcing tableau to handle billions of records, and stacking other data sources on it, wouldn't solve your problem.


            I would never recommend this solution with Billions of records W/O the proper database. Thanks for helping me make this more clear "tableau Tester".


            If you're having trouble making it work, it's because you are suggesting a bad use case. Overall no one at Tableau would recommend billions of records in tableau without the correct data source , open a support case and look-n-see my friend. Another engineer will tell you "no that's not possible."


            This solutions works FINE with the correct data base for the correct data sizes. I promise, it is more a big data thing you are "dealing with" and it has nothing to do with a tiny bit of SQL, rather 1 function of SQL... 1 function of SQL will not make a difference at that size, you're pretty much staring at a tableau that's crashing! So.. That should tell you to connect to a better data source...


            I've seen it work over and over and over, with bigger sizes than "billion." You can do it, I believe in your ability to do it too. There are likely 100+ open source databases that can get you started too.


            When attempting to "punish tableau" with large data, why not consider a live connection to the appropriate database, and speak with the database owner to materialize the solution because based on what you're purposing, they will likely be able to offer you help aggregating your data...


            It's likely a document oriented database would make sense depending on this "brutal" request.


            When i was at [INSERT STATE] Medicaid, we had a really BIG data issue too. So it had a lot to do with dealing with how much we render, and that can be handled with user experience. They did not have the CASH at the time for a BIG database purchase, and it require a different mindset of solving.


            Feel free to ping me, I'm available to come up with a better solution, 9-5 cst Monday-Friday. Cheers



            Tyler Garrett


            PS. If you need me to tell your boss to buy a better database, I will. I've left appropriate edits so other folks know not to try to do something "unpractical" with Tableau. Tableautester

            1 of 1 people found this helpful