This version of the Ed-Fi Dashboards is no longer supported. See the Ed-Fi Technology Version Index for a link to the latest version.

 

Operational Data Store Version Compatibility Approach

Overview


This document contains conceptual information regarding the approach taken to allow the Ed-Fi Dashboard ETL code to be compatible with the Ed-Fi ODS v1.2 data sets and Ed-Fi ODS v2.0 data sets.

Background

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.

As of Dashboards v1.3.1, the impact to most current implementers is simply that the ETL is forward compatible with the upcoming Ed-Fi ODS / API v2.0 release. Implementers who wish to run the Ed-Fi Dashboards against the current version of the Ed-Fi ODS / API v2.0 may do so by leveraging the Compatibility Views feature available on the Ed-Fi Exchange.

Approach for Dashboard Compatibility

The general requirement behind the compatibility work was to have only one set of Dashboard and DashboardDW packages that are sustainable, maintainable, and effective at creating metrics with minimal performance implications and minimal code changes. This section describes the various techniques in more detail.

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

GradeLevelTypeIdCodeValueShortDescriptionDescription
1First Grade1st Grade1st Grade
2Second Grade2nd Grade2nd Grade
3Third Grade3rd Grade3rd Grade

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

GradeLevelTypeIdCodeValueShortDescriptionDescription
5First GradeFirst GradeFirst Grade
7Second GradeSecond GradeSecond Grade
8Third GradeThird GradeThird 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

AcademicSubjectTypeIdCodeValueShortDescriptionDescription
1ReadingReadingReading
2ELAEnglish Language ArtsEnglish Language Arts
3MathematicsMathematicsMathematics

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

AcademicSubjectTypeIdCodeValueShortDescriptionDescription
1ReadingReadingReading
2English Language ArtsEnglish Language ArtsEnglish Language Arts
3MathematicsMathematicsMathematics
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