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.