Transferring grades from Blackboard your student information system – a semi automated process

Recently on one of the Blackboard mailing lists someone asked about transferring grades from Blackboard to their student information system.  Now this is something that off and on people keep asking about, there are integrations in America but in the UK none of the big student information systems have been integrated.
Thats not to say people haven’t tried a number of Uni’s have attempted it with varying success – but if anyone has totally solved it I haven’t come across them sharing their methods.  I believe Blackboard corp are even looking at making some integrations themselves but this might take some time.

Now in my last job at City of Bristol College about 6 years ago we did a lot of work moving grades around and I had something that worked well as a “Semi” automatic method.
So I thought I would show people how because with a few tweaks it could work for you.

If you dont use Blackboard the rest is probably a bit boring for you….

Einstein blackboard

The first thing you need to do is in a totally blank test course setup a new grade centre category called “Export” (it can be called anything – just make sure you change the SQL below to match it).
You need a way to copy this into all the courses you plan to use this with (ask me if you are interested – I don’t want to confuse this answer with details on how to do that, if anyone comments asking then I will write a post on it).
Then any staff who want their grade exported simply change the grade centre column to use that category.
(another option is to copy the category and some assessments that are already setup and assigned to the category – again this is fairly straightforward just ask and I will post about it).

Then you can get all of the grades on the system that are in the category “Export” by using the following SQL

—————————-

SELECT T.NAME,
CS.COURSE_NAME,
CS.COURSE_ID,
M.title,
u.user_id,
att.score,
G.manual_grade,
g.manual_score,
ATT.date_added as attempt_date_added,
ATT.date_modified as attempt_date_modified,
g.date_added as grade_date_added,
g.date_modified as grade_date_modified
FROM GRADEBOOK_TYPE T, GRADEBOOK_MAIN M, GRADEBOOK_GRADE G,
COURSE_MAIN CS, attempt ATT, course_users CU, Users U

where T.Name like ‘Export%’

and T.pk1 = M.gradebook_type_pk1
and M.pk1 = g.gradebook_main_pk1
and M.crsmain_pk1 = CS.PK1
and att.pk1 = g.last_attempt_pk1
and g.course_users_pk1 = cu.pk1
and cu.users_pk1 = U.pk1;

—————————

If you want to finesse it a bit you could include a ” and ATT.date_added > ’01/09/2014′ ” and of course you can make any of the column headings say what you want by using the “as” function.  e.g. CS.COURSE_NAME as Unit title would make that column header “Unit title”

Then you copy and paste the results into a spreadsheet or text file.  Remove any columns you dont need (a lot of the date added and date modified columns I put in to see when things happened, they didnt need uploading to any system) and change any column headings to what they need to be for your upload.

Now a lot of student information systems (SIS) allow bulk upload of data and you should be able to turn the information in the spreadsheet into something that they will accept.  Every SIS is different so I cant really provide specific advice.

So where might you find problems?

The identifier of the piece of assessment

(in the SQL above this is “m.title” and would pull whatever the title of the grade was in Blackboard).  This might not match what your SIS is looking for either because of spelling or because they are looking for a totally different identifier.

For example at my current University we use SITS and that uses the identifier 001 for the first piece of assessment and 002 for the second and so on.  The solution here would be to create a category for each (so 001, 002, 003 etc) and change the SQL above so it found all of them and then T.Name would be the identifier of the piece of assessment.

Student identifier

You need to be sure that your Blackboard system has the same user_id as your SITS system. Again at my university we use the username to login to everything, however students are identified on our SITS system by their student number so the SQL above would be useless for us.  If we were to use this method at my University we would have to import the student number when we created our students (probably in the field “student_id” so the SQL above would be changed to pull u.student_id instead of u.user_id)

Course or Unit identifier

Finally the identifier of your Blackboard course might be different to the identifier in your SIS.  Now you are probably thinking that no-one would be that stupid, but I am quite good with Blackboard by now and…… at my university we have course codes like DRAMM1001 and we have been having a new version of the course each year so on Blackboard the courses starting in September will have codes like DRAMM1001_2014.  Fortunately this is easy to fix so CS.COURSE_ID, will become Left(CS.COURSE_ID, 9),

Conclusion

Its not perfect and it will take some time and care to move your grades, but its much quicker than getting admin staff to enter them all or to download individual course grade centres and try to upload them.  It would also make a good “Proof of concept” project, get a couple of hundred courses to try it and show how it moves the grades across with a little bit of effort. Contrast that with the amount of time it takes for courses that do it by hand, then say why don’t we invest some time and money in doing it properly.

 

Advertisements

One thought on “Transferring grades from Blackboard your student information system – a semi automated process

  1. simon.starr@canterbury.ac.uk

    Hey Joseph,

    I was asking about this on the mailing list. It’s really useful thanks. We’re looking at custom reporting using a database querying tool (BIRT) which we understand can package the query as a building block for regular Blackboard instructors to use. Our idea at the moment is to pilot a one-by-one grade centre download approach for individual upload to our SRS using a BIRT report to package the data in the right format. In the future we will likely look to cross-institutional queries such as yours, however either way this info will help us.

    Thanks again.

    Si.

    Simon Starr
    Faculty Learning Technologist (Cross-Faculty)
    Learning & Teaching Enhancement Unit, Canterbury Christ Church University
    Office: +44 (0)1227 767700 ext 2018
    E-mail: simon.starr@canterbury.ac.uk
    Web: http://www.canterbury.ac.uk/LTEU

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s