Basic Data Reporting with Blackboard

´╗┐The built in reporting tools available for Blackboard are somewhat limited. To answer specific reporting questions, you may need to run custom SQL queries against the database. What follows are a few rough examples of such queries.

All of the below examples are intended to outline the interactions you might need to answer specific reporting needs. It is important to note that these queries are in no way optimized, and could quite possibly put excess load on your system. Further, some of these are not actually tested (i.e. they are pulled or adapted from memory), and are only included as guides to help you get going in the right direction.

These queries are focused on Blackboard Learning System 8.x, however they should run just as well on 9.x (Blackboard Learn) releases.

One of the broader questions that you may be trying to ask is, how are people using the system? Since there are any number of contexts in which that question can be answered, we’ll start by breaking it down into more manageable chunks.

Aggregate Tool usage

A broad picture of the tools being employed across the application can give hints about how instructors are using the system to actually teach. For example, we might start by asking how many instructors are using tools like discussion boards and assessments in their courses.

There are no built in reporting methods with which to view specific tool usage in Blackboard. Looking at the database, we are left with some detective work to determine which tables and fields will be most useful. Like all detective work, there is some guessing involved. One good guess is that some of the data we are looking for can be found in the ACTIVITY_ACCUMULATOR table.

We also might be able to guess that the column within the ACTIVITY_ACCUMULATOR named internal_handle has a list of tool identifiers available on the system. From there, we can start building a query to, say, count the most accessed tool pages as listed in the accumulator, grouped by tool and sorted by page view.

SELECT COUNT(*) AS page_views, activity_accumulator.internal_handle
FROM activity_accumulator
WHERE activity_accumulator.event_type = 'COURSE_ACCESS'
GROUP BY activity_accumulator.internal_handle
ORDER BY COUNT(*) DESC

This gives us an idea of the tools available for further inspection, as well as which ones we might want to take a closer look at.

Tool usage by course

Now that we have the tools and their identifiers, we can use the ACTIVITY_ACCUMULATOR table to begin piecing together answers for a more specific set of questions, such as: Which courses are using which tools the most? Are some departments using certain tools more than others? Can we contact instructors that are using specific tools (i.e. in order to get feedback and offer further training)?

Say we wanted an idea of how many instructors were using assessments in their course. If we assume that the cp_test_manager is the handle for the Test Manager in the Control Panel, then by looking at how often the instructor views that tool’s page we can surmise a rough usage pattern. In other words, courses with a significant number of hits to that tool can be assumed to be using said tool in some fashion.

SELECT COUNT(*) AS page_views, course_main.course_name, course_main.course_id
FROM activity_accumulator, course_main
WHERE activity_accumulator.course_pk1 = course_main.pk1 
AND activity_accumulator.internal_handle = 'discussion_board' 
GROUP BY course_main.course_name, course_main.course_id 
ORDER BY page_views DESC;

This can be done with other tools, such as the discussion board, to make educated guesses about its use. Note that since the “cp_” designation appears to indicate a control panel page, hits to the discussion_board page should be assumed to be by all users in the course, and not limited to the instructor in the case of the test manager.

Tool deployment

Learning Objects Campus Pack LX products don’t show up in Blackboard’s internal tool handle tables in the same way as native tools. However, you can get an idea of the usage of these tools by looking at which courses have them installed. Running queries against the CONTENT_HANDLER table will let you see how many blogs or wiki’s have been installed a course.

SELECT course_main.pk1, course_main.course_name, 
course_main.course_id, course_contens.pk1, course_contents.title 
WHERE course_contents.crsmain_pk1 = course_main.pk1 
AND course_contents.cnthndlr_handle = 'resource/x-lobj-journal';

While this may not paint as clear a picture of usage as page view counts, it will still allow you to make general assumptions about pedagogical impacts.

Enrollment and Participation

You can also get an approximate idea of enrollment in a course using data in the activity accumulator. This can be done by joining the acumulator on the course_users and course_main tables. Querying the course user’s role and their last access is often a better indicator of recent activity in a course than the built in “active user” numbers generated by the GUI reporting tools. This is especially true if you are only looking for activity in a course within a specific timeframe, such as a term/semester.

Course usage by term can also be determined by querying on the start date fields if they are populated. In this way you can identify not only course usage in general, but also student vs. faculty usage by term.

If you don’t have start date fields populated in your tables, you may need to query off of other fields. If your course id’s contain term data, for example, you might be able to do something along the lines of “LIKE ‘200809%'”.

Other Usage Metrics and Tools

The administrator manual documents some of the feilds and tables above, but a full schema documentation does not appear to be available at this time. I’ve been told that version 10.x will ship with a fully documented (and public?) schema, but that’s probably more rumor than fact at this point.

How do you aggregate usage on your system? Are there specific tools you use in your reporting process (i.e. Crystal Reports, SAS)? Are you using GUI-side tools like the nascent Project ASTRO? Are there open source tools that you are using? Do you have special queries you run to address certain questions?

12 Comments