T-ODS / T-API Reference Application

Introduction

The T-ODS Reference Application is provided for ODS / API implementers as a practical demonstration of key features and functionality available through the T-ODS. Implementers may find the application useful for initial testing of a T-ODS installation, and developers can refer to the source code for examples on how to code applications for the system.

The Reference Application includes two modules, the Database Utility and T-API Utility. The initial version of the reference application includes the Database Utility which demonstrates how data in the the temporal operational data store (T-ODS) can be queried directly or viewed together with current operational data in the Ed-Fi ODS. Sample queries and user interface views have been included as examples to highlight the additional insights available with a time-based dimension layered on the Ed-Fi data structures.

Future versions will include additional functionality providing T-API interactivity — including snapshot metadata creation, maintenance, and CRUD-based operations on T-ODS historical data.

Database Utility Features

The T-ODS Database Utility is a Web application distributed as source code. Installation and setup instructions can be found below. The distribution is optimized to run locally on a developer machine. This section outlines the key features of the Database Utility.

The /wiki/spaces/EDFIODS/pages/19727812 and /wiki/spaces/EDFIODS/pages/19726823 documentation contains useful background for the information that follows.

T-ODS Queries

The Database Utility includes a set of parameter-driven queries. These queries primarily target T-ODS table-valued functions. The user interface allows targeting a set of tables joined at the aggregate root level. For example, the Student aggregate root would include Student Address, Student Language, and so forth. Queries can also be targeted against the individual tables. Results can be filtered based on user-entered filter criteria, and tables and columns can be shown or hidden.

Building queries in the UI dynamically generates SQL for the T-ODS which is also shown in the UI. That SQL can be a useful starting point for your own queries.

Queries by As-Of Date

This query identifies records for a given aggregate or aggregate subset based on a given point in time, which in T-ODS terms is called an "as-of" date. Records returned are those where the provided "as-of" date falls within the snapshot record effective dates. For example, an as-of date query on Student for "1/15/2017" will return records with an effective begin date on or before 1/15/2017 AND an effective end date after 1/16.

The first four columns of the search results include metadata about the snapshot to which the historical record belongs. IsLocked, Effective Begin Date, Effective End Date and Snapshot Id are always shown in the results.

The number of results returned is capped based on the value selected in Result Length on the search criteria screen. The Show SQL/Hide SQL button provides the user with the exact SQL statement used. Clicking New Query returns the user to the previously entered Search Criteria options.

Queries by Effective Date Range

This query identifies all records within snapshots where the snapshot's effective begin date and effective end date falls within the supplied date range. For example, an effective date range query on Student with an effective begin date of 1/15/2017 and an effective end date of 3/15/2017 will return records for Snapshots where the effective begin date is greater than or equal to 1/15/2017 and where the effective end date is less than 3/15/2017.

This query may return results from more than one snapshot, if applicable.

Queries by Snapshot Identifiers

This query returns records for a specific snapshot using the Snapshot Identifier or GUID for the snapshot. Users can select from a list of snapshots in the Snapshot Ids drop-down. The list provides the snapshot identifier in parentheses to the right of the snapshot code.

Queries by Snapshot Codes

This query returns records for a specific snapshot using the snapshot code to uniquely identify a single snapshot. Users can select from a pre-populated list of snapshot codes in the targeted T-ODS in the Snapshot Codes field.

By Snapshot Dates

This query returns records for a specific snapshot using the snapshot date to uniquely identify a single snapshot. Users can select from a pre-populated list of snapshot dates in the T-ODS in the Snapshot Dates field.

Setting filters and hiding columns or tables

Filtering the T-ODS query results is accomplished by clicking on the Filters link next to the table you'd like to filter and entering data to filter. Filters require exact entries for accurate matches.

Hiding Tables and Columns

To show or hide tables, switch the toggle to the left of the table name to the off position. This will affect both the information shown in the UI and the dynamically generated SQL.

To show or hide specific columns, switch the toggle to the right of the column name to the off position. To hide all columns, switch the toggle to the right of the Table Name Columns toggle at the top of the Filter dialog box to the off position.

Scenario-Based Queries

Additional model queries have been included to provide examples of how basic, historical trend information can be queried against the historical data tracked in the T-ODS. The queries in the system are designed to model common use cases for temporal data, and can be useful starting points for your own queries.

In the UI, these model queries are listed under "Scenarios."

Program Participation and Evaluation

This scenario provides a simple example of evaluating a single student's performance based on participation in a program. A program, student, and academic subject related to the program must be present in the targeted T-ODS and selected in the UI. Results returned include all historical grades for the selected academic subject for the selected student.

Student Discipline

This scenario provides a view comparing student discipline events year over year for a current cohort of students, along with an indication on whether discipline events are trending up or down for each student in the cohort.

In this case, the cohort is a section of enrolled students. The user selects the section, and the query results return discipline counts for the current year and previous year. The current year count is derived from the transactional ODS (i.e., the current-year data) by tallying incidents between August 1st and the user-provided date. This is compared with the count of incidents in the T-ODS from August 1st of the previous school year to up to the user-entered date in the previous year. For this example, we assume a school year is between August 1st - July 31st.

Effect of being Economically Disadvantaged

This scenario displays performance data related to discipline, attendance, grades and assessments for students who at any point in their T-ODS history had been flagged as economically disadvantaged.

Setup and Configuration

This section describes how to get up and running with the Database Utility.

Finding the Reference Application

The Database Utility Reference Application is included in the Ed-Fi-ODS repository on GitHub. Once you've cloned or downloaded the repository, the Database Utility can be found in the Utilities folder:

\Ed-Fi-ODS\Utilities\EdFiTemporalOds\EdFi.Ods.Tods.DatabaseReferenceApplication\

Building the Reference Application

Open the project solution and Build in Visual Studio 2015 or later.

Running the Reference Application

The reference application can be run either by selecting Start in Visual Studio (make sure EdFi.Ods.Tods.DatabaseReferenceApplication.Web is the Start Up Project) or by locating the build in the bin folder and running EdFi.Ods.Tods.DatabaseReferenceApplication.Web.exe.

Configuring Database Connectivity

By default, the Reference Application is configured to point to the EdFi_Ods_Sandbox_populatedSandbox that is installed with the build.

To modify the connection string when running the app out of Visual Studio, open up the app.config and change the connection string to point to the desired database. 

If running from the .exe application, update the connection string found in EdFi.Ods.Tods.DatabaseReferenceApplication.Web.exe.config.

Technical Notes

The majority of the code for retrieving the SQL and scenarios can be viewed in the EdFi.Ods.Tods.DatabaseReferenceApplication.Resources. This project wraps the actual SQL used to support individual scenarios and the general use of the table-valued functions and stored procedures. If you're just looking the raw SQL, the UI output is still the best resource as it will provide the raw SQL run for a particular query or scenario -- leveraging pre-built and helper functions in the database.

This reference application is specifically targeted at the core Ed-Fi Data Standard, with a focus on v2.0 and v2.1 RFC versions. In many cases, changing the data standard or adding extensions will not break the core features, but certain more fine-tuned cases (like the scenarios) may not to work if the underlying entities involved are modified or removed.