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

Mark Dodrill
Contributor
April 25, 2024

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.

1 answer

1 accepted

1 vote
Answer accepted
Allan Maxwell
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
April 26, 2024

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.

Screenshot 2024-04-26 at 8.41.06 AM.png

 

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.

 

Screenshot 2024-04-26 at 8.54.59 AM.png

Mark Dodrill
Contributor
April 26, 2024

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.

 

Like Allan Maxwell likes this

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events