How to join Brightspace Data Sets: keys and a worked query

Choose where to run it

• In Rowseta: open Reports > New report and paste the query into the SQL editor on the Query tab. Sync the data sets listed below first.
• In your own setup: load your Data Hub extracts into your own SQL tool, one table per data set, named without spaces (User Enrollments becomes UserEnrollments), after applying differentials to the fulls. Another SQL tool may need small changes to these queries.

A free Rowseta Desktop app is coming soon.

This query needs User Enrollments, Users, Organizational Units, Role Details, Grade Results and Grade Objects. The reference links explain the columns and keys in each data set.

Join enrollments to courses and grades

Run the complete query unchanged. It covers enrollments in roles that pass the learner rule, in course offerings across the data you loaded.

Join keys combine data sets without matching names. User Enrollments supplies each course, user and role; the other tables add details:

Grade Results matches Grade Objects on both GradeObjectId and OrgUnitId, so the grade belongs to the enrollment's course.

-- This example includes course enrollments passing the learner rule.
-- It shows the lowest numbered calculated final grade result, when one exists.
WITH learner_roles AS (
    SELECT DISTINCT RoleId
    FROM RoleDetails
    WHERE RoleId IS NOT NULL
      AND NULLIF(trim(CAST(DeletedBy AS VARCHAR)), '') IS NULL
      AND COALESCE(lower(trim(CAST(ShowInGrades AS VARCHAR))) IN ('true', '1'), false)
), Grades AS (
    SELECT GradeResults.OrgUnitId, GradeResults.UserId, GradeObjects.GradeObjectId,
           GradeObjects.Name AS GradeName,
           GradeResults.PointsNumerator * 100.0 / NULLIF(GradeResults.PointsDenominator, 0) AS GradePercent,
           ROW_NUMBER() OVER (
               PARTITION BY GradeResults.OrgUnitId, GradeResults.UserId
               ORDER BY GradeObjects.GradeObjectId
           ) AS GradeRank
    FROM GradeResults
    JOIN GradeObjects ON GradeObjects.GradeObjectId = GradeResults.GradeObjectId
                     AND GradeObjects.OrgUnitId = GradeResults.OrgUnitId
    WHERE GradeObjects.GradeObjectTypeId = 7
      AND COALESCE(TRY_CAST(GradeObjects.IsDeleted AS BOOLEAN), false) = false
      AND COALESCE(TRY_CAST(GradeResults.IsDeleted AS BOOLEAN), false) = false
)
SELECT Courses.OrgUnitId AS CourseId, Courses.Name AS CourseName,
       Users.UserId, Users.FirstName, Users.LastName, UserEnrollments.RoleId, RoleDetails.RoleName,
       Grades.GradeObjectId, Grades.GradeName, Grades.GradePercent
FROM UserEnrollments
JOIN learner_roles ON learner_roles.RoleId = UserEnrollments.RoleId
JOIN Users ON Users.UserId = UserEnrollments.UserId
JOIN OrganizationalUnits AS Courses ON Courses.OrgUnitId = UserEnrollments.OrgUnitId
JOIN RoleDetails ON RoleDetails.RoleId = UserEnrollments.RoleId
LEFT JOIN Grades ON Grades.OrgUnitId = UserEnrollments.OrgUnitId
                AND Grades.UserId = UserEnrollments.UserId AND Grades.GradeRank = 1
WHERE Courses.Type = 'Course Offering'
  AND COALESCE(TRY_CAST(Courses.IsDeleted AS BOOLEAN), false) = false
  AND Users.DateDeleted IS NULL
ORDER BY CourseId, Users.UserId;

A role counts as a learner role when Role Details has ShowInGrades true and DeletedBy empty. Each RoleId is judged on its own and role names are never read. Preview and testing roles that show in grades pass this rule too, so to use your own roles, replace the body of learner_roles with your role IDs: SELECT UNNEST([103, 121]) AS RoleId.

This selects calculated final grades (type 7), keeping the lowest grade object ID per learner and course. It covers every term loaded. A final grade may not exist until late in a course, so it is not early on its own. Earlier in a term, use the inactivity guide. For adjusted-first selection and a passing threshold, use the final grades guide.

The optional LEFT JOIN keeps enrollments without a calculated final grade. Blank grade columns, including a percentage with a zero denominator, are different from a recorded zero.

Read the joined enrollment and grade rows

Fictional sample data: the first eight rows. Your own data will give different names, IDs and values.

Each joined row links an enrollment to its calculated final grade. GradePercent is out of 100; blank grade columns mean no matching value. RoleId keeps the two sample roles named Learner apart.