5 Replies Latest reply on Jul 24, 2018 2:37 PM by Chris McClellan

    How to let user pick values to be passed through to SQL?

    mark.welsh

      I believe this question is as old as Tableau itself, but I'm wondering what workarounds you guys use to enable users to select a list of values to pass through to a SQL object. 

       

      The background is that we have a request for a user to be able to retrieve data by inputting 1 to 5 different fields and associated values.  They will need to be able to select the field values from a drop down list.  After they've selected their values then the query needs to run and return results.  The alternative, I think, is to refresh this very large data source once a day, so that the user can just filter it within Tableau.  This seems like bad practice due to the overhead required to refresh it everyday, so we'd rather just run the query with the user selected field choices.  If that doesn't work, we're considering incremental refreshes so that the overhead is, hopefully, drastically reduced. 

       

      What do you guys do if you need to let a user pick field values before a result set is returned?

        • 1. Re: How to let user pick values to be passed through to SQL?
          Chris McClellan

          Tableau usually handles this automatically.

           

          If you want to pass values to Custom SQL then you have to use a parameter (which is a single value only, you can't choose multiple values), but Custom SQL does cause Tableau to slow down sometimes.

           

          Why can't the user just access all the data and filter the dimensions to the required values ?

          • 2. Re: How to let user pick values to be passed through to SQL?
            mark.welsh

            The issue is that we have a user that needs access to up to 10 years of very large data source.  To add to it, the user's behavior is totally unpredictable and their needs are urgent.  So the only feasible solution is to refresh the entire data source once per day - although we may end up looking into incremental extracts. 

             

            So, we thought if the user could pass through their time frame and other filters before running the report, we'd save a lot of computational resources. 

             

            I'm sure this a common scenario and I'm also pretty sure the functionality is built into SSRS which is something our users are familiar with. 

            • 3. Re: How to let user pick values to be passed through to SQL?
              Chris McClellan

              It depends on what you consider "very large" along with how fast the database engine is.  I regularly deal with a database of approx 50million records.  Traditionally the users had a live connection which was quite slow at times depending on user load.  By simply creating an extract, the dashboards were a LOT faster.  Also, we are further analysing how much detail they need.  (in a way) I know they don't need 50 million records, so if they can use a source that is aggregated at (eg) a daily level then the record count drops a lot but there is no loss of functionality .... unless they want to look at the time of day from 5yrs ago, then they switch over to the larger/slower/more granular data source.

               

              There's no "one right answer" to this, it's always a balance of record count, database engine speed and user expectation.

              • 4. Re: How to let user pick values to be passed through to SQL?
                mark.welsh

                Well, we haven't run any official speed tests or anything like that, but for the sake of argument let's assume it's too slow to work for this use case. 

                 

                In that case, I'm wondering what options are out there for letting the end user pick values for up to 10 parameters, none of which they will know off of the top of their head and many of them being a list of values with 10 or more items in said list.  Is this possible to do out of the box with Tableau Desktop/Server?

                • 5. Re: How to let user pick values to be passed through to SQL?
                  Chris McClellan

                  I'd be doing some speed testing before deciding what direction to take.

                   

                  Tips are:

                  - avoid custom SQL if possible (I used to be a huge Custom SQL fan when I first started, but I've learned that letting Tableau handle the SQL is usually faster and better.  Custom SQL means that you might be forcing Tableau to run a non-optimal query compared to what you're trying to display).

                   

                  - do some proper speed tests - is the database fast enough or too slow ?

                   

                  - database too slow ? create an extract and retest everything to compare.