Dashboard Synonym Updates

The Ed-Fi Dashboard (v1.3.1 and above) 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 of using synonyms was chosen to support Dashboard ETL maintainability for current implementations (no impact on SSIS packages) and forward compatibility with future versions. For example, the Dashboard ETL has a seamless integration with the latest release of the Ed-Fi ODS / API 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 ODS implementations ...\Ed-Fi-Core\Database\Structure\EdFi.

It also involved the replacement of the edfi database schema in the dashboard ETL 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 ETL packages (v1.3.1 and above) 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 implementers who choose to upgrade to the Dashboards (v1.3.1 and above):

• 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.4 from an Ed-Fi ODS v2.0 Data Store.