T-ODS Reference Queries

Owned by Chris Moffatt (Deactivated)
Jun 13, 2018

Introduction

The Temporal ODS (T-ODS) consists of data structures and database support for an efficient means of loading, storing, and making available multiple years’ worth of fine-grained data in a single instance of the Ed-Fi ODS. These structures support point-in-time data snapshots and other historical data functions, and provide support for applications to access and query that historical data.

This documentation provides several reference queries which may be useful in understanding and developing for the T-ODS. Note that the Database Utility Reference Application provides a graphical entry point to queries that some developers and analysts may also find helpful.

Overview

The processes and queries are supported by MetaEd generated stored procedures and table-valued functions.

A Snapshot Utility was developed to support:

  • Creating the snapshot metadata required to take a snapshot
  • Taking a snapshot of current-year data
  • Backing a snapshot out of the system
  • Querying the snapshot metadata
  • Managing and querying configuration tables

T-API functionality for resource level entities was developed to:

  • Endpoints to support querying and returning Temporal ODS data by As-Of Date.
  • Endpoints to support inserting, reading, editing Temporal ODS data by Snapshot Code.
  • Endpoints to support inserting of Temporal ODS data by arbitrary effective date.
  • Endpoints to view all data associated with a snapshot, including data related to Ed-Fi Extensions
  • Endpoints to insert, read, edit, and delete Snapshot Metadata.

While the Temporal data can be queried via the T-API resource endpoints, the Database Utility Reference Application is provided as a demonstration of how data in the temporal operational data store (T-ODS) can be queried directly and even viewed together with current, operational data in the Ed-Fi ODS.

Direct access to the T-ODS is required in order to create customized scripts.

Key Concepts

The T-ODS is comprised of data structures and database support for time-based functions in a single instance of the Ed-Fi ODS. The following is a summary of key concepts that are useful to understand before diving into query examples:

  • Database structures handle snapshot and other historical data functions and configurations
  • Processes, generally triggered at load or snapshot time, include audits to check and report data discrepancies between:
    • Current and temporal data
    • Previous snapshot and current snapshot
  • External referential integrity is maintained across resource-level entities
  • Support is provided for applications to access and query historical data
  • Metadata tables and data tables combine to form the time-based support:
    • T-ODS contains two metadata tables and a set of metadata-to-data bridge tables to support the snapshot architecture and T-ODS/T-API functionality
    • T-ODS also contains a set of data tables parallel to the present and future Ed-Fi ODS data tables, including extensions
  • Data tables in the T-ODS follow the same pattern relative to their ODS counterparts, with a few notable exceptions, such as replacement of ODS surrogate key columns
  • Snapshot processes store only the unique combinations of domain record values
  • The T-ODS is not an audit trail / change data capture solution

See the /wiki/spaces/EDFIODS/pages/19726823 documentation for complete design details.

Examples

Below are T-ODS. SQL query examples that can be used to solve temporal scenarios.

Example 1. Discipline Incidents by School

This example groups together last year's discipline incidents by SchoolId to determine how many incidents each school had in 2016.

This example assumes snapshots are taken yearly and do not contain any data from the previous year.

Incidents by School 
DECLARE @lastYear datetime = '2016-07-31';

SELECT DisciplineIncident.SchoolId,
       COUNT(*) Count
FROM [t_edfi].[ufn_GetDisciplineIncidentByDate](@lastYear) as DisciplineIncident
GROUP BY DisciplineIncident.SchoolId

Example 2. Compare Student Current-Year and Prior-Year Record

This examples unions a student's record from 2016 with the current student record, presumably 2017.

Compare Student Record 
DECLARE @snapshotDate datetime = '2016-07-31'
DECLARE @studentUniqueId nvarchar(32) = '604888'

SELECT Snapshot.IsLocked,
       Snapshot.EffectiveBeginDate,
       Snapshot.EffectiveEndDate,
       Snapshot.SnapshotId,
       Student.StudentUniqueId,
       Student.PersonalTitlePrefix,
       Student.FirstName,
       Student.MiddleName,
       Student.LastSurname,
       Student.GenerationCodeSuffix,
       Student.MaidenName,
       Student.SexType,
       Student.BirthDate,
       Student.BirthCity,
       Student.BirthStateAbbreviationType,
       Student.BirthInternationalProvince,
       Student.BirthCountryDescriptorCodeValue,
       Student.DateEnteredUS,
       Student.MultipleBirthStatus,
       Student.ProfileThumbnail,
       Student.HispanicLatinoEthnicity,
       Student.OldEthnicityType,
       Student.CitizenshipStatusType,
       Student.EconomicDisadvantaged,
       Student.SchoolFoodServicesEligibilityDescriptorCodeValue,
       Student.LimitedEnglishProficiencyDescriptorCodeValue,
       Student.DisplacementStatus,
       Student.LoginId,
       Student.LimitedEnglishProficiencyDescriptorNamespace,
       Student.SchoolFoodServicesEligibilityDescriptorNamespace,
       Student.BirthCountryDescriptorNamespace,
       Student.CreateDate,
       Student.LastModifiedDate,
       Student.Id
FROM [t_edfi].[ufn_GetStudentByDate](@snapshotDate) as Student
JOIN tods.Snapshot Snapshot
  ON Snapshot.SnapshotId = Student.SnapshotId
WHERE Student.StudentUniqueId = @studentUniqueId

UNION ALL

SELECT NULL,
       NULL,
       NULL,
       NULL,
       Student.StudentUniqueId,
       Student.PersonalTitlePrefix,
       Student.FirstName,
       Student.MiddleName,
       Student.LastSurname,
       Student.GenerationCodeSuffix,
       Student.MaidenName,
       StudentSexType.Description as SexType,
       Student.BirthDate,
       Student.BirthCity,
       StudentBirthStateAbbreviationType.Description as BirthStateAbbreviationType,
       Student.BirthInternationalProvince,
       StudentBirthCountryDescriptor.CodeValue as BirthCountryDescriptorCodeValue,
       Student.DateEnteredUS,
       Student.MultipleBirthStatus,
       Student.ProfileThumbnail,
       Student.HispanicLatinoEthnicity,
       StudentOldEthnicityType.Description as OldEthnicityType,
       StudentCitizenshipStatusType.Description as CitizenshipStatusType,
       Student.EconomicDisadvantaged,
       StudentSchoolFoodServicesEligibilityDescriptor.CodeValue as SchoolFoodServicesEligibilityDescriptorCodeValue,
       StudentLimitedEnglishProficiencyDescriptor.CodeValue as LimitedEnglishProficiencyDescriptorCodeValue,
       Student.DisplacementStatus,
       Student.LoginId,
       StudentLimitedEnglishProficiencyDescriptor.Namespace as LimitedEnglishProficiencyDescriptorNamespace,
       StudentSchoolFoodServicesEligibilityDescriptor.Namespace as SchoolFoodServicesEligibilityDescriptorNamespace,
       StudentBirthCountryDescriptor.Namespace as BirthCountryDescriptorNamespace,
       Student.CreateDate,
       Student.LastModifiedDate,
       Student.Id
FROM [edfi].[Student] as Student
LEFT JOIN edfi.SexType as StudentSexType
  ON Student.SexTypeId = StudentSexType.SexTypeId
LEFT JOIN edfi.StateAbbreviationType as StudentBirthStateAbbreviationType
  ON Student.BirthStateAbbreviationTypeId = StudentBirthStateAbbreviationType.StateAbbreviationTypeId
LEFT JOIN edfi.Descriptor as StudentBirthCountryDescriptor
  ON StudentBirthCountryDescriptor.DescriptorId = Student.BirthCountryDescriptorId
LEFT JOIN edfi.OldEthnicityType as StudentOldEthnicityType
  ON Student.OldEthnicityTypeId = StudentOldEthnicityType.OldEthnicityTypeId
LEFT JOIN edfi.CitizenshipStatusType as StudentCitizenshipStatusType
  ON Student.CitizenshipStatusTypeId = StudentCitizenshipStatusType.CitizenshipStatusTypeId
LEFT JOIN edfi.Descriptor as StudentSchoolFoodServicesEligibilityDescriptor
  ON StudentSchoolFoodServicesEligibilityDescriptor.DescriptorId = Student.SchoolFoodServicesEligibilityDescriptorId
LEFT JOIN edfi.Descriptor as StudentLimitedEnglishProficiencyDescriptor
  ON StudentLimitedEnglishProficiencyDescriptor.DescriptorId = Student.LimitedEnglishProficiencyDescriptorId
WHERE Student.StudentUniqueId = @studentUniqueId

Example 3. Prior-Year Grades for a Student

This example shows the grades for a student in 2016. By using the ufn_GetGradeByDate, we can grab the latest info regarding a students grades at the end of year. Grades contain a begin date, so the example below pulls grades between August 2015–July 2016 to get 2016 school year grades.

This query makes the simplifying assumption that a school year is between August 1st - July 31st.

Last Year Grades for a Student 
DECLARE @priorYearDate datetime = '2016-07-31';
DECLARE @studentUniqueId nvarchar(32) = '604888';

SELECT Grade.BeginDate,
       Grade.ClassPeriodName,
       Grade.ClassroomIdentificationCode,
       Grade.GradeType,
       Grade.GradingPeriodBeginDate,
       Grade.GradingPeriodDescriptorCodeValue,
       Grade.LocalCourseCode,
       Grade.SchoolId,
       Grade.SchoolYear,
       Grade.SequenceOfCourse,
       Grade.StudentUniqueId,
       Grade.TermDescriptorCodeValue,
       Grade.UniqueSectionCode,
       Grade.LetterGradeEarned,
       Grade.NumericGradeEarned,
       Grade.DiagnosticStatement,
       Grade.PerformanceBaseConversionType,
       Grade.TermDescriptorNamespace,
       Grade.GradingPeriodDescriptorNamespace,
       Grade.CreateDate,
       Grade.LastModifiedDate,
       Grade.Id
FROM [t_edfi].[ufn_GetGradeByDate](@priorYearDate) as Grade
WHERE Grade.StudentUniqueId = @studentUniqueId
  AND Grade.BeginDate >= DATEADD(YEAR, -1, @priorYearDate);

Example 4. Compare Student Address over Time

This example joins an ODS Student with it's T-ODS Student Address. The example takes a look at how a current student's address has changed over time.

Student Address Over Time 
DECLARE @studentUniqueId nvarchar(32) = '604888';

SELECT DISTINCT Snapshot.EffectiveBeginDate,
                Snapshot.EffectiveEndDate,
                Student.StudentUniqueId,
                Student.FirstName,
                Student.MiddleName,
                Student.LastSurName,
                StudentAddress.AddressType,
                StudentAddress.StreetNumberName,
                StudentAddress.ApartmentRoomSuiteNumber,
                StudentAddress.BuildingSiteNumber,
                StudentAddress.City,
                StudentAddress.StateAbbreviationType,
                StudentAddress.PostalCode,
                StudentAddress.NameOfCounty,
                StudentAddress.CountyFIPSCode,
                StudentAddress.Latitude,
                StudentAddress.Longitude,
                StudentAddress.BeginDate,
                StudentAddress.EndDate,
                StudentAddress.CreateDate
FROM [edfi].[Student] as Student
JOIN [t_edfi].[Student] as TodsStudent
  ON Student.StudentUniqueId = TodsStudent.StudentUniqueId
JOIN [t_edfi].[StudentSnapshotRecord] Snapshot
  ON TodsStudent.tid_Student = Snapshot.tid_Student
LEFT JOIN [t_edfi].[StudentAddress] as StudentAddress
  ON StudentAddress.tid_Student = TodsStudent.tid_Student
WHERE Student.StudentUniqueId = @studentUniqueId

Further Reading

The following conceptual and technical material may be useful in understanding these examples and building your own queries:

 

DRAFT NOTES (to be removed)
RequirementDOC-05 (1 of 2)
AudienceTechnical & Business Analyst.
TypeHow-To Article
POC ActivityNeeds outline & draft examples
Due DateTBD