The current Ed-Fi community has struggled with the non-longitudinal nature of many "indicators" in the model. For the purposes of this analysis, an "indicator" is generally regarded as a field describing an important characteristic of a primary data model entity (e.g. a student, school, etc.). Also, for the purposes of this analysis, indicators will have a very broad definition: the value of an indicator can be binary in nature (true/false) or have a value associated with them ("red", "not submitted", "3.3", etc.).
Use Cases
Historically, there have been a 2 important use cases relating to indicators:
Use Case 1: as an agency performing data collections and reporting, I want to capture the value of a indicator for a period of time or at a moment in time (e.g. at the end of a month) so I can perform operations such as issuing payments to districts or performing state reporting.
Use Case 2: as a school district, I want to track the movement of indicators over time so I can make instructional decisions on how to intervene to improve the education of the student.
The longitudinal nature of indicators is important in case #2, but not in case #1. The evolution of Ed-Fi has also meant that many indicators only support case #1.
Current Situation
The obvious solution is to add dates, but due to the natural key system of Ed-Fi, the adding of dates is not straightforward. To take a very simple example, consider this data model; this not Ed-Fi today, but it illustrates the problem:
Figure 1: a hypothetical common situation today where an indicator has no dates attached.
This is a typical setup in Ed-Fi's data model today, and it illustrates why adding dates is not straightforward: to add a dates to StudentIndicator would not solve use case #2, as the data model would reject multiple records for the same student and IndicatorName:
Figure 2: The attempt to use this model longitudinally will fail as the StudentUniqueId and the IndicatorName form the primary key; a second record for this same combination cannot therefore be loaded.
The obvious solution is to add BeginDate to the key, and that option is often a good one. However:
- it is a breaking change
- it has other drawbacks we will examine below.
This document is an attempt to "step back" and consider this problem holistically. To do that, this analysis examines multiple options below for adding longitudinality.
Options
The 3 main options we will consider are depicted in this diagram:
Generated from: Example.sql