2 Replies Latest reply on Aug 21, 2018 12:18 AM by Vivek Nayagam

    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.