Operational Data Store Version Compatibility

Overview

The Ed-Fi Alliance is committed to providing backward-compatibility with recent versions of software, and to enable an easy transition to new versions of the data standard and related technology.

The Ed-Fi Dashboards use an Ed-Fi Operational Data Store (ODS) as a data source, both to populate the student metric calculations and to provide fine-grained detail for the dashboard UI. The v1.4 Dashboard ETL is compatible with the Ed-Fi ODS v1.2 and can also be used with the Ed-Fi ODS / API v2.0 when used in conjunction with the Compatibility Views feature distributed through the Ed-Fi Exchange. The sections that follow provide detail on populating the dashboards from different data sources.

Populating the Dashboards from a v1.2 ODS Data Store

Some implementers may wish to update the dashboards to the latest code but continue to run on an Ed-Fi v1.2 Data Store. This configuration is supported, but requires the use of database synonyms (introduced in v1.3.1) to support the latest ETL packages. The following links provide useful details for this scenario:

The technical article Dashboard Synonym Updates provides an overview of synonyms and also downloadable SQL Scripts to enable the latest code to be run on an Ed-Fi ODS v1.2.
The article How To: Load Dashboard v1.4 Data from an Ed-Fi ODS v1.2 Data Store covers the details of loading the latest dashboards from an Ed-Fi ODS v1.2.

Updating to the Ed-Fi ODS / API v2.0 from a v1.2 ODS

Some existing implementers may wish to update to the latest Ed-Fi ODS / API from an existing implementation using Ed-Fi ODS v1.2. Since the Ed-Fi ODS v1.2 implementation is a "Drop and Rebuild" process, to convert from an Ed-Fi ODS v1.2 implementation to an Ed-Fi ODS v2.0 implementation using the Ed-Fi Dashboard v1.4, the Ed-Fi ODS / API v2.0 must be installed first, followed by an update to the load process for the dashboards. The following links provide details:

The Ed-Fi ODS/API documentation Getting Started - Installation Steps covers installation and setup of the Ed-Fi ODS / API v2.0 data store.
The Ed-Fi Exchange publishes the Compatibility Views necessary to load data from the ODS / API v2.0.
The article How To: Load Dashboard v1.4 Data from an Ed-Fi ODS v2.0 Data Store covers the details of loading the latest dashboards from an Ed-Fi ODS v1.2.

A detailed discussion of the approach to supporting multiple versions of the ODS can be found in the sections below.

Dashboard ETL Compatibility Approach

The Ed-Fi Dashboard ETL packages are built and managed against the Ed-Fi ODS v1.2 schema and the included Ed-Fi core types and Ed-Fi Descriptors. The release of the ODS in the Ed-Fi ODS / API v2.0 includes schema changes, as well as updated values to Ed-Fi core types and Ed-Fi Descriptor values, which would normally be a breaking change for existing implementers.

Ideally, there should be one set of Ed-Fi Dashboard ETL packages that can work against the current and future versions of the Ed-Fi ODS with minimal performance impact as well as minimal impact to the Dashboard ETL code base. The "compatibility approach" described below was developed to provide that ability.

Handling Table Structure Differences

The approach was taken to create synonyms and views in a database schema called ods that acts as a layer on top of the v2.0 ODS to mimic the table structures of the v1.2 ODS tables. The views are currently available via the Ed-Fi Exchange for current implementers wishing to use the ODS / API v2.0 version as a data source.

This allows the same Dashboard and DashboardDW ETL packages to be used against both v1.2 and v2.0 implementations. The views are designed to pull the data from the v2.0 ODS tables to match the v1.2 table structures.

See the sample views below for representative examples:

Sample ods View for table

CREATE VIEW [ods].[StudentLanguageUse]
WITH SCHEMABINDING
AS
	SELECT	
			[StudentUSI], 			
			LD.[LanguageDescriptorId],
			[LanguageUseTypeId]
	FROM edfi.StudentLanguageUse SL
	INNER JOIN ods.LanguageDescriptor LD
		ON SL.LanguageDescriptorId = LD.DescriptorId

Sample ods View for Type table

CREATE VIEW [ods].[AcademicSubjectType]
WITH SCHEMABINDING
AS
SELECT [AcademicSubjectTypeId], 
		[Description] AS CodeValue,
		[Description], 
		[ShortDescription]
FROM edfi.AcademicSubjectType

Sample ods View for Descriptor table

CREATE VIEW [ods].[AcademicSubjectDescriptor] 
WITH SCHEMABINDING
AS
SELECT cast (ROW_NUMBER()OVER(ORDER BY AcademicSubjectDescriptorId ASC) as int) AS AcademicSubjectDescriptorId,
AcademicSubjectDescriptorId AS DescriptorId,
AcademicSubjectTypeId 
FROM edfi.AcademicSubjectDescriptor

Handling Special-case Value Type Discrepancies

There is one instance in the views, TermType, where the view is hard coded to mimic the v1.2 values consumed by the ETL metric packages. The TermType table values in v2.0 have changed significantly from the values in v1.2. See below for the difference in the TermType tables. The Metric packages are coded specifically for the TermTypeIds in v1.2 and are looking specifically for the Codevalues and/or Descriptions provided in the v1.2 ODS. It was therefore necessary to hard code this view as all other options could not mimic the data in the specific v1.2 format.

The contents of edfi.TermType table in v1.2 is:

The contents of edfi.TermType table in v2.0 is:

The view of ods.TermType table in v2.0 matches v1.2 exactly:

There is also another instance in the views, GradeLevelType, where the database view is hard coded to mimic the v1.2 values consumed by the Dashboards UI. The GradeLevelType table values changed significantly from the values in v1.2. See below for the difference in the GradeLevelType tables. The Dashboard UI is coded specifically for the Description column as provided in the v1.2 ODS. It was therefore necessary to hard code this view for the Description values for grades 1st through 12th as all other options did not mimic the Description data in that specific format.

The contents of edfi.GradeLevelType in v1.2 is:

The contents of edfi.GradeLevelType in v2.0 is:

The view of ods.GradeLevelType in v2.0 matches v1.2 exactly:

Also worth noting, in previous versions of the Ed-Fi ODS, there are instances where the values used in CodeValue, Short Description, and Description are not the same, an example of which is below in Table 1. In the latest version of the Ed-Fi ODS, the values in the Description and Short Description are the same – and the Code Value field now reflects the Short Description truncated to 50 characters to fit the length of this column, as seen in Table 2.

Table 1. Ed-Fi ODS v1.2 example showing CodeValue, Description, ShortDescription with different values

GradeLevelTypeId	CodeValue	ShortDescription	Description

GradeLevelTypeId	CodeValue	ShortDescription	Description
1	First Grade	1st Grade	1st Grade
2	Second Grade	2nd Grade	2nd Grade
3	Third Grade	3rd Grade	3rd Grade

Table 2. Ed-Fi ODS v2.0 example showing Description, Short Description and CodeValue with same values

GradeLevelTypeId	CodeValue	ShortDescription	Description

GradeLevelTypeId	CodeValue	ShortDescription	Description
5	First Grade	First Grade	First Grade
7	Second Grade	Second Grade	Second Grade
8	Third Grade	Third Grade	Third Grade

In the type tables where the Descriptions are different between v1.2 and v2.0 ODS and the metric packages used either the CodeValue or Description fields in the ETL components and query filters, the components and filters were extended to include the description values from v2.0 that were not already coded, such as seen in the GradeLevelType (Tables 1 and 2, above). See sample code change below.

ORIGINAL 1.3 CODE BASE ETL QUERY FILTER FOR GRADELEVEL

WHERE GLT.Description IN ('6th Grade', '7th Grade', '8th Grade', '9th Grade', '10th Grade',
                            '11th Grade', '12th Grade', 'Postsecondary', 'Ungraded',
							'Other', 'Grade 13', 'Adult Education'
						)

ORIGINAL 1.3.1 CODE BASE ETL QUERY FILTER FOR GRADELEVEL

WHERE GLT.Description IN ('6th Grade', '7th Grade', '8th Grade', '9th Grade', '10th Grade',
                            '11th Grade', '12th Grade', 'Postsecondary', 'Ungraded',
							'Other', 'Grade 13', 'Adult Education', 'Sixth grade', 'Seventh grade',
							'Eighth grade', 'Ninth grade', 'Tenth grade', 'Eleventh grade',
							'Twelfth grade'
						)

In Type tables where the Code Values in v1.2 are different from the Description values in v2.0 but the Descriptions in v1.2 are the same Descriptions in v2.0, the ETL components and query filters are looking specifically for the v1.2 Code values; the ETL packages were updated to use the Description field instead of the code value field. An example of this is the AcademicSubjectType (Tables 3 and 4, below). See sample code changes below as well.

Table 3. Ed-Fi ODS v1.2 example showing CodeValue, Description, ShortDescription with different values

AcademicSubjectTypeId	CodeValue	ShortDescription	Description

AcademicSubjectTypeId	CodeValue	ShortDescription	Description
1	Reading	Reading	Reading
2	ELA	English Language Arts	English Language Arts
3	Mathematics	Mathematics	Mathematics

Table 4. Ed-Fi ODS v2.0 example showing Description, Short Description and CodeValue with same values

AcademicSubjectTypeId	CodeValue	ShortDescription	Description

AcademicSubjectTypeId	CodeValue	ShortDescription	Description
1	Reading	Reading	Reading
2	English Language Arts	English Language Arts	English Language Arts
3	Mathematics	Mathematics	Mathematics

ORIGINAL 1.3 CODE BASE ETL QUERY

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

UPDATED 1.3.1 CODE BASE ETL QUERY

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

Original 1.3 Code Base Derived Column Component

Updated 1.3.1 Code Base Derived Column Component

Dashboard ETL Updates

The Dashboard and DashboardDW packages were all updated to replace the 'edfi' database schema that stems from the table names, with the 'ods' database schema that stems from the synonyms and views. This allows the use of the same Dashboard code base by all implementations. This change was applied to all ETL packages and stored procedures used to load the dashboard. See example of the change below.

ORIGINAL 1.3 CODE BASE ETL QUERY

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

UPDATED 1.3.1 CODE BASE ETL QUERY

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

Components Affected

All Dashboard packages
All DashboardDW packages
All DashbordDW Stored Procedures