Skip to end of metadata
Go to start of metadata

The Ed-Fi Dashboards show student transcript data for current and historical courses. This article provides a brief overview of transcript data handling during import, along with code snippets that illustrate the core function.

Overview  

Unlike the majority of values displayed on the Dashboards, the transcript data points are not calculated; rather, the information is presented as imported.  

However, even though there are no metric calculations involved, the ETL Application does include Readers and Translators for loading this information in the dashboard. 

Student Transcript Readers

For student transcript data load there are not a lot of business rules that apply MET-213 - Getting issue details... STATUS , as the main goal is to have historical course grade data loaded when available. An excellent resource to understand the details can be found in the Reader code and data source queries.

From: ~\Ed-Fi-Dashboard\Etl\src\EdFi.Runtime\Reading\CoreReaders\StudentCourseTranscriptReader.cs:

StudentCourseTranscript.sql
DECLARE @currentSchoolYear int = (SELECT TOP(1) SchoolYear FROM ##CurrentSchoolYear$runtimeId)

;WITH [TranscriptPriorityByPerformance] AS
(
    SELECT
        ct.*
        , s.SchoolId
        , td.TermDescriptorId AS TermTypeId
        , tdd.[Description] AS TermTypeDescription
        , gld.CodeValue AS GradeLevelWhenTakenCodeValue
        , ROW_NUMBER() OVER 
        (
            PARTITION BY 
                ct.StudentUSI,
                ct.SchoolYear,
                tdd.[Description],
                ct.CourseCode,
                gld.CodeValue,
                s.SchoolId
            ORDER BY
                CASE 
                    WHEN (cadd.CodeValue = 'Pass') THEN 1 
                    WHEN (cadd.CodeValue = 'Fail') THEN 2
                    WHEN (cadd.CodeValue = 'Withdrawn') THEN 3
                    WHEN (cadd.CodeValue = '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 
            ON gld.DescriptorId = ct.WhenTakenGradeLevelDescriptorId
        INNER JOIN edfi.CourseAttemptResultDescriptor cad
            ON cad.CourseAttemptResultDescriptorId =  ct.CourseAttemptResultDescriptorId
        INNER JOIN edfi.Descriptor cadd 
            ON cadd.DescriptorId = cad.CourseAttemptResultDescriptorId
        INNER JOIN edfi.TermDescriptor td
            ON ct.TermDescriptorId = td.TermDescriptorId
        INNER JOIN edfi.Descriptor tdd
            ON td.TermDescriptorId = tdd.DescriptorId
        INNER JOIN edfi.School s
            ON s.SchoolId = ct.CourseEducationOrganizationId
), [CourseTranscriptsWithPriorityByGradeAndSchool] AS
(
    SELECT
        t.*
        ,ROW_NUMBER() OVER 
            (
                PARTITION BY
                    t.StudentUSI,
                    t.SchoolYear,
                    t.TermTypeId,
                    t.CourseCode,
                    t.GradeLevelWhenTakenCodeValue
                ORDER BY 
                    t.TranscriptPriorityByPerformanceOrderKey ASC,
                    ssa.EntryDate DESC,
                    t.SchoolId ASC
            ) AS [PriorityKeyByGradeAndSchool]
        FROM [TranscriptPriorityByPerformance] t
        LEFT JOIN ##CurrentStudentSchoolAssociation$runtimeId ssa
            ON ssa.StudentUSI = t.StudentUSI
            AND ssa.SchoolId = t.SchoolId
            AND ssa.IsEnrolledToSchool = 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.CourseLevelCharacteristicDescriptorId AS NVARCHAR(10))
                FROM edfi.CourseLevelCharacteristic clc
                WHERE clc.CourseCode = ct.CourseCode AND clc.EducationOrganizationId = CourseEducationOrganizationId
                ORDER BY clc.CourseLevelCharacteristicDescriptorId FOR XML PATH('')), 2, 2000000)) AS CourseLevelCharacteristics
    ,ct.[PriorityKeyByGradeAndSchool] AS [DashboardOrderKeyForHistoricalTranscriptsWithNoSchool]
FROM edfi.Student stu
    INNER JOIN [CourseTranscriptsWithPriorityByGradeAndSchool] ct ON
        ct.StudentUSI = stu.StudentUSI
        AND ct.[TranscriptPriorityByPerformanceOrderKey] = 1
    LEFT JOIN edfi.Course c
        ON ct.CourseEducationOrganizationId = c.EducationOrganizationId
        AND ct.CourseCode = c.CourseCode
    LEFT JOIN edfi.AcademicSubjectDescriptor asd
        ON c.AcademicSubjectDescriptorId = asd.AcademicSubjectDescriptorId
    INNER JOIN ##CurrentStudentSchoolAssociation$runtimeId sortOrder
        ON sortOrder.StudentUSI = ct.StudentUSI
        AND sortOrder.LocalEducationAgencyId = @currentLeaId
ORDER BY sortOrder.LocalEducationAgencyId, sortOrder.SchoolId, ct.StudentUSI, ct.SchoolYear, ct.TermDescriptorId, GradeLevelWhenTaken

Student Transcript Translators

The classes responsible for writing transcript information can be found:

  • ~\Etl\src\Core Metrics\StudentAdvancedCourseMastery
  • ~\Etl\src\Core Metrics\StudentCourseGrades
  • ~\Etl\src\Core Metrics\StudentCreditAccumulation

Detailed information on configuring and running the Ed-Fi ETL application can be found in the ETL Developers' Guide.

Contents

  • No labels