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:

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

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

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

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

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.

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'
						)

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

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

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

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.

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,
	   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