Knowledge BaseMetric InsightsMetrics & ReportsAlertsData Quality: Alert When Missing Data

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

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 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

View Report Data

Here is what my report looks like.

Create a Metric the inserts Zeros and Alerts

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

Calendar Table

Metric Chart

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

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)

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

Save as MySQL Table

Create Metric that Counts Report Records

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

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

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

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.