Schedule Alerts for PDTs that Don't Build on Schedule

PDTs are a wonderful tool for analysis and query performance but as your usage of PDTs increases it can get difficult to keep management of your PDTs’ health.

One way to stay on top of any issues with your PDT regeneration is to setup scheduled emails that alert you that something is awry rather than when your end users report unexpected results.

Note: These alerts require you to be an admin so you can access i__looker (if you aren’t familiar with i__looker read about it here) the documentation on this is still in progress, at the time of this writing the pdt_log explore is not yet covered

Setup your explore query in i__looker

This may be recommended when you have few PDTs or want to keep an extra careful eye on your PDTs. If you have lots of PDTs or especially if you have cascading PDTs (where one PDT relies on one or more other PDTS to build) you may get more alerts than you expect.

Steps:

  1. access the pdt_log explore by going to https://yourdomain.com/explore/i__looker/pdt_log
  2. select these fields:
  • view_name
  • model
  • action
  • action_data
  • occur_time
  1. set these filters:
  • view name = to PDT name
  • occur_time “is on or after” “x time” (this will depend on the frequency of your expected builds 24hrs may suffice)
  • action “is equal to” “create_complete”
  1. Add these calculations:
  • Hours Since Last Build: diff_hours(${pdt_log.occur_time}, now())1 (this calculation assumes your instance is hosted by looker and the i__looker logs and server timezone will be in UTC, if you host your own looker instance your server timezone may be different and this calculation will need to have hours added or subtracted to account for the difference in occur_time that will always be in UTC)

  • Trigger Alert: ${hours_since_last_build}>24 (change the hour number to suit your needs)

  1. set the row limit to 1 ( this ensures that only the last build entry is considered in the calculation)

  2. from the option gear on “Trigger Alert” select “Hide No’s from visualization” ( this ensures that if the last build is within the acceptable range, an alert is not sent)

Create your schedule

With these steps you now have an alert system for each of your PDTs that will be sent whenever one of them doesn’t build for the specific amount of time you have set.

Some Notes:

  1. with this method you can’t set a specific trigger time per PDT to alert you within the schedule (at this time calculations cannot be filtered so you can’t set a filter on Hours Since Last Build at each schedule). That means that you may need to create a few different looks to roughly fit your PDTs regular build intervals

  2. This won’t tell you why a PDT did not build. You will need to view the admin > “persistent derived table” panel to check for error messages, analyze “Recent Trigger Events” from the option gear, or contact support

  3. if you change the view name of a PDT in your project, that change will also need to be updated in your schedule

If you have comments about how this did or did not work for you please comment!

2 2 912
2 REPLIES 2

This can be a huge help in monitoring PDTs - thanks Jiro!

Since this was written, you can now use custom fields to do the “hours since last build” calculation directly in SQL.  Which means you can filter directly on this field being greater than 24 and you don’t need to create the yes no dimension and go through that aspect.

What would be even cooler is if Looker would allow you to do a custom measure of type max on the time dimension.  Feature Request Then you’d be able to alert on the time since the last build