How To: Power Historical Metrics in the Dashboard UI

The Ed-Fi Dashboards support showing historical information, which provides a useful look at trends over time. This information is created by a set of DashboardDW ETL packages implemented in SSIS that populate the Data Warehouse schema distributed with the Ed-Fi Dashboard source. This guide provides how-to instructions on enabling historical and trend metrics.

Populating the Historical Metrics

The DashboardDW ETL packages should be executed after the LocalEducationAgency ETL packages in the build process. The execution of these packages allows historical data to be displayed on the dashboard UI at the Student level as well as the school level. The metrics do not display at the District level at this time. The steps to load the Data Warehouse schema are as follows:

1. Create the DashboardDW database to store historical data. The DashboardDW database is a persistent database that gets created only once (i.e., it does not follow the drop-and-recreate model used by v1.2 of the ODS and DDS).

2. Execute the scripts found in the following location in the Ed-Fi-Core repository at Ed-Fi-Core\Database\Structure\DashboardDW. These scripts create the table structures, stored procedures, and permissions required for the DashboardDW database.

3. Execute the package with the Excel spreadsheet found in Ed-Fi-Core\Database\Data\DashboardDW. This package and spreadsheet will load the dw.PeriodType table in the DashboardDW database. This package leverages an Excel spreadsheet which in turn requires 32-bit SQL Server.

4. Execute the FactAutomateBOY.dtsx package first. All other DashboardDW packages are dependent on this package. It will load data in the following tables: dw.DimPeriod, dw.DimSchool, dw.LocalEducationAgency, dw.DimSchoolYear and dw.LocalEducationAgencySnapshot.

Aside from the first Fact package referenced in Steps 4 above, all other Fact packages are independent of each other and also can be executed in any order. For example, if you only need to view Attendance historical information on the dashboard, you only need to execute the FactAttendance.dtsx package.

The FactMetricInstance.dtsx package is the package that builds the prior year data displayed on the dashboard and is only executed at the end of the school year when a snapshot will be taken of all the data in the Dashboard Data Store at that point in time and is then written to the DashboardDW database. For the Prior Year metrics to be created, the MetricSnapshot column in the dw.LoaclEducationAgencySnapshot table in the DashboardDW database needs to be manually updated to “True”. The FactMetricInstance.dtsx package check to see if this field is flagged as true which means that the Prior Year metrics are ready to be taken. This flag should be set after verification has been given that the data in the ODS is accurate and complete for the school year.

All referenced files are included in the Ed-Fi Dashboards source code distribution. 

Historical Metric Detail and User Interface Examples

This section provides screen captures of the Dashboard user interface along with additional detail about each metric. The different fact packages support the display of historical data for different time periods. Detail is provided for each historical metric below.

Student-Level Attendance Historical

Attendance Historical will display data only for periods that have been reached by the current point in time. If the school year is not complete there will not be historical data for the year. Note that the Days Absent metrics (metricIds 1483 and 1671) do not have historical data at the student level or school level at this time. History can be viewed by:

• Daily Periods
• Weekly Periods
• Grading Periods
• Semester Periods
• Yearly Periods

Course Grades Historical

Course Grade History will display data only for Grading Periods that have been reached by the current point in time. If the Grading Period is not complete there will not be historical data for that Grading Period. All Course Grade metrics will display historical data except for Algebra metrics (MetricId 29). History can be viewed by Grading Period.

Student-Level Course Grades Historical

School-Level Course Grades Historical

Discipline Historical

Discipline Historical data will display only for periods that have been reached by the current point in time. If the school year is not complete there will not be historical data for the year. History can be displayed by:

• Weekly periods
• Grading Periods
• Semester Periods
• Yearly periods

Student-Level Discipline Historical

School-Level Discipline Historical

School-Level Benchmark Historical

Benchmark Historical will display data only for at the campus/school level for each Grading Period that has been reached at that point in time, for each of the Core subject areas. If the Grading Period is not complete there will not be historical data for that Grading Period. Benchmark performance can be displayed for each Grading Period.

School-Level Teacher Attendance Historical

Teacher Attendance Historical will display data only at the campus/school level for Periods that have been reached at that point in time. If the School Year is not complete there will not be historical data for that Year. History can be displayed by:

• Weekly periods
• Grading Periods
• Semester Periods
• Yearly Periods

Student-Level Benchmark Historical

Local Assessment Benchmark historical will display only at the student level for each Grading Period at the end of the School year. The Dashboard metric package calculates the current year historical for each grading period for each of the Core subject areas and displays at the end of each Grading Period.