Overview

The Ed-Fi Dashboards have a variety of metric calculations for the current school year. Most of the data that populates the dashboard is transient, meaning that when the dashboard is updated new data replaces old data. However, the dashboard metrics are more useful when a user can track trends in attendance, grades, and discipline over time. Many metrics can provide a historical view via an end-of-year snapshot process that can show the metric value for the prior year. 

The end-of-year process outlined in this documentation will guide you through how to prepare the dashboards for the upcoming school year in a way that preserves prior-year information while preparing your system for the new school year. This process is typically run after a school year ends and data from the spring is considered final.

Approach

This section provides details about the end-of-year rollover with links to additional information.

Step 1. Prepare Your Data For Rollover With an End-of-Year Snapshot

The first step in the end-of-year rollover process is to create an end-of-year snapshot. Some Districts load early iterations of "final" grades and attendance data into the dashboards and subsequently correct the data over the course of days or weeks. Once the final grades and attendance data are loaded, your system is ready to create an end-of-year snapshot.

Generally speaking, the end-of-year snapshot process populates the Dashboard Data Warehouse (Dashboard DW), which can contain metric values for different historical snapshot periods. The data model design for the Dashboard DW is a hybrid between a dimensional model (to support the storage of information for longitudinal analysis) and a presentation data layer (to support the rendering of historical drill-down metric detail).

See Creating a Year-End Snapshot and ETL Developers' Guide - Application Configuration for more details.

Determining a Rollover Deadline

You should complete the end-of-year snapshot process before proceeding with the preparation of dashboards for the next academic year. Administrators of an Ed-Fi ODS / API should set a deadline for districts to indicate that their data is ready. This deadline should consider the last instructional day in the spring semester for participating districts.

When setting the end-of-year snapshot deadline, consider the following:

• Ensure that the last instructional day of the has passed for each district in the group.
• Ensure that results from final assessment exams are loaded.
• Ensure that the data has passed any quality assurance reviews and is in a reasonably final state.
• Consider training, especially if it is to be done at the beginning of August, when determining the snapshot deadline.

Step 2. Validate the End-of-Year Snapshot

Once the end-of-year snapshot process is complete, a few things to validate:

1. Prior year values should be available in the database with the current year in the school year column:
   1. domain.StudentSchoolMetricInstance
   2. domain.SchoolMetricInstance
   3. domain.LocalEducationAgencyMetricInstance
   4. domain.StudentSchoolMetricInstanceExtendedProperty
   5. domain.SchoolMetricInstanceExtendedProperty
   6. domain.LocalEducationAgencyMetricInstanceExtendedProperty
   7. domain.StudentSchoolMetricComponent
   8. domain.SchoolMetricComponent
   9. domain.LocalEducationAgencyMetricComponent
   10. domain.SchoolMetricInstanceStudentList
   11. domain.SchoolMetricInstanceTeacherList
   12. domain.LocalEducationAgencyMetricInstanceSchoolList
   13. domain.SchoolMetricHistorical
   14. domain.StaffEducationOrgCategory
2. The MetricSnapshotTaken column in the dw.LocalEducationAgencySnapshot table should be set as TRUE for the current school year. There should also be a new record for the new school year with the MetricSnapshot and MetricSnapshotTaken columns set as FALSE (see Creating a Year-End Snapshot).

Step 3. Update for the New School Year

Once the end-of-year snapshot process completes for all districts and the snapshots are determined to be accurate, the edfi.SchoolYearType table will need to be updated in the source ODS / API. In addition, administrators will need to update the dashboard.LocalEducationAgencySchoolYear table in the ApplicationsDB to reflect the new school year for each district. 

System administrators will determine the date in which the Dashboards will be switched to the next school year. This configuration change is implementation wide, meaning all LEA dashboards need to be rolled over at the same time. The process of soliciting the necessary information from districts to determine an optimal rollover date should be owned by system administrators.

USE EdFi

Update [edfi].[SchoolYearType]
  Set CurrentSchoolYear=1
where SchoolYear=2011

Update [edfi].[SchoolYearType]
  Set CurrentSchoolYear=0
where SchoolYear=2013

Update [/*Provide DDW name here*/].[dw].[LocalEducationAgencySnapshot]
Set MetricSnapshot = 1 
Where LocalEducationAgencyKey in (/*Provide list of districts to be included in this snapshot*/)
      and SchoolYear = 2018 --current school year (2017-2018) prior to rollover

Update [/*Provide ApplicationsDB name here*/].[dashboard].[LocalEducationAgencySchoolYear]
Set [SchoolYear] = 2019,  --new SchoolYear value (2018-2019)
    [EffectiveDate] = '08-01-2018',  --make sure this is the correct date
    [SchoolYearDescription] = '2018-2019'
Where [LocalEducationAgencyId] in (/*Provide districts here*/)

Summary Process

Recapping the detail above, a typical, high-level sequence for performing an end-of-year rollover is as follows:

1. Administrators determine a timeline for the end-of-year rollover process, including deadlines for the completion of end-of-year snapshots and for rolling to the next school year.
2. Administrators communicate the timeline to districts and advise that the district will be expected to confirm when the spring data is final.
3. A database administrator will take pre-snapshot Dashboard DW backups.
4. End-of-year snapshots for all districts, including "year-round" districts must be completed prior to changing the school year in the dashboards. 
   1. A database administrator will run a statement to change the flag in the MetricSnapshot column in the LocalEducationAgencySnapshot table for all appropriate districts to TRUE.
   2. End-of-year snapshots will then be completed during the next build.
   3. A database administrator will create backups of the ODS, Dashboard, and Dashboard DW after the end-of-year snapshot is taken.
5. At the point when the end-of-year snapshots for all districts have completed, the prior-year and current-year metric values will be the same. Administrators may wish to give districts the opportunity to check their end-of-year snapshots prior to rolling the school year forward.
6. Administrators will change the dashboards to the following year (in the example above, 2018-2019), by updating the LocalEducationAgencySchoolYear table in the ApplicationsDB, and by updating the EdFi.SchoolYearType table in the ODS.
7. Once a successful build completes, districts will be notified that the dashboards have been activated for the next school year.

Metric snapshots will be captured at different periodicities depending on the frequency and freshness of the data elements. DimPeriod stores the different periodicities in which metric snapshots will be calculated. Metrics will be snapshotted by Year, Semester, Grading Period, Week, and/or Day. Metric periodicity snapshots vary according to the frequency in which their corresponding data domains are refreshed.