Overview of the ETL Application Runtime Process

This article provides an in-depth look at the Ed-Fi ETL Application runtime process. It covers the ETL bootstrap processes and the main ETL process to extract data from an Ed-Fi ODS, calculate metrics on ODS data, and write metrics to the dashboard data store (DDS) and dashboard data warehouse (DW).

Background

This article assumes you have completed the setup instructions the ETL Developers' Guide - Installation section.

Before diving in, you may find it useful to get an overview of Dapper .NET (see, e.g., http://stackexchange.github.io/dapper-dot-net/) and StructureMap (see, e.g., http://structuremap.github.io/) if you're not already familiar with those technologies.

ETL Process Diagram

The diagram below outlines the steps involved for a complete ETL run. The sequence at the bottom of the diagram represents an example using the calculation for student attendance — but there are many more Readers and Translators in the ETL Application for calculating grade metrics, assessment metrics, discipline metrics, and so forth.

The sections that follow provide details about each step.

ETL Runtime Process

Executing the EdFi.Runtime.exe starts the ETL process. (See the Running the ETL Application section of the ETL Developers' Guide for complete details.)

Load ODS Enumerations

Since all ODS types were replaced with descriptors in v3, there aren't many enumerations to cache. However, there are a few type enumerations from the Application database that are cached, such as CourseLevelType. By convention, the enumeration class names must match the table name in the Application database. These enumerations are located throughout the ETL application, mainly for calculating metrics.

Load ODS Descriptors

The ETL will cache the ODS Descriptors from the ODS database. All cached descriptor collections are located in the EdFi.ObjectModel\Ods\Descriptors.tt. Additionally, these cached descriptors will be mapped through the operational context if that was set up properly. (See the Configure Dashboard Operational Context how-to guide for more information.)

By convention, the descriptor class names must match the table name in the ODS database.  The ODS descriptors are located throughout the ETL application for calculating metrics and reading data from the ODS.

Load Required Runtime Assemblies and Extensions

The ETL Application uses the StructureMap IoC container to load all the required assemblies for running the ETL process. The dependency resolution logic is located in the EdFi.Runtime\DependencyResolution folder. It includes the logic for ordering stream subscribers on the bus. The logic will load any core metric Translator projects or extensions located in the Core Metric solution folder.

Subscriber Ordering

The subscriber stream ordering ensures that one subscriber is called before the other, if one depends on the other. For example, in the core metric solution folder there are student-, school-, and LEA-level Translators. If a student and school Translator both listen to a streamed school object, the student-level Translator's OnStreamBegin(School message) method will be called before the school Translator's OnStreamBegin(School message) method by the subscriber order convention. This ensures that the student-level Translator can wrap up any metric calculations before passing results up to the school-level Translator.

SubscriberOrder Attribute

The SubscriberOrder attribute overrides the implicit subscriber stream ordering on the bus. For example the core Reader StudentSectionAssociationReader depends the objects streamed by the StudentSchoolAssociationReader, however both Readers steam objects that are a child of a Student object and does provide the dependency implicitly.

The SubscriberOrder attribute explicitly sets the stream ordering to allow the StudentSchoolAssociationReader to publish its StudentSchoolAssociation objects first, which allows the StudentSectionAssociationReader to listen to those objects.

Year End Snapshot

If the application configuration variable RunOnlyYearEndSnapshot is set to true, then the ETL will copy the current school year data from DDS to the DW. Then, it will exit the EdFi.Runtime.exe and not perform metric calculations on ODS.

Bootstrap Loader Process

The bootstrap processes the necessary steps to ensure a successful ETL execution. The bootstrap logic is located in the EdFi.Runtime\Boostrap folder.

Add SQL Dapper Type Handlers

The ETL uses the ORM SQL Dapper to read data from the ODS. The SQL Dapper type handlers extend the Dapper reading logic to convert comma-separated lists returned by SQL queries into object lists in C#.

Add SQL Table Types

Creates table types for holding temporary data that will be merged into the DDS or DDW using the SQL MERGE statement. Any objects that inherit the IGetMerged interface have the ability to update or insert data in the DDS or DDW.

Toggle Foreign Keys 

Before the ETL operation can start writing metric data into the DDS, the foreign key check constraints need to be disabled. Data that is inserted into the DDS by ETL may not be in the correct order for the foreign key constraint. This allows all data to be inserted before the foreign keys are re-enabled at the end of the ETL process.

Clear DDS Data

This operation clears all data in the DDS for a new ETL operation.

Clear Current Year DW Data

If the application configuration variable RebuildHistoricalCalculations is set to true, the ETL will delete the current school year data from DW. This allows the ETL to rebuild the all the historical data for the current school year. This is useful when historicals need to be recalculated for historicals that have already been calculated for a previous day.

Load Dashboard Types

This operation loads the metric metadata into the DDS by reading the metadata CSVs in the EdFi.Runtime\Reading\Queries\{OdsVersion}\DashboardTypes folder.

Execute Bootstrap Queries

This operation will execute any SQL scripts located in EdFi.Runtime\Reading\Queries\{OdsVersion}\Bootstrap\{Database} to initialize temp tables, views, historical dimensional data, Parent/Staff/Student USI mapping tables, and so forth for the ETL operation. If the scripts need to access a different database than the one currently being executed on, the variables $ODS, $APPLICATION, $DASHBOARD, $WAREHOUSE, and $ETLLOGDB can be used to target the other database.

Initialize DW USI Type Handlers

The DW USI Type handlers provide the mechanism for mapping Student, Staff, and Parent USIs between the ODS and DDS/DW databases. It creates the SQL Dapper type handlers specifically for setting the StudentUSI, ParentUSI, and StaffUSI properties in any objects that implement them.

Create USI Mapping Temp Tables

This operation creates global temp tables containing Student, Staff, and Parent USI mappings between the ODS unique ID and USI ID in the Warehouse table. The temp tables are used in Reader queries for mapping the ODS Student, Staff, Parent unique IDs and USI IDs stored in the DW.

Set System School Year

This operation pulls the current school year from the ODS from the edfi.Session table. It sets current year in the CurrentSchoolYear singleton object for use by Readers and Translators.

ETL Metric Calculation Process 

This section walks through an example calculation.

Calculating Student Attendance Example

The metric calculation process starts when the AppStartEvent is published to the bus.

• When the LocalEducationAgencyReader receives the AppStartEvent on the bus, it will query the ODS for all LEAs and publish them one by one.
• When the SchoolReader receives a LocalEducationAgency object on the bus, it will query the ODS for all the schools that belong to the LEA and publish them one by one.
• When the StudentReader receives a School object on the bus, it will query the ODS for students that belong to the school and publish them one by one.
• When the StudentAttendanceEventReader receives a Student object on the bus, it will query the ODS for all attendance events that belong to the student and publish them one by one.

As the student attendance Translator first receives all the LEA, School, Student, and Attendance events, the OnStreamBegin method for each object type will be called.

When OnStreamBegin is called with an LEA object, the Translator knows that this is a new LEA object. Then, when the OnStreamBegin method is called with the School object, the Translator knows that all schools streamed belong to that LEA. The same logic is used for the student and their attendance events.

When OnStreamEnd is called with a Student object, the Translator knows that all the attendance events received belong to the student. The Translator will calculate the attendance rate for the student and publish the metric calculation to the bus. When the OnStreamBegin is called with a new Student object, the Translator clears out all the attendance events and other data for the previous Student object and starts the calculation process over again. It will continue to calculate the attendance metrics for all students in the ODS until the last LEA is finished and the ETL Application runtime process ends.

All Translator calculations in the ETL Application runtime follow similar pattern as the student attendance calculation example above.

Bus Eventing Architecture 

The logic for the Bus is located in EdFi.Runtime\Eventing folder. Any classes that implement the IStream<T> or IReadChildrenOf<T> interfaces can be called by the Bus when the specific object (T) is published to the Bus. The Bus architecture resembles a stack where the first object placed on the Bus is the last object removed from the Bus. The AppStartEvent will be the first and the very last event placed on the Bus. The bus also logs any exceptions that are thrown by core Readers and Translators. If a fatal exception is thrown, then the ETL Application runtime will exit with an error code.

Core Readers

The logic for the core Readers is located in EdFi.Runtime\Reading\CoreReaders folder. The Readers are responsible for reading objects from the ODS and setting up the stream hierarchy on the Bus. Many Readers inherit the base class BaseReader<TParent, Child, TParentId>. The base takes the parameters for setting relationship between parent and child objects, for example, an LEA parent object would have schools as child objects.