Data Quality: Alert When Missing Data
There are two popular scenarios for alerting with there is missing data or a data source fails to update. In both cases, we start by pulling in your source data. Then we create a metric to implement some logic and set an alert rule.
Use Case #1: Alert me when I'm missing data / Alert me on NULL's
Values are missing on 8-21 and 8-27. If we create a metric on this data then nothing will be charted for those days. I want to be alerted when I'm missing data like this. If my data source returned a 0 then it would chart (and I could alert on it) so I'll have to insert the missing values in Metric Insights. The first step is to create a report and pull the source data into the Metric Insights Database.
Create a Report and Save as MySQL Table
Create a Report that pulls in your data. Use the data source for your source system and the appropriate data collection trigger (schedule).
Create a Metric the inserts Zeros and Alerts
Use the Dashboard Database so that we can query both the system's 'calendar_date' table and your report's table. This query only pulls data from 2015 and newer (the calendar table goes back to 1980).
SELECT c.calendar_date,coalesce(x.Value,0) as 'Value'
FROM calendar_day c
LEFT JOIN (
SELECT your_date, your_value as Value
FROM fred_report_data_segment_0.your_data_report) x
ON c.calendar_date = x.your_date
WHERE c.calendar_date >= '2015-01-01'
ORDER BY c.calendar_date
Use Case #2: Alert me when my Data Source Doesn't Update (Variation)
I have a Tableau view and I want to be alerted when it doesn't update. The strategy here is to create a report and pull in data from the data source (Tableau in this case but could be anywhere data is expected to load consistently).
Create Metric that Counts Report Records
Note: Using version 3.3's Single Existing Report Data Source (New). You can use the 'Existing Report' or 'Dashboard DB' to query the same data like this:
SELECT measurement_time, count(*)
GROUP BY 1
'measurement_time' is the snapshot date Metric Insights applies when saving the report as a MySQL table. The count represents the numbers of records Metric Insights pulls into the report. If the count is zero than no data has been loaded. If you have false alerts check the timing of data collection.