Introduction

Analytics Middle Tier 2.0 is becoming a full citizen in the Ed-Fi platform in 2020, rather than just a proof-of-concept on the Ed-Fi Exchange. As it grows up, it needs to correct some architectural concerns that came up as feedback from the field. It also needs to be up to par with the latest release of the ODS/API, version 3.3. This document aims to inform about the challenges and elicit feedback on the real-world usefulness of the proposed solutions.

Naming Convention

Requirement

Hold names to under 63 characters for PostgreSQL compatibility.

Design

Currently, none of the objects have a name that violates this constraint. To help avoid problems with future views, it is proposed to either truncate "Dimension" to "Dim" or drop the word altogether. Generally, clarity should be preferred over length when naming objects, hence the question is: how much clarity is lost if we move away from the "Dimension" suffix?

Multi Data Standard Support

Requirement

Support installing the views on ODS databases supporting multiple data standards (2.2, 3.1, 3.2).

Design

Version 1.3.0 added support for Data Standard 3.1, which was used by ODS/API 3.1.1 and 3.2, through the use of the –dataStandard <Ds2 | Ds31>  argument. 

Forcing the user to remember which data standard is installed is sub-optimal. We should be able to detect this implicitly and install the correct version without user input.

1. If table AddressType exists, then install Data Standard 2.  if needed

2. Else if table VersionLevel exists, then install Data Standard 3.1.

3. Else if table DeployJournal exists, then install Data Standard 3.2.

4. Else throw an error: "Unable to determine the ODS database version".

Student, Parent, and Staff Keys

Requirement

The views should expose "Key" fields based on the natural key of the underlying table. 

Design

In the case of StudentDimension , ContactPersonDimension , and UserDimension , the original release used StudentUSI , ParentUSI , and StaffUSI  respectively. The "USI" columns are primary keys and were used by mistake. The "UniqueId" columns are the correct natural keys.

Change all instances of StudentKey , ContactPersonKey , and UserKey  to use the corresponding "UniqueId" column from the source table.

Descriptor and Type Mapping

Requirement

Decouple the views from hard-coded Descriptor and Type values. 

Context

many of the views need to lookup records by Descriptor value - for instance, looking up the Attendance records where a student has an "Excused Absence" or "Unexcused Absence." Because the original developer had access to only a limited dataset, it was not realized that the Descriptor values will vary widely from one implementation to the next. Thus the hard-coding needs to be decoupled, allowing the implementation to provide a mapping from their Descriptor value to the concept used by the Analytics Middle Tier.

In theory, the various "Types" values in Data Standard 2 should provide a more universal constant than the Descriptors. However, some community members report that these too are mutable. Therefore, (a) using Types is not a solution for Data Standard 2, and (b) even those views with hard-coding to Types instead of Descriptors must be modified for greater independence. Note: Type tables were removed in Data Standard 3 precisely because they were not being used in the originally-designed manner. 

Design

Summary

1. Move hard-coded values to a "Constants" table.

2. Create mapping tables that link Descriptors or Types to Constants.

3. Modify all views as needed to join to the Constants and new mapping tables.

Open

Example

In Version 1.x, the StudentEarlyWarningFact view reports on excused and unexcused absences, looking for StudentSchoolAttendanceEvent  records with attendance descriptor values of either "Excused Absence" or "Unexcused Absence".

In version 2, the view would now search for all StudentSchoolAttendanceEvent records whose descriptor maps to the relevant constant. Thus there would be two DescriptorMap  values, one each for "Excused Absence" and "Unexcused Absence." Any school who uses a different term than these two would create a DescriptorMap  record mapping that term to the DescriptorConstant  value of "Absent".

Implications

Those who install the Analytics Middle Tier will need to carefully assess their Descriptors and Types, and then manage the DescriptorMap table (and TypeMap , for Data Standard 2) accordingly. 

Default Mappings

A new command-line Option will be provided to run a script that loads the default Descriptor mapping for the default Ed-Fi descriptors (minimal/populated template descriptors).

Changes to the Student Dimension

Requirements

1. Create a "Student" dimension with a single unique key.

2. Provide intuitive access to student demographics.

Context

Student Dimension Uniqueness

The Early Warning System fact views both assumed that the StudentDimension  would only have a single record for a student. However, a student could be enrolled in multiple schools at the same time, resulting in two records in the StudentDimension for the same StudentKey. This is problematic for the PowerBI Starter Kit, which has a hard requirement for unique StudentKeys.

Demographics in Ed-Fi UDM v2.2

Sources for student demographics:

• edfi.Student  contains sex, Hispanic/Latino ethnicity, economic disadvantaged (Bool), school foodservice eligibility, limited English proficiency.

• One-to-many tables:

  • edfi.StudentCohortYear 

  • edfi.StudentDisability

  • edfi.StudentLanguage

  • edfi.StudentLanguageUse

  • edfi.StudentProgramAssociation

  • edfi.StudentCharacteristic is a generic table, and contains begin/end date

  • edfi.StudentRace 

Demographics in Ed-Fi UDM v3.x

Sources for student demographics:

• edfi.StudentSchoolAssociation  contains School Year, Enrollment Date and Grade Level

• edfi.StudentEducationOrganizationAssociation  contains Sex, Hispanic/Latino ethnicity, and Limited English Proficiency

• There are a series of many-to-many tables to store specific types of multi-value demographic characteristics -  note these can be saved for either the school or the district (or charter, state, ESC, etc.)

  • edfi.StudentEducationOrganizationAssociationCohortYear 

  • edfi.StudentEducationOrganizationAssociationDisability 

  • edfi.StudentEducationOrganizationAssociationLanguage 

  • edfi.StudentEducationOrganizationAssociationLanguageUse 

  • edfi.StudentEducationOrganizationAssociationRace 

  • edfi.StudentEducationOrganizationAssociationTribalAffiliation 

• And there is the generic edfi.StudentEducationOrganizationAssociationStudentCharacteristic table, which has a time Period associated with it.

  • Includes food service eligibility, which was present on Student  as a Boolean in version 1.

Dimension or Fact?

While gender, race, and ethnicity all have strings associated with them, some elements of the demographic and enrollment data are more fact-oriented than dimension-oriented:

• IsHispanic

• IsEconomicallyDisadvantaged

• LimitedEnglishProficiency

• SchoolEnrollmentDate

The ODS does not support slowly-changing dimensions, so there is only ever one current snapshot of these data - one cannot tie them to a date unless referring to the enrollment date... except in the case of food service eligibility because there is a time period.

Foodservice Eligibility

Foodservice eligibility is tracked via a Program Association, which not a demographic in the Ed-Fi Unified Data Model. Therefore it should be removed from demographics and placed in the program association views. For supporting the Power Bi Starter Kit, a new Early Warning System view might be needed that tries to preserve the old StudentDimension in some ways, including flattening the foodservice eligibility into a single Boolean value.

Design Proposal

Summary:

1. Eliminate the idea of a separate "Student Dimension" in the core data collection.

2. Create two new, very similar, dimensions to replace the old StudentDimension:

   1. StudentSchoolDim 

   2. StudentLocalEducationAgencyDim 

3. Combine the data from various edfi.StudentEducationOrganizationAssociationXYZ  tables into a single view, DemographicDim.

4. Create two bridge tables to link student information to the characteristics

   1. StudentSchoolDemographicBridge 

   2. StudentLocalEducationAgencyDemographicBridge 

Rationale for the Student Dimension replacement:

1. Students don't (or shouldn't) exist in isolation from an organization - hence no need for a StudentDim.

2. Across the Ed-Fi data model, there are two different student relationships:

   1. With the school (e.g. StudentSchoolAssociation, StudentDisciplineIncident, StudentGradebookEntry, etc.).

   2. And with the more generic Education Organization - which in the current context of the Analytics Middle Tier, generally means Local Education Agency.

3. In defining meaningful StudentSchool  and StudentLocalEducationAgency  entities, there will be some overlap of fields - but the data could be different. This is an inherently dangerous area of the Ed-Fi data model. If we were to combine the data into a single perspective, then we would be hiding the danger. The data analyst will need to read and understand why there are two "root entities" for data reporting, and then choose which one to use based on their implementation.

4. The Analytics Middle Tier is intended for Local Education Agency use cases. Other use cases can be added in the future as needed to support other types of Education Organizations (e.g. a future view StudentStateEducationAgencyDim ).

Rationale for combining the various characteristic tables into a single point in time view:

1. There are seven (in Data Standard 3+) similar characteristics tables that do not have time periods associated with them

   1. CohortYear

   2. Disability

   3. DisabilityDesignation

   4. Language

   5. LanguageUse

   6. Race

   7. TribalAffiliation

2. And there is one with a time period: StudentCharacteristic

3. Those without a time period can be combined into a single view for "demographics"

4. Those  without a time period can also be included in that view, so long as the data analyst understands that the "Bridge" between the student and the demographics represents "data as of right now".

5. The two with a time period can, in the future, be used to create new Fact views that link to the date range. See Program Views below.

DemographicDim

Ultimately these values come from the edfi.Descriptor  table, although not all descriptors will be here. String values will be used for keys instead of DescriptorId in order to allow combining data from multiple year-specific ODS databases into a single data mart - this would not be possible with the auto-incremented DescriptorId  since that value will differ between ODS database instances.

Structure

Data Standard 2.2 Source Tables

Descriptors for the following tables:

1. StudentCohortYear

2. StudentDisability

3. StudentLanguage

4. StudentLanguageUse

5. StudentRace

6. StudentCharacteristic (where time dates encompass "now")

Student contains "IsEconomicDisadvantaged" in DS 2, whereas this is now one of the "StudentCharacteristics" in DS 3. In order to have parity between the two data standards, the DemographicDim  view therefor needs a hard-coded row that does not come from a table:

Otherwise the sample records will be as with the Data Standard 3+ samples below.

Data Standard 3+ Source Tables

Descriptors for the following tables:

1. StudentEducationAgencyCohortYear

2. StudentEducationAgencyDisability

3. StudentEducationAgencyDisabilityDesignation

4. StudentEducationAgencyLanguage

5. StudentEducationAgencyLanguageUse

6. StudentEducationAgencyRace

7. StudentEducationAgencyTribalAffiliation

8. StudentEducationAgencyStudentCharacteristic (where time StudentEducationAgencyStudentCharacteristicPeriod encompasses "now")

Sample Records

StudentSchoolDim

Data Standard 2.2