Date: Thu, 28 Mar 2024 07:19:51 -0500 (CDT) Message-ID: <610435523.29848.1711628391876@PUBEDFIPRDWEB5.public.local> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_29847_1665144016.1711628391874" ------=_Part_29847_1665144016.1711628391874 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
The Ed-Fi Dashboards show student transcript data for current and histor= ical courses. This article provides a brief overview of transcript dat= a handling during import, along with code snippets that illustrate the core= function.
Unlike the majority of values displayed on the Dashboards, the transcrip= t data points are not calculated; rather, the information is presented as i= mported.
However, even though there are no metric calculations involved, the ETL Application does in= clude Readers and Translators for loading this information in the dashboard= .
For student transcript data load there are not a lot of business rules t= hat apply
, a= s the main goal is to have historical course grade data loaded when availab= le. An excellent resource to understand the details can be found in the Rea= der code and data source queries.From: ~\Ed-Fi-Dashboard\Etl\src\EdFi.Runtime\Reading\CoreReaders\Student= CourseTranscriptReader.cs:
DECLARE = @currentSchoolYear int =3D (SELECT TOP(1) SchoolYear FROM ##CurrentSchoolYe= ar$runtimeId) ;WITH [TranscriptPriorityByPerformance] AS ( SELECT ct.* , s.SchoolId , td.TermDescriptorId AS TermTypeId , tdd.[Description] AS TermTypeDescription , gld.CodeValue AS GradeLevelWhenTakenCodeValue , ROW_NUMBER() OVER=20 ( PARTITION BY=20 ct.StudentUSI, ct.SchoolYear, tdd.[Description], ct.CourseCode, gld.CodeValue, s.SchoolId ORDER BY CASE=20 WHEN (cadd.CodeValue =3D 'Pass') THEN 1=20 WHEN (cadd.CodeValue =3D 'Fail') THEN 2 WHEN (cadd.CodeValue =3D 'Withdrawn') THEN 3 WHEN (cadd.CodeValue =3D 'Incomplete') THEN 4 ELSE 5 END ASC, ct.FinalNumericGradeEarned DESC, CASE WHEN ct.FinalLetterGradeEarned IS NOT NULL THEN 1 ELSE= 2 END ASC, ct.FinalLetterGradeEarned ASC ) AS [TranscriptPriorityByPerformanceOrderKey] FROM edfi.CourseTranscript ct LEFT JOIN edfi.Descriptor gld=20 ON gld.DescriptorId =3D ct.WhenTakenGradeLevelDescriptorId INNER JOIN edfi.CourseAttemptResultDescriptor cad ON cad.CourseAttemptResultDescriptorId =3D ct.CourseAttemptRes= ultDescriptorId INNER JOIN edfi.Descriptor cadd=20 ON cadd.DescriptorId =3D cad.CourseAttemptResultDescriptorId INNER JOIN edfi.TermDescriptor td ON ct.TermDescriptorId =3D td.TermDescriptorId INNER JOIN edfi.Descriptor tdd ON td.TermDescriptorId =3D tdd.DescriptorId INNER JOIN edfi.School s ON s.SchoolId =3D ct.CourseEducationOrganizationId ), [CourseTranscriptsWithPriorityByGradeAndSchool] AS ( SELECT t.* ,ROW_NUMBER() OVER=20 ( PARTITION BY t.StudentUSI, t.SchoolYear, t.TermTypeId, t.CourseCode, t.GradeLevelWhenTakenCodeValue ORDER BY=20 t.TranscriptPriorityByPerformanceOrderKey ASC, ssa.EntryDate DESC, t.SchoolId ASC ) AS [PriorityKeyByGradeAndSchool] FROM [TranscriptPriorityByPerformance] t LEFT JOIN ##CurrentStudentSchoolAssociation$runtimeId ssa ON ssa.StudentUSI =3D t.StudentUSI AND ssa.SchoolId =3D t.SchoolId AND ssa.IsEnrolledToSchool =3D 1 ) SELECT stu.StudentUniqueId as ParentId ,ct.SchoolYear ,ct.CourseEducationOrganizationId AS SchoolId ,ct.EducationOrganizationId AS LocalEducationAgencyId ,ct.TermTypeId ,ct.TermTypeDescription ,ct.CourseCode AS IdentityCourseCode ,c.CourseTitle ,asd.AcademicSubjectDescriptorId as AcademicSubjectDescriptor ,ct.CourseAttemptResultDescriptorId AS CourseAttemptResultDescriptor ,ct.AttemptedCredits AS CreditsAttempted ,ct.EarnedCredits AS CreditsEarned ,ct.WhenTakenGradeLevelDescriptorId AS GradeLevelWhenTaken ,ct.FinalLetterGradeEarned AS FinalLetterGrade ,ct.FinalNumericGradeEarned AS FinalNumericGrade ,(SUBSTRING((SELECT ',' + CAST(clc.CourseLevelCharacteristicDescriptorI= d AS NVARCHAR(10)) FROM edfi.CourseLevelCharacteristic clc WHERE clc.CourseCode =3D ct.CourseCode AND clc.EducationOrg= anizationId =3D CourseEducationOrganizationId ORDER BY clc.CourseLevelCharacteristicDescriptorId FOR XML = PATH('')), 2, 2000000)) AS CourseLevelCharacteristics ,ct.[PriorityKeyByGradeAndSchool] AS [DashboardOrderKeyForHistoricalTra= nscriptsWithNoSchool] FROM edfi.Student stu INNER JOIN [CourseTranscriptsWithPriorityByGradeAndSchool] ct ON ct.StudentUSI =3D stu.StudentUSI AND ct.[TranscriptPriorityByPerformanceOrderKey] =3D 1 LEFT JOIN edfi.Course c ON ct.CourseEducationOrganizationId =3D c.EducationOrganizationId AND ct.CourseCode =3D c.CourseCode LEFT JOIN edfi.AcademicSubjectDescriptor asd ON c.AcademicSubjectDescriptorId =3D asd.AcademicSubjectDescriptorI= d INNER JOIN ##CurrentStudentSchoolAssociation$runtimeId sortOrder ON sortOrder.StudentUSI =3D ct.StudentUSI AND sortOrder.LocalEducationAgencyId =3D @currentLeaId ORDER BY sortOrder.LocalEducationAgencyId, sortOrder.SchoolId, ct.StudentUS= I, ct.SchoolYear, ct.TermDescriptorId, GradeLevelWhenTaken
The classes responsible for writing transcript information can be found:=
Detailed information on configuring and running the Ed-Fi ETL applicatio= n can be found in the E= TL Developers' Guide.