Overview

The Ed-Fi Dashboards v1.3.1 application supports implementers who wish to remain on v1.2 of the Ed-Fi ODS but take advantage of the latest dashboard code. The approach uses a technique called synonyms to abstract naming differences between v1.2 and v2.0 of the Ed-Fi ODS from the Dashboard ETL, thus enabling one code base to support both versions of the schema. 

Actual field implementation of this feature relies on SQL scripts to make non-breaking additions to the Ed-Fi ODS. The scripts are not part of the core dashboard code, but can be found on the Ed-Fi Exchange. This documentation provides a conceptual overview of the the approach, and the Exchange provides detailed instructions on how to apply the SQL scripts.

The approach to use synonyms for the v1.2 implementation was chosen to support Dashboard ETL maintainability for current implementations and forward compatibility with future versions. For example, the Dashboard ETL will have a seamless integration with the upcoming ODS / API v2.0. In addition, the forward compatibility supports use of the current ODS / API v2.0 (Beta) via the Compatibility View work available on the Ed-Fi Exchange.

Technical Approach

This compatibility approach entails the creation of synonyms for all tables in the v1.2 ODS with a new database schema named ods. The new schema is essentially a wrapper around the existing edfi schema, and so does not change any existing tables. The script 0001-Create ODS Schema and Synonym.sql was used to effect the synonyms. This script can be found in the following location for v1.2 implementations ...\Ed-Fi-Core\Database\Structure\EdFi. The script is also attached for convenience.

It also involved the replacement of the edfi database schema in the dashboard packages that pointed to the tables in the v1.2 ODS, with the ods schema that points to the synonyms created on the v1.2 ODS. The script ReplaceEdFi.ps1 was used to do the replacement of schema names.

The following code sample provides an example of the synonym approach. Note the change from edfi database schema to ods. 

SELECT EducationOrganizationId,
       IdentityCourseCode,
	   SUBSTRING(CourseTitle,1,45) As CourseTitle,
	   edfi.AcademicSubjectType.CodeValue As SubjectArea
FROM edfi.Course
INNER JOIN edfi.AcademicSubjectType
	ON edfi.AcademicSubjectType.AcademicSubjectTypeId=edfi.Course.SubjectAreaTypeId
ORDER BY EducationOrganizationId, IdentityCourseCode

SELECT EducationOrganizationId,
       IdentityCourseCode,
	   SUBSTRING(CourseTitle,1,45) AS CourseTitle,
	   CAST(AST.Description AS NVARCHAR(50)) AS SubjectArea
FROM ods.Course C
INNER JOIN ods.AcademicSubjectType AST
	ON AST.AcademicSubjectTypeId = C.SubjectAreaTypeId
ORDER BY EducationOrganizationId, IdentityCourseCode

Components Affected

All dashboard and dashboardDW packages were affected by this replacement. These packages now reference the new ods schema (i.e., the schema implementing the synonyms) in the source ODS data store. Note that this change did not result in a change to the metric calculation logic or the Dashboard UI code, only the ETL populating the dashboard data stores from an Ed-Fi ODS.

Action Necessary for Implementers

This change will need to be consumed by all implementations who choose to upgrade to the v1.3.1 Dashboards. This can be consumed by pulling the latest code from the Ed-Fi-Core repository that has the script to create the synonyms as well as the updated ETL packages.

For ODS v1.2 implementations using the drop-and-restore model for their ODS implementation, the script will be automatically executed once a dashboard build is started. No action will be necessary to effect this change.

For ODS v2.0 implementations, see How To: Load Ed-Fi Dashboards v1.3.1 from an Ed-Fi ODS v2.0 Data Store.