Thursday, February 5, 2009

SQL Script to Download Grades from Blackboard Learning System CE8

I just finished a SQL query to download all grade book data from Blackboard Learning System CE8.
select
LC2.name as Semester,
GB.learning_context_name as Section,
P.source_id as StudentID,
GB.user_login_name,
GB.given_name,
GB.family_name,
GB.column_name,
GB.column_type,
GB.original_value,
GB.override,
GB.max_points,
GB.final_value
from rpt_ext_gradebook as GB
inner join rpt_person as P
on P.person_id = GB.person_id
inner join rpt_learning_context as LC1
on GB.learning_context_id = LC1.learning_context_id
inner join rpt_learning_context as LC2
on LC1.parent_learning_context_id = LC2.learning_context_id
where P.demo_user = 0
order by LC2.name, GB.learning_context_name, column_name, user_login_name
This depends on the RPT_EXT_GRADEBOOK background job to run successfully to have the data populated properly.

I ran it against a clone of our production database. It took 1.5 minutes to retrieve 2.2 millions grade records. I spot checked a number of courses (sections). It included the resulting value of grades even when they were calculated by formula. It also gave me the original grades, any overrides made, and the final grades. Very nice!

1 comment:

Ezra said...

Oooh, this looks really good. Thanks for sharing.

Anyone seeking to use it: The RPT_EXT_GRADEBOOK background job by default runs daily at midnight. Some administrators move when this runs as database servers typically become less responsive to help users not have a poor experience. How unresponsive depends on the amount of data. Very, very large amounts of data can result in failing to commit the data (so this table doesn't have data).