My SQL Based Raw Data Has Changed!? - Introducing SQL Based Refresh Triggers on Measures and Events

In some use cases,  raw data in a Data Source might have been altered after the fact. If we think about measures, this could have bee a recon number to update the "Actual" stock or production figures. For events, it could be that an production run's SKU, run time or reason codes might have been altered.

If the Flow front end is not used in these scenarios, we need to be able to "re-query" the source for the new version of the raw values.

This is where Refresh Triggers come into play. 

The Refresh Trigger configuration is currently only possible with our SQL based Data sources connector.

Adding Refresh Triggers to Measures

To add a refresh trigger onto a retrieved Measure from a SQL based Data Source, Select the properties tab in the context section of the measure:

A new Refresh Trigger can be added by performing a "Right-Click/New" command on the Refresh Trigger property:

This will allow you to specify an Interval as well as a Query for the Refresh Trigger functionality(The use of the Interval and Query properties will be explained in the latter part of the document):

Adding Refresh Triggers to Events

A new Refresh Trigger can be added by performing a "Right-Click/New Refresh Trigger" command on Trigger Properties of you event:

This will allow you to specify an Interval as well as a Query for the Refresh Trigger functionality(The use of the Interval and Query properties will be explained in the latter part of the document):

The Refresh Trigger Functionality Explained

The normal Flow engine operation is to, at the end of the interval for a measure, or after every engine scan cycle for events, run the SQL query that has been configured for that measure retrieval or event trigger, which should return  Value, Quality and Timestamp of the raw data.

Flow will then either create a trigger point for events or use the raw data and preform an aggregate on the data for the relevant measure interval.

If you have Refresh Offset/s  configured, then the Flow Engine will re-run the above mentioned SQL Query based on a time lapse and, if the value/quality has changed, it will update the measure / event for that interval from which it has been offset.

With the Refresh Trigger functionality, you configure an additional query for the measure/event, which returns a Timestamp corresponding to the period in which the value of this measure/event has changed, and for which you want the Flow Engine to go back and re-run the Retrieval SQL Query for. For example,

• You have a query that runs every day that fetches the Production Volume from a SQL databases. Let’s say (this is completely fictitious…)

SELECT SUM(VolumeProduced) AS Value, 192 AS Quality
FROM dbo.PackedVolumes
WHERE Line = ‘ALL’
AND StartDate >= [PeriodStartUTC]
AND StartDate <= [PeriodEndUTC]

• This will run every day at 06:01, for the previous day’s 06:00 to 06:00 production
• You could even configure a Refresh Offset to re-run the above query at 09:00, 14:00 – what will happen then is that at 09:00 & 14:00, the Flow Engine will re-run the above query, but still for the previous day’s 06:00 to 06:00 production
• But maybe you have other fields in your database, or you could write a query, that indicates when any record has been updated, such as a LastUpdated field. 
  • You could then write a Refresh Trigger query which uses this LastUpdated field, and compares it to the internal timestamp that Flow keeps, indicated by the [Since] placeholder.
  • The query should return a timestamp which corresponds to the interval in which the data has been updated.  For example, if the data that needs to be updated was from Saturday 29 June, then this query should return a timestamp of ‘2019/06/29 06:00:00’

SELECT DISTINCT StartDate as Timestamp
FROM dbo.PackedVolumes
WHERE Line = ‘ALL’
AND LastUpdated IS NOT NULL
AND LastUpdated >= [Since]

• The internal [Since] timestamp is updated every time the Refresh Trigger query is processed
• When you configure the Refresh Trigger, you also select an interval at which it must execute - it could be Minutely, Hourly, etc; it could even be different from the interval of the measure itself)
  • The Flow engine will run the query that you have configured as the Refresh Trigger query for that measure at the specified interval
  • You could even try using this without the [Since] placeholder.  Continuing with my hypothetical example,
    • Perhaps when the records are first updated in the PackedVolume table, the LastUpdated field is set to the StartDate + 24 hours, but then if there are any changes thereafter to the production number, while the StartDate can’t change, the LastUpdated does change.
    • So, you could do something like this for your Refresh Trigger:

SELECT TOP 1 StartDate as Timestamp
FROM dbo.PackedVolumes
WHERE Line = ‘ALL’
AND LastUpdated IS NOT NULL
AND DATEDIFF(hour, StartDate, LastUpdated) > 25

• • • Now, if there are any records anywhere in your table where the LastUpdated time is more than 25 hours after the StartDate, even if it’s from last month/year/etc, the Flow Engine will be notified of the exact StartDate (i.e. period) that it must go back to and run the original Retrieval SQL query for that period.
    • If the value/quality is different when it re-runs the Retrieval SQL query, it will update the measure value in that period
    • If multiple values have changed, then each time the Refresh Trigger is processed, a different timestamp will be returned, and the Flow Engine will run the above 2 steps, until no more records are returned by the Refresh Trigger query