Introduction

Data Standard 3.3.1-b introduces an important usage change that impacts the new StudentInternetAccessDim View - Experimental view that was added to support the online engagement starter kit (see  DATASTD-1594 - Getting issue details... STATUS )

Previously, there was a recommendation to store Digital Equity information in the StudentEducationOrganizationAssociationStudentIndicator  table (ED-FI WORKING DRAFT 3 - DIGITAL EQUITY COLLECTION). This recommendation still holds for Data Standard 3.1, 3.2, and 3.3.0-a. However, Data Standard 3.3.1-b introduces a more formal pattern for storing these data by adding columns to the StudentEducationOrganizationAssociation  table. Thus for the upcoming release of the Ed-Fi ODS/API Tech Suite 3, version 5.3, the Analytics Middle Tier needs to change how it is getting the data.

For additional context, currently the Analytics Middle Tier treats Data Standard 3.3.0-a as if it were Data Standard 3.2, because there were no breaking changes between them that impacted the views.

An Observation About the Data Model

When StudentInternetAccessDim  was first envisioned, it was assumed that there would be a time period to it, and thus the relationship from StudentSchoolDim View to StudentInternetAccessDim  was assumed to be one-to-many. However, there was a mistake in the data modeling for the Student Indicator entity: the date was not part of the natural key. Therefore there could only be one record. Thus even with the initial release, it would have been possible to consolidate these columns onto the StudentSchoolDim  view.

This perspective is reinforced with the new Data Standard, where the Digital Equity columns are directly on the StudentEducationOrganizationAssociation. The Analytics Middle Tier works best when the data model is kept as simple as possible (while remaining accurate). Therefore it would be better to eliminate the StudentInternetAccessDim  in favor of adding new columns to the StudentSchoolDim .

Normally that would mean a breaking change and an update to the Major version number for the Analytics Middle Tier. However, with version 2.5.x, the view was labeled "experimental" and thus subject to change. In that sense, it is reasonable to consider this a feature update instead of a breaking change.

Default Solution

The procedure to date for handling breaking changes at the Data Standard level has been:

1. Fix the automated tests
   1. With help from the ODS Platform team, create a new dacpac file with an empty version of the database for the new data standard.
   2. Create a new test harness that loads this dacpac.
   3. Modify / add tests scripts as necessary for loading sample data into the new test harness.
2. Fix the source code
   1. Create a new .NET project for the new Data Standard.
   2. Copy the final state of all of the views from the prior Data Standard project to the new one. There is no reason to copy an prior state migration files.
   3. Adjust broken view files as required to make them work (correctly) in the new Data Standard.
   4. Add code SqlServerMigrationStrategy.cs class to detect the new Data Standard
3. Run the automated tests to confirm that everything works.

Concerns About this Approach

This approach has generally been useful to date. However, it does mean that any bugs found in a view must be fixed in several places. Is it really worth creating that level of potential tech debt in order to support this one small change?

Proposed Alternative

1. Continue treating Data Standards 3.2 and 3.3.1-b the same from a C# migration strategy perspective.
2. Create new scripts to drop StudentInternetAccessDim 
3. Add new columns (described below) to the StudentSchoolDim .
   1. For "DS22", the new columns will have to have null value, as there is no available source.
   2. For "DS31", the new columns would be derived from StudentEducationOrganizationAssociationStudentIndicator .

   3. In this case, the "DS32" script will have to detect if it is really installing into Data Standard 3.3.1-b, with something like this:

      IF EXISTS (
      	SELECT
      		1
      	FROM
      		INFORMATION_SCHEMA.COLUMNS 
      	WHERE
      		TABLE_NAME = 'StudentEducationOrganizationAssociation' 
      	AND
      		COLUMN_NAME = 'InternetAccessInResidence')
      BEGIN
      	-- Build the query based on columns in StudentEducationOrganizationAssociation
      END
      ELSE
      BEGIN
      	-- Build the query based on columns in StudentEducationOrganizationAssociationStudentIndicator  
      END

New Columns

The columns in the StudentInternetAccessDim  view are:

• InternetAccessInResidence
• InternetAccessTypeInResidence
• InternetPerformance
• DigitalDevice
• DeviceAccess

Data Standard 3.3.1-b uses more descriptive column names and adds two more columns. To improve the reader's understanding about the source of data in the view, it is proposed that we instead use the following columns in the modified StudentSchoolDim  view.

• PrimaryLearningDeviceAwayFromSchool (string from a Descriptor) (previously "DigitalDevice")
• PrimaryLearningDeviceAccess (string from a Descriptor) (previously "DeviceAccess")
• PrimaryLearningDeviceProvider (string from a Descriptor) (new)
• InternetAccessInResidence (bool) (previously "AccessInResidence")
• BarrierToInternetAccessInResidence (string from a Descriptor) (new)
• InternetAccessTypeInResidence (string from a Descriptor) (previously "InternetAccessInResidence")
• InternetPerformanceInResidence (string from a Descriptor) (previously "InternetPerformance")