Data Quality: Alert When Missing Data
New feature: How does 'Insert 0s for missing values' work? provides an updated way to alert on 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).
View Report Data
Here is what my report looks like.
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).
SQL:
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
Calendar Table
Metric Chart
Here is the joined data on the metric's chart. On 8-21 and 8-27 we chart the inserted zeros.
Alert Rule if Value = 0
Then create an alert rule based on a fixed value.
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).
Save as MySQL Table
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(*)
FROM report_table_name
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.
Metric Chart
Remember the measurement_time column only has dates on a go-forward basis. When you create the metric you will only have one data point to start with. This chart tells is the report pulled in 89 records. That is good. All we care about is that there are records at all.
Alert if there are No Records
Create the alert rule to fire when there are no records (value =0). You can change the threshold if needed so that you are alerted when there are unusual values (statistical alerting).
0 Comments
Add your comment