...

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. The release notes for 2.0 will provide a script that maps the default Ed-Fi Descriptors and Types as a starting point.

Student Dimension Uniqueness

Requirement

The Early Warning System fact views both assumed that the StudentDimension  would only have a single record for a student, as shown in these diagrams:

Image AddedImage Added

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.

At the same time, the Program fact and event views are looking at a Student in a given LocalEducationAgency, whereas the facts above are looking at a Student at a School. Although the ODS database supports a Student enrolled in a Program associated with a School, our input from the field tells us that Program association is on the LEA not the school. Thus we need to balance these competing tensions in the current model.

Design

Data Standard 3 moved the student demographics into the StudentEducationOrganizationAssociation  table, away from the Student table. The current StudentDimension  view provides student name, primary contact, and demographics. The proposal is to split this into two different views, change the Early Warning System star schema to a snowflake.

The demographics on the view are secondary to the student-school relationship. A data analyst might naturally look for school enrollment data. Therefore we will have a new view called StudentEnrollment to show active enrollment/school association information, with a StudentEnrollmentKey  that combines the StudentUniqueId  and SchoolId . The early warning views would then change to use the single StudentEnrollmentKey  instead of the two separate StudentKey  and SchoolKey  columns.

The StudentDimension  would continue to exist in a reduced form, providing only name and primary contact information. The Program views would continue to have the StudentKey  in them.

<create diagrams here>

Problem

What if someone wants to build queries that analyze program participation by demographic data?