03-31-2023 07:44 AM
Hello, I'm trying to setup a data alert when the data read from our source is not new. What happens is, sometimes our external source table has not been updated when our Incorta Schema load runs and we end up pulling in no data for 'today'. When this happens I'd like to send a data alert.
The issue is, data alerts only trigger when some data is present. I can't filter the dataset on 'data that isn't from today' because all the previous data gets returned. I can't filter on the max(effective_date) because 'max' is not supported in filter formulas..
How do I return data to trigger the alert when what I'm checking for is missing data?
03-31-2023 08:28 AM
Do you have a calendar table in your model? Using that with "show empty groups" might get you there.
03-31-2023 08:45 AM
One possibility is to get the row count from source tables and compare the row count with the tables loaded into Incorta. We can then send the data alert when there are significant difference found.
It may hard to be exactly the same when the data can be added to the source table anytime. However, the number is the last incorta scheduled refresh should be greater than the last runcount SQL run and should be less than the next runcount SQL run. We had implemented this in a couple of incorta customers.
03-31-2023 08:49 AM
The other issue you mentioned about the aggregate filter is not available in Data Alert may be solvable via the result set feature. If the aggregated result set is used, the measure showing the aggregate will be a regular column in the outside and can be placed in filter.