Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Datagroup on max id from the table and date of the view?

  • 15 May 2018
  • 3 replies
  • 221 views
G

I was wondering if it’s possible to create a datagroup for scheduling dashboard sending for example.


The following conditions should be met for datagroup:



  1. When max(id) changes in the table

  2. Day of the week is Wednesday


Can’t find my way around it at the moment.


Thank you! 🙂

3 replies

bernard.kavanagh
Rank
Userlevel 2

Hi German,


Thanks for reaching out.


It is possible to use the datagroup to schedule, however datagroups do their trigger check every 5 minutes by default. Therefore you’re MAX(id) will work, but it may not be the best use for scheduling as it may send many many schedules a day.


It will however work very well as a sql_trigger_value for rebuilding a PDT as it will ensure fresh results on every query.


I would suggest creating a schedule on Wednesday mornings, and use a sql_trigger_value: SELECT MAX(id);; to rebuild any PDT’s you may have.


If you have any other questions, please feel free to visit help.looker.com.


Regards,

Bernard

G

Hey @bernard.kavanagh,


thank you for the quick reply!


I understand the concept for the PDTs and using sql_trigger_value there to refresh the data.


We are in a bit different situation. We have an ETL job that loads data to data warehouse let’s say at 8am daily and reports go out at 9am via scheduled emails. However, there are situations when by 9am data has not been loaded yet, hence report doesn’t reflect latest data. That’s why I was thinking to schedule report on when MAX(id) from the physical db table + use the day and time constraints to make sure we send report on certain day and time, but only once the ETL is complete.

fabio
Rank
Userlevel 7
Badge

The easiest solution will probably be to have your ETL also append to an “ETL Log” table with the timestamp of ETL start/completion. Then you can write a trigger like:


SELECT MAX(completed_timestamp) FROM etl_log WHERE completed_timestamp < DATEADD(day,3,DATE_TRUNC(week,DATEADD(-3,day,CURRENT_DATE)))

Reply


Terms of Service