12 Replies Latest reply on Jun 15, 2013 7:31 PM by Richard Leeke

    Create a Dynamic Parameter List

    chris.rezny

      I have several million rows in a data set that i need to be able to filter.  the quick filter option is not a viable solution as number of values in the list is in the 20,000+ range.  i would like to use a parameter but in order to get the visualization to work the way i would like it to, and to be able to distribute to others, i need the list of parameter values to be populated based on dynamic values from the database.  i have read that there are some work arounds based on using actions in dashboards.  are there any other ways of getting around this limitation?

        • 1. Re: Create a Dynamic Parameter List
          Robert Morton

          Hi Chris,

          Can you please explain why a quick filter is not an option but a parameter is, even though you want to populate a parameter with the same 20,000+ range of values dynamically?

          Thanks,

          Robert

          • 2. Re: Create a Dynamic Parameter List
            Noel Avison

            I would say that it would be a good idea to categorize the values in the data into broad groups. At that point you can select one of the broad groups then set other filters to context to further limit the amount of values shown.

             

            What sort of values are you dealing with?

            • 3. Re: Create a Dynamic Parameter List
              chris.rezny

              My biggest concern with hierarchical filters is the re-calculation of the quick filters, if i have the filter only show relative values, won't that require an additional query after every select?

               

              i will experiment with this further and report back with my performance finding.

               

              Thanks for your help.

              • 4. Re: Create a Dynamic Parameter List
                Robert Morton

                Hi Chris,

                 

                Tableau 8.0 introduced an important performance enhancement for quick filters that show only relevant values. Tableau now attempts to query in advance for the combinations of values in all quick filter domains, allowing Tableau to determine the hierarchical (i.e. nested) nature of the quick filter fields. The results are stored in an in-memory Data Engine representation for the lifetime of your data connection, and this in-memory result set can be queried very quickly to provide a great, interactive experience with Quick Filters showing only relevant values.

                 

                Some caveats prevent this from working in all cases. One example is the way in which certain date filters (such as relative or range of dates) may slice through the hierarchical quick filter domains, and it is very hard to model all possible combinations of date start/end with other relevant value quick filter domains. To determine if this mechanism is working or not, look through your logs for lines such as the following.

                Example of success:

                  Acceleration view for sheet 'Products'. Definition composition took 0.0155 sec.
                  ...
                  Acceleration view for sheet 'Products'. Building view took 2.6693 sec.
                

                 

                Example of failure:

                  Acceleration view for sheet 'Customers'. Building view failed due to: ...
                

                 

                I hope this helps,

                Robert


                (cc Shawn Wallwork, Ben Sullins, Richard Leeke)


                • 5. Re: Create a Dynamic Parameter List
                  Shawn Wallwork

                  Robert, thanks for the ping. Just to be clear, these performance enhancements only get implemented when using "Only Relevant Values" in a quick filter. So we should use this option whenever possible to make our workbooks more responsive. Correct?

                   

                  --Shawn

                  • 6. Re: Create a Dynamic Parameter List
                    Robert Morton

                    Hi Shawn,

                    No, "Relevant Values" is not a choice you make as a performance consideration. Use that option only if it provides the most meaningful experience for your Quick Filters. In the past, users have avoided Relevant Values when they resulted in numerous database queries on each interaction, but that problem never existed with normal Quick Filters. We have improved the performance of Relevant Values so that it is on par with normal Quick Filters.

                    -Robert

                    • 7. Re: Create a Dynamic Parameter List
                      Shawn Wallwork

                      Got it thanks for the clarification. Glad I asked.

                       

                      --Shawn

                      • 8. Re: Create a Dynamic Parameter List
                        Richard Leeke

                        Thanks for pinging me on this thread, Robert - I'd seen that acceleration views existed but had only vaguely guessed what they were doing.

                         

                        This also reminded me to check up on the status of an issue I raised during the 8.0 beta where acceleration views were actually slowing me down a lot. I've just rechecked with 8.0.2 and the issue is still there - so I'd be interested in your thoughts on this one (at the risk of high-jacking this thread)..

                         

                        I have a workbook with 50 odd views using a 90,000,000 row extract. I have a lot of calculated fields in the data connection, and with the size of the extract and the nature of some of the calculated fields I get a huge performance increase from optimising the extract when I add or change calculated fields. With version 7, optimising the extract after adding a new calculation was very quick - typically a few seconds for materialising the field - and I don't recall there being a packaging phase, but I'm not certain about that. The whole process was certainly quick.

                         

                        With version 8, whenever you optimise an extract, as well as materialising any new or changed calculated fields it also re-calculates all acceleration views for all sheets using the extract. In my case some of the views take a couple of minutes to create and the whole process takes 10 minutes or more. So if I just want to make one small change to a calculation and use it on one sheet I have to wait while acceleration views are created for all 50 sheets. And as the acceleration views are not persisted, it is 10 minutes every time I want to optimise the extract. Even if I don't optimise the extract, if I go to one of the sheets where the views take a couple of minutes to create there is still a long wait.

                         

                        I'm sure this is a corner case and the acceleration views are a good thing for most people most of the time, but they definitely hurt sometimes.

                         

                        The only workaround I can see is to change the filters to Show All instead of Relevant Values.

                         

                        Interested in any thoughts you have n this one.

                         

                        Richard

                        • 9. Re: Create a Dynamic Parameter List
                          Robert Morton

                          Hi Richard,

                          Please file a case with Support. The acceleration views are supposed to be persisted in with the extract, but it may be the case that they are recomputed even when unnecessary.

                          -Robert

                          • 10. Re: Create a Dynamic Parameter List
                            Richard Leeke

                            Thanks Robert, will do.

                             

                            I actually had some long discussions on the go with the beta support thread about this. I wasn't quite sure where it had been left, but my interpretation was that the issue came down to the fact that the required acceleration views depend on the state of worksheets (specifically what quick fllters are defined), rather than just on the state of the extracted data. So to be able to persist the acceleration views and know whether or not they needed to change when a new optimise operation happened would require the state of the sheets at the time of each optimise operation to be recorded in the extract. Based on the behaviour I was seeing I assumed that that wasn't happening.

                             

                            Anyway, I'll log it with support again.

                            • 11. Re: Create a Dynamic Parameter List
                              Robert Morton

                              Thanks Richard. It's one thing to have an explanation, it's another thing to believe the explained behavior is appropriate, desired or effective. Thanks for always giving us feedback!

                              -Robert

                              • 12. Re: Create a Dynamic Parameter List
                                Richard Leeke

                                Quite so. Case logged with support.