2 Replies Latest reply on Sep 23, 2015 7:39 PM by Matt Coles

    Refreshing an extract to use parameters in custom SQL

    Benjamin Kuhn

      Hi everyone,


      I'm publishing a workbook on Tableau server which is using a data extract derived from a SQL database. I'd like to use a parameter that can be modified by the user as a variable in the SQL query to the database. I found this page: http://onlinehelp.tableau.com/v8.0/pro/online/en-us/customsql.html which explains the process if you are connecting directly to the database. Performance is really slow when I direct connect, so that's not a realistic option. At the bottom of that help page it says that if you are using an extract, the extract must be refreshed to reflect changes to the workbook. Is there a way to force a refresh of the extract, say, when a certain variable is changed in the workbook? This will be a fairly rare occurrence so I'm ok if this takes a little bit of time, but I'm not aware of a way to ask Tableau to refresh the extract without doing it manually, which I don't want to have to ask my users to do. Is this possible?



        • 1. Re: Refreshing an extract to use parameters in custom SQL

          Hey Benjamin,


          I feel like you could probably do this in a Rube Goldberg sort of fashion by having custom alerts that trigger when a database changes and triggers an extract refresh through tabcmd. Perhaps Matt Coles has an idea.

          • 2. Re: Refreshing an extract to use parameters in custom SQL
            Matt Coles

            I'm not exactly sure I understand the set of needs that got you to the state where you needed this feature, Benjamin. Here's my series of questions as I read through your problem:


            1. Are you using 8.0? 9.0 (and now, 9.1) has a lot of features that make working with Custom SQL way easier--namely, the ability to have multiple SQL queries as separate "tables" within the Tableau connection screen. I'd definitely advise upgrading if you're able.


            2. Why is Custom SQL necessary? If possible, avoid it wherever you can--it will introduce extreme inefficiencies into your workbook, and is generally more difficult to maintain.


            3. What does having the parameter injected into the SQL accomplish for you that a Quickfilter wouldn't? Performance?


            4. Whatever the Parameter is set to in your workbook when you publish it, will be used in the extract generation query. So when the extract refreshes, you will be stuck with the data that the parameter told it to get. Someone who is simply viewing one of the Views from the workbook on Tableau can change the parameter, but I believe that's just for their own session, and doesn't actually change the default value for it in the workbook itself--so I suspect you'll get the same data no matter what a Viewer sets the parameter to, unless they Web Edit or publish a new version of the workbook. Again, if you have an extract, why not extract all the data, then let the viewer pick a quickfilter value to filter it?


            All that now said, if there really is no better solution, yes, you could write a custom script to query the Tableau Server repository and identify some event there that would then trigger an extract refresh. But Diego is right in calling that a Rube Goldberg machine. I'm confident there are better solutions for you than that route!