ETL Developers' Guide - Coding

This section covers the development tasks involved in adding, editing, and extending metric calculations to the ETL Application. 

Before you resort to coding, though, definitely look over the options for application configuration and metric metadata configuration. Many changes such as changing the reporting period for attendance, adjusting the date after which students are considered late enrollments, and so forth can be accomplished without coding.

Having said that, coding is required if you need to add a metric calculation that relies on data that is not part of the core data set. In Ed-Fi parlance, if you've extended the data standard and want to derive metrics from extended data fields in an Ed-Fi ODS / API, then you must modify or extend the code. 

ETL Coding Basics

The best way to extend the ETL code is by example, using existing code as a starting point. There are a few basic things you will need to do to begin.

Get the Code

See the Dashboard Installation documentation for instructions on how to download the source code and get the Ed-Fi Dashboards running on a development machine. Once you have the Dashboards up and running with the sample databases, the ETL Application Installation section can help you compile and run the ETL application against your own customized database instance.

Understand the ETL Solution Organization

The Ed-Fi Dashboard distribution comes with the source code for the ETL Application solution. The table below describes the projects and directories that make up the ETL Application solution.

Understand EdFi.Common.Eventing

Understanding the eventing framework employed in the ETL Application is fundamental to extending the codebase. The basics are straightforward. The ETL runs on an eventing framework where a common IBus instance is shared to publish messages. 

    public interface IBus
    {
        void Publish(IEnumerable<object> messages);
    }

These messages can then be streamed by one or more interested objects using the IStream interface.

    public interface IStream<in TInput>
    {
        void OnStreamBegin(TInput message);
        void OnStreamEnd(TInput message);
    }

Once you understand these basics, you can delve into the Readers, Translators, and Writers that make up the ETL application.

Readers

The Reader operations get the data needed from the ODS. There are two main parts to the Reader: the query and the implementation.

The core set of metric calculations use several types of ODS data including attendance, discipline, grades, and assessments – therefore, Readers may already exist for a new metric calculation you are considering. Depending on the data you need from the ODS, it may be beneficial to reuse an existing Reader versus creating a new one. If you must create a new Reader, it is recommended that you use an existing query to start.

Reader Queries

These are the SQL scripts that actually extract the data from the source ODS. Because the ETL application is compatible with multiple ODS versions, these scripts are contained in version-specific folders:

EdFi.Runtime\Reading\Queries

Below is an example of a Reader query (for ODS v3.1) for Student Attendance events:

DECLARE @excused INT, @unexcused INT
SELECT @excused = AttendanceEventCategoryDescriptorId FROM edfi.AttendanceEventCategoryDescriptor ct INNER JOIN edfi.Descriptor d ON ct.AttendanceEventCategoryDescriptorId = d.DescriptorId WHERE CodeValue = 'Excused Absence'
SELECT @unexcused = AttendanceEventCategoryDescriptorId FROM edfi.AttendanceEventCategoryDescriptor ct INNER JOIN edfi.Descriptor d ON ct.AttendanceEventCategoryDescriptorId = d.DescriptorId  WHERE CodeValue = 'Unexcused Absence'

SELECT
     stu.StudentUniqueId as ParentId
    ,ssae.EventDate
    ,ttd.TermDescriptorId as TermDescriptor
    ,ssae.SchoolYear
    ,aecd.AttendanceEventCategoryDescriptorId as AttendanceEventCategoryDescriptor
    ,ssae.AttendanceEventReason
FROM edfi.Student stu
    INNER JOIN edfi.StudentSchoolAttendanceEvent ssae ON ssae.StudentUSI = stu.StudentUSI
    INNER JOIN edfi.[Session] sess
        ON sess.SchoolId = ssae.SchoolId
        AND sess.SchoolYear = ssae.SchoolYear
        AND sess.SessionName = ssae.SessionName
    INNER JOIN edfi.TermDescriptor ttd ON ttd.TermDescriptorId = sess.TermDescriptorId
    INNER JOIN edfi.AttendanceEventCategoryDescriptor aecd ON aecd.AttendanceEventCategoryDescriptorId = ssae.AttendanceEventCategoryDescriptorId
    INNER JOIN ##CurrentStudentSchoolAssociation$runtimeId ssa
        ON ssa.SchoolId = ssae.SchoolId
        AND ssa.StudentUSI = ssae.StudentUSI
        AND ssa.LocalEducationAgencyId = @currentLeaId
ORDER BY
    ssa.LocalEducationAgencyId
    , ssae.SchoolId
    , ssae.StudentUSI
    , [EventDate]
    -- for times when there is an excused and unexcused absence on the same day; count the excused absence first (Business Rule: Conflicting attendance events)
    , (CASE aecd.AttendanceEventCategoryDescriptorId WHEN @excused THEN 0 WHEN @unexcused THEN 1 ELSE 2 END)

Reader Implementations

These are classes that use the Reader queries to hydrate the object models (see the EdFi.ObjectModel project) to be used in the application (for the Translators as well as for other dependent Readers). 

EdFi.Runtime\Reading\CoreReaders

Most standard Reader implementation classes will implement the BaseReader and the specific IStream object models needed. Some Reader implementations also include global validation, for example, the StudentAttendanceEventReader class checks for attendance events with dates in the future and issues a warning if any matching records are found.

        public IEnumerable<object> ReturnChildrenOf(Student input)
        {
            foreach (var attendanceEvent in GetAttendanceEvents(input))
            {
                if (attendanceEvent.EventDate > DateTime.Today)
                {
                    _logger.Warning("Found a future attendance event: {@attendanceEvent}", attendanceEvent);
                    continue;
                }
                if (!_schoolCalendarDates.Contains(attendanceEvent.EventDate))
                {
                    _logger.Warning("Found an attendance event that falls on a non-instructional day: {@attendanceEvent}", attendanceEvent);
                    continue;
                }

                var studentSchoolAssociation = _studentSchoolAssociations.FirstOrDefault(ssa => attendanceEvent.EventDate.IsInRange(ssa));
                if (studentSchoolAssociation == null)
                {
                    _attendanceEventsForStudentsNotActive++;
                    _logger.Verbose("Found an attendance event for a student not currently enrolled in the school: {@attendanceEvent}", attendanceEvent);
                    continue;
                }
                attendanceEvent.StudentSchoolAssociation = studentSchoolAssociation;

                yield return attendanceEvent;
            }
        }

Translators

Once the Readers that will inform the metric calculations have been created or identified, then the Translators are created to implement the business logic of the metric calculation. 

Each project in the Container Metrics directory represents a set of metrics that rely on some type of logical grouping of calculations. This promotes consistency across similar metrics as well as shared use of common elements. For example, the StudentAttendance project has Translators to calculate the Daily Attendance Rate, Days Absent, Tardy Rate, and Class Period Absence Rate among others – all of which rely on common logic in the AttendanceEventCalculator class.

Typically, development of a new metric calculation would start at the student level. The Translator should implement any of the IStream object models needed to calculate the metric. Below is an example of the AttendanceEventCalculator class from which most of the attendance-related Translators derive.

    public abstract class AttendanceEventCalculator :
        IStream<School>
        , IStream<Student>
        , IStream<StudentSchoolAssociation>
        , IStream<StudentSectionAssociation>
        , IStream<StudentAttendanceEvent>
        , IStream<SchoolCalendar>
        , IStream<EducationOrganizationGoal>
        , IStream<AppStartEvent>

Once the student-level Translators have been developed, the school-level and district-level rollups typically follow. Along with the other needed IStream object models, these Translators will implement the IStream of the related student-level MetricInstance. For example, the SchoolDisciplineOffenseCalculator class from which the school-level discipline Translators derive implements the StudentDisciplineMetricInstance.

    public abstract class SchoolDisciplineOffensesCalculator :
        IStream<StudentDisciplineMetricInstance>
        , IStream<School>
        , IStream<EducationOrganizationGoal>
        , IStream<PreviousYearSchoolMetricInstance>

Writers

The final step in the ETL process is for the results of the metric calculations to be written to a database. The ETL employs buffering to make writing to the database efficient. Just as there are object models for the source tables, there are classes for the destination tables as well.

Adding Custom Tables for Writing

In the event you need to extend the core Ed-Fi Dashboard UI, you may require a different table to be populated. In this case, you will need to execute the T4 template against a database with your new table. 

Testing

The ETL Application has an extensive set of tests for ensuring the accuracy of metric calculations.

SpecFlow Unit Tests

In order to verify the metric calculation logic contained within the Translator addresses the business requirements, the ETL solution uses SpecFlow unit tests. See the SpecFlow documentation for details on how to write SpecFlow tests. The SpecFlow tests are contained within the EdFi.MetricBusinessLogic.Tests project and are organized in folders named by the Container Metrics project for which the tests provide coverage. Documentation for the as-shipped SpecFlow tests is available online at https://dashboards.ed-fi.org/SpecFlow/. 

Integration Tests

While the SpecFlow unit tests provide test coverage of the Reader implementations and Translator business logic, they do not provide coverage for the Reader queries because they don't actually query data from the database. The integration tests are simplified SpecFlow tests that have scenarios written against a specific test set of ODS data. 

Running Integration Tests

Given the extensive test coverage present in the ETL solution, the integration tests can take a while to run (40 minutes to an hour). You may find it useful to limit which test are run locally during the development cycle. To run only certain tests against, for example, the ODS 3.2 database, edit TestConfiguration.cs to specify which extension (i.e., Container Metric project output) has their integration tests run:

ExcludeExtensionList = GetExtensionExclusionList("StudentAssessments");

This will still attempt to run all tests, but only the specific extensions will be included in the runtime.

ETL Bootstrapping

There are a few instances where, in order for everything to play nice in the ETL, there are operations that need to happen before the metric calculations start chugging away. Examples include Dapper mappings, getting the current school year, and clearing Dashboard tables from previous runs. The ETL application has a hook to allow these operations to execute successfully before publishing the AppStartEvent onto the bus.

See Program.cs in the EdFi.Runtime project of the solution for details.

Coding Details

The following sections in this documentation contain additional information and examples to get you started with coding for the ETL Application: