Manually Update/Change Discriptor Values in the ODS

Although the descriptor values in the ODS can be manually updated/changed, we highly recommend that any updates needed to the descriptors be done through the normal build process.
The functionality of the descriptors is non-trivial to the dashboard metric calculations and erroneous/incomplete updates to the descriptor values in the ODS would severely affect metric calculations and information displayed on the dashboards.

Issue
NDE made a mistake in recent descriptor changes for English Language Learner data submissions. It would be ideal to make updates and redeploy changes to sandbox and ODS. However, districts have already started loading data. They need to understand the process for making changes (manually) for defect corrections to avoid a complete reload of all district data.
The following was changed and custom descriptors were created, some core descriptors were removed. NDE needs to revert back to the core values for LimitedEnglishProficiency.


Revert to core
01 - Limited
02 - Limited - Modified 1
03 - Limited - Modified 2
04 – NotLimited

It is important to understand Descriptors, how they are implemented and the relationship between descriptor tables and columns in the ODS before attempting to perform manual changes.


Understanding Descriptors
A Descriptor is an Enumeration vocabulary that is not "fixed," thereby providing a more "malleable" alternative to Enumerations by referring to enumerated collections of values. From the XSD perspective, a Descriptor uses the concept of Generalization, which means that there is a Complex Type "Descriptor" for all Descriptors and there is a particular Complex Type as Specialization for every particular case where a Descriptor is used.
This is important to know to identify the ODS tables implemented by this concept and to follow the relationships between these tables. The common table used is the edfi.Descriptors table, which has all metadata for all Descriptors; this is the Generalization part since all Descriptors have a set of common attributes.
The Specialization part involves the use of a table that is regularly named according to the Enumeration used. For example, the LimitedEnglishProficiencyMap, or Enumeration, is used in the LimitedEnglishProficiencyDescriptor. Note the similar name used on both to identify that they are part of the Descriptor. However, there may be cases when there is no Enumeration associated with the Descriptor.


Generalized Descriptor Table
The generalized descriptor table, edfi.Descriptor has the following attributes with the following definition:
  • DescriptorId – This is the unique Identifier for each descriptor entered into the edfi.descriptor table. It is an identity column hence the IDs are auto-generated
  • Namespace: URL that defines the scope of use of the descriptor e.g. http://www.ed-fi.org/Descriptor/LimitedEnglishProficiencyDescriptor.xml. This is a mandatory column
  • CodeValue: A code or abbreviation that is used to refer to the descriptor. This is a mandatory column
  • ShortDescription: A shortened description for the descriptor. This is a mandatory column
  • Description: The long description. This is a mandatory column
  • PriorDescriptorId: PriorDescriptorId used for the descriptor value. Note: Values in this column are currently not utilized in the dashboards.
  • EffectiveBeginDate: Effective date of the descriptor
  • EffectiveEndDate: End date of the descriptor
  • Id: uniqueIdentifier associated with the file upload
  • LastModifiedDate: Date the record was last modified.
  • CreateDate: Date the record was created.
Specialized Descriptor Tables
The specialized descriptor tables act as a bridge between the fixed Ed-Fi enumerations and the descriptor values supplied by the vendor/implementation. The specialized descriptor table has the following attributes:
  • [DomainEntity]DescriptorId: This is the descriptorId associated with the descriptor value in the edfi.Descriptor table. This column has a foreign key constraint to the edfi.descriptor table on descriptorId. This is a mandatory field.
  • [DomainEntity]TypeId: This is the Id associated with the value map for this descriptor. This column has a foreign key constraint to the edfi.[Domain]Type table.E.g – An academic description of 'Algebra I' will have an academic value map of 'Mathematics' which has a typeId of 9 in the edfi.academicsubjecttype table. This is a mandatory field.
The [DomainEntity]DescriptorId is referenced across the ODS. These child columns do not have update cascade or delete cascade options turned on.
Note: When loading descriptors, the generalized descriptor table, i.e. the edfi.descriptor table, is loaded before the specialized descriptor table(s).

Sample ER-Model that shows the relationship between the descriptor tables and domain.


Resolution
In this instance, the changes made replaced the name of the CodeValue, ShortDescription, and Description of the LimitedEnglishProficiency descriptor. A descriptor was also deleted from the edfi.descriptor table. The following steps were taken:


Insert New/Deleted Descriptor Value
1. Insert the Namespace, CodeValue, ShortDescription, Description and EffectiveBeginDate for the new descriptor value into the edfi.Descriptor table in the ODS.
Sample Insert 1:
INSERT INTO edfi.Descriptor
(Namespace, CodeValue, ShortDescription, Description, EffectiveBeginDate)
Values
('http://www.ed-fi.org/Descriptor/LimitedEnglishProficiencyDescriptor.xml' -- Namespace
, '01' -- CodeValue
, 'Limited' -- ShortDescription
, 'Limited' -- Description
, '2012-07-01' -- EffectiveBeginDate
)


2. Insert the descriptor information into the specialized descriptor table, i.e. edfi.[Domain]Descriptor table. This will require the descriptorId for the descriptor and the ValueMap which will be used to get the [Domain]TypeId. In this case, the ValueMap will be the EdFi enumeration for LimitedEnglishProficiency that is associated with the descriptor.
Sample Insert 2:
INSERT INTO edfi.LimitedEnglishProficiencyDescriptor
SELECT Descriptorid,
(SELECT LimitedEnglishProficiencyTypeId
FROM edfi.LimitedEnglishProficiencyType
WHERE ShortDescription = 'Limited' --[Domain]ValueMap enumeration
) as LimitedEnglishProficiencyTypeId
FROM edfi.descriptor d
WHERE namespace = 'http://www.ed-fi.org/Descriptor/LimitedEnglishProficiencyDescriptor.xml'
and d.Description = 'Limited' --edfi.Descriptor description value.


Update/Change Existing Descriptor Values
Note: DescriptorIds do not have cascading updates or deletes. Extensive changes to the descriptor values might require you to reload the data.

1. For an update/change to the descriptor value, check the edfi.descriptor table, edfi.[Domain]Descriptor table and edfi.[Domain]Type table for the current descriptors and their associated value map/typeid. In this case, perform a join to the descriptor table, edfi.LimitedProficiencyDescriptor table and the edfi.LimitedEnglishProficiencyType table to check the existing value map. This can be done with the query below:
Sample Update 1:
SELECT D.[DescriptorId] as Descriptor_DescriptorId
, D.[Namespace]
, D.[CodeValue] as Descriptor_CodeValue
, D.[ShortDescription] as Descriptor_ShortDescription
, D.[Description] as Descriptor_Description
, LE.LimitedEnglishProficiencyDescriptorId
, LE.LimitedEnglishProficiencyTypeId
, LEPT.CodeValue
, LEPT.ShortDescription
, LEPT.Description
FROM edfi.descriptor D
LEFT JOIN edfi.LimitedEnglishProficiencyDescriptor LE
ON d.DescriptorId = LE.LimitedEnglishProficiencyDescriptorId
LEFT JOIN edfi.LimitedEnglishProficiencyType LEPT
ON LEPT.LimitedEnglishProficiencyTypeId = LE.LimitedEnglishProficiencyTypeId
WHERE D.Namespace = 'http://www.ed-fi.org/Descriptor/LimitedEnglishProficiencyDescriptor.xml'


2. If the current descriptors have the same [Domain]ValueMap/TypeId as the new descriptors, then all you will have to do is update the edfi.descriptor table. If not, follow the steps to insert new descriptor values . The script below was used to update the LimitedEnglishProficiencyDescriptors.
Sample Update 2:
Begin Tran Update_DescriptorTable
---------------------------------
---------------------------------
UPDATE edfi.Descriptor
SET CodeValue = (CASE ShortDescription WHEN 'Not Applicable' THEN '04' Else CodeValue END),
ShortDescription = (CASE ShortDescription WHEN 'Redesignated as English Fluent Two Years or Less' THEN 'Limited Monitored 1'
WHEN 'Redesignated as English Fluent More Than Two Years' THEN 'Limited Monitored 2'
WHEN 'Not Applicable' THEN 'NotLimited'
ELSE ShortDescription END),
Description = (CASE Description WHEN 'Redesignated as English Fluent Two Years or Less' THEN 'Limited Monitored 1'
WHEN 'Redesignated as English Fluent More Than Two Years' THEN 'Limited Monitored 2'
WHEN 'Not Applicable' THEN 'NotLimited'
ELSE Description END)
WHERE Namespace = 'http://www.ed-fi.org/Descriptor/LimitedEnglishProficiencyDescriptor.xml'
AND DescriptorId in (740, 741, 742)
/*__________________________________________________________________________________________________________________________
/****VERIFY YOUR CHANGES WITH THE SCRIPT BELOW*****/
__________________________________________________________________________________________________________________________*/

SELECT D.[DescriptorId] as Descriptor_DescriptorId
, D.[Namespace]
, D.[CodeValue] as Descriptor_CodeValue
, D.[ShortDescription] as Descriptor_ShortDescription
, D.[Description] as Descriptor_Description
, LE.LimitedEnglishProficiencyDescriptorId
, LE.LimitedEnglishProficiencyTypeId
, LEPT.CodeValue
, LEPT.ShortDescription
, LEPT.Description
FROM edfi.descriptor D
LEFT JOIN edfi.LimitedEnglishProficiencyDescriptor LE
ON d.DescriptorId = LE.LimitedEnglishProficiencyDescriptorId
LEFT JOIN edfi.LimitedEnglishProficiencyType LEPT
ON LEPT.LimitedEnglishProficiencyTypeId = LE.LimitedEnglishProficiencyTypeId
WHERE D.Namespace = 'http://www.ed-fi.org/Descriptor/LimitedEnglishProficiencyDescriptor.xml'


3. If the changes do not look good, run the script below in the same window as the script above, adjust your script used for Step 2 and try again. Once you verify that the changes look good, move on to step 4.
Sample Update 3:
Rollback Tran Update_DescriptorTable


4. If the changes look good, run the script below in the same window as the script above. This will commit the changes.
Sample Update 4:
COMMIT Tran Update_DescriptorTable
Creation date: 12/30/2015 3:07 PM ()      Updated: 12/30/2015 3:49 PM ()