3 Replies Latest reply on Dec 19, 2018 8:04 AM by Jim Thompson

    Server: refresh extract with new parameters in Custom SQL

    Louis-Marius Gendreau

      I have a lot of data in BigQuery. I need users to compare data from any two dates. So I built a custom SQL with BEFORE and AFTER date parameters that builds an extract with just the data the user needs. It works fine on Desktop: change before and after date parameters, refresh extract and they get just the data they need. They can then filter and dissect the data at will in a very reactive dashboard. Now I want to push this unto tableau server so many users can compare whichever two dates they wish to compare. I've tried everything I could think of. It's as if the published extract has the BEFORE and AFTER date parameters hard coded: it's always the same two dates.


      Even when I Edit the view to make the parameter changes permanent, the extract refresh returns the old dates.


      The only way I can make this work is to make it a live connection. But then there are way too many queries sent to BQ as users perform filtering actions and the view are updated. Those queries have a lot of latency and scan a lot of data so costs add up fast. I tried putting the data in a pre-reduced table but the latency remains too high with BQ. Extracting that data to a more reactive MySql server is a last resort. It must be possible to do something using an extract?


      1) is there any way, relatively easy or not, to get a server extract with modifiable parameters embedded in custom SQL? Ideally I don't want the users to have to Edit the view and then manually trigger a refresh.


      2) is there any clever hack around that kind of problem?  I am testing doing queries with outer joins comparing every monday to every other monday in the year and then using a filter to just show the two dates of choice but it's not a very elegant solution... I end up with a few million rows.


      3) I have a few other ideas like using embedded views; or using an external web page to start a separate process which would execute the query and then trigger the extract refresh. I even looked into using the new dashboard extension features but I'd rather keep it real simple for now with no extra programming.



        • 1. Re: Server: refresh extract with new parameters in Custom SQL
          chiranjeevi prathipati

          Hi Louis,


          I don't have any real good answer for you. But I have a couple of suggestions regarding live connections.


          1) If am sure there is a lot of data in BIG query table you are using. But there is a concept of partition in BIG Query where you can partition the table based on date column.  I would suggest to if the table is partitioned based on date or not. If not please partition the table by date first.  Create a data source Filter with condition Date Column = Before Date or Date Column= After Date. This will ensure that every query fired on table will access only those 2 partitions. I am positive this will speed up the process by a lot. Tableau doesn't do great with custom SQL live connections. Just directly use the Table as data source instead of using "select * from table".


          2) Can you explore the option of using context filter on date column in live connection?

          • 2. Re: Server: refresh extract with new parameters in Custom SQL
            Vivek Nayagam

            Hi Louis,


            I don't have direct solution for this, but may be you could  try having an extract with all the data and then have date filters at the worksheet / dashboard level. You could then schedule the extract refresh.





            • 3. Re: Server: refresh extract with new parameters in Custom SQL
              Jim Thompson

              Louis - Experienced the same problem. Had a Data Selector page before the results page where I produced a list of "acceptable" dates, and asked the User to Type into a Parameter box the desired date- this parameter was a (the) parameter passed in the Custom SQL. Worked great in Desktop, but not so much after publishing to the server. It did work, but, required a manual Refresh on the Tableau Server after entering in the new parameter value. Took the advice of Chiranjeevi (above) and made the date based on the parameter a Context filter, and Voila, Tableau server always refreshes. Note: handy tip-- on the Data Selector Page, I put a workbook with just a simple shape of a Right Green Arrow and used same data Source. Added a Dashboard Action on Select to jump to the results page. User can click arrow to move to the results page. When an invalid parameter string was entered on that Selector page, the Arrow disappears as the data set is empty! Otherwise, the User can click the arrow and proceed to the results page. Must press Enter after entering the parameter. Enjoy!