What do the "Fact Valid From" and "Fact Valid To" fields in Enterprise insights for Epics mean?

[Mark Dodrill]

I am investigating how I can use Enterprise Insights to give me snapshots at a point of time of the status of Epics, Features, and Stories, using the "History" EI tables.  I have the spreadsheet of the EI schema that I am using.

In those History tables, there are fields with names like:
* Epic Fact Valid From

* Feature Fact Valid To

etc.

These fields are full date and time fields, but I am unable to find out what those fields mean, in terms of what data that it is conveying.  At first glance, it looks like the "Fact Valid From" is storing the date/time stamp that something was updated in that work item (Epic/Feature/Story), which makes some sense.  But then what does "Fact Valid To" mean?

In looking at actual data, I see two patterns:

1. The Fact Valid From value is exactly the same as the previous record's Fact Valid To value (for the same work item).

2. The last row for a given work item always has the Fact Valid To value set to the date: 12/31/9999.

I was thinking that I would write my SQL query something like this:

SELECT *

FROM

    [current_dw].[Epic History] AS EpicHistory

WHERE

    EpicHistory.[FK Epic ID] = 123 AND

    EpicHistory.[Epic Fact Valid From] < '2024-04-04 15:33:00'

To get the full state of the Epic up to the given time.

But now I'm not sure if that's correct, since I don't understand the usage of the Fact Valid To field.

I would appreciate any assistance in understanding.  My Google searches didn't yield anything.

[Allan Maxwell · Accepted solution]

The "valid from" and "valid to" fields work different from my expectations; you need to say greater than the Valid From AND less than the Valid To.  Please note that in the example below, multiple records are returned for a single Epic id.  This is because that Epic was edited multiple times in a single day ...the timestamp is slightly different for each record.

I assume you would want the Epic as it appeared when the clock struck midnight that day.  Thus your query becomes a bit more complicated.  There are many ways to accomplish getting the Max record, but I believe the method below would be consider the "most" correct.

[Mark Dodrill]

Thanks for that very helpful information and knowing I should check both fields.

The only corner case I can think of would be where the change comes in just before midnight, and the Valid From value is in one day, and the Valid To field is in the next day, but that case is probably not worth consideration.