T-ODS Developer Guide

Introduction

The Temporal ODS (T-ODS) consists of data structures and database support that provide 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. This documentation provides conceptual and technical material for analysts, database administrators, and developers interested in working with the T-ODS.

Purpose

This documentation includes guidance on building queries against the T-ODS.

Audience

The audience for this page are current and prospective ODS / API platform hosts, including database administrators who work with educational database systems. Similarly, data analysts who work with Temporal data may find this information and query examples useful.

Overview

This section provides an overview of the T-ODS technology.

The core of the T-ODS is implemented using database technology, including multiple schemas, tables, stored procedures, views, table-valued functions, and so forth. These database-centered features support the primary capture, storage, and querying capabilities of the T-ODS.

The T-ODS solution also include the T-ODS Snapshot Utility, which supports:

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

A T-ODS Reference Application is provided with the solution to demonstrate:

How data in the T-ODS can be queried directly or viewed together with current operational data in the Ed-Fi ODS
SQL coding examples for implementers to create scripts to fulfill their time-based querying needs

T-ODS processes and queries are supported by the Alliance's MetaEd tool, which generates stored procedures and table valued functions. Additionally, the functions were provided to:

Allow a data analyst familiar with the current ODS database schema to write queries against the T-ODS
For analysts and database administrators requiring direct access to the database, it will be easy to distinguish current data from historical data

Key Concepts

This section covers important conceptual material related to the T-ODS.

T-ODS Patterns and Conventions

The patterns and conventions of the T-ODS are documented in the T-ODS Design & Data Model documentation.

Querying the T-ODS

To simplify querying the T-ODS data tables, table-valued functions are available for each TODS data table:

t_[ODS namespace].ufn_Get[ODS table name]ByDate (e.g., t_edf.ufn_GetStudentByDate). Table-valued function for viewing historical data by AsOfDate - returns data for single date within a snapshot, based on snapshot record effective dates.
t_[ODS namespace].ufn_Get[ODS table name]ByRange (e.g., t_edf.ufn_GetStudentByRange). Table-valued function for viewing historical data by Effective date range - returns data for effective date range within one or more snapshots, based on snapshot record effective dates.
t_[ODS namespace].ufn_Get[ODS table name]BySnapshot (e.g., t_edf.ufn_GetStudentBySnapshot). Table-valued function for viewing historical data by Snapshot. The arguments are passed using a pipe-delimited string:
- Identifier. Returns ALL data within the Snapshot for the given snapshot identifier.
- Snapshot Date. Returns ALL data within the Snapshot for the given snapshot date.
- Snapshot Code. Returns ALL data within the Snapshot for the given snapshot code.

All table-valued functions include only Snapshots that have an active status ([Status] LIKE 'ACTIVE') and are not locked for processing ([IsLockedForProcessing] = 0).

Examples of each are provided below.

The T-ODS Database Utility includes a set of parameter-driven queries that target the T-ODS table-valued functions. These queries are a useful resource that demonstrate key features and functionality available through the T-ODS.

Extending the T-ODS

The T-ODS contains a set of data tables parallel to Ed-Fi ODS data tables – including Ed-Fi Extensions. The T-ODS schema artifacts (e.g., tables, foreign keys, stored procedures) are generated using the MetaEd domain-specific language files plus enhancements to the MetaEd generation capability.

To support the existing ODS extension schema patterns, as well as plans for future namespace extensions, T-ODS data tables are generated in a separate schema for every ODS table generated presently. Therefore, any customizations to the Ed-Fi Core types are supported in the T-ODS as well through the MetaEd generators is achieved through the Ed-Fi Extension Framework.

For example, consider the case where an extension to the ODS edfi.Student entity was created via the MetaEd IDE. The resulting artifacts include scripts to create the ODS extension table and its T-ODS counterpart.

MetaEd generated the following script to create the ODS Student extension:

...
/****** Table: [extension].[StudentExtension] ******/

CREATE TABLE [extension].[StudentExtension](
    [StudentUSI] [INT] NOT NULL,
    [YearOfEntry] [SMALLINT] NULL,
    [AddressAsOfDate] [DATE] NULL,
    [RaceAsOfDate] [DATE] NULL,
    [SchoolFoodServicesEligibilityAsOfDate] [DATE] NULL,
    CONSTRAINT [StudentExtension_PK] PRIMARY KEY CLUSTERED (
        [StudentUSI] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
...

MetaEd also generated the following script to create the T-ODS t_extension schema and the T-ODS Student extensions:

...
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N't_extension')
EXEC sys.sp_executesql N'CREATE SCHEMA [t_extension]'
GO
...

/****** TABLE: [t_extension].[StudentExtension] ******/

CREATE TABLE [t_extension].[StudentExtension]
(
    [tid_StudentExtension] [INT] IDENTITY(1,1) NOT NULL,
    [tid_Student] [INT] NOT NULL,
    [YearOfEntry] [SMALLINT] NULL,
    [AddressAsOfDate] [DATE] NULL,
    [RaceAsOfDate] [DATE] NULL,
    [SchoolFoodServicesEligibilityAsOfDate] [DATE] NULL,
    [t_PartitionYearId] [SMALLINT] NULL
CONSTRAINT [PK_StudentExtension] PRIMARY KEY NONCLUSTERED
(
    [tid_StudentExtension] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];

...

Additionally, any new entities created as an extension also result in an extension in the T-ODS. For example, a StudentTransportation Domain Entity is created via the MetaEd IDE. The resulting artifacts include scripts to create the ODS extension table for the new Domain Entity and its T-ODS counterpart.

MetaEd generated the following script to create the StudentTransporation extension in the ODS:

...
/****** Table: [extension].[StudentTransportation] ******/

CREATE TABLE [extension].[StudentTransportation](
    [SchoolId] [INT] NOT NULL,
    [StudentUSI] [INT] NOT NULL,
    [BusNumber] [NVARCHAR](50) NOT NULL,
    [CreateDate] [DATETIME] NOT NULL, 
    [LastModifiedDate] [DATETIME] NOT NULL,
    [Id] [UNIQUEIDENTIFIER] NOT NULL, 
    CONSTRAINT [StudentTransportation_PK] PRIMARY KEY CLUSTERED (
        [SchoolId] ASC,
        [StudentUSI] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
...

...and also generated a T-ODS counterpart:

...
/****** TABLE: [t_extension].[StudentTransportation] ******/

CREATE TABLE [t_extension].[StudentTransportation]
(
    [tid_StudentTransportation] [INT] IDENTITY(1,1) NOT NULL,
    [SchoolId] [INT] NOT NULL,
    [StudentUniqueId] [NVARCHAR](32) NOT NULL,
    [BusNumber] [NVARCHAR](50) NOT NULL,
    [CreateDate] [DATETIME] NOT NULL,
    [LastModifiedDate] [DATETIME] NOT NULL,
    [Id] [UNIQUEIDENTIFIER] NOT NULL,
    [t_PartitionYearId] [SMALLINT] NULL
CONSTRAINT [PK_StudentTransportation] PRIMARY KEY NONCLUSTERED
(
    [tid_StudentTransportation] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
...

...as well as the related table-valued type and table-valued functions:

... 
/****** TYPE: [t_extension].[udt_tStudentTransportation] ******/

CREATE TYPE [t_extension].[udt_tStudentTransportation] AS TABLE
(
    [SnapshotId] [INT] NOT NULL
    , [t_EffectiveBeginDate] [DATE] NOT NULL
    , [t_EffectiveEndDate] [DATE] NULL
    , [tid_StudentTransportation] [INT] NOT NULL
    , [SchoolId] [INT] NOT NULL
    , [StudentUniqueId] [NVARCHAR](32) NOT NULL
    , [BusNumber] [NVARCHAR](50) NOT NULL
    , [CreateDate] [DATETIME] NOT NULL
    , [LastModifiedDate] [DATETIME] NOT NULL
    , [Id] [UNIQUEIDENTIFIER] NOT NULL
);
GO

/****** FUNCTION: [t_extension].[ufn_GetStudentTransportationByDate] ******/

CREATE FUNCTION [t_extension].[ufn_GetStudentTransportationByDate]
(
    @pDate DATE
)
RETURNS @T TABLE
(
    [SnapshotId] [INT] NOT NULL
    , [t_EffectiveBeginDate] [DATE] NOT NULL
    , [t_EffectiveEndDate] [DATE] NULL
    , [tid_StudentTransportation] [INT] NOT NULL
    , [SchoolId] [INT] NOT NULL
    , [StudentUniqueId] [NVARCHAR](32) NOT NULL
    , [BusNumber] [NVARCHAR](50) NOT NULL
    , [CreateDate] [DATETIME] NOT NULL
    , [LastModifiedDate] [DATETIME] NOT NULL
    , [Id] [UNIQUEIDENTIFIER] NOT NULL)
AS BEGIN
    INSERT INTO @T
    SELECT SR.[SnapshotId]
        , SR.[EffectiveBeginDate]
        , SR.[EffectiveEndDate]
        , T.[tid_StudentTransportation]
        , T.[SchoolId]
        , T.[StudentUniqueId]
        , T.[BusNumber]
        , T.[CreateDate]
        , T.[LastModifiedDate]
        , T.[Id]
    FROM [t_extension].[StudentTransportation] AS T     
    JOIN [t_extension].[StudentTransportationSnapshotRecord] AS SR 
        ON T.[tid_StudentTransportation] = SR.[tid_StudentTransportation]
    JOIN [tods].[Snapshot] S
        ON SR.[SnapshotId] = S.[SnapshotId]    
    WHERE SR.[EffectiveBeginDate] <= @pDate
        AND (SR.[EffectiveEndDate] > @pDate OR SR.[EffectiveEndDate] IS NULL)
        AND (S.[IsLockedForProcessing] = 0
        AND  S.[Status] LIKE 'ACTIVE');
    RETURN
END
GO

/****** FUNCTION: [t_extension].[ufn_GetStudentTransportationByRange] ******/

CREATE FUNCTION [t_extension].[ufn_GetStudentTransportationByRange]
(
    @pBeginDate DATE,
    @pEndDate DATE
)
RETURNS @T TABLE
(
    [SnapshotId] [INT] NOT NULL
    , [t_EffectiveBeginDate] [DATE] NOT NULL
    , [t_EffectiveEndDate] [DATE] NULL
    , [tid_StudentTransportation] [INT] NOT NULL
    , [SchoolId] [INT] NOT NULL
    , [StudentUniqueId] [NVARCHAR](32) NOT NULL
    , [BusNumber] [NVARCHAR](50) NOT NULL
    , [CreateDate] [DATETIME] NOT NULL
    , [LastModifiedDate] [DATETIME] NOT NULL
    , [Id] [UNIQUEIDENTIFIER] NOT NULL
)
AS BEGIN
    -- ERROR CONDITIONS
    -- end date is before begin date
    IF @pBeginDate >= @pEndDate
    RETURN

    INSERT INTO @T
    SELECT SR.[SnapshotId]
        , SR.[EffectiveBeginDate]
        , SR.[EffectiveEndDate]
        , T.[tid_StudentTransportation]
        , T.[SchoolId]
        , T.[StudentUniqueId]
        , T.[BusNumber]
        , T.[CreateDate]
        , T.[LastModifiedDate]
        , T.[Id]
    FROM [t_extension].[StudentTransportation] AS T
    JOIN [t_extension].[StudentTransportationSnapshotRecord] AS SR
        ON T.[tid_StudentTransportation] = SR.[tid_StudentTransportation]
    JOIN [tods].[Snapshot] S
        ON SR.[SnapshotId] = S.[SnapshotId]
    WHERE SR.[EffectiveBeginDate] >= @pBeginDate
       AND (SR.[EffectiveBeginDate] < @pEndDate OR @pEndDate IS NULL)
       AND (S.[IsLockedForProcessing] = 0
       AND  S.[Status] LIKE 'ACTIVE');
    RETURN
END
GO

/****** FUNCTION: [t_extension].[ufn_GetStudentTransportationBySnapshotId] ******/

CREATE FUNCTION [t_extension].[ufn_GetStudentTransportationBySnapshotId]
(
    @pIds tods.udt_IntList READONLY
)
RETURNS @T TABLE
(
    [SnapshotId] [INT] NOT NULL
    , [t_EffectiveBeginDate] [DATE] NOT NULL
    , [t_EffectiveEndDate] [DATE] NULL
    , [tid_StudentTransportation] [INT] NOT NULL
    , [SchoolId] [INT] NOT NULL
    , [StudentUniqueId] [NVARCHAR](32) NOT NULL
    , [BusNumber] [NVARCHAR](50) NOT NULL
    , [CreateDate] [DATETIME] NOT NULL
    , [LastModifiedDate] [DATETIME] NOT NULL
    , [Id] [UNIQUEIDENTIFIER] NOT NULL
)
AS BEGIN
    INSERT INTO @T
    SELECT SR.[SnapshotId]
        , SR.[EffectiveBeginDate]
        , SR.[EffectiveEndDate]
        , T.[tid_StudentTransportation]
        , T.[SchoolId]
        , T.[StudentUniqueId]
        , T.[BusNumber]
        , T.[CreateDate]
        , T.[LastModifiedDate]
        , T.[Id]
    FROM [t_extension].[StudentTransportation] AS T
    JOIN [t_extension].[StudentTransportationSnapshotRecord] AS SR 
        ON T.[tid_StudentTransportation] = SR.[tid_StudentTransportation]
    JOIN [tods].[Snapshot] S 
        ON SR.[SnapshotId] = S.[SnapshotId]
    JOIN (SELECT List FROM @pIds) I
        ON S.[SnapshotId]=I.List
    WHERE SR.[EffectiveBeginDate] <= S.[SnapshotDate]
        AND (SR.[EffectiveEndDate] > S.[SnapshotDate] OR SR.[EffectiveEndDate] IS NULL)
        AND (S.[IsLockedForProcessing] = 0
        AND  S.[Status] LIKE 'ACTIVE');
    RETURN
END
GO

/****** FUNCTION: [t_extension].[ufn_GetStudentTransportationBySnapshot] ******/

CREATE FUNCTION [t_extension].[ufn_GetStudentTransportationBySnapshot]
(
    @pPipedDelimString NVARCHAR(4000)
)
RETURNS @T TABLE
(
    [SnapshotId] [INT] NOT NULL
    , [t_EffectiveBeginDate] [DATE] NOT NULL
    , [t_EffectiveEndDate] [DATE] NULL
    , [tid_StudentTransportation] [INT] NOT NULL
    , [SchoolId] [INT] NOT NULL
    , [StudentUniqueId] [NVARCHAR](32) NOT NULL
    , [BusNumber] [NVARCHAR](50) NOT NULL
    , [CreateDate] [DATETIME] NOT NULL
    , [LastModifiedDate] [DATETIME] NOT NULL
    , [Id] [UNIQUEIDENTIFIER] NOT NULL
)
AS BEGIN
    DECLARE @Ids tods.udt_IntList;

    WITH SnapshotStrings AS
    (
        SELECT CAST(S.Id AS NVARCHAR(1024)) AS IdString
            , CAST(YEAR(S.SnapshotDate) AS NVARCHAR(4)) + RIGHT('00' + CAST(MONTH(S.SnapshotDate) AS NVARCHAR(2)),2) + RIGHT('00' + CAST(DAY(S.SnapshotDate) AS NVARCHAR(2)),2) AS DateString
            , SN.Code
            , S.SnapshotId
        FROM [tods].[Snapshot] S
        JOIN [tods].[SnapshotCode] SN ON SN.SnapshotId = S.SnapshotId
        WHERE S.[IsLockedForProcessing] = 0
        AND S.[Status] LIKE 'ACTIVE'
    )
    INSERT INTO @Ids
    SELECT S.SnapshotId
    FROM (SELECT Strings FROM [tods].[split_string](@pPipedDelimString, '|')) AS P
    JOIN SnapshotStrings S ON S.SnapshotId = COALESCE(CASE WHEN S.IdString = P.Strings THEN S.SnapshotId
                                                           ELSE NULL
                                                      END,
                                                      CASE WHEN (ISDATE(P.Strings) = 1) AND (S.DateString = (CAST(YEAR( P.Strings) AS NVARCHAR(4)) + RIGHT('00' + CAST(MONTH( P.Strings) AS NVARCHAR(2)),2) + RIGHT('00' + CAST(DAY( P.Strings) AS NVARCHAR(2)),2))) THEN S.SnapshotId
                                                           ELSE NULL
                                                      END,
                                                      CASE WHEN S.Code = P.Strings THEN S.SnapshotId
                                                      END)
    GROUP BY S.SnapshotId;

    INSERT INTO @T
    SELECT SR.[SnapshotId]
        , SR.[EffectiveBeginDate]
        , SR.[EffectiveEndDate]
        , T.[tid_StudentTransportation]
        , T.[SchoolId]
        , T.[StudentUniqueId]
        , T.[BusNumber]
        , T.[CreateDate]
        , T.[LastModifiedDate]
        , T.[Id]
    FROM [t_extension].[StudentTransportation] AS T
    JOIN [t_extension].[StudentTransportationSnapshotRecord] AS SR
        ON T.[tid_StudentTransportation] = SR.[tid_StudentTransportation]
    JOIN [tods].[Snapshot] S
        ON SR.[SnapshotId] = S.[SnapshotId]
    JOIN (SELECT List FROM @Ids) I
        ON S.[SnapshotId]=I.List
    WHERE SR.[EffectiveBeginDate] <= S.[SnapshotDate]
        AND (SR.[EffectiveEndDate] > S.[SnapshotDate] OR SR.[EffectiveEndDate] IS NULL)
        AND (S.[IsLockedForProcessing] = 0
        AND  S.[Status] LIKE 'ACTIVE');
    RETURN
END
GO
...

The patterns above are what drive the T-ODS functionality. The MetaEd IDE generates these T-ODS artifacts, including extensions.

These patterns can be duplicated by hand or by a custom code generation tool, but implementers wishing to extend the T-ODS will want to consider the MetaEd IDE for that work.

Query Examples

This section provides basic example queries useful for the T-ODS. For examples that illustrate complex use-cases, see the T-ODS Reference Query documentation.

Table-valued functions used in the following examples are:

Table Valued Function	Comments	Possible Usage
`ufn_GetStudentByDate`	table-valued function for viewing historical data by AsOfDate returns data for single date within a snapshot, based on snapshot record effective dates includes only Snapshots that have an active status (`[Status] LIKE 'ACTIVE'`) and are not locked for processing (`[IsLockedForProcessing] = 0`). result set contains one row per student	point-in-time data analysis based on the Student Record effective date
`ufn_GetStudentByRange`	table-valued function for viewing historical data by Effective date range returns data for one or more Snapshots, based on snapshot record effective date range includes only Snapshots that have an active status (`[Status] LIKE 'ACTIVE'`) and are not locked for processing (`[IsLockedForProcessing] = 0`). result set could contain one or more rows per student	longitudinal data analysis based on the Student Record effective date range
`ufn_GetStudentBySnapshot`	table-valued function for viewing historical data by SnapshotCode, SnapshotDate or Snapshot Identifier (GUID) returns data for single Snapshot, based on a unique parameter identifying a snapshot includes only Snapshots that have an active status (`[Status] LIKE 'ACTIVE'`) and are not locked for processing (`[IsLockedForProcessing] = 0`). result set could contain one or more rows per student	point-in-time data analysis based on the Snapshot Code useful for Snapshot administrators to confirm or debug the snapshot load

Example 1. Query T-ODS: Get Student By As of Date

This example assumes that a Snapshot with a covering the Date of 10/20/2014 has been loaded.

To return ALL t_edfi.Student data for single date within a snapshot, based on snapshot record effective dates, query on the t_edfi.ufn_GetStudentByDate table-valued function:

SELECT [SnapshotId]
    , [t_EffectiveBeginDate]
    , [t_EffectiveEndDate]
    , [tid_Student]
    , [StudentUniqueId]
    , [PersonalTitlePrefix]
    , [FirstName]
    , [MiddleName]
    , [LastSurname]
    , [GenerationCodeSuffix]
    , [MaidenName]
    , [SexType]
    , [BirthDate]
    , [BirthCity]
    , [BirthStateAbbreviationType]
    , [BirthInternationalProvince]
    , [BirthCountryDescriptorCodeValue]
    , [DateEnteredUS]
    , [MultipleBirthStatus]
    , [ProfileThumbnail]
    , [HispanicLatinoEthnicity]
    , [OldEthnicityType]
    , [CitizenshipStatusType]
    , [EconomicDisadvantaged]
    , [SchoolFoodServicesEligibilityDescriptorCodeValue]
    , [LimitedEnglishProficiencyDescriptorCodeValue]
    , [DisplacementStatus]
    , [LoginId]
    , [LimitedEnglishProficiencyDescriptorNamespace]
    , [SchoolFoodServicesEligibilityDescriptorNamespace]
    , [BirthCountryDescriptorNamespace]
    , [CreateDate]
    , [LastModifiedDate]
    , [Id]
FROM [t_edfi].[ufn_GetStudentByDate]('20141020');

Example 2. Query T-ODS: Get Student Information by a Range of Dates

This example assumes that Snapshots with Snapshot effective dates falling within the range from 10/20/2014 through 4/1/2015 have been loaded.

To return ALL t_edfi.Student data for a given date range, query on the t_edfi.ufn_GetStudentByRange table-valued function:

SELECT [SnapshotId]
    , [t_EffectiveBeginDate]
    , [t_EffectiveEndDate]
    , [tid_Student]
    , [StudentUniqueId]
    , [PersonalTitlePrefix]
    , [FirstName]
    , [MiddleName]
    , [LastSurname]
    , [GenerationCodeSuffix]
    , [MaidenName]
    , [SexType]
    , [BirthDate]
    , [BirthCity]
    , [BirthStateAbbreviationType]
    , [BirthInternationalProvince]
    , [BirthCountryDescriptorCodeValue]
    , [DateEnteredUS]
    , [MultipleBirthStatus]
    , [ProfileThumbnail]
    , [HispanicLatinoEthnicity]
    , [OldEthnicityType]
    , [CitizenshipStatusType]
    , [EconomicDisadvantaged]
    , [SchoolFoodServicesEligibilityDescriptorCodeValue]
    , [LimitedEnglishProficiencyDescriptorCodeValue]
    , [DisplacementStatus]
    , [LoginId]
    , [LimitedEnglishProficiencyDescriptorNamespace]
    , [SchoolFoodServicesEligibilityDescriptorNamespace]
    , [BirthCountryDescriptorNamespace]
    , [CreateDate]
    , [LastModifiedDate]
    , [Id]
FROM [t_edfi].[ufn_GetStudentByRange]('20141020', '20150401');

Example 3. Query T-ODS: Get Students By Snapshot

This example assumes the following three snapshots have been loaded:

A snapshot with Snapshot Code 'S02'
A snapshot with the SnapshotDate '20141201'
A snapshot with the GUID 'E6AE95CB-FC32-43D6-AC42-61E93A5FC120'

The bySnapshot function accepts multiple parameters which uniquely identify a snapshot. Users can pass any combination of snapshot codes, snapshot dates and snapshot guids. Parameters are provided via a piped delimited string. The following query would return student records from each of the 3 aforementioned snapshots.

DECLARE @PipedDelimString NVARCHAR(1024);
SET @PipedDelimString = 'S02|20141201|F46A0706-AC0E-4FE6-BCEE-E060971198D1';

SELECT [SnapshotId]
    , [t_EffectiveBeginDate]
    , [t_EffectiveEndDate]
    , [tid_Student]
    , [StudentUniqueId]
    , [PersonalTitlePrefix]
    , [FirstName]
    , [MiddleName]
    , [LastSurname]
    , [GenerationCodeSuffix]
    , [MaidenName]
    , [SexType]
    , [BirthDate]
    , [BirthCity]
    , [BirthStateAbbreviationType]
    , [BirthInternationalProvince]
    , [BirthCountryDescriptorCodeValue]
    , [DateEnteredUS]
    , [MultipleBirthStatus]
    , [ProfileThumbnail]
    , [HispanicLatinoEthnicity]
    , [OldEthnicityType]
    , [CitizenshipStatusType]
    , [EconomicDisadvantaged]
    , [SchoolFoodServicesEligibilityDescriptorCodeValue]
    , [LimitedEnglishProficiencyDescriptorCodeValue]
    , [DisplacementStatus]
    , [LoginId]
    , [LimitedEnglishProficiencyDescriptorNamespace]
    , [SchoolFoodServicesEligibilityDescriptorNamespace]
    , [BirthCountryDescriptorNamespace]
    , [CreateDate]
    , [LastModifiedDate]
    , [Id]
FROM [t_edfi].[ufn_GetStudentBySnapshot](@PipedDelimString);

Example 4. Query T-ODS: JOIN with Current ODS Table Student for a Given Date Range

This example makes a few assumptions:

Snapshots with Snapshot effective dates 1/1/2015 through 6/1/2015 have been loaded
A Student with a StudentUniqueId of 604893 was included in one of the Snapshot loads

To return current data (stored in edfi.Student) and temporal data (stored in t_edfi.Student) for a Student with StudentUniqueId = 604893 for a given date range, query based on the t_edfi.ufn_GetStudentByRange table-valued function UNIONed with the ODS edfi.Student table:

SELECT US.[SnapshotId]
    , US.[t_EffectiveBeginDate]
    , US.[t_EffectiveEndDate]
    , US.[tid_Student]
    , US.[StudentUniqueId]
    , US.[PersonalTitlePrefix]
    , US.[FirstName]
    , US.[MiddleName]
    , US.[LastSurname]
    , US.[GenerationCodeSuffix]
    , US.[MaidenName]
    , US.[SexType]
    , US.[BirthDate]
    , US.[BirthCity]
    , US.[BirthStateAbbreviationType]
    , US.[BirthInternationalProvince]
    , US.[BirthCountryDescriptorCodeValue]
    , US.[BirthCountryDescriptorNamespace]
    , US.[DateEnteredUS]
    , US.[MultipleBirthStatus]
    , US.[ProfileThumbnail]
    , US.[HispanicLatinoEthnicity]
    , US.[OldEthnicityType]
    , US.[CitizenshipStatusType]
    , US.[EconomicDisadvantaged]
    , US.[SchoolFoodServicesEligibilityDescriptorCodeValue]
    , US.[SchoolFoodServicesEligibilityDescriptorNamespace]
    , US.[LimitedEnglishProficiencyDescriptorCodeValue]
    , US.[LimitedEnglishProficiencyDescriptorNamespace]
    , US.[DisplacementStatus]
    , US.[LoginId]
    , US.[CreateDate]
    , US.[LastModifiedDate]
    , US.[Id]
FROM (
    SELECT T.[SnapshotId]
    , T.[t_EffectiveBeginDate]
    , T.[t_EffectiveEndDate]
    , T.[tid_Student]
    , T.[StudentUniqueId]
    , T.[PersonalTitlePrefix]
    , T.[FirstName]
    , T.[MiddleName]
    , T.[LastSurname]
    , T.[GenerationCodeSuffix]
    , T.[MaidenName]
    , T.[SexType]
    , T.[BirthDate]
    , T.[BirthCity]
    , T.[BirthStateAbbreviationType]
    , T.[BirthInternationalProvince]
    , T.[BirthCountryDescriptorCodeValue]
    , T.[BirthCountryDescriptorNamespace]
    , T.[DateEnteredUS]
    , T.[MultipleBirthStatus]
    , T.[ProfileThumbnail]
    , T.[HispanicLatinoEthnicity]
    , T.[OldEthnicityType]
    , T.[CitizenshipStatusType]
    , T.[EconomicDisadvantaged]
    , T.[SchoolFoodServicesEligibilityDescriptorCodeValue]
    , T.[SchoolFoodServicesEligibilityDescriptorNamespace]
    , T.[LimitedEnglishProficiencyDescriptorCodeValue]
    , T.[LimitedEnglishProficiencyDescriptorNamespace]
    , T.[DisplacementStatus]
    , T.[LoginId]
    , T.[CreateDate]
    , T.[LastModifiedDate]
    , T.[Id]
    FROM [t_edfi].[ufn_GetStudentByRange]('20150101', '20150601') AS T
    
    UNION ALL 
    
    SELECT CAST(NULL AS INT) AS [SnapshotId]
    , CAST(NULL AS DateTime) AS [t_EffectiveBeginDate]
    , CAST (NULL AS DateTime) AS [t_EffectiveEndDate]
    , CAST (NULL AS INT) AS [tid_Student]
    , S.[StudentUniqueId] 
    , S.[PersonalTitlePrefix]
    , S.[FirstName] 
    , S.[MiddleName] 
    , S.[LastSurname] 
    , S.[GenerationCodeSuffix] 
    , S.[MaidenName]
    , ST.[ShortDescription] AS [SexType]
    , S.[BirthDate] 
    , S.[BirthCity] 
    , BSAT.[ShortDescription] AS [BirthStateAbbreviationType]
    , S.[BirthInternationalProvince] 
    , BCD.[CodeValue] AS [BirthCountryDescriptorCodeValue]
    , BCD.[Namespace] AS [BirthCountryDescriptorNamespace]
    , S.[DateEnteredUS]
    , S.[MultipleBirthStatus]
    , S.[ProfileThumbnail]
    , S.[HispanicLatinoEthnicity] 
    , OET.[ShortDescription] AS [OldEthnicityType]
    , CST.[ShortDescription] AS [CitizenshipStatusType]
    , S.[EconomicDisadvantaged]
    , SFSED.[CodeValue] AS [SchoolFoodServicesEligibilityDescriptorCodeValue]
    , SFSED.[Namespace] AS [SchoolFoodServicesEligibilityDescriptorNamespace]
    , LEPD.[CodeValue] AS [LimitedEnglishProficiencyDescriptorCodeValue]
    , LEPD.[Namespace] AS [LimitedEnglishProficiencyDescriptorNamespace]
    , S.[DisplacementStatus]
    , S.[LoginId]
    , S.[CreateDate]
    , S.[LastModifiedDate]    
    , S.[Id]
    FROM [edfi].[Student] AS S
    JOIN [edfi].[SexType] ST ON ST.SexTypeId = S.[SexTypeId]
    LEFT JOIN [edfi].[StateAbbreviationType] AS BSAT
        ON BSAT.[StateAbbreviationTypeId] = S.[BirthStateAbbreviationTypeId]
    LEFT JOIN [edfi].[Descriptor] AS BCD
        ON BCD.[DescriptorId] = S.[BirthCountryDescriptorId]
    LEFT JOIN [edfi].[OldEthnicityType] AS OET
        ON OET.[OldEthnicityTypeId] = S.[OldEthnicityTypeId]
    LEFT JOIN [edfi].[CitizenshipStatusType] AS CST
        ON CST.[CitizenshipStatusTypeId] = S.[CitizenshipStatusTypeId]
    LEFT JOIN [edfi].[Descriptor] AS SFSED
        ON SFSED.[DescriptorId] = S.[SchoolFoodServicesEligibilityDescriptorId]
    LEFT JOIN [edfi].[Descriptor] AS LEPD
        ON LEPD.[DescriptorId] = S.[LimitedEnglishProficiencyDescriptorId]
    ) AS US
WHERE US.StudentUniqueId = 604893;

To demonstrate the behavior of the table-valued functions, consider the scenario where the ODS and TODS contain the following data:

Assume the following queries run AFTER the Snapshots S01, S02, S03, S04, S05, S06, and S07 were loaded. For discussion purposes the queries have been labeled Query-A, Query-B, etc.

Demonstrating the behavior of the [t_edfi].[ufn_GetStudentByDate]() table-valued function:

The following is the result set from Query-A: SELECT ... FROM [t_edfi].[ufn_GetStudentByDate]('20141020');

The following is the result set from Query-B: SELECT ... FROM [t_edfi].[ufn_GetStudentByDate]('20150220');

The following is the result set from Query-C: SELECT ... FROM [t_edfi].[ufn_GetStudentByDate]('20150320');

Each query returns all t_edfi.Student rows related to the Snapshot identified by the date parameter value passed to the ufn_GetStudentByDate table-valued function.

Each result set contains one row per student.

Query-A and Query-B result sets contain the same t_edf.Student rows but contain different t_edfi.StudentSnapshotRecord data.

Query-C result set contains two t_edfi.Student rows that are also in the Query-A and Query-B result sets and one t_edfi.Student row that is different. This difference reflects the data change for student with StudentUniqueId 604893: FirstName updated from Cloudy to Sunshine.

The "Get By Date" table-valued functions may be used to view data at a specific point-in-time.

Demonstrating the behavior of the [t_edfi].[ufn_GetStudentByRange]() table-valued function which returns data for an effective date range within one or more snapshots, based on snapshot record effective dates:

The following is the result set from Query-D: SELECT ... FROM t_edfi.ufn_GetStudentByRange('20141020', '20141101');

Since the effective date range passed to the function identifies only the Snapshot S02, the result set of Query-D contains the same data as the Query-A result set.

Executing the function using a wider effective date range returns data for multiple Snapshots, as shown in the result set from Query-E:

SELECT ... FROM t_edfi.ufn_GetStudentByRange('20141020', '20150401');

The query returns ALL t_edfi.Student rows related to ALL Snapshots identified by the date range parameter values pass to the ufn_GetStudentByRange table-valued function.

The result set may contain one or more Snapshots. If multiple Snapshots are included, the result set may contain one or more rows per student as well.

For Query-E, the effective date range passed to the function identifies Snapshots S02, S03, S04, S05, S06, and S07. If a student was snapshotted in one or more of the Snapshots, the result will contain multiple t_edfi.Student rows for the student.

The "Get By Range" table-valued function may be used to view data changes over time.

Demonstrating the behavior of the [t_edfi].[ufn_GetStudentBySnapshot]() table-valued function which returns data for one or more string values provided via a piped delimited string parameter:

The following is the result set from Query-F:

SELECT ... FROM t_edfi.ufn_GetStudentBySnapshot('S02|20141201|E6AE95CB-FC32-43D6-AC42-61E93A5FC120');

The piped delimited string parameter accepts any combination of snapshot codes, snapshot dates, and snapshot GUIDs. In the Query-F SELECT statement, the piped delimited string values represent:

- the SnapshotCode 'S02'
- the SnapshotDate '20141201'
- the Snapshot GUID 'E6AE95CB-FC32-43D6-AC42-61E93A5FC120'

Each value uniquely identifies a Snapshot.

The query returns ALL t_edfi.Student rows related to ALL Snapshots identified by the piped delimited string values passed to the ufn_GetStudentBySnapshot table-valued function.

The "Get By Snapshot" table-valued function may be used for a number of cases:

- pass in a single SnapshotDate to view data at a specific point-in-time
- pass in a single SnapshotCode to view data for a specific snapshot
- pass in multiple snapshot dates and / or snapshot codes and / or snapshot GUIDs to view data at multiple points-in-time or for multiple snapshots

Demonstrating joining current data and temporal data:

The following is the result set for Query-G

SELECT ... FROM (SELECT ... FROM [t_edfi].[ufn_GetStudentByRange]('20141020', '20141101') UNION ALL SELECT ... FROM [edfi].[Student]...)...;

The result set contains current ODS data stored in [edfi].[Student] and temporal data stored in [t_edfi].[Student] for a Student with StudentUniqueId = 604893 for the date range from '20150101' to '20150601'.

The result set is created based a UNION ALL between the result set of the [t_edfi].[ufn_GetStudentByRange] table-valued function and a query of the ODS [edfi].[Student] table.

The current data is the row containing NULL in ALL temporal columns: SnapshotId, t_EffectiveBeginDate, t_EffectiveEndDate, tid_Student

Single Database Implementations

Implementations running a single database such as SQL Azure must perform additional steps to replace a set of internal stored procedures which are used in the default setup for targeting instances of SQL Snapshot or Restored backups as part of the snapshot process. A MetaEd script is included in ..\ODS\TODS\SingleDB\ (where ... refers to your MetaEd output location) which will replace all stored procedures that reference an external database. This script should be placed in the Implementation repository, inside a new folder called "TemporalOds" in the existing folder Ed-Fi-ODS-Implementation/Database/Structure/EdFi. This script will then run after core, and replace the existing functions with functions that support SingleDB.